SQL & PL/SQL

Hi,

Is there way get bind variables name from SQL string or cursor?

Example

DECLARE
  l_sql VARCHAR2(2000);
  desctab DBMS_SQL.DESC_TAB;
  curid   PLS_INTEGER;
BEGIN

  l_sql := 'SELECT * FROM emp WHERE mgr = :X and deptno = :Y';

  curid := dbms_sql.open_cursor;
  dbms_sql.parse(curid, l_sql, dbms_sql.NATIVE);
  ....
END;

What I mean with SQL string:
I like get using some function from above code variable l_sql all bind variables.
In this case function should return e.g array where is :X and :Y

Return bind variable names from cursor I mean same but instead string I pass cursor number.

Is there sucks ready function or can some share custom code for this purpose?

Thanks advance

Regards,
Jari

http://dbswh.webhop.net/dbswh/f?p=BLOG:HOME:0Regards,

Edited by: jarola on Dec 19, 2011 2:44 AM

I did find there is undocumented function wwv_flow_utilities.get_binds in APEX packages that do what I want.
Example usage

set serveroutput on
DECLARE
  binds DBMS_SQL.varchar2_table;
BEGIN
  binds := wwv_flow_utilities.get_binds('select :P1_TEST from dual');
  FOR i IN 1 .. binds.count
  LOOP
    dbms_output.put_line(binds(i));
  END LOOP;
END;
/

anonymous block completed
:P1_TEST

But I would not like use these undocumented functions as those might change or not exists in future APEX versions.
Is there any documented function or custom function that do same as wwv_flow_utilities.get_binds?