Control not returning from Sqlplus to calling UNIX shell script. Post: 302951198

Sponsored Content

Top Forums Shell Programming and Scripting Control not returning from Sqlplus to calling UNIX shell script. Post 302951198 by vikas_trl on Monday 3rd of August 2015 10:30:18 PM

08-03-2015

Registered User

Control not returning from Sqlplus to calling UNIX shell script.

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.

Code:

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.

vikas_trl

View Public Profile for vikas_trl

Find all posts by vikas_trl

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

calling sqlplus from shell

Hi All, I am executing the following code :- sqlplus -s ${DATABASE_USER} |& print -p -- 'set feed off pause off pages 0 head off veri off line 500' print -p -- 'set term off time off serveroutput on size 1000000' print -p -- "set sqlprompt ''" print -p -- "SELECT run_command from...

2. Programming

Returning Strings from C program to Unix shell script

Hi, Iam calling a C program from a Unix shell script. The (C) program reads encrypted username/password from a text file , decrypts and returns the decrypted string. Is there any way i can return the decrypted string to Unix shell program. My shell script uses the output of the program to...

3. Programming

Returning Strings from C program to Unix shell script

Hi, I'm having a requirement where I need to call a C program from a shell script and return the value from the C program to shell script. I refered a thread in this forum. But using that command in the code, it is throwing an error clear_text_password=$(get_password) Error: bash:...

4. UNIX for Advanced & Expert Users

Returning a value to a calling script

Hi. I'm trying to call a script named checkForFile.sh from within my main script named master.sh. In checkForFile.sh I want to set a value to the variable fileExist, but then I want to reference the value in the master.sh script. Whenever I attempt this the echo statement is just blank. ...

5. Shell Programming and Scripting

Error in calling store procedure using SQLPLUS in unix

Hi, I am facing the following error in calling the stored procedure from SQLPLUS in unix environment. SQL> set serveroutput on SQL> var store number; SQL> exec test_proc(:store, 200); BEGIN TEST_PROC(:store, 200); END; * ERROR at line 1: ORA-01858: a non-numeric character was found...

6. UNIX for Dummies Questions & Answers

calling a unix shell script from sqlplus

I want to execute a shell script from sqlplus prompt and get its output back to sqlplus. Is this possible? if yes just give me an example for doing that.

7. Shell Programming and Scripting

Calling sqlplus from Korn shell heredoc issue

Hi, I am facing an issue wherein some temporary files (here docs) are getting created in /tmp and are not getting deleted automatically. When i check the list of open files with below command i can see one file is getting appended continuously.(In this case /tmp/sfe7h.34p) The output is...

8. Shell Programming and Scripting

Control from UNIX script is not returning to the Parent program

Hi All, My program flow is as below Windows batch -- > Cygwin batch --> zsh script There are multiple Cygwin batch scripts that are called from Windows batch file . But when i am executing the first cygwin batch script the control goes to the zsh file and executes and stoping from...

9. Shell Programming and Scripting