Trouble executing a stored procedure

[Guest]

Hello:

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
End Using

When I execute, the line, "Dim reader As OracleDataReader =
command.ExecuteReader()" raises an exception, "{"Parameter 'P_ERROR': No size
set for variable length data type: String."}

What am I doing wrong?

 

[Guest]

Hello:

I solved it partially. I added the length of the string to the parameter:

command.Parameters.Add("P_ERROR", OracleType.VarChar,
100).Direction = ParameterDirection.Output

I also changed the syntax using DataReader:

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?

venki

 

[Guest]

vvenk,

reader.Read

Kerry Moorman

Hello:

I solved it partially. I added the length of the string to the parameter:

command.Parameters.Add("P_ERROR", OracleType.VarChar,
100).Direction = ParameterDirection.Output

I also changed the syntax using DataReader:

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?

venki