my stored procedure uses dynamic sql. After the select statement, I have an execute immediate and using clause and then a dbms_output.print_line which displays the sql statement. It displays the sql statement with the bind variables still as variables instead of the actual values. Is there any way to display the sql statement with the values for the bind variables instead of the variables so that I could copy and paste the query produced and run it to make sure it's correct?
I probably didn't understand the question ... though, is this what you are talking about? Displaying value of a "l_str" which equals "select count(*) from emp where deptno = 10", can be copied and executed in SQL*Plus (for example)?
SQL> get p.sql
2 l_str varchar2(2000);
3 l_dept number;
4 l_count number;
6 select min(deptno) into l_dept
7 from dept;
8 l_str := 'select count(*) from emp where deptno = ' || l_dept;
10 execute immediate (l_str) into l_count;
11 dbms_output.put_line('There are ' || l_count || ' employees there');
select count(*) from emp where deptno = 10
There are 3 employees there
PL/SQL procedure successfully completed.