Not able to execute many SQL scripts within a shell script

Shell Programming and Scripting
1

I am using HP-UX:

I have written a ksh script where I need to connect to sqlplus and execute few sql scripts. Part of this code is -

sqlplus user/temp1234 <<!

set serverout on
set feedback off
set pages 1000
set colsep ,
set echo off

spool /home/supp1/pks/output.csv
@/home/supp1/pks/a1.sql
@/home/supp1/pks/a2.sql
@/home/supp1/pks/a3.sql
@/home/supp1/pks/a4.sql
@/home/supp1/pks/a5.sql
@/home/supp1/pks/a6.sql
@/home/supp1/pks/a7.sql
@/home/supp1/pks/a8.sql


spool off
exit
!

The problem is only a1.sql is executed and disconnecting from sqlplus. Any idea why remaining scripts a2 to a8 are not being executed ?
Is there any error in the syntax?

2

Hi.

The syntax looks fine.

Does a1.sql file have an exit in it?

3

No. 'a1.sql' has commit at the end

4

Post the contents of the following files:

/home/supp1/pks/output.csv
/home/supp1/pks/a1.sql
/home/supp1/pks/a2.sql

Also, have you posted the entire content of this script ? Is there a "whenever sqlerror..." or "whenever oserror..." sqlplus command in your script ?

tyler_durden

5

Sorry guys, can't share the complete script( its illegal :P). I just verified the code and there are no "whenever sqlerror..." or "whenever oserror..." sqlplus command in the script.

6

Interchange the order of the .sql files and check whether it will struck at the end of first .sql file execution.

7

Fair enough :slight_smile:

Is it SQL or PL/SQL?

If the latter is it being executed?

i.e.

BEGIN
 ....
 ....
EXCEPTION
  WHEN .....
END;
/
8

it si PL/SQL and yes it is being executed in a1.sql(ie: begin....end; ) but in a2.sql there are simply some update statements without begin... end;

could it be problem if it is not included within begin...end; ?

9

No, it's not a problem if it's only SQL in a2.sql.

But in a1.sql, you need a / to execute the PL/SQL, otherwise it's just an anonymous block of code that won't do anything. And nothing else will happen until it gets one.

# cat Test1
sqlplus -s /nolog <<!
conn user/pass@db
set serverout on
set feedback off
set pages 1000
set colsep ,
set echo off

@/home/oracle/admin/bin/SQL1
@/home/oracle/admin/bin/SQL2
exit;
!

# cat SQL1.sql
begin
  dbms_output.put_line( 'OK, lets go...' );
end;

# cat SQL2.sql
select 12345 from dual;

# ./Test1
#

(change SQL1.sql...)
# cat SQL1.sql
begin
  dbms_output.put_line( 'OK, lets go...' );
end;
/

# ./Test1
OK, lets go...

     12345
----------
     12345