Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10

    Question Unanswered: display output of dynamic sql

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Bozo34 View Post
    Is there any way to display the sql statement with the values for the bind variables instead of the variables
    No.
    You will need you replace the placeholders with the actual values yourself.

  3. #3
    Join Date
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10
    Oh ok. Thanks for the info

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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)?
    Code:
    SQL> get p.sql
      1  declare
      2    l_str   varchar2(2000);
      3    l_dept  number;
      4    l_count number;
      5  begin
      6    select min(deptno) into l_dept
      7      from dept;
      8    l_str := 'select count(*) from emp where deptno = ' || l_dept;
      9    dbms_output.put_line(l_str);
     10    execute immediate (l_str) into l_count;
     11    dbms_output.put_line('There are ' || l_count || ' employees there');
     12* end;
     13  /
    select count(*) from emp where deptno = 10
    There are 3 employees there
    
    PL/SQL procedure successfully completed.
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •