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  
Old 08-03-2015
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.
# 2  
Old 08-04-2015
Put the sql commands in a file, say file.sql, and call the file as below:

Code:
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)

Login or Register to Ask a Question

Previous Thread | Next Thread

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 exactly same issue @vikas_trl had in following link: https://www.unix.com/shell-programming-and-scripting/259854-control-not-returning-sqlplus-calling-unix-shell-script.html I wonder if he or somebody else could find the issue's cause or the solution. Any help would... (4 Replies)
Discussion started by: RicardoQ
4 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
Login or Register to Ask a Question