Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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

2 Answers 2

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.

share|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
share|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 at 20:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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