Re: Scanning through the table columns in PL/SQL

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Mon, 11 Jan 1999 11:28:13 +0800
Message-ID: <36996FCD.1183_at_bhp.com.au>

Peter Le wrote:
>
> I'm a newbie in Oracle PL/SQL, and I just wonder if there is any techniques
> to look at the values of each column one by one? For example, I just want
> to see what the column type or values for a particular column in a database
> table without knowing a specific fieldname.
>
> Please email me at peter.le_at_cwix.com ...
>
> Thanks,
>
> Peter

Most probably you will need to use DBMS_SQL which lets you parse strings as sql statements....

The example below takes any SQL query and produces the output in comma separated format - but you can see from the code that it doesn't know the column's name just the order in which they appear in the query...

Have fun...

create or replace procedure csv( p_query     in varchar2,
                                 p_separator in varchar2 default ',' )
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';

begin

    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

    for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000
);
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;

    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            dbms_output.put( l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        dbms_output.new_line;

    end loop;
    dbms_sql.close_cursor(l_theCursor);
end csv;

-- 
==============================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad"
				- Salvador Dali

Received on Mon Jan 11 1999 - 04:28:13 CET