Re: PL-SQL: variable column processing

A copy of this was sent to Carsten Wagener <carsten_wagener_at_yahoo.com> (if that email address didn't require changing) On Tue, 22 Sep 1998 15:19:56 +0200, you wrote:

>Hi,
>maybe a stupid question:
>
>Table xx contains 5 columns (a,b,c,d,e).
>
>I'd like to read each row and for each column of it I'd like to call the
>same procedure.
>
> I don't want to do it like this (that's stupid if there are hundrets of
>coluns):
>doit(xx.a);
>doit(xx.b);
>doit(xx.c);
>doit(xx.d);
>doit(xx.e);
>
>I like to do it like this:
>loop
> doit(yy)
>endloop
>
>Thanks for any hints
>Carsten
>

you can do this with a GLOBAL package specification record and dbms_sql. Consider this example (compile in the scott/tiger schema and run it via:

SQL> my_pkg.run_demo

It loops over every emp record and then, using user_tab_columns, loops over the column in the record

create or replace package my_pkg
as

    global_record emp%rowtype;

    procedure doit( p_name in varchar2, p_data in varchar2 );

    procedure run_demo;
end;
/

create or replace package body my_pkg
as

g_exec_cursor integer default dbms_sql.open_cursor;

procedure execute_immediate( sql_stmt in varchar2 ) as

    rows_processed number default 0;
begin

    dbms_sql.parse(g_exec_cursor, sql_stmt, dbms_sql.native );     rows_processed := dbms_sql.execute(g_exec_cursor); end;

procedure doit( p_name in varchar2, p_data in varchar2 ) is
begin

    dbms_output.put_line( rpad( p_name, 30 ) || ':' || p_data ); end;

procedure run_demo
is
begin

    for x in ( select * from emp )
    loop

        global_record := x;

        for y in ( select column_name
                     from user_tab_columns
                    where table_name = 'EMP' )
        loop
            execute_immediate( 'begin
                                    my_pkg.doit( '''||y.column_name||''',
                                      my_pkg.global_record.' ||
                                      y.column_name || '); end;' );
        end loop;

end;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.