run sql script from shell script

Question

I have the below shell script that calls an sql script. The problem is it calls the sql script, i enter the username,password ,dbname and after that it creates the table but it doesn't exit from the sql prompt.

test.sh

#!/usr/bin/ksh


sql_test=test.sql

$ORACLE_HOME/bin/sqlplus /nolog @${sql_test}

exit

test.sql

CONNECT &&usr/&&password@&&dbname


CREATE TABLE report1
(
    product_code                       VARCHAR2(30)
  , test_inc                           NUMBER(20,2)

);

Answer 1

Shouldn't you also put an exit statement in test.sql ?

Answer 2

Or you could use a HERE document to eliminate the need to put the exit command into the SQL file:

$ORACLE_HOME/bin/sqlplus /nolog << HERE
@${sql_test};
exit sql.sqlcode
HERE

Share and enjoy.