Select dname into v from dept where DEPTNO = :x You have now run through query optimization (a cpu intensive task) 1,000 more times - using excessive CPU (1000 times MORE cpu then you needed to use). Select * from emp where ename = 'AFDAFDFSDAS'
Ok, instead of hard parsing "select * from emp where ename = :x" once and then soft parsing it 1,000 times (thus SKIPPING the optimization step 1,000 times) you instead submit 1001 queries like: All plsql variable references will be bound and any literals in there are OK cause the query will be reused over and over again. It is when people build queries on the fly (using EXECUTE IMMEDIATE or DBMS_SQL in plsql) that you want to take care to BIND values.Īnytime you have STATIC sql in PLSQL - your job is done. You need not bind that particular one - if you execute it OVER and OVER and OVER. If executed over and over and over - is just as shareable as: Sorry Tom, I might be sounding a bit frustrated but believe me Iam. "It is when you are dynamically building a query - putting hard coded literals in there - that you MUST bind." Give us the link for an example for the statement Out of all the consequences of not using bind variables, how will the below 2 happen, please explainġ.long lines to get into the library cache due to excessive latching caused by hard parses,Ģ.excessive CPU used to optimize queries( why in this world do we have to use excessive CPU to optimize queries). HOW IS THE QUERY SHAREABLE TO EERY ONE THAT RUNS THE PROCEDURE. No need to bind in this particular case.Įvery time the sql given in the procedure executes doesnt it have to be parsed, as a hard coded variable been used in the where clause. This query is shareable - everyone that runs the procedure, runs the "With this query - the value that is hard coded is also IMMUTABLE, it'll never change. How do I write this code to use DBMS_SQL package ?Ĭan you go into the specifics or inother words can you dig further into your statement I am reading Experts one on one by Tom Kyte and was trying this.Īlso, if my stored procedure is already compiled then how does a bind variable used inside procedure going to make a difference as far as parcing goes?Īlso if I have to do this on Oracle 8.0.6, I will need to use MGR-00072: Warning: PROCEDURE PR_SONALI created with compilation errors.ġ12/22 PLS-00103: Encountered the symbol "insert into Auth (AUTH_ID, Auth_ Using inResID, inWorkID, inRoleID, dtStartDate, dtEndDate, inRestricted, inTimecard, inAmount I have a part of the code here from a test stored procedure-Įxecute immediate 'insert into Auth (AUTH_ID, Auth_Res_ID, Auth_Work_ID, Auth_Role_ID, Auth_Status,Īuth_Start_Date, Auth_Fin_Date, Auth_Restricted, Auth_Timecard,Īuth_Secondary_Status, Auth_Amount, Auth_Rem_Amount, Auth_Schedule) I am trying to use bind variables for the 1st time.
0 kommentar(er)
