Re: table flip......

From: <fitzjarrell_at_cox.net>
Date: 3 Jun 2005 15:09:25 -0700
Message-ID: <1117836565.164828.318990@z14g2000cwz.googlegroups.com>

Thomas Kyte wrote:
> In article <1117818211.563882.73930_at_g47g2000cwa.googlegroups.com>, oracle_man
> says...
> >
> >All,
> >
> >Does anyone remember/know where to find the code to flip a table on its
> >side so that output from a select statement aligns the columns in a
> >vertical fasion? I'm looking for a function that I though either
> >Millsap or Kyte wrote, but could not find either.
> >
> >Any help appreciated,
> >
> >oracle_man.
> >
>
>
> http://asktom.oracle.com/~tkyte/print_table/
>
>
> --
> Thomas Kyte
> Oracle Public Sector
> http://asktom.oracle.com/
> opinions are my own and may not reflect those of Oracle Corporation

Modified to use the existing NLS_DATE_FORMAT setting:

create or replace procedure print_table( p_query in varchar2 ) AUTHID CURRENT_USER
is

    --
    -- Tom Kyte suthored this
    --
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    --

• This is added to hold the current NLS_DATE_FORMAT setting -- l_datefmt varchar2(40); begin --
• Returns the current NLS_DATE_FORMAT setting for the session --
• To be used later to reset session parameter settings -- select value into l_datefmt from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';

    --
    -- We now return you to Tom Kyte's code
    --

    execute immediate
    'alter session set

        nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse( l_theCursor, p_query, 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;
    --

• Tom's original code modified to use the actual NLS_DATE_FORMAT setting -- execute immediate 'alter session set nls_date_format='''||l_datefmt||''' '; exception when others then --
  • The same modification was made here -- execute immediate 'alter session set nls_date_format='''||l_datefmt||''' '; --
  • And, we're back to the original code -- raise; end; /

The procedure works like a charm. My apologies to Tom for 'hacking away' at his code.

David Fitzjarrell Received on Fri Jun 03 2005 - 17:09:25 CDT