Clarification on pase code

[gsmathew]

Hi all ,

select merchant_name , amount from transctions
where tran_date = '20020210' ;

select merchant_name , amount from transctions
where tran_date = '20020325' ;

1) Is these two statements identical or needs separate
parsing in the shared pool.

2) If these two statements are not identical what is a
real life scenario of two statements being identical and
shares the same parse execution plan ?

3) what is meant by using bind variables and making
statements identical. How is it done in the above case

Thanking in advance

Regards

George

[dbadoc61]

1. They are not identical. The date string makes them different. BOTH statements would have to be parsed and reside in the shared pool.

2 & 3. Using PL/SQL declare a bind variable ( read the doc if you don't know what a bind variable is). Your sql then should be executed something like ......where tran_date = :v_trandate ;
the : in front of v_trandate tells the parser that it is a bind variable and not a literal. The sql gets parsed only once and multiple copies of essentially the same sql don't reside in the shared pool making room for other sql.

[firestormISR]

little advice:if this app was hard coded
just add parameter in init.ora
cursor_sharing=similar.It will replace your literal with system-named bind variables like ":sys.b1"