DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: bind variables

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I am trying to use bind variables for the 1st time.

    I have a part of the code here from a stored procedure-

    Declare
    x number;
    y number;
    z number;
    a date;
    b date;
    c number;
    d number;
    e number;

    execute immediate 'insert into Auth (AUTH_ID, Auth_Res_ID, Auth_Work_ID, Auth_Role_ID, Auth_Status,
    Auth_Start_Date, Auth_Fin_Date, Auth_Restricted, Auth_Timecard,
    Auth_Secondary_Status, Auth_Amount, Auth_Rem_Amount, Auth_Schedule)
    Values (mwebAuth_SEQ.NextVal, :x, :y, :z, 70, :a, :b , :c, :d, 40, :e, 0, 10)'
    Using inResID, inWorkID, inRoleID, dtStartDate, dtEndDate , inRestricted, inTimecard, inAmount;

    We are using Oracle 8. Can you use them in Oracle 8 like this?
    I keep getting error -

    MGR-00072: Warning: PROCEDURE PR_SONALI created with compilation errors.
    SQLWKS>
    SQLWKS> show errors
    Errors for PROCEDURE PR_SONALI:
    LINE/COL ERROR
    --------------------------------------------------------------------------------
    112/22 PLS-00103: Encountered the symbol "insert into Auth (AUTH_ID, Auth_


    What is wrong with my code. I am reading Experts one on one by Tom Kyte and was trying this.

    Also, if my stored procedure is already compiled then how does a bind variable use inside procedure going to make a difference as fr as parcing goes?

    Thanks for the help
    Sonali
    Sonali

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    EXECUTE IMMEDIATE is an 8i thingy.
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    Does that mean I cannot use bind variables ?
    If I can what would my insert statement look like ?

    Thanks
    Sonali
    Sonali

  4. #4
    Join Date
    Apr 2001
    Posts
    118
    It means that you can't use the EXECUTE IMMEDIATE command to use bind variables in dynamic SQL.

    You can still use them for dynamic SQL using the DBMS_SQL package in Oracle8.

    HTH,

    Heath

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width