Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

format sqlplus output

Posted on 2010-11-28
11
Medium Priority
?
1,110 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 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