Visit The New, Modern Unix Linux Community


Control not returning from Sqlplus to calling UNIX shell script.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Control not returning from Sqlplus to calling UNIX shell script.
# 1  
Control not returning from Sqlplus to calling UNIX shell script.

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:
Mod Comment edit by bakunin: fixed your link

Last edited by bakunin; 04-10-2017 at 03:40 PM..
# 2  
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
# 3  
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?
# 4  
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.
# 5  
Quote:
Originally Posted by RicardoQ
...
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?

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #362
Difficulty: Medium
The sed command first appears in Version 5 AT&T UNIX.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

SQLPLUS calling Via Script

Hello All, Could you please help me if i am doing anything wrong in below script, especially the sqlplus part performance wise or anything else i could improvise in the script. Thank you. #!/bin/ksh ## Batch Obj Id MP_BCH_OBJ_ID=$1 PASS=$2 partition=$3 ## script dir... (6 Replies)
Discussion started by: Ariean
6 Replies

2. Shell Programming and Scripting

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. cat > $v_Input_File 2>>$v_Log << EOF BEGIN EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DML PARALLEL 16'; EXECUTE... (1 Reply)
Discussion started by: vikas_trl
1 Replies

3. 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... (1 Reply)
Discussion started by: Hypesslearner
1 Replies

4. 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... (4 Replies)
Discussion started by: Navin_Ramdhami
4 Replies

5. 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. (2 Replies)
Discussion started by: boopathyvasagam
2 Replies

6. 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... (8 Replies)
Discussion started by: pradeep7986
8 Replies

7. 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 Replies)
Discussion started by: buechler66
5 Replies

8. 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:... (24 Replies)
Discussion started by: venkatesh_sasi
24 Replies

9. 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... (11 Replies)
Discussion started by: satguyz
11 Replies

10. 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 Replies)
Discussion started by: suds19
2 Replies

Featured Tech Videos