Shell Programming and Scripting

Hello All,

I have exactly same issue @vikas_trl had in following link:

Control not returning from Sqlplus to calling UNIX shell script.


I wonder if he or somebody else could find the issue's cause or the solution.

Any help would be appreciated!

PD: The solution provided on that thread is not even close.

Moderator's Comments:

edit by bakunin: fixed your link

I have no Oracle DB system at hand to test, so the following will only be general help.

First, the many different levels of "redirections" (you have a here-document calling a subshell depending on a shell variable of dubious [that is: not anywhere defined in the script part posted], etc..) which make me wary about the outcome.

I'd start with stripping the SQL down to something very simple, try to call that from a shell script and only increase gradually the complexity of my calling mimic. This might also shed some light on where exactly the fragile thing vikas_trl whipped together might hae gone awry.

I hope this helps.

bakunin

I think the person who replied to the OP of that thread was on the right track.
The OP did not have the "exit" command in their sql script ($v_Input_File).
Once the anonymous PL/SQL block has been executed, you need to exit from sqlplus otherwise it typically waits for the next input.

So I guess any of the following ways should work:
(a) Put an "exit" command in the sql script after the PL/SQL block.

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;
/
EXIT
EOF
...

(b) Or pipe the exit to the sqlplus command like so:

Code:

 echo exit | sqlplus -s "$Oracle_User/$Oracle_Pwd@$Oracle_Conn_String" <<EOF  >> $v_Log 2>&1
...

or

Code:

 exit | sqlplus -s "$Oracle_User/$Oracle_Pwd@$Oracle_Conn_String" <<EOF  >> $v_Log 2>&1
...

@OP: did you try the suggestion in that thread?
Does your shell script wait for the sqlplus process to complete after the database has been committed?

Thank you all for your responses.

I tried the "exit" solution but how @vikas_trl described
if does not return control to calling shell when it is a long time
running pl-sql block.

It works fine for a short time running block.

It is not a matter of exit command appending.

Ok, fair enough.
Can you post your scripts here, in order to show what exactly you are trying to do?