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! UseSILENT
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.