Sponsored Content
Top Forums UNIX for Dummies Questions & Answers Reurning Oracle Proc out parameter Post 302300712 by Zdnas_79 on Tuesday 24th of March 2009 09:25:50 PM
Old 03-24-2009
Reurning Oracle Proc out parameter

Hi. I need sime help with a unix script. The script runs a Oracle Procedure which then runs
some child procedures which are autonomous.
When the child procedures fail, the sql error is not passed back to the parent procedure -
i.e all the othert child procedures run.
I want to execute the parent proc using a unix script and capture the fact that a child proc
has failed. I do this by returning a status code of 0 or -1 from the child proc and use that
in teh parent proc to determine a failure. I need to capture this out parameter from
the parent proc and pass it to a Unix variable.
My sample code is below:

#! /usr/bin/ksh
export ORACLE_HOME=/apps/oracle/product/10.2.0.4
export ORACLE_SID=test
cd $ORACLE_HOME/bin
sqlplus user/pwd@$ORACLE_SID << END_OF_SQL
whenever sqlerror exit 1
set serveroutput on;
DECLARE
P_RUNDATE DATE;
v_STATUS DATE;

begin
P_RUNDATE := TO_DATE('$1','DD/MM/YYYY');
PKG_TEST.TEST2(P_RUNDATE => P_RUNDATE,P_STATUS => v_STATUS);
end;
/
exit;
END_OF_SQL
$retcode=$?
echo $retcode
print end

Any help will be appreciated.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

how do I make dynamic parameter names? Or get the value of a parameter evaluated twi

Say I write something like the following: var1=1 var2=2 for int in 1 2 do echo "\$var$int" done I want the output to be: 1 2 Instead I get something like: $var1 $var2 (2 Replies)
Discussion started by: Awanka
2 Replies

2. Shell Programming and Scripting

Unix call to Oracle PL/SQL pkg/store.proc

HI, I'm trying to get this right, please can you help. In my unix korn shell script, I call an oracle stored proc within a package and I specify 3 parameters, 2 of which are IN OUT parameters (i.e. I expect the stored proc to change them and return them back to me). Does the unix code... (7 Replies)
Discussion started by: csong2
7 Replies

3. UNIX for Dummies Questions & Answers

_/proc/stat vs /proc/uptime

Hi, I am trying to calculate the CPU Usage by getting the difference between the idle time reported by /proc/stat at 2 different intervals. Now the 4th entry in the first line of /proc/stat will give me the 'idle time'. But I also came across /proc/uptime that gives me 2 entries : 1st one as the... (0 Replies)
Discussion started by: coderd
0 Replies

4. UNIX for Dummies Questions & Answers

Help Passing An Oracle parameter to a unix shell.

I have an Oracle concurrent program that I'm passing a parameter to a unix shell script. An example value of the Oracle parameter is PO_TOP. The Oracle parameter represents the unix env var PO_TOP, meaning, on the unix side there is env var called PO_TOP (ex value: /oradev/apps/po/11.0.3/). My... (7 Replies)
Discussion started by: Mark_Wright
7 Replies

5. Programming

how to obtain a persistent oracle connection in proc*c pthread

Hi all, I have problem to set up a persistent oracle connection in child thread. We have a massive batch process dealing with large data. Application was written and impossible to be rewritten. I am trying to extract the most time consuming data fetching and put it into a dedicated child... (3 Replies)
Discussion started by: ora-dev
3 Replies

6. Shell Programming and Scripting

Command that takes one parameter and then searches for the passed in parameter

Hi I am looking for a unix command or a small shell script which can takes one parameter and then searches for the passed in the parameter in any or all files under say /home/dev/ Can anyone please help me on this? (3 Replies)
Discussion started by: pankaj80
3 Replies

7. Shell Programming and Scripting

To call Oracle procedure by reading parameter file in UNIX

