well, you have the text of the sql statement already - it is right there in lv_select!
to see the binds, we can:
ops$tkyte%ORA11GR2> declare
2 l_x dual.dummy%type := 'a';
3 l_y dual.dummy%type := 'b';
4 lv_select long := 'select * from dual where dummy = :x or dummy = :y';
5 begin
6 open :x for lv_select using l_x, l_y;
7
8 for x in ( select bd.cursor_num, bd.position, bd.value
9 from v$sql_bind_data bd, v$sql_cursor c, v$sql s
10 where s.sql_text = lv_select
11 and s.address = c.parent_handle
12 and c.curno = bd.cursor_num )
13 loop
14 dbms_output.put_line( 'cnum = ' || x.cursor_num || ', position = ' || x.position || ', value = "' || x.value || '"' );
15 end loop;
16 end;
17 /
cnum = 3, position = 2, value = "b"
cnum = 3, position = 1, value = "a"
PL/SQL procedure successfully completed.
you can dump out all of the open sql using something like this:
ops$tkyte%ORA11GR2> create or replace procedure p( l_refcur in out sys_refcursor )
2 as
3 l_x dual.dummy%type := 'd';
4 l_y dual.dummy%type := 'e';
5 lv_select long := 'select * from dual where dummy = :x or dummy = :y';
6
7 l_curno number;
8 begin
9 open l_refcur for lv_select using l_x, l_y;
10
11 for x in ( select s.sql_text, bd.cursor_num, bd.position, bd.value, row_number() over (partition by bd.cursor_num order by bd.position) rn,
12 case when s.program_id <> 0 then (select object_name from dba_objects where object_id = s.program_id)
13 else 'anonymous'
14 end pname, s.program_line#
15 from v$sql_bind_data bd, v$sql_cursor c, v$sql s
16 where s.address = c.parent_handle
17 and c.curno = bd.cursor_num )
18 loop
19 if ( x.rn = 1 )
20 then
21 dbms_output.put_line( rpad( '-', 100, '-' ) );
22 dbms_output.put_line( x.sql_text );
23 dbms_output.put_line( x.pname || ',' || x.program_line# );
24 end if;
25 dbms_output.put_line( 'cnum = ' || x.cursor_num || ', position = ' || x.position || ', value = "' || x.value || '"' );
26 end loop;
27 end;
28 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec p(:x)
-------------------------------------------------------------------------------
select * from dual where dummy = :x or dummy = :y
anonymous,5
cnum = 3, position = 1, value = "d"
cnum = 3, position = 2, value = "e"
-------------------------------------------------------------------------------
SELECT S.SQL_TEXT, BD.CURSOR_NUM, BD.POSITION, BD.VALUE, ROW_NUMBER() OVER (PAR
P,11
cnum = 4, position = 0, value = ""
-------------------------------------------------------------------------------
BEGIN p(:x); END;
anonymous,0
cnum = 6, position = 1, value = ""
PL/SQL procedure successfully completed.