Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Compiling n number of SQL files in loop

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compiling n number of SQL files in loop
# 1  
Old 07-12-2010
Compiling n number of SQL files in loop
Trying to compile all SQL files using a shell script. But the below code is not working. Below Code works fine when for loop is not there(commenting line no: 1,2 and 9).

Code:
1. sq_lfile=`ls *.sql`
2. for current_sql_file in $sql_file
3. do
4. sqlplus uname/pass@Service>>SQLLOG << -ENDOFSQL
5. spool DATA_FILE.DAT
6. @ $current_sql_file
7. spool off
8. exit;
9. ENDOFSQL
10. done

Please help.
Last edited by Dip; 07-12-2010 at 09:56 AM.. Reason: code tags, please...
# 2  
Old 07-12-2010
Hi Dip,

It may be just the obvious difference in variable names between lines 1 and 2.

I've always found the following method for running SQL in scripts works:

Code:
$(printf "spool DATA_FILE.DAT\n@$current_sql_file\nspool off"|sqlplus uname/pass@Service>>SQLLOG)

(all one line)

You'll find that DATA_FILE.DAT keeps getting overwritten though.

Cheers,
Ed
Last edited by edstertech; 07-12-2010 at 10:38 AM.. Reason: mistake!
# 3  
Old 07-12-2010
Thanks for reply edstertec. Your one line script is working fine. The variable name mistake was just a typo mistake. In actuall script it is fine. However 'm trying with my code but giving below error:

syntax error at line 18: `end of file' unexpected

If you could tell me why this error is coming that will be really great and helpful for me.
# 4  
Old 07-12-2010
Quote:
Originally Posted by Dip
Code:
sqlplus uname/pass@Service>>SQLLOG << -ENDOFSQL

Is that heredoc redirection a typo as well? Judging from the use of "ENDOFSQL" later in the script, you do not want to use the word "-ENDOFSQL", so I assume that's a typo of:
Code:
sqlplus uname/pass@Service>>SQLLOG <<-ENDOFSQL

or
Code:
sqlplus uname/pass@Service>>SQLLOG <<- ENDOFSQL

Regards,
Alister
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to use for loop to execute multiple .sql files while using SQLPLUS for db connection.?

Hello , Im calling every single file inside my script like 1.sql,2.sql so on it looks so tedious. I want to replace with for loop where every file gets executed. When i use for loop im getting errorUnexpected EOF] , can anyone please help me out in this.. How i can use for loop to invoke my... (6 Replies)
Discussion started by: preethi87
6 Replies

2. Programming

SQL not working in a for loop

Need help. Any reason why the update is not working in this sql: #!/bin/ksh #setup your environment . /opt/lnpsite/nm00/scripts/setup_env nm00 for tn in `cat /home/cpac/Resync/sv_tn.list` do `sqlplus -s ${DB_USERID} << EOF SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF... (5 Replies)
Discussion started by: mrn6430
5 Replies

3. Shell Programming and Scripting

PL/SQL: Specified Number Is Not Valid

Hi I have Unix shell script that invokes PL/SQL procedure. The batch job when executed terminated with the error message:-unlimited: The specified number is not valid for this command.Please let me know what is the root cause of the issue and how to fix the issue. Thanks (1 Reply)
Discussion started by: moonkhan1
1 Replies

4. Shell Programming and Scripting

How to count number of files in directory and write to new file with number of files and their name?

Hi! I just want to count number of files in a directory, and write to new text file, with number of files and their name output should look like this,, assume that below one is a new file created by script Number of files in directory = 25 1. a.txt 2. abc.txt 3. asd.dat... (20 Replies)
Discussion started by: Akshay Hegde
20 Replies

5. Shell Programming and Scripting

For loop for number of files in a folder

Hi All, Need a for loop which should run for number of files in a folder and should pass the file name as parameter to another shell script for each loop. Please help me. Thanks. (2 Replies)
Discussion started by: chillblue
2 Replies

6. Shell Programming and Scripting

UNIX/SQL loop to spool files!!

I want to spool files from unix using a sql script that would take values from another sql query within the KSH script.. unix loop select order_date from date_tbl for each order_Date i need to call this spool script with the value sqlplus scott/tiger@order_db @/ordspool/order_date.sql... (2 Replies)
Discussion started by: vr23
2 Replies

7. Shell Programming and Scripting

Sql in a linux loop

Hi i want to run a query in loop(unix loop) every 2 hours and spool the result to a file. i am trying but getting error here is the script $ while true do sqlplus ank/ank<<! spool ank.lst select * from v$database; exit sleep 7200 done :D (2 Replies)
Discussion started by: ankurk
2 Replies

8. Shell Programming and Scripting

Help shell script to loop through files update ctl file to be sql loaded

I am currently trying to find a way to loop through files in a given directory and for each file modify a ctl file and sql load it. I have been using the sed command to change the infile, badfile parameters of the control file. I have not yet tried to sql load it. Requirement: files are ftp to... (1 Reply)
Discussion started by: dba_nh
1 Replies

9. UNIX for Dummies Questions & Answers

Problem with while loop and SQL

Connected to oracle database sqlplus << EOF $CONNECTSTR set heading off set trimspool on set feedback off select ID,DATE from sysadm.TEST where VALUE = 'A' order by ID; value_id = ID value_date = DATE EOF 1. Is it possible to reference the values, ID,DATE in unix shell script. 2.... (20 Replies)
Discussion started by: nandajk
20 Replies

10. Shell Programming and Scripting

Unix-Sql Loop error

Hi, I am getting the error "No matching <<", when i run the below. Is it that we can't execute SQL in a Unix loop. I am executing a SQL in a loop. Is it the EOF is written wrongly. . /opt/app/wlsconfigv61/domains/profiles/oracleV901.profile export DBUSER=ecdb01 set -A DBINSTANCE... (3 Replies)
Discussion started by: manu_byc
3 Replies
Login or Register to Ask a Question