Trouble accessing a Oracle Procedure

[vvenk]

I wrote a test procedure on Oracle that returns a string and a value.

CREATE OR REPLACE PROCEDURE EBMS.p_CSV_Upload
(
P_ERROR OUT VARCHAR2,
P_ERROR_NO OUT Number 
)
AS
 
BEGIN
P_ERROR := 'Test Successful';
P_ERROR_NO := '1';
END;

I then tried to execute the SP from VB.Net. Here's the code snippet:

Dim queryString As String = "p_CSV_Upload"
 
Using connection As New OracleConnection(myConnectionString)
Dim command As New OracleCommand(queryString)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("P_ERROR", OracleType.VarChar).Direction 
= ParameterDirection.Output
command.Parameters.Add("P_ERROR_NO", OracleType.Int32).Direction 
= ParameterDirection.Output
command.Connection = connection
Try
connection.Open()
 
Dim reader As OracleDataReader = command.ExecuteReader
MsgBox((reader.GetString(0)))
MsgBox((reader.GetInt32(1)))
 
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

But the line, MsgBox, throws an exception,

No data exists for the row or column.

Any idea?

Thanks.

venki

 

[vvenk]

Figured out

I changed the code to :

connection.Open()
command.ExecuteNonQuery()
MessageBox.Show(command.Parameters("P_Error").Value)
MessageBox.Show(command.Parameters("P_Error_No").Value.ToString)

Live and learn!

Who said programming is dull?

Venki

 

[cjard]

Note that your procedure doesnt "return" any values - functions return values, procedures simply alter the values of what is passed in; they run and leave things in a changed state (a bit like a natural disaster )

When using in/out/inout parameters, be sure to set the directions correctly. When using a function, be sure to set a parameter to direction ReturnValue

When running any queries:
If you are executing a query that returns no rows, such as insert, update, delete, create, alter etc or functions.procedures, use ExecuteNonQuery

When your select statement returns a single value (1x1 result set) you can use executescalar

Otherwise, use executereader