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.
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)
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)
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)
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)
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)
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)
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)
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)
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)
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
LEARN ABOUT CENTOS
reset
RESET(7) PostgreSQL 9.2.7 Documentation RESET(7)NAME
RESET - restore the value of a run-time parameter to the default value
SYNOPSIS
RESET configuration_parameter
RESET ALL
DESCRIPTION
RESET restores run-time parameters to their default values. RESET is an alternative spelling for
SET configuration_parameter TO DEFAULT
Refer to SET(7) for details.
The default value is defined as the value that the parameter would have had, if no SET had ever been issued for it in the current session.
The actual source of this value might be a compiled-in default, the configuration file, command-line options, or per-database or per-user
default settings. This is subtly different from defining it as "the value that the parameter had at session start", because if the value
came from the configuration file, it will be reset to whatever is specified by the configuration file now. See Chapter 18, Server
Configuration, in the documentation for details.
The transactional behavior of RESET is the same as SET: its effects will be undone by transaction rollback.
PARAMETERS
configuration_parameter
Name of a settable run-time parameter. Available parameters are documented in Chapter 18, Server Configuration, in the documentation
and on the SET(7) reference page.
ALL
Resets all settable run-time parameters to default values.
EXAMPLES
Set the timezone configuration variable to its default value:
RESET timezone;
COMPATIBILITY
RESET is a PostgreSQL extension.
SEE ALSO SET(7), SHOW(7)PostgreSQL 9.2.7 2014-02-17 RESET(7)