?
Solved

format sqlplus output

Posted on 2010-11-28
11
Medium Priority
?
1,083 Views
Last Modified: 2012-06-27
Hi there,

how can I achive that sqlplus does not output in columns

Is there simple way to do ?

select A,B from mytable;
A    B
1    Maus
2   Katze
3   Uhu
4  Pferd
 
I would like to have the following output
A : 1
B : Maus
------
A : 2
B : Katze
etc...
0
Comment
Question by:hgraesslin1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34227042
Try:
Select 'A: ' || A from mytable
Union all
Select 'B: ' || B from mytable
0
 

Author Comment

by:hgraesslin1
ID: 34228128
no, sorry. There must be an sql-directive to do that.
0
 
LVL 19

Expert Comment

by:Thommy
ID: 34229963
select Decode(Mod(rownum,2),1,'A:'||A,'B:'||B) from
(select rownum,a,b from ((select a,b from test) union all (select a,b from test) order by a))
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34229991
>>  ...(select a,b from test) union all (select a,b from test)...

Not sure what this 'union all' does other than duplicate all the data.

Also using rownum in the outer query as you did gets the rownum there.  I expect you are wanting the rownum from the inner query.  If so, you will need to alias it.
0
 
LVL 32

Expert Comment

by:awking00
ID: 34230046
>>how can I achive that sqlplus does not output in columns<<
Any sql query is going to retrieve values in columns, even if it is formed to only retrieve one column. Are there more columns other than A and B in your actual data that you want to display? Perhaps some more detailed examples of the data and the expected output would give us a clearer understanding of your intent.

0
 
LVL 7

Accepted Solution

by:
jocave earned 1000 total points
ID: 34230542
If you use Tom Kyte's print_table (or some of the subsequent versions), you can do something like

SQL> exec print.tbl( 'select * from emp' );

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL> exec print.tbl( 'select * from emp' );
EMPNO                         : 7369
ENAME                         : smith
JOB                           : CLERK
MGR                           : 7902
HIREDATE                      : 17-dec-1980 00:00:00
SAL                           : 800
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7499
ENAME                         : ALLEN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 20-feb-1981 00:00:00
SAL                           : 1600
COMM                          : 300
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7521
ENAME                         : WARD
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 22-feb-1981 00:00:00
SAL                           : 1250
COMM                          : 500
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7566
ENAME                         : JONES
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 02-apr-1981 00:00:00
SAL                           : 2975
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7654
ENAME                         : MARTIN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 28-sep-1981 00:00:00
SAL                           : 1250
COMM                          : 1400
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7698
ENAME                         : BLAKE
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 01-may-1981 00:00:00
SAL                           : 2850
COMM                          :
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7782
ENAME                         : CLARK
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 09-jun-1981 00:00:00
SAL                           : 2450
COMM                          :
DEPTNO                        : 10
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7788
ENAME                         : SCOTT
JOB                           : ANALYST
MGR                           : 7566
HIREDATE                      : 19-apr-1987 00:00:00
SAL                           : 3000
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7839
ENAME                         : KING
JOB                           : PRESIDENT
MGR                           :
HIREDATE                      : 17-nov-1981 00:00:00
SAL                           : 5000
COMM                          :
DEPTNO                        : 10
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7844
ENAME                         : TURNER
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 08-sep-1981 00:00:00
SAL                           : 1500
COMM                          : 0
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7876
ENAME                         : ADAMS
JOB                           : CLERK
MGR                           : 7788
HIREDATE                      : 23-may-1987 00:00:00
SAL                           : 1110
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7900
ENAME                         : JAMES
JOB                           : CLERK
MGR                           : 7698
HIREDATE                      : 03-dec-1981 00:00:00
SAL                           : 950
COMM                          :
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7902
ENAME                         : FORD
JOB                           : ANALYST
MGR                           : 7566
HIREDATE                      : 03-dec-1981 00:00:00
SAL                           : 3000
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7934
ENAME                         : MILLER
JOB                           : CLERK
MGR                           : 7782
HIREDATE                      : 23-jan-1982 00:00:00
SAL                           : 1300
COMM                          :
DEPTNO                        : 10
COL_FOO                       :
KEY                           :
-----------------

PL/SQL procedure successfully completed.

Open in new window

0
 
LVL 19

Expert Comment

by:Thommy
ID: 34230715
@slightwv:

select Decode(Mod(rownum,2),1,'A:'||A,'B:'||B) from
(select rownum,a,b from ((select a,b from test) union all (select a,b from test) order by a))

The "union all" in the inner query duplicates the rows.

Decode outputs 'A:'||A for odd rownums (e. g. A:1) and 'B:'||B for even rownums (e. g. B:Maus)...

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34231293
@Thommy,

Why duplicate all rows just to get odd/even?  Seems like a lot of work.

Also, are you sure the rownum in the decode works the way you think it does?
0
 
LVL 19

Expert Comment

by:Thommy
ID: 34238944
@slightwv:
I want the rownum from the outer query.

Of course the "rownum" of the inner query is unnecessary and can be omitted!!!

select Decode(Mod(rownum,2),1,'A:'||A,'B:'||B) from
(select a,b from ((select a,b from test) union all (select a,b from test) order by a))

Do you have a better idea how to get the desired output with 2 rows from originally one row without duplicating???
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34239101
Guess not.  Even I used a 'union all'.  It just seemed weird initially to return all rows in both (duplicate queries).

The only way I can think to get around it is a pipelined function.
0
 

Author Closing Comment

by:hgraesslin1
ID: 34280430
I am still waiting for an sqlplus directive... there must be one....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question