This discussion is archived
1 Reply Latest reply: Jan 25, 2013 7:19 AM by Frank Kulash RSS

COLUMN ... NOPRINT (blank lines printed)

JackK Explorer
Currently Being Moderated
Hi,
I have a script login_me.sql:
define connect_str=my_db

COLUMN conn_u NEW_VALUE myuser
COLUMN conn_p NEW_VALUE mypass

WITH passwds AS (SELECT 'TEST' AS conn_u, 't' AS conn_p FROM dual UNION ALL
                 SELECT 'ROGADM',         'mypass'      FROM dual
                )
SELECT * FROM passwds
 WHERE upper(conn_u) = upper('&1');
prompt ******* Connecting as &myuser....  (to DB &connect_str) *******
conn &myuser/&mypass@&connect_str
I run the script as:
@login_me TEST
and the output is:
CONN_U    CONN_P
--------- -------
TEST      t

******* Connecting as TEST...  (to DB my_db) *******
Connected.
After changing 2 lines of my script:
COLUMN conn_u NOPRINT NEW_VALUE myuser
COLUMN conn_p NOPRINT NEW_VALUE mypass
the output is
******* Connecting as TEST...  (to DB my_db) *******
Connected.
There are printed blank lines. How to make them not print at all?

SQL*Plus: Release 10.2.0.3.0
  • 1. Re: COLUMN ... NOPRINT (blank lines printed)
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Use the SQL*Plus commands
    <tt> SET TERMOUT OFF </tt>     to suppress output, and
    <tt> SET TERMOUT ON </tt>       to re-enable it, like this:
    ...
    COLUMN conn_u NEW_VALUE myuser
    COLUMN conn_p NEW_VALUE mypass
    
    SET  TERMOUT  OFF  
    
    WITH passwds AS (SELECT 'TEST' AS conn_u, 't' AS conn_p FROM dual UNION ALL
                     SELECT 'ROGADM',         'mypass'      FROM dual
                    )
    SELECT * FROM passwds
     WHERE upper(conn_u) = upper('&1');
    
    SET  TERMOUT  ON
    ...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points