Write filename, servername/IP and date+time in spool file

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.

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:

SET PAGESIZE 0 LINESIZE 80 FEEDBACK OFF
SET TERMOUT OFF
SET APPINFO ON

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
@header.sql
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 --------------------
SPOOL OFF
SET TERMOUT ON
EXIT

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.

col dt1 noprint new_value dt1

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

spool C:log_file_&dt1

sql commands

spool off