1 Reply Latest reply: Jan 25, 2013 9:19 AM by Frank Kulash RSS

    COLUMN ... NOPRINT (blank lines printed)

    JackK
      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
          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
          ...