Trouble executing a stored procedure

11 Oct 2006 6:15 PM

vvenk

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?

11 Oct 2006 6:29 PM

vvenk

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

Show quoteHide quote

"vvenk" wrote:

> 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?
>

11 Oct 2006 7:04 PM

Kerry Moorman

vvenk,

reader.Read

Kerry Moorman

Show quoteHide quote

"vvenk" wrote:

> 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
>
> "vvenk" wrote:
>
> > 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?
> >