Execute and log each statement/block SQL file

Shell Programming and Scripting
1

Hi friends,

I would like to get some help on the following requirement. I have a SQL file with following things,

select 1 from dual;
select user from dual;
select sysdate
from
dual;
BEGIN
PL/SQL Code
END;
/

This file will be saved as sql file. When I run my expected shell script, it should read this sql fille, it should read each SQL statement based on the end of a statement using ';' or a PL/SQL block alone using '/' . If this can happen in loop, I will have the statement in one variable which I can execute using SQLPLUS. This variable will have SQL statement , which I can also write to a csv file.
So after executing my shell script, I will execute 4 statements ( 3 sql and 1 Pl/SQL) and write 4 entries to a CSV with date and statement.

Any help is appreciated.

SSN

2

Well, a lot of data comes out, so I put in a dummy first field and use sed to post-process it. BTW, CSV escapes " to "" and needs " around any field with a ',', so something like this:

echo " . . . select 'dAtA' as X, ......" | sqlplus . . . |sed '
  /^X /d
  /^[-|]*$/d
  s/^dAta *| *\(.*\)|$/\1/
  t data
  w log_file
  d
  :data
  s/  *|//g
  s/"/""/g
  s/\([^|,]*,[^|]*\)/"\1"/g
  s/ *| */,/g
 ' >data_file.csv