Re: DBMS_SQL: How to fetch from dynamically built SELECT?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 01 Dec 1999 11:11:41 -0500
Message-ID: <j2ia4skdtd9186ej1qsrh2p0q8j296igc5@4ax.com>

A copy of this was sent to Klim Samgin <klimsamgin_at_yahoo.com> (if that email address didn't require changing) On Wed, 01 Dec 1999 15:55:12 GMT, you wrote:

>Hi!
>I've got table, for example,
>
>SQL> desc mytab;
> Name Null? Type
> ------------------------------- -------- ----
> F1 NUMBER(10)
> F2 CHAR(1)
> F3 VARCHAR2(15)
> ..................................................
> ..................................................
> Fxx DATE
>
>With DBMS_SQL, my PL/SQL programm dynamically built
>query on some columns, for example,
>
>....
>stmnt varchar2(1500);
>....
>stmnt = 'select f1, f4, f15, f26 from mytab'
>
>I don't know which columns are in the query, all of them
>or only F3, or F6 and F11. But I know how many columns
>are in the query, 10 or 1, or 2.
>
>If I know, which columns are in the query,
>for example, f1, f2, f3:
>
>stmnt = 'select f1, f2, f3 from mytab',
>
>Oracle Documentation recommends the following way:
>

step 1: fetch everything into a varchar2 (dates, numbers, etc)

step 2: use just one host variable to fetch into.

Here is an example. give it a query -- it'll dump the result to a 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(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' );

• use this in 8.0.5 and up to get around the ~1000 byte limit of utl_file
• 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, 4000 );

    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;
/

--
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 Wed Dec 01 1999 - 10:11:41 CST