work-around on PLS-00103 error


If a stored procedure has a default value parameter defined in Oracle (8i) backend and you want to call this stored procedure using ADO with MSDAORA provider, it may fail and return a error message for some scenarios, for example:


Stored Procedure:


##########################


CREATE OR REPLACE PROCEDURE SP001( InParam IN NUMBER DEFAULT 25, OutParam OUT NUMBER) AS 


BEGIN


OutParam := InParam;


END SP001;


##########################


Client side program:


##########################


Sub Main()


Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim vExpected As Variant
Dim vActual As Variant
Dim iInParamVal As Integer
Dim iOutParamVal As Integer


cn.Open "Provider=MSDAORA.1;Password=******;User ID=******;Data Source=ORA81EN"


On Error GoTo ERR_HANDLER


cmd.ActiveConnection = cn
cmd.CommandText = "SP001"
cmd.CommandType = adCmdStoredProc


' Setup input param
Set param = cmd.CreateParameter(stInParamName, adInteger, adParamInput, 4, Empty)
cmd.Parameters.Append param
       
' Setup output param
Set param = cmd.CreateParameter(stOutParamName, adInteger, adParamOutput, 4)
cmd.Parameters.Append param


cmd.Execute


vExpected = 25
vActual = cmd(0).Value


If vExpected <> vActual Then MsgBox "compare failed"


cn.Close
Exit Sub


ERR_HANDLER:
    MsgBox Err.Number & "  <->  " & Err.Description
    Resume Next
End Sub


##########################



After execute the client program, an error message returned:


-2147217900, ORA-06550: line **, column **:
PLS-00103: Encountered the symbol ">" when expecting one of the following:
   
. ( ) , * @ % & = - + < / > at in mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling


This case works fine with SQL Server backend or use MSDASQL-ORA driver with Oracle backend, but it fails with MSDAORA provider. Then how could we do if want to use the default value of the stored procedure in this scenario?


The solution (or work-around) is to re-define the stored procedure and transpose the input parameter and output parameter, just like this:


##########################


CREATE OR REPLACE PROCEDURE SP001(OutParam OUT NUMBER, InParam IN NUMBER DEFAULT 25) AS 


BEGIN


OutParam := InParam;


END SP001;


##########################


In client program, only append the output parameter and execute the COMMAND to call the stored procedure. Then the case will run successfully.


The reason I guess may be this:


In PL/SQL, if you want to call this stored procedure, the code should be like "SP001(OutParam => variable)", variable is used to store the output value. After we transpose the input and output parameter, the calling code could be like "SP001(variable)". I guess MSDAORA provider compose an invalid SQL clause. (maybe use "=" instead of "=>")

Skip to main content