Re: Using define_column when number of columns are varying

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 12 Nov 1999 07:56:03 -0500
Message-ID: <ew0sOP+WRyh9Ti8W5QArUuAjTHSY_at_4ax.com>

A copy of this was sent to "K.T.RAJA" <sudip.roy_at_in.bosch.com> (if that email address didn't require changing) On 12 Nov 1999 03:43:48 GMT, you wrote:

>Hi,
> I am writing a package using DBMS_SQL.I build a select statement using
>the table name and the Keys supplied by the user.I select the columns from
>cols and build the select statement.So my select statement has varying
>number of columns.Lets assume that the data Types of these columns are
>CHAR,NUMBER and DATE.So how do i Use the DEFINE_COLUMN procedure.I cant
>declare variables offhand.
> Please i request suggestions as early as possible.Please
>write to me at Madhushekhar_at_bosch.com.
> Thanks in advance.
>
> Regards Madhu Shekar
>
> Bangalore

Just use VARCHAR for all of them. Since you don't know the types at compile time, you cannot be doing anything 'really intelligent' with the columns at run time so handleing them all as strings doesn't hurt. You might consider changing the NLS_DATE_FORMAT for your session to something that includes the 4 digit year and time so dates don't lose anything on the conversion (or leave it up to the package to determine that it is selecting a date and use the appropriate to_char/to_date functions on input/output of the date STRING. For example, this procedure takes any query and dumps it to a flat file. It only uses one "host" variable, a varchar2(4000), to fetch into:

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(4000);
    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' );

• 8.0.5 and up only:
• 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 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
            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 Fri Nov 12 1999 - 13:56:03 CET