ORA-01036: illegal variable name/number

From: CJM <cjmnews04_at_REMOVEMEyahoo.co.uk>
Date: Wed, 6 Sep 2006 17:28:43 +0100
Message-ID: <4m8b9rF4onb7U1@individual.net>

I'm trying to call a package/procedure in oracle (from an ASP page) which requires a number of parameters. I have got this working using OO40 but unfortunately the transaction rollback function doesnt seem to do much.

So I'm now trying to use ADO instead (in the hope that ADO transactions will work), however I'm getting the above error. My initial searches havent turned up any suitable suggestions - there appear to be many reasons why this error might occur - none of which I have found so far fit my situation.

Can anyone suggest where I am going wrong? Better still, can anyone post a working code snippet that I can use as a model? I already have another [very similar] function which calls a different package/procedure which works fine.

Thanks in advance...

CJM Code Snippets:

  PROCEDURE AddSerialToHistory2(sSerialNo in varchar2, sPartNo in varchar2, sSequenceNo in number,

    sShopOrderNo in varchar2, sLineNo in varchar2, sRelNo in varchar2, sSuperiorSerialNo in varchar2,

    sSuperiorPartNo in varchar2, sOrderType in varchar2, sHistoryPurpose in varchar2, sCurrentPosition in varchar2,

    iResult Out number)
  IS

      sDesc Varchar2(100) := 'Received into stock against Shop Order ' || sShopOrderNo || ', ' || sLineNo || ', ' || sRelNo;

      dtDate Date := CURRENT_DATE;
  Begin
    Insert Into IFSAPP.PART_SERIAL_HISTORY_TAB       (Part_No, Serial_No, Sequence_No, Order_No, Line_No, Release_No,

Transaction_Date,
      RowVersion, Transaction_Description, Order_Type, History_Purpose, 
Current_Position, User_Created,
      Part_Ownership)
    Values
      (sPartNo, sSerialNo, sSequenceNo, sShopOrderNo, sLineNo, sRelNo, 
dtDate, dtDate, sDesc,
      sOrderType, sHistoryPurpose, sCurrentPosition, 'IFSAPP', 'COMPANY 

OWNED');     If SQL%ROWCOUNT = 1 Then

      iResult := 0;
      --Commit;
    Else
      iResult := 1;
      --Rollback;

    End If;
  END AddSerialToHistory2;

Function AddHistory (sSerialNo, sPartNo, sShopOrderNo, sLineNo, sRelNo, sSuperiorSerialNo, sSuperiorPartNo)

 Dim iResult2

 Dim oParam
 With oCmd
.CommandType=adCmdText

  Set oParam = .CreateParameter("sSerialNo", adVarchar, adParamInput, 50, sSerialNo)
.Parameters.Append oParam

  Set oParam = .CreateParameter("sPartNo", adVarchar, adParamInput, 50, sPartNo)
.Parameters.Append oParam

  'Set oParam = .CreateParameter("sSequenceNo", adSmallInt, adParamInput, 50, 1)
  '.Parameters.Append oParam

  Set oParam = .CreateParameter("sShopOrderNo", adVarchar, adParamInput, 50, sShopOrderNo)
.Parameters.Append oParam

  Set oParam = .CreateParameter("sLineNo", adVarchar, adParamInput, 50, sLineNo)
.Parameters.Append oParam

  Set oParam = .CreateParameter("sRelNo", adVarchar, adParamInput, 50, sRelNo)
.Parameters.Append oParam

  Set oParam = .CreateParameter("sSuperiorSerialNo", adVarchar, adParamInput, 50, sSuperiorSerialNo)
.Parameters.Append oParam

  Set oParam = .CreateParameter("sSuperiorPartNo", adVarchar, adParamInput, 50, sSuperiorPartNo)
.Parameters.Append oParam

  'Set oParam = .CreateParameter("sHistoryPurpose", adVarchar, adParamInput, 50, "INFO")
  '.Parameters.Append oParam

  'Set oParam = .CreateParameter("sCurrentPosition", adVarchar, adParamInput, 50, "InInventory")
  '.Parameters.Append oParam

  Set oParam = .CreateParameter("iResult", adDecimal, adParamReturnValue)
.Parameters.Append oParam

  'Insert row into SNE (Shop Order)
.CommandText="{Call ADDROWS.AddSerialToCatalog(?, ?, 1, ?, ?, ?, ?, ?,
'INFO', 'InInventory', ?)}"
.Execute() '<======= this is where
the error occurs

  AddHistory = .Parameters("iResult").Value

.Parameters.Delete "sSerialNo"
.Parameters.Delete "sPartNo"
.Parameters.Delete "sSequenceNo"
.Parameters.Delete "sShopOrderNo"
.Parameters.Delete "sLineNo"
.Parameters.Delete "sRelNo"
.Parameters.Delete "sSuperiorSerialNo"
.Parameters.Delete "sSuperiorPartNo"
.Parameters.Delete "sHistoryPurpose"
.Parameters.Delete "sCurrentPosition"
.Parameters.Delete "iResult"

 End With

End Function Received on Wed Sep 06 2006 - 11:28:43 CDT