Control not returning from Sqlplus to calling UNIX shell script.

Shell Programming and Scripting
1

Hello All,

I have a UNIX script which will prepare anonymous oracle pl/sql block in a temporary file in run time and passes this file to sqlplus as given below.

cat > $v_Input_File 2>>$v_Log << EOF
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DML PARALLEL 16';

EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO $v_Tbl SELECT  ${v_Primary_Key},${v_Col_List} FROM ${v_SRC_DB_NM}.${v_SRC_TBL_NM} T
WHERE SOME_DATE > TO_TIMESTAMP(''$v_START_DTTM'',''YYYY-MM-DD HH24:MI:SS.FF'') 
AND CSOME_DATE <= TO_TIMESTAMP(''$v_END_DTTM'',''YYYY-MM-DD HH24:MI:SS.FF'')';

COMMIT;

END;
/

EOF

#--------------------

sqlplus -s "$Oracle_User/$Oracle_Pwd@$Oracle_Conn_String" <<EOF  >> $v_Log 2>&1
	WHENEVER OSERROR EXIT 9;
	WHENEVER SQLERROR EXIT SQL.SQLCODE;
	SET LINESIZE 32000
	SET FEEDBACK ON
	SET HEADING OFF
	SET ECHO ON
	SET TIME ON
	SET TIMING ON
	SET SERVEROUTPUT ON

	`cat $v_Input_File`
EOF

if [ $? -eq 0 ]
then
   echo "Completed successfully"
   exit 0
else
   echo "Failed"
   exit 1
end if

The issue here is insert query runs around 2.5-3 hours and commits successfully on the oracle side. But the script is waiting indefinitely for return status from sqlplus and neither failing nor succeeding.

The same sqlplus functionality is working fine for queries that take less time like 1hr or so.

If any one can help me in this regard that would be really great.

Thanks in advance.

2

Put the sql commands in a file, say file.sql, and call the file as below:

user@host~>cat file.sql
desc user_tables;
user@host~>echo exit | sqlplus -S user/password @file.sql
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)