Remove "connection established" from spooling in Oracle-CodePudding

How can I remove the string "connection established" in an SQL script which is connection to different databases and spools some output in one global file?

This is my simple script actually:

set feedback off
set echo off
set lines 200
set pages 999

spool check_control_file_record_keep_time.txt;

conn /@"HOST1:1521/DB1"
    set echo off
    set feedback off
    set serveroutput on
    DECLARE
        actual_param_setting NUMBER;
     BEGIN
        select value into actual_param_setting from v$parameter where name='control_file_record_keep_time';
        IF actual_param_setting >= 11 THEN
            DBMS_OUTPUT.PUT_LINE('[OK] - DB1 - Parameter control_file_record_keep_time for this database is OK!');
        ELSE
            DBMS_OUTPUT.PUT_LINE('[NOK] - DB1 - Parameter control_file_record_keep_time need to be changed for this database!');
        END IF;
     END;
     /
conn /@"HOST2:1521/DB2"
    set echo off
    set feedback off
    set serveroutput on
    DECLARE
        actual_param_setting NUMBER;
     BEGIN
        select value into actual_param_setting from v$parameter where name='control_file_record_keep_time';
        IF actual_param_setting >= 11 THEN
            DBMS_OUTPUT.PUT_LINE('[OK] - DB2 - Parameter control_file_record_keep_time for this database is OK!');
        ELSE
            DBMS_OUTPUT.PUT_LINE('[NOK] - DB2 - Parameter control_file_record_keep_time need to be changed for this database!');
        END IF;
     END;
     /
conn /@"HOST3:1521/DB3"
    set echo off
    set feedback off
    set serveroutput on
    DECLARE
        actual_param_setting NUMBER;
     BEGIN
        select value into actual_param_setting from v$parameter where name='control_file_record_keep_time';
        IF actual_param_setting >= 11 THEN
            DBMS_OUTPUT.PUT_LINE('[OK] - DB3 - Parameter control_file_record_keep_time for this database is OK!');
        ELSE
            DBMS_OUTPUT.PUT_LINE('[NOK] - DB3 - Parameter control_file_record_keep_time need to be changed for this database!');
        END IF;
     END;
     /

spool off;
exit

And this is the output in spooled file

Connection established.
[NOK] - DB1 - Parameter control_file_record_keep_time need to be changed for this database!                                                                                                       
Connection established.
[NOK] - DB2 - Parameter control_file_record_keep_time need to be changed for this database!                                                                                                       
Connection established.
[NOK] - DB3 - Parameter control_file_record_keep_time need to be changed for this database!

Any idea to "throw away" the "Connection established." by executing in sqlplus?

CodePudding user response：

You can use the -s[ilent] flag when you invoke SQL*Plus; from the documentation:

3.5.1.10 SILENT Option
-S[ILENT]
Suppresses all SQL*Plus information and prompt messages, including the command prompt, the echoing of commands, and the banner normally displayed when you start SQL*Plus. If you omit username or password, SQL*Plus prompts for them, but the prompts are not visible! Use SILENT to invoke SQL*Plus within another program so that the use of SQL*Plus is invisible to the user.

That applies to connect commands issued within the program too, not just the initial connection (if you have one; you may be using /nolog).

You could also then redirect the output to a file as an alternative to using spool - by default the banner etc. would be in the output, but as this suppresses that, the output is much cleaner.