Home » SQL & PL/SQL » Client Tools » vertical display of table (10g)  () 1 Vote
vertical display of table [message #463142] Tue, 29 June 2010 15:50 Go to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
I would like to format my output for the select query.

Example:
SQL>select empno, ename from emp;

empno										ename
1987766									Jack Blake
187765									Greg Ni
...
...
...



But I would like to get the output in the below format

EMPNO:.................1987766
ENAME:.................Jack Blake

EMPNO:.................187765
ENAME:.................Greg Ni


I will need help in printing the column name. I would like to know how do we address the column name to print.

Thanks
geneeyuss
Re: formatting output for select stmt [message #463157 is a reply to message #463142] Tue, 29 June 2010 19:19 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
it works for me

  1  select 'EMPNO..........'||empno,
  2	    'ENAME..........'||ename
  3* from emp
SQL> /
EMPNO..........7499
ENAME..........ALLEN

EMPNO..........7521
ENAME..........WARD

EMPNO..........7566
ENAME..........JONES

EMPNO..........7654
ENAME..........MARTIN
Re: formatting output for select stmt [message #463175 is a reply to message #463157] Wed, 30 June 2010 00:36 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
@Blackswan,
It even does not work for me either!! Smile
SQL> ed
Wrote file afiedt.buf

  1  select 'EMPNO..........'||empno empid,
  2    	    'ENAME..........'||ename ename
  3*    from emp where rownum<3
SQL> /

EMPID                                              ENAME               
-------------------------------------------------- --------------------
EMPNO..........7369                                ENAME..........SMITH
EMPNO..........7499                                ENAME..........ALLEN



Could be?
SQL> ed
Wrote file afiedt.buf

  1  select 'EMPNO..........'||empno  ||chr(10)||
  2    	    'ENAME..........'||ename output
  3*    from emp --where rownum<3
SQL> /

OUTPUT
---------------------
EMPNO..........7369  
ENAME..........SMITH 
                     
EMPNO..........7499 
ENAME..........ALLEN

@OP, Search for pivot here in this forum

Regards
Ved

[Updated on: Wed, 30 June 2010 00:49]

Report message to a moderator

Re: formatting output for select stmt [message #463178 is a reply to message #463175] Wed, 30 June 2010 01:04 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SQL> select rpad('EMPNO:',23,'.')||empno  ||chr(10)||
  2         rpad('ENAME:',23,'.')||ename output
  3  from emp where rownum<3;

OUTPUT 
----------------------------
EMPNO:.................7369
ENAME:.................SMITH
                            
EMPNO:.................7499 
ENAME:.................ALLEN


Regards
Ved
Re: formatting output for select stmt [message #463180 is a reply to message #463178] Wed, 30 June 2010 01:41 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> SET HEAD OFF
SQL> /

EMPNO..........7369
ENAME..........It`smeved

EMPNO..........7499
ENAME..........Blackswan

EMPNO..........7521
ENAME..........littleFoot


Op should read how to format sql output.

sriram
Re: formatting output for select stmt [message #463260 is a reply to message #463175] Wed, 30 June 2010 07:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BlackSwan wrote on Wed, 30 June 2010 07:42
I did not reveal what I used for "colsep"

Then it was not very helpful, was it? (Or in some wise man's words: "were you asking or just bragging?" Wink )
vertical display of table [message #463285 is a reply to message #463142] Wed, 30 June 2010 09:44 Go to previous messageGo to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
vertical display of table

Example:

SQL> select empno, ename from emp;
empno								ename
12345								john Blake
65432								Michael George


I would like to get the output like below:
SQL>@vertical_output
Enter Table Name: emp
EMPNO:............12345
ENAME:............John Blake

EMPNO:............65432
ENAME:............Michael George



Could I know how do we get this. I am stuck at a point where I print the column names.

Can anyone let me know how to do this?

Thanks
geneeyuss
Re: vertical display of table [message #463288 is a reply to message #463285] Wed, 30 June 2010 09:52 Go to previous messageGo to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
In your case you know the table name and the columns you wanna output. I want this to be generic for any given table. In that case I should how to print the column name.

Could someone please help me on that.

Thanks
geneeyuss
Re: vertical display of table [message #463292 is a reply to message #463288] Wed, 30 June 2010 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
T.Kyte's print_table function.

Regards
Michel
Re: vertical display of table [message #463295 is a reply to message #463292] Wed, 30 June 2010 10:22 Go to previous messageGo to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
I tried this before also. one of my collegues gave permission to me to execute it. but doesnt seems to give me any output at all.

Example:
SQL> exec pv('select * from FNCL_AST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01


I just renamed the same file to pv. Could I know if someone can help me.

thanks
geneeyuss
Re: vertical display of table [message #463297 is a reply to message #463295] Wed, 30 June 2010 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we have not your "PV" procedure we can't help.
But I bet it needs you "set serveroutput on".

Regards
Michel
Re: vertical display of table [message #463299 is a reply to message #463142] Wed, 30 June 2010 10:28 Go to previous messageGo to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
SQL> set serveroutput on
SQL> exec pv('select * from FNCL_AST');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

pv.sql

create or replace 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;
/
Re: vertical display of table [message #463308 is a reply to message #463299] Wed, 30 June 2010 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
geneeyuss wrote on Wed, 30 June 2010 17:28
let me know if anyone can help please ...

As your script creates a procedure named print_table and you called a procedure named pv, I think you didn't call the procedure you think you called.
... or you faked your output, in both cases we can't help.

Regards
Michel

Re: vertical display of table [message #463320 is a reply to message #463308] Wed, 30 June 2010 13:48 Go to previous messageGo to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
that way my mistake while copying over here. i dint do it when I was running. I had pv at both places. Kindly let me know what could go wrong. as I dubble checked on what u said.

Re: vertical display of table [message #463321 is a reply to message #463320] Wed, 30 June 2010 13:59 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
it works for me

SQL> exec print_table('SELECT * FROM EMP where rownum <3');
EMPNO			      : 7499
ENAME			      : ALLEN
JOB			      : SALESMAN
MGR			      : 7698
HIREDATE		      : 20-feb-1981 00:00:00
SAL			      : 1600
COMM			      : 300
DEPTNO			      : 30
INSERT_DATE		      : 01-jun-2010 21:36:29
-----------------
EMPNO			      : 7521
ENAME			      : WARD
JOB			      : SALESMAN
MGR			      : 7698
HIREDATE		      : 22-feb-1981 00:00:00
SAL			      : 1250
COMM			      : 500
DEPTNO			      : 30
INSERT_DATE		      : 01-jun-2010 21:36:29
-----------------

PL/SQL procedure successfully completed.

SQL> 
Re: vertical display of table [message #463322 is a reply to message #463321] Wed, 30 June 2010 14:27 Go to previous messageGo to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
I tried keeping the name of the procedure print_table and it worked for me. but if I change the procedure name to pv and exec pv I dont get any output. but I dot get it with print_table as the procedure name.

I have no idea y this is happening?
Re: vertical display of table [message #463323 is a reply to message #463322] Wed, 30 June 2010 14:34 Go to previous messageGo to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
i just tried it works for me if the procedure name is print_table and doesnt work if I change the procedure name to pv

SQL> exec print_table('select * from im2.emp where rownum <3');
EMPNO                   			: 100775146
ENAME               					: John Blake
HIRE_DT                       : 01-jun-2009 16:36:10
SALARY                   			: 90000
-----------------
EMPNO                  			  : 100800985
ENAME               					: Michael George
HIRE_DT                       : 30-may-2009 18:52:52
SALARY                   			: 80000
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01


if I change the procedure name to pv I cannot run it.

SQL> exec pv('select * from im2.emp where rownum <3')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

there is no difference other than me change the name of the procedure from print_table to pv.

it works fine with print_table. but just curious why it ditn work with procedure name pv.

Thanks
geneeyuss
Re: vertical display of table [message #463325 is a reply to message #463323] Wed, 30 June 2010 14:50 Go to previous messageGo to next message
geneeyuss
Messages: 53
Registered: June 2010
Member
This is what I have with that command:

SQL> SELECT OBJECT_TYPE, OWNER from DBA_OBJECTS WHERE OBJECT_NAME = 'PV';

OBJECT_TYPE         OWNER
------------------- ------------------------------
PROCEDURE           B8345B
SYNONYM             Im2_TOOL
SYNONYM             Im2
SYNONYM             B22222              ----- thats me

Elapsed: 00:00:00.06
Re: vertical display of table [message #463326 is a reply to message #463325] Wed, 30 June 2010 15:14 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
You (B22222) do not have/own a procedure called "PV" despite your erroneous claims to the contrary.
When reality is not accurately reported & posted, confusion reigns supreme.
This is a prime example why COPY & PASTE are vital debugging tools.

SELECT OBJECT_TYPE, OWNER from DBA_OBJECTS WHERE OBJECT_NAME = 'PV';
Re: vertical display of table [message #463327 is a reply to message #463326] Wed, 30 June 2010 15:32 Go to previous message
geneeyuss
Messages: 53
Registered: June 2010
Member
I think the mistake was I forgot that my colleague had granted select privilege on a function pv which didn't work. Now when I try to create a procedure that's where it strying to look per my understanding.

Thanks for troubleshooting my problem.
Previous Topic: oracle tools
Next Topic: /SQL *Plus Commands ?
Goto Forum:
  


Current Time: Sun Oct 20 11:14:55 CDT 2019