how to do two sqlplus queries and display the result in single line

Question

I do not know sqlplus. But trying to complete one task at work. The task is to logon to a schema and get following information in single line - schema_name, database_name, database_link_name, user_name

I am able to get that information in TWO lines. I will be grateful if somebody suggests a simple way of getting results of two different select queries in a single line.

Following works but gives me required results in TWO lines. I want them in single line.

SQL> select * from
 (select user || ' ' || sys_context('USERENV','DB_NAME') as Instance from dual),
 (select DB_LINK  || ' ' || username from user_db_links);

TSTSCRIPT2 ORADEV
MCCODEVTOMCCOSTG_TSTSCRIPT1  TSTSCRIPT1

I got two responses. That fixed my problem. I am trying to create a script which finds out ALL the dblinks under ALL the schemas and then by using "alter database link" command changes the link password. We have to change the link passwords every time we change schema passwords. I will be writing the shell script using "expect" function. — user1149518
– user1149518, Commented Nov 23, 2013 at 23:53

Nick Krasnov · Accepted Answer · 2013-11-23 18:48:08Z

2

Simply specify display format for a column. In your situation, as there several values are being concatenated it'll be aliases.

/* Here character value is 11 characters long "a11"
   If you need it to be longer or shorter simply increase or decrease
   the value of the constant, make it "a20", for instance
*/
SQL> column instance format a11; 
SQL> column res2 format a11;

SQL> select user || ' ' || sys_context('USERENV','DB_NAME') as instance
          , DB_LINK  || ' ' || username                     as res2
       from user_db_links t

Result:

INSTANCE    RES2      
----------- -----------
HR CDB      NK1 HR

edited Nov 23, 2013 at 18:48

answered Nov 23, 2013 at 18:27

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2013-11-23 18:26:12Z

0

select (select user || ' ' || sys_context('USERENV','DB_NAME') as Instance from dual) as user_info,
       (select DB_LINK  || ' ' || username from user_db_links where rownum < 2) as db_link
from dual;

answered Nov 23, 2013 at 18:26

user330315

1 Comment

user1149518 Over a year ago

Thanks Both. SQL> select user || ' ' || sys_context('USERENV','DB_NAME') as instance , DB_LINK || ' ' || username as res2 from user_db_links t; INSTANCE RES2 -------------------- ----------------------------------------------------------- TSTSCRIPT1 MCCODEV MCCODEVTOMCCOSTG_TSTSCRIPT1.SLAC.STANFORD.EDU TSTSCRIPT1 TSTSCRIPT1 MCCODEV MCCODEVTOMCCOSTG_TSTSCRIPT2.SLAC.STANFORD.EDU TSTSCRIPT2

Collectives™ on Stack Overflow

how to do two sqlplus queries and display the result in single line

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related