Re: How to use dbms_sql_define_column when no of columns are not known in Advance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 23 Nov 1999 07:23:03 -0500
Message-ID: <6h1l3sss35g96o3a9nrakdihphd5cea763_at_4ax.com>

A copy of this was sent to Madhushekhar <Madhushekhar.MC_at_in.bosch.com> (if that email address didn't require changing) On 23 Nov 1999 03:36:05 GMT, you wrote:

>Hi,
> Can any body tell me how to use the DBMS_SQL.DEFINE_COLUMN
>when the no of columns and its dat Types are not known before hand.I am
>passing the table name as a parameter and get the columns from COLS data
>dictionary.I tried using Varrays but could not solve my problem.
> Any help would be greatly Appreciated.
> Bye

If you do not know the types -- the best course of action is to bind everything to a varchar.

2 ways to find the number of columns:

1. dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

fills in a variable colCnt with the number of colums. descTbl has datatype, column_name, etc....

2) just loop defining columns until you catch the error that says "no more columns". eg:

    for i in 1 .. 10000 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;

Here is an example that takes a query and dumps it to a file using utl_file:

create or replace function  dump_csv( p_query     in varchar2,
                                      p_separator in varchar2 default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )

return number
is

    l_output        utl_file.file_type;
    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 '';
    l_cnt           number default 0;

begin

    l_output := utl_file.fopen( p_dir, p_filename, 'w' );

• Oracle8.0.5 and up only, set maxlinesize to about 32k
• l_output := utl_file.fopen( p_dir, p_filename, 'w', 32000 );

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

    for i in 1 .. 10000 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 );
            utl_file.put( l_output, l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;

    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end dump_csv;
/

create or replace procedure test_dump_csv as

    l_rows number;
begin

    l_rows := dump_csv( 'select * from all_users where rownum < 25', ',', '/tmp', 'test.dat' );
end;
/

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Tue Nov 23 1999 - 13:23:03 CET