bind variables

[sonaliak]

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

[marist89]

EXECUTE IMMEDIATE is an 8i thingy.

[sonaliak]

Does that mean I cannot use bind variables ?
If I can what would my insert statement look like ?

Thanks
Sonali

[Heath]

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