Trouble executing a stored procedure

[vvenk]

Hello:

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

CREATE OR REPLACE PROCEDURE EBMS.p_CSV_Uplo ad
(
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_Uplo ad"

Using connection As New OracleConnectio n(myConnectionS tring)
Dim command As New OracleCommand(q ueryString)
command.Command Type = CommandType.Sto redProcedure
command.Paramet ers.Add("P_ERRO R", OracleType.VarC har).Direction
= ParameterDirect ion.Output
command.Paramet ers.Add("P_ERRO R_NO", OracleType.Int3 2).Direction
= ParameterDirect ion.Output
command.Connect ion = connection
Try
connection.Open ()
Dim reader As OracleDataReade r = command.Execute Reader()
MsgBox((reader. GetString(0)))
MsgBox((reader. GetInt32(1)))

Catch ex As Exception
Console.WriteLi ne(ex.Message)
End Try
End Using

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

What am I doing wrong?

[vvenk]

Hello:

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

command.Paramet ers.Add("P_ERRO R", OracleType.VarC har,
100).Direction = ParameterDirect ion.Output

I also changed the syntax using DataReader:

Try
connection.Open ()

Dim reader As OracleDataReade r = command.Execute Reader
MsgBox((reader. GetString(0)))
MsgBox((reader. GetInt32(1)))

Catch ex As Exception
Console.WriteLi ne(ex.Message)
End Try

But the line, MsgBox, throws an exception, " {"No data exists for the row or
column."}

Any idea?

venki

"vvenk" wrote:

Hello:

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

CREATE OR REPLACE PROCEDURE EBMS.p_CSV_Uplo ad
(
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_Uplo ad"

Using connection As New OracleConnectio n(myConnectionS tring)
Dim command As New OracleCommand(q ueryString)
command.Command Type = CommandType.Sto redProcedure
command.Paramet ers.Add("P_ERRO R", OracleType.VarC har).Direction
= ParameterDirect ion.Output
command.Paramet ers.Add("P_ERRO R_NO", OracleType.Int3 2).Direction
= ParameterDirect ion.Output
command.Connect ion = connection
Try
connection.Open ()
Dim reader As OracleDataReade r = command.Execute Reader()
MsgBox((reader. GetString(0)))
MsgBox((reader. GetInt32(1)))

Catch ex As Exception
Console.WriteLi ne(ex.Message)
End Try
End Using

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

What am I doing wrong?

[Kerry Moorman]

vvenk,

reader.Read

Kerry Moorman
"vvenk" wrote:

Hello:

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

command.Paramet ers.Add("P_ERRO R", OracleType.VarC har,
100).Direction = ParameterDirect ion.Output

I also changed the syntax using DataReader:

Try
connection.Open ()

Dim reader As OracleDataReade r = command.Execute Reader
MsgBox((reader. GetString(0)))
MsgBox((reader. GetInt32(1)))

Catch ex As Exception
Console.WriteLi ne(ex.Message)
End Try

But the line, MsgBox, throws an exception, " {"No data exists for the row or
column."}

Any idea?

venki

"vvenk" wrote:

Hello:

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

CREATE OR REPLACE PROCEDURE EBMS.p_CSV_Uplo ad
(
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_Uplo ad"

Using connection As New OracleConnectio n(myConnectionS tring)
Dim command As New OracleCommand(q ueryString)
command.Command Type = CommandType.Sto redProcedure
command.Paramet ers.Add("P_ERRO R", OracleType.VarC har).Direction
= ParameterDirect ion.Output
command.Paramet ers.Add("P_ERRO R_NO", OracleType.Int3 2).Direction
= ParameterDirect ion.Output
command.Connect ion = connection
Try
connection.Open ()
Dim reader As OracleDataReade r = command.Execute Reader()
MsgBox((reader. GetString(0)))
MsgBox((reader. GetInt32(1)))

Catch ex As Exception
Console.WriteLi ne(ex.Message)
End Try
End Using

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

What am I doing wrong?