Oracle.DataAccess.Client.OracleException: ORA-00900: invalid SQL statement

Question

I have created a Oracle SP with following code. If I execute it from SQL developer then it runs without any error/issue. However, when I try to call the same SP from .NET code, an exception is getting thrown with the message "Oracle.DataAccess.Client.OracleException: ORA-00900: invalid SQL statement".

Oracle SP code:

PROCEDURE sp_UpdateLSR(
    po_Info        OUT VARCHAR2,
    pi_gId         IN VARCHAR2,
    pi_cid         IN VARCHAR2
 ) IS pragma autonomous_transaction;
BEGIN

    UPDATE TableName
    SET colName = 6603
    WHERE colName21 = pi_gId AND COL2 = pi_cid;
    COMMIT;
    
    OPEN po_Info FOR SELECT pi_cid as MSG FROM dual;

EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
   common.sp_InsertErrorLog('sp_Name', SQLCODE, SQLERRM); -- nothing gets logged
 END;

my VB.NET code:

Public Function SetValues(ByVal gid As String, ByVal CId As String) As Boolean

     Dim connection As OracleConnection = New OracleConnection(GetConnectionString())
     Dim command As OracleCommand = connection.CreateCommand()
     Dim parameters As OracleParameter = New OracleParameter

     Dim dataAdapter As New OracleDataAdapter(command)
     Dim rtnMsg As String
     Dim ds As New DataSet

            Try
                connection.Open()
                command.Parameters.Add("parameters", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output)
                command.Parameters.Add("pi_gId", OracleDbType.Varchar2, gid, ParameterDirection.Input)
                command.Parameters.Add("pi_cid", OracleDbType.Varchar2, CId, ParameterDirection.Input)
                

                command.CommandText = "sp_UpdateLSR"

                dataAdapter.Fill(ds)
                If ds.Tables(0).Rows.Count > 0 Then
                    rtnMsg = ds.Tables(0).Rows(0)(0).ToString()
                End If
                SetLIMSValues = rtnMsg.ToLower() = CharId.ToLower()
            Catch ex As Exception
                Throw New Exception("error", ex)
            Finally
                command.Dispose()
                parameters.Dispose()
                If Data.ConnectionState.Open Then
                    connection.Close()    'close the connection
                End If
            End Try

        End Function

Any idea?

Thank you.

Why do you return a RefCursor for a single static value? Why do you return the input value pi_cid? The exception handler is also useless. If the UPDATE fails then it will be rollback anyway. If the errors appear somewhere else then the rollback is useless, because you issued already the COMMIT. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jan 11, 2021 at 22:03
For a single return value I prefer to use a FUNCTION rather than a procedure. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jan 11, 2021 at 22:04

JayV · Accepted Answer · 2021-01-11 20:10:30Z

1

As you are executing a Stored Procedure against the Database, you need to tell the Command object that, the default is CommandText

Add the line:

command.CommandType = CommandType.StoredProcedure

Before or after you set the CommandText - just a convenient place to remember and close by.

answered Jan 11, 2021 at 20:10

JayV

3,2712 gold badges11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TechTurtle Over a year ago

Thank you JayV. [ Not sure how I missed it, I really don't know what to say :) ]

Collectives™ on Stack Overflow

Oracle.DataAccess.Client.OracleException: ORA-00900: invalid SQL statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related