hi Guys, is there a way to pass parameter into oracle store procedure by reading date range in file and increment accordingly. Something like this file.txt 01-JAN-2015 03-JAN-2015 sqlplus -s user/pwd@DB execute TEST( to_date( '01-JAN-2015, 'dd.mm.yyyy' ), to_date( '03-JAN-2015', ... (1 Reply)
Discussion started by: rohit_shinez
1 Replies

8. Shell Programming and Scripting

Parallel execution of Oracle PROC in UNIX

Hi, Trying to run the Oracle Proc in parallel, but not working.. below code runs each call of the procedure and its coming out of the loop before its completion and printing the last statement! not waiting for the sqlplus to complete. for name in `cat abc.txt` do (sqlplus -s ... (3 Replies)
Discussion started by: ATWC
3 Replies

9. Shell Programming and Scripting

parameter issue in oracle shell script

Hi All, I am new to this code and shell scripting. I have code like below CREQID=`echo $* | awk '{gsub(/"/, ""); print$9}'` REQID=$3 echo CREQID=$CREQID I have another parameter added to the report/program(interchanged) 1st one is now, order_no, 2nd one is CREQID. Now if... (2 Replies)
Discussion started by: Mist123
2 Replies

10. UNIX for Beginners Questions & Answers

Passing output parameter(Oracle) to variable in ksh Script

Hello, I have researched and tried many way to pass OUT parameter to be stored in variable in KSH Script.Still not success, please help. Here is my Store Procedure. create procedure testout3(v_in varchar2,v_out OUT integer) as begin v_out := 1; end; Here is my testvout.ksh #!/bin/ksh... (1 Reply)
Discussion started by: palita2601
1 Replies
ptree(1)																  ptree(1)

NAME
ptree - print process trees SYNOPSIS
/usr/bin/ptree [-a] [-c] [-z zone] [pid | user] ... ptree prints the process trees containing the specified pids or users, with child processes indented from their respective parent pro- cesses. An argument of all digits is taken to be a process-id, otherwise it is assumed to be a user login name. The default is all pro- cesses. The following options are supported: -a All. Print all processes, including children of process 0. -c Contracts. Print process contract memberships in addition to parent-child relationships. See process(4). This option implies the -a option. -z zone Zones. Print only processes in the specified zone. Each zone ID can be specified as either a zone name or a numerical zone ID. This option is only useful when executed in the global zone. The following operands are supported: pid Process-id or a list of process-ids. ptree also accepts /proc/nnn as a process-id, so the shell expansion /proc/* can be used to specify all processes in the system. user Username or list of usernames. Processes whose effective user IDs match those given are displayed. Example 1: Using ptree The following example prints the process tree (including children of process 0) for processes which match the command name ssh: $ ptree -a `pgrep ssh` 1 /sbin/init 100909 /usr/lib/ssh/sshd 569150 /usr/lib/ssh/sshd 569157 /usr/lib/ssh/sshd 569159 -ksh 569171 bash 569173 /bin/ksh 569193 bash The following exit values are returned: 0 Successful operation. non-zero An error has occurred. /proc/* process files See attributes(5) for descriptions of the following attributes: +-----------------------------+-----------------------------+ | ATTRIBUTE TYPE | ATTRIBUTE VALUE | +-----------------------------+-----------------------------+ |Availability |SUNWesu | +-----------------------------+-----------------------------+ |Interface Stability |See below. | +-----------------------------+-----------------------------+ The human readable output is Unstable. The options are Evolving. gcore(1), ldd(1), pargs(1), pgrep(1), pkill(1), plimit(1), pmap(1), preap(1), proc(1), ps(1), ppgsz(1), pwd(1), rlogin(1), time(1), truss(1), wait(1), fcntl(2), fstat(2), setuid(2), dlopen(3C), signal.h(3HEAD), core(4), proc(4), process(4), attributes(5), zones(5) 11 Oct 2005 ptree(1)
All times are GMT -4. The time now is 04:30 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy