| Script to run oracle packeges [message #235252] |
Fri, 04 May 2007 09:22  |
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 #235268 is a reply to message #235263] |
Fri, 04 May 2007 10:33  |
ThomasG
Messages: 3211
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
$
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
