Re: Dynamic SQL and dynamic variable names

On Jan 18, 5:52 am, "William Robertson" <williamr2..._at_googlemail.com> wrote:
> saketkha..._at_gmail.com wrote:
> > I have a peculiar problem I've been working on for a while now...
>
> > I am trying to call a procedure in a loop and pass the value of each
> > column in a cursor.
>
> > something like this:
>
> > for v_cursor_columns in c_columns
> > loop
> > begin
> > v_dynamic := 'BEGIN compare_proc ( v_cursor_columns.column_name)';
> > execute immediate v_dynamic;
> > end loop;
>
> > Basically - I am trying to access variable in the current anonymous
> > pl/sql block.
>
> > However, I get this error whenever I try:
>
> > PLS-00201: identifier 'v_cursor_diff.column_name' must be declared
>
> > In some other parts, I am even trying to generate the variable name (in
> > above example v_cursor_columns.column_name ) on the fly using
> > concatenation.
>
> > How can I accomplish these tasks?
>
> > I've searched all the web I could, but couldn't find such an example.
> > Someone mentioned that dynamic sql will access global variables only -
> > Can someone please help me?Shouldn't that be
>
> v_dynamic := 'BEGIN compare_proc (:b1)';
>
> for v_cursor_columns in c_columns
> loop
> execute immediate v_dynamic USING v_cursor_columns.column_name;
> end loop;- Hide quoted text -- Show quoted text -

Depending on exactly what needs to be done this might be a situation where the capabilities of the dbms_sql package can be used to good effect.

HTH -- Mark D Powell -- Received on Thu Jan 18 2007 - 08:43:33 CST