Re: printtab code

From: A. Coskan Gundogar <gundogar_at_gmail.com>
Date: Fri, 12 Feb 2010 13:57:12 +0000
Message-ID: <d1f742de1002120557q4350fc5djc728709a1021cbf5_at_mail.gmail.com>

This is the code you looking for I think

set serverout on size 1000000

• Tom Kyte's printtab code ( http://asktom.oracle.com )

declare

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;

    procedure execute_immediate( p_sql in varchar2 )     is
    BEGIN

        dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
        l_status := dbms_sql.execute(l_theCursor);

    END;
begin

    execute_immediate( 'alter session set nls_date_format=

                        ''dd-mon-yyyy hh24:mi:ss'' ');
    dbms_sql.parse(  l_theCursor,
                     replace( '&1', '"', ''''),
                     dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor,
                               l_colCnt, l_descTbl );
    for i in 1 .. l_colCnt loop
        dbms_sql.define_column( l_theCursor, i,
                                l_columnValue, 4000 );

    end loop;
    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,
                                   l_columnValue );
            dbms_output.put_line
                ( rpad( l_descTbl(i).col_name,
                  30 ) || ': ' || l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );

    end loop;
    execute_immediate( 'alter session set nls_date_format=

                           ''dd-MON-yy'' ');

exception

    when others then

        execute_immediate( 'alter session set
                         nls_date_format=''dd-MON-yy'' ');
        raise;

end;
/

On 12 February 2010 13:51, Pani Babu <panibabu.mail_at_gmail.com> wrote:

> Have you guys ever come across code for _at_printtab. I have seen this one
> been being used in a number of places. It lists all the columns of a query
> as separate rows. Google doesn't provide any hits for the code. I would
> appreciate if anyone can provide links to the code.
>
> Pani Babu
>

-- 
A. Coskan GUNDOGAR

Oracle DBA

gundogar_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 12 2010 - 07:57:12 CST