Clarification on pase code Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Clarification on pase code

  1. #1
    Join Date
    Oct 2001
    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


    Oracle DBA
    National Bank Of Kuwait

  2. #2
    Join Date
    Mar 2002
    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.

  3. #3
    Join Date
    Oct 2004
    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"

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