How to simulate SQL execution with application Bind Variable

When the SQLs are executed from the application, it will appear with Bind variables such as :1, :2, :B1, :B2 etc.)
And sometime, you want to test the performance of the SQL in your session (Not from application). Unless, you run the exact same SQL and with the same bind variables, Oracle will treat it as a new SQL and will generate different plan.

Here is a sample script to test SQLs with aplication Bind variable so that Oracle will treat as the same SQL ID and SQL Text. (Our sample SQL Id has 2 Bind Variables; :1 for acct nbr and :2 for acct Code. (This SQL selects only two columns)


DECLARE
 v1 varchar2(100);
 v2 varchar2(100);
 v_sql   varchar2(32767);
 v_cd VARCHAR2(8) := '8PMD';
BEGIN


 SELECT sql_text into v_sql from dba_hist_sqltext where sql_id='ghdgm4dampwd5' and rownum=1;

 FOR acct_rec IN (Select acctnbr from acct where acctcd=v_cd) 
 LOOP 
 BEGIN
  execute immediate v_sql into v1, v2 using acct_rec.acctnbr, v_cd ;

  EXCEPTION 
 WHEN NO_DATA_FOUND THEN
 dbms_output.put_line('Acct_nbr :'||acct_rec.acctnbr); 
END;

END LOOP;

END;
/