I'm having some problems to verify the installation/execution of some oracle scripts in different production servers. I've created a launcher script which executes all the sql scripts in sqlplus and writes the output in different spool files. But i wish to if i can define 'a header' to write before the sql output with sql filename, servername or server ip and date+time to ensure or have proofs to demonstrate the execution of these scripts.

Thanks in advance.

  • 2
    What is the script written in? Is it executing on the database box or a remote one? Details! – Philᵀᴹ Dec 19 '13 at 22:26
  • You have also not updated the previous question you asked – Philᵀᴹ Dec 19 '13 at 22:27

I hope my answer is not erroneous, because maybe I do not understand very well you question.

A simple solution can be the following. In every server you can create an header file (header.sql) like this, for example:


SELECT 'The database is: ' || upper(instance_name) || '. The server is: ' || upper(host_name) || '.'
FROM v$instance;

SELECT 'Execution Time: ' || to_char(sysdate, 'HH24:MI MM/DD/YYYY') FROM dual;

For IP address, you can parse output of host command, maybe like this:

!host host_name

In the bash script file (test.sh) you can put the main calls...

sqlplus -s system/sys@TESTDB1 @modify.sql
sqlplus -s system/sys@TESTDB2 @insert.sql
sqlplus -s system/sys@MYDB @delete.sql

For example, a very basic sql script file can be like this one:

spool scripts.log APPEND
SELECT 'Filename: ' || sys_context('USERENV', 'MODULE') FROM dual;

-- beginning of the code.

    UPDATE test.A SET id = 11; -- sample code
    -- ..

-- end of the code.

PROMPT --------------------

You need to put sql files in a folder assigned to the variable SQLPATH.

The log file appears like this one:

The database is: TESTDB1. The server is: SERVERA.
Execution Time: 18:39 12/28/2013
Filename: 01@ modify.sql
------------------- spool off
The database is: TESTDB2. The server is: SERVERA.
Execution Time: 18:39 12/28/2013
Filename: 01@ insert.sql
insert into C values(10)
ERROR at line 1:
ORA-00942: table or view does not exist

------------------- spool off
The database is: MYDB. The server is: SERVERB.
Execution Time: 18:39 12/28/2013
Filename: 01@ delete.sql

Hope it helps.

| improve this answer | |
col dt1 noprint new_value dt1

SELECT   TO_CHAR (SYSDATE, 'yyyymmdd') || '.log' dt1 FROM DUAL;

spool C:\log_file_&dt1

sql commands

spool off
| improve this answer | |
  • Hi will and thanks for the answer, but could you add a bit of explanation for those who don't speak PLSQL natively? This answer looks spammish to some of our readers because they don't understand what you're encouraging Jorge to do! – jcolebrand Oct 13 '14 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.