Re: sqlplus question

From: Remigiusz Sokołowski <rems_at_wp-sa.pl>
Date: Thu, 19 Oct 2006 10:08:02 +0200
Message-id: <45373262.40508@wp-sa.pl>

John Dunn wrote:

> Apologies I meant to say select * from mytable
>
> -----Original Message-----
> *From:* David Sharples [mailto:davidsharples_at_gmail.com]
> *Sent:* Tuesday, October 17, 2006 5:26 PM
> *To:* jdunn_at_sefas.com
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: sqlplus question
>
> no, beucase count(*) is just a simple count - you woul dbe asking
> the database to give you something different.
>
> You must code that if you want it with a group by
>
>
> On 17/10/06, *John Dunn* <jdunn_at_sefas.com
> <mailto:jdunn_at_sefas.com>> wrote:
>
> Is there a quick way to configure sqlplus so that when I do
> select(*) from mytable I get one line for each column
> name/value pair?
>

well, somewhere on askTom there is a code for procedure print_table

I have not right address, but....

procedure print_table( p_query in varchar2 ) AUTHID CURRENT_USER
is

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

begin

    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;
    execute immediate

        'alter session set nls_date_format=''dd-MON-rr'' '; exception

    when others then

      execute immediate
          'alter session set nls_date_format=''dd-MON-rr'' ';
      raise;

end;

-- 
---------------------------------------
Remigiusz Sokolowski <rems_at_wp-sa.pl>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL  v.  4.x
Oracle v. 10.x
---------------------------------------

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

Received on Thu Oct 19 2006 - 03:08:02 CDT