Visit Our UNIX and Linux User Community


Execute and log each statement/block SQL file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Execute and log each statement/block SQL file
# 1  
Old 06-06-2011
Execute and log each statement/block SQL file

Hi friends,

I would like to get some help on the following requirement. I have a SQL file with following things,
Code:
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  
Old 06-06-2011
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:
Code:
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


Previous Thread | Next Thread
Test Your Knowledge in Computers #694
Difficulty: Medium
SCO Skunkware, often referred to as simply "Skunkware", is a collection of open-source software projects ported, compiled, and packaged for free redistribution in SCO operating environments.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to pass tablenames from a file to shell script to execute create statement in DB2

Hi, I am new to Shell Scripting, and I need to create nicknames for 600 tables in db2. I have the file names in a text file and i have to pass these table names to a shell script create nicknames in db2. Can some one please help me in this regard. (1 Reply)
Discussion started by: kamalanaatha
1 Replies

2. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

3. Shell Programming and Scripting

Executing sql statement from .sh file

Hi, How to execute sql statements from the .sh file ?? Means, when we run .sh file then the sql statements within it should be get executed one by one from the sqlplus With Regards (3 Replies)
Discussion started by: milink
3 Replies

4. Shell Programming and Scripting

Creating a .sh script to execute an SQL file

Hello I'm hoping someone may be able to help. I'm absolutely brand new to these shell scripts and have tried to bash bits together from the little learnt but my final script still doesn't work. Sorry if something similar is already posted but couldn't find anything existing close enough to help... (1 Reply)
Discussion started by: Dan27
1 Replies

5. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

6. Shell Programming and Scripting

TO execute .sql 2005 query file in shell script

Hi, I know in oracle a .sql file is called by @ <path> /<filename>. But how to call in sql 2005, I am opening the sql sessionwith sqsh, is there any command to execute there a .sql file (query in sql 2005) in K shell script. (0 Replies)
Discussion started by: n2ekhil
0 Replies

7. Shell Programming and Scripting

Creating an sql statement from a file. Problem with '

Hi, I am trying to create sql statements from a file, but I have a problem with ': This is what I do: cat filex.txt | awk -F: '{print $1,"A","and personnavn like",$5}' | sed -e "s/^/select bruker.brukernavn, person.personnavn from bruker, person where brukernavn like '/" -e "s/$/' and... (2 Replies)
Discussion started by: hannem
2 Replies

8. HP-UX

SQL statement output to Log file-How?

Hi all, I need to bring the message to log file.Teradat/Hp-Ux script: ----- ### Update Log Table bteq <<- EOC .run file ${SRC_DATA}/logon.txt .run file ${SRC_DATA}/dbstagebteq.txt .set format off .set foldline off all .set sidetitles off ... (1 Reply)
Discussion started by: vsubbu1000
1 Replies

9. Shell Programming and Scripting

How to execute a .sql file with shell script

hi everybody... can anyone help me in executing the .sql file with shell scripting.... thanx in advance (2 Replies)
Discussion started by: abuanas
2 Replies

10. Shell Programming and Scripting

How can I get an if statement to execute based on number of lines in a file?

I need to have an if statement in a script to run if there are certain processes running. Easiest way I can see to do this is to run a ps and grep the results based on what I am looking for: $ ps -ef | grep wtrs --- webtrend 5046 1 0 May 12 ? 0:28 /webtrends/versions/6.1/wtrs_ui... (6 Replies)
Discussion started by: LordJezo
6 Replies

Featured Tech Videos