ORA-01036: illegal variable name/number

Discussion in 'ASP General' started by CJM, Sep 6, 2006.

  1. CJM

    CJM Guest

    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
     
    CJM, Sep 6, 2006
    #1
    1. Advertising

  2. CJM

    CJM Guest

    "Steve Howard" <> wrote in message
    news:...
    >
    >
    > I also noticed the procedure you are calling is not the one for which
    > you have provided the definition. Are you sure they are the same?
    >


    Sorry, I cut and paste the wrong function! It doesnt make much difference,
    since they are near identical.

    Update: I'n the meantime, I've re-created the package call from first
    principles, and it appears to be working. I'm not sure where the error lay,
    but clearly there was one in there some where.

    What is more, having moved away from OO4O to ADO, I now have transactions
    working! Hurray to Microsoft! I'm not sure why transactiosn didnt work in
    OO4O but it's academic now.

    Thanks for your help Steve.

    CJM
     
    CJM, Sep 7, 2006
    #2
    1. Advertising

  3. CJM

    CJM Guest

    "DA Morgan" <> wrote in message
    news:...
    > CJM wrote:
    >> 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.

    >
    > Doesn't seem to do much ... means precisely what?
    >


    Unfortunately it means exactly what it says... I issued transactional
    commands against a database object which neither returned an error or did
    anything useful. Specifically, I couldnt rollback series of transactions; se
    my earlier thread 'OO4O Transactions: Updates not being rolled=back'.

    >> So I'm now trying to use ADO instead (in the hope that ADO transactions
    >> will work), however I'm getting the above error.

    >
    > And that error, presumably, came with a line number. Use it to track
    > down the offending line.


    The offending line was marked (perhaps not clearly enough) in my code
    snippet. Not surprisingly, it was at the point were the oracle package was
    called.

    Anyway, I've finally got it all working, and thankfully I have transactional
    control through ADO (see my reply to Steve).

    Thanks for your efforts anyway.

    CJM
     
    CJM, Sep 7, 2006
    #3
  4. CJM

    CJM Guest

    "Steve Howard" <> wrote in message
    news:...
    >
    > It would make all the difference, actually, if they are only "near"
    > identical.
    >


    Ok, obviously it would make a difference. What I was trying to say (but
    glossing-over) was that the error was not with the general approach, because
    the other similar routines work fine. It was with the specifics of the this
    example, where I had presumably mis-typed something or had made a
    syntactical mistake. When I recreated the code from scratch (using the same
    knowledge and approach) it worked.

    Going by your example (thanks, btw), I can only assume that the problem was
    a similar mismatch between the parameters object and the procedure call.

    Thanks

    Chris
     
    CJM, Sep 7, 2006
    #4
  5. CJM

    CJM Guest

    "DA Morgan" <> wrote in message
    news:...
    > CJM wrote:
    >
    > Perhaps your driver, being Microsoft technology, is autocommitting so
    > there is nothing to roll back.
    >


    Initially, I was using OO4O which is a middleware layer from Oracle. Of
    course, this will autocommit be default so there would be nothing to
    rollback.

    But I set the database object's Autocommit property to false and explicitly
    started and rolled-back the transactions to no avail...

    > Try this simple test.
    >
    > INSERT ONE ROW
    > ROLLBACK;
    > COMMIT;
    >
    > Is the row committed or rolled back?
    > --


    I tried something similar along the lines of:

    AutoCommit = false
    DB.BeginTrans
    Insert Single Row
    Rollback

    Although OO4O never objected to the Rollback (ie no errors), it simply didnt
    work - the rows were inserted anyway.


    I moved across to ADO, and apart from the expected re-coding (although both
    ADO and OO4O are very similar in approach), I had no problems at all.
    Unfortunately, I'm using the MS ODBC For Oracle drivers, which have a
    chequered history, so I would like to 'upgrade' to a better solution; either
    a MS OLEDB driver or ideally the Oracle OLEDB driver which is included in
    the same package as OO4O. Unfortunately, it doesnt seemt to have installed
    correctly - at least, I can't see any Oracle OLEDB drivers shown in the ODBC
    Administrator (the instant client and full client are listed though).

    CJM
     
    CJM, Sep 7, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Damjan

    a cx_Oracle ORA-01036 problem

    Damjan, May 5, 2005, in forum: Python
    Replies:
    4
    Views:
    2,426
    Damjan
    May 12, 2005
  2. Replies:
    0
    Views:
    970
  3. Bobby Chamness
    Replies:
    2
    Views:
    2,401
    Joe Smith
    Apr 22, 2007
  4. Mauricio Martinez Garcia
    Replies:
    0
    Views:
    659
    Mauricio Martinez Garcia
    Jan 4, 2011
  5. Ian Kelly
    Replies:
    0
    Views:
    773
    Ian Kelly
    Jan 4, 2011
Loading...

Share This Page