Compiling n number of SQL files in loop

Shell Programming and Scripting
1

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).

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.

2

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:

$(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

3

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

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:

sqlplus uname/pass@Service>>SQLLOG <<-ENDOFSQL

or

sqlplus uname/pass@Service>>SQLLOG <<- ENDOFSQL

Regards,
Alister