sqlplus exit statement prematurely terminates unix script

[tngoe]

#!/bin/ksh
echo ""
echo "Enter person ID:"
read person_id
sqlplus -silent '/nolog'<<end_sql 1
connect useract/passwd
start $BANNER_LINKS/gurlsid.sql
$person_id
FAISMGR
end_sql1
cat person.lst >person_list
sqlplus -silent '/nolog'<<end_sql 2
connect useract/passwd
start $BANNER_LINKS/gurlsid.sql
$person_id
FIMSMGR
end_sql2
cat person.lst >>person_list
sqlplus -silent '/nolog'<<end_sql 2b
connect useract/passwd
spool person
set feedback off
column "Table Name" format a12
column "Column Name" format a30
column "Count" format 9999
column "Condition" format a32
select 'FOBTEXT' "Table Name",
'FOBTEXT_CODE' "Column Name",
count(fobtext_c ode) "Count",
'WHERE FOBTEXT_DTYP_SE Q_NUM = 73' "CONDITION"
from fobtext
where fobtext_code = '$person_id'
and fobtext_dtyp_se q_num = 73
having count(fobtext_c ode) > 0;
spool off
exit
end_sql2b
#--------------------------
cat person.lst >>person_list
echo ""
echo "finish my program"

*******

I am runing Oracle 10.2 on Sun OS.

The last three statements of this script did not execute at all. The last person.lst has something, but it didn't append to person_list. The last two echo didn't display on the screen either. I think somehow when exiting from sqlplus, it just exited out of the script completely.

Can someone indicate why the last three statements of this script did not carry out?

Thanks,
Trang

[Mondo Tofu]

#!/bin/ksh
echo ""
echo "Enter person ID:"
read person_id
sqlplus -silent '/nolog'<<end_sql 1
connect useract/passwd
start $BANNER_LINKS/gurlsid.sql
$person_id
FAISMGR
end_sql1
cat person.lst >person_list
sqlplus -silent '/nolog'<<end_sql 2
connect useract/passwd
start $BANNER_LINKS/gurlsid.sql
$person_id
FIMSMGR
end_sql2
cat person.lst >>person_list
sqlplus -silent '/nolog'<<end_sql 2b
connect useract/passwd
spool person
set feedback off
column "Table Name" format a12
column "Column Name" format a30
column "Count" format 9999
column "Condition" format a32
select 'FOBTEXT' "Table Name",
'FOBTEXT_CODE' "Column Name",
count(fobtext_c ode) "Count",
'WHERE FOBTEXT_DTYP_SE Q_NUM = 73' "CONDITION"
from fobtext
where fobtext_code = '$person_id'
and fobtext_dtyp_se q_num = 73
having count(fobtext_c ode) > 0;
spool off
exit
end_sql2b
#--------------------------
cat person.lst >>person_list
echo ""
echo "finish my program"

*******

I am runing Oracle 10.2 on Sun OS.

The last three statements of this script did not execute at all. The last person.lst has something, but it didn't append to person_list. The last two echo didn't display on the screen either. I think somehow when exiting from sqlplus, it just exited out of the script completely.

Can someone indicate why the last three statements of this script did not carry out?

Thanks,
Trang

The last SQL was not valid, hence your person.lst file would be empty.
In the list of columns and expressions for the select statement, you have an extra comma.

That would make life a bit difficult for you.

[ENDBA]

#!/bin/ksh
echo ""
echo "Enter person ID:"
read person_id
sqlplus -silent '/nolog'<<end_sql 1
connect useract/passwd
start $BANNER_LINKS/gurlsid.sql
$person_id
FAISMGR
end_sql1
cat person.lst >person_list
sqlplus -silent '/nolog'<<end_sql 2
connect useract/passwd
start $BANNER_LINKS/gurlsid.sql
$person_id
FIMSMGR
end_sql2
cat person.lst >>person_list
connect useract/passwd
spool person
set feedback off
column "Table Name" format a12
column "Column Name" format a30
column "Count" format 9999
column "Condition" format a32
select 'FOBTEXT' "Table Name",
'FOBTEXT_CODE' "Column Name",
count(fobtext_c ode) "Count",
'WHERE FOBTEXT_DTYP_SE Q_NUM = 73' "CONDITION"
from fobtext
where fobtext_code = '$person_id'
and fobtext_dtyp_se q_num = 73
having count(fobtext_c ode) > 0;
spool off
exit
end_sql2b
#--------------------------
cat person.lst >>person_list
echo ""
echo "finish my program"

*******

I am runing Oracle 10.2 on Sun OS.

The last three statements of this script did not execute at all. The last person.lst has something, but it didn't append to person_list. The last two echo didn't display on the screen either. I think somehow when exiting from sqlplus, it just exited out of the script completely.

Can someone indicate why the last three statements of this script did not carry out?

Thanks,
Trang

=============== =====
try adding the following:

sqlplus -silent '/nolog'<<end_sql 2b >/dev/null

[iwantvarun]

I am facing the same issue...

script is terminating in between due to SQL exit

I am trying below script

cd $ROOT_APPLI/kcust/ddl
sqlplus -s e210/$PWD_E210@$DB_N AME <<FIN>
/E210/kcust/ddl
* @USERS_LDAP_SYN CHRO.sql $1 $1
commit;
exit 1;
FIN
cd $ROOT_APPLI/sh

script is terminating at exit line.

Please help