Home » Infrastructure » Unix » Script to run oracle packeges
Script to run oracle packeges [message #235252] Fri, 04 May 2007 09:22 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
Are there any scripts available to run oracle procedures and packages from UNIX. ?

Using the following script but it looks like it doesn't wait to get return code back from oracle and until the procedure ends. ? Logging is done in oracle itself by UTL_FILE.. How do we manage running oracle packages from unix ?

# File Name: execute_oracle_package.prog
# Description: Executes oracle process
#
# History: 05/01/2007, version 1.0
#


fn_PROCESS_DATA()
{
echo "Processing Staged Data"
sqlplus -s <<process
$fcp_login$fcp_db
exec MYSCHEMA.PKG_ENR.EXTRACT_ENR_DATA(pDirLog => '$LOG', pDirOutput => '$OUT');
exit;
process
}

# ************ MAIN CALL *************
# parse the parameters
echo "=========================\n"
echo "Starting Executing Process " `date "+%m/%d/%y %H:%M:%S"` "\n"
echo "=========================\n"
#

#set the path names

LOG=ENR_LOG
OUT=ENR_OUTPUT

fn_PROCESS_DATA

echo "Completed executing process " `date "+%m/%d/%y %H:%M:%S"` "\n"
Re: Script to run oracle packeges [message #235263 is a reply to message #235252] Fri, 04 May 2007 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How do we manage running oracle packages from unix ?
You manage them any way you want to & write the appropriate script(s) to do so.
Re: Script to run oracle packeges [message #235268 is a reply to message #235263] Fri, 04 May 2007 10:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, a few things might help you :

You can finish the sql script with "exit 1 / exit 0" for example, which gets passed as errorcode to the OS.

You can use dbms_output.put_line() to write output to stdout.

Another thing to use is "whenever sqlerror..." which handles the way errors are handled.

If you start the SQL with WHENEVER SQLERROR EXIT FAILURE for example, the moment an error or an user defined exception is raised SQLPLUS terminates with errorlevel.

Example :

$ sqlplus scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on Fr Mai 4 17:30:05 2007
(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Verbunden mit:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> WHENEVER SQLERROR EXIT FAILURE
 
BEGIN
raise_application_error(-20100,'YO');
END;
/SQL> SQL>   2    3    4    5    6
BEGIN
*
FEHLER in Zeile 1:
ORA-20100: YO
ORA-06512: at line 4


Verbindung zu Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production beendet
$ echo $?
1
$




Re: Script to run oracle packeges [message #235309 is a reply to message #235252] Fri, 04 May 2007 19:24 Go to previous message
yog_23
Messages: 79
Registered: March 2007
Member
Thank you.. that helps Smile
Previous Topic: Natural/Adabas from mainframe to unix
Next Topic: Regarding MAILX
Goto Forum:
  


Current Time: Sat Dec 10 20:42:14 CST 2016

Total time taken to generate the page: 0.15815 seconds