Re: sqlplus question
- From: Remigiusz SokoÅowski <rems@xxxxxxxx>
- To: jdunn@xxxxxxxxx
- Date: Thu, 19 Oct 2006 10:08:02 +0200
John Dunn wrote:
> Apologies I meant to say select * from mytable
>
> -----Original Message-----
> *From:* David Sharples [mailto:davidsharples@xxxxxxxxx]
> *Sent:* Tuesday, October 17, 2006 5:26 PM
> *To:* jdunn@xxxxxxxxx
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *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@xxxxxxxxx
> <mailto:jdunn@xxxxxxxxx>> 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@xxxxxxxx>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL v. 4.x
Oracle v. 10.x
---------------------------------------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
