Re: ORA-01036: illegal variable name/number

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 7 Sep 2006 06:58:24 -0700
Message-ID: <1157637504.340063.218110@h48g2000cwc.googlegroups.com>

CJM wrote:

> "Steve Howard" <stevedhoward_at_gmail.com> wrote in message
> Sorry, I cut and paste the wrong function! It doesnt make much difference,
> since they are near identical.
> CJM

It would make all the difference, actually, if they are only "near" identical.

Look at the following...

/**********************************************************************************************

...create a simple procedure that receives two arguments and returns one as an OUT parameter to the caller...

SQL> CREATE OR REPLACE PROCEDURE ADDSERIALTOHISTORY2 (p1 in number,

  2                                                   p2 in number,
  3                                                   iResult Out

number) IS
  4 BEGIN
  5 iResult := p1 * p2;
  6 END;
  7 /

Procedure created.

...and then run a simple vbs script that executes this procedure and echoes the results to the screen...

SQL> $type oracle_stored_proc.vbs
Set rs = CreateObject("ADODB.Recordset")

Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") strsid = "test10g"
strUser = "rep"
strpassword = "rep"

con.Open "Provider=MSDAORA;" _


       & "Password=rep;" _

       & "User ID=rep;" _

       & "Data Source=test10g;" _

       & "Persist Security Info=True"


Set ocmd = CreateObject("ADODB.Command")

ocmd.ActiveConnection = con
ocmd.CommandType = 4
ocmd.CommandText = "AddSerialToHistory2"
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 1)
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 1)

ocmd.Parameters.Append ocmd.CreateParameter(, 3, 2) ocmd.Parameters.Append ocmd.CreateParameter(, 4, 2)

ocmd(0) = 2
ocmd(1) = 2
ocmd.Execute

wscript.echo ocmd(2)

SQL> $cscript oracle_stored_proc.vbs
Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

...and then add a parameter to the command object (uncomment the fourth one in the example above), for which there is not a corresponding parameter in the oracle procedure definition...

SQL> $type oracle_stored_proc.vbs
Set rs = CreateObject("ADODB.Recordset")

Set con = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") strsid = "test10g"
strUser = "rep"
strpassword = "rep"

con.Open "Provider=MSDAORA;" _


       & "Password=rep;" _

       & "User ID=rep;" _

       & "Data Source=test10g;" _

       & "Persist Security Info=True"


Set ocmd = CreateObject("ADODB.Command")

ocmd.ActiveConnection = con
ocmd.CommandType = 4
ocmd.CommandText = "AddSerialToHistory2"
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 1)
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 1)

ocmd.Parameters.Append ocmd.CreateParameter(, 3, 2) ocmd.Parameters.Append ocmd.CreateParameter(, 4, 2)

ocmd(0) = 2
ocmd(1) = 2
ocmd.Execute

wscript.echo ocmd(2)

...and voila, instant exception your script threw...

C:\SCRIPTS\vbs\oracle_stored_proc.vbs(27, 1) Microsoft OLE DB Provider for Oracl
e: ORA-01036: illegal variable name/number

SQL>

The moral of the story is, if you ever get that error again, count your parameters, in both the caller and the oracle procedure.

Regards,

Steve Received on Thu Sep 07 2006 - 08:58:24 CDT