CODEWITHSUNDEEP
vb.netoracle-database
TechTurtle
TechTurtle

Reputation: 3167

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

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.

Upvotes: 0

Views: 1402

Answers (1)

JayV
JayV

Reputation: 3271

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.

Upvotes: 1

Related Questions