Re: How to gather bind params in SQL?

mvici_at_yandex.ru (Victor) wrote in message news:<2c447de6.0408100827.1dbf4f55_at_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...

I see what you want to do. I don't know if there's a way in PL/SQL to list the bind variable names you defined in your session but not yet bound to any values.

SQL*Plus can do this. VAR N NUMBER followed by PRINT which really does SELECT :n n FROM DUAL. How does PRINT know you have defined N? Since SQL*Plus is an OCI program (I think), you probably have to use OCI to look at bind handles to achieve this. Just a guess.

Yong Huang Received on Wed Aug 11 2004 - 14:14:29 CDT