Re: How to gather bind params in SQL?

From: Victor <mvici_at_yandex.ru>
Date: 10 Aug 2004 09:27:46 -0700
Message-ID: <2c447de6.0408100827.1dbf4f55@posting.google.com>

mvici_at_yandex.ru (Victor) wrote in message news:<2c447de6.0408090906.12466ac1_at_posting.google.com>...
> Oracle Server 9.2.0.5
> How can I gather param names (bind variables) in dynamic SQL (or PL/SQL)
> string?
> It seems that dbms_sql wouldn't help resolve the problem.
> Manual SQL string parsing is not acceptable...

ok, more tangible explanation...
in response to some posting in this thread (about sys views and lack in understanding of my question)

declare

	l_cur				number;
	p_query_text		varchar2(2000);
	l_param				varchar2(2000);
	i					number;

begin
--(1)

        p_query_text := 'select object_name from all_objects where object_name = :p_object_name';

        l_cur := dbms_sql.open_cursor;

--(2)

        dbms_sql.parse(l_cur, p_query_text, dbms_sql.native);

• here I wanna know all the names of bind variables defined in p_query_text
• in this sample it's 'p_object_name'
• then I could loop through all that names, get somehow their values and bind 'em for rec_in_param in ( select name from some_place ... ) loop dbms_sql.bind_variable(l_cur, rec_in_param.name, get_param_value(rec_in_param.name)); --(3) dbms_sql.define_column(l_cur, 1, l_param, 2000); end loop; i := dbms_sql.execute(l_cur); i := dbms_sql.fetch_rows(l_cur); dbms_sql.column_value(l_cur, 1, l_param); exception when others then if dbms_sql.is_open(l_cur) then dbms_sql.close_cursor(l_cur); end if; end;

at the point (1) in the listing p_query_text may contain another sql text with other bind params.
at the point (2) recommended views (V_$SQL_BIND_DATA, V_$SQL_BIND_METADATA) have no bind variables for the open cursor. at the point (3) one of the views contains a row for my bind variable, but with empty bind_name.

that name appears after I recall my PL/SQL block one more... Received on Tue Aug 10 2004 - 11:27:46 CDT