1

I am using Oracle 10g

I have a abc.sql file that has many insert statements ( 100 + )

Basically this file will be handed over to another team for execution. I want this abc.sql file to generate abc.log file that has messages like "1 rows inserted" or "commit successful" for each and every insert / commit statement.

The intent is this log file will be sent back to developers for verification , to check if all inserts were successful.

I am not an Oracle SQL expert but i know this is possible ( as i used it in one of my companies before ) , opening the question to SQL experts for their advice and help

Any suggestions ?

Lav
  • 1,283
  • 5
  • 21
  • 48
  • 3
    If the abc.sql script will get run by sql*plus then maybe this can help http://stackoverflow.com/questions/8590826/how-to-create-a-oracle-sql-scrpt-spool-file – A.B.Cade Aug 20 '13 at 13:47
  • thanks for the pointer , I am looking for more example on this ... what term shall i google for ? sql auditing ?? – Lav Aug 20 '13 at 13:48
  • I think i need not add dbms_output.putline() 100 times ... when i run insert in sql*plus then i see messages like "1 rows inserted" or "commit successful" is there a way to direct this output to log file ? – Lav Aug 20 '13 at 13:53
  • @Lav From where abc.sql will be executed? – Jacob Aug 20 '13 at 15:44
  • 3
    You can [spool the output](http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve043.htm) which you control from the script, or you can rely on whoever is running it to capture the output via a redirect. You probably want to explicitly do [`set feedback on` etc.](http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve039.htm) either way in your script, in case they have a login script that defaults them to be off. How and where it will be run will affect exactly what you do though. – Alex Poole Aug 20 '13 at 16:05
  • This might be more than what you are asking for, but you can also change it to be a PL/SQL block and use Oracle I/O Util to create your own log file, then ask them to send that file back to you, or if you just put it in a temp folder you can email it back to you automatically. – Jafar Kofahi Aug 20 '13 at 16:34

1 Answers1

0

Look at the following link.

"dynamic logging with global application context"

You probably don't need to read all of it but read from "global application context" until "a non-interactive test".

In the "start_log" procedure you can open file.

And In the "log" procedure you can write to file instead of:

DBMS_OUTPUT.PUT_LINE(
TO_CHAR(SYSDATE,'HH24:MI:SS ": "') || p_msg);

and then in the "end_log" you can close file.

To open, write and close file in Oracle see Oracle PL/SQL write to file example.

Java SE
  • 2,073
  • 4
  • 19
  • 25