how to capture oracle function returning 2 values in unix
i have an oracle function which returns two values, one is the error message if the function encounters anything and another one which returns a number
i need to capture both
and pass it on to unix shell script
i have an oracle function which returns two values, one is the error message if the function encounters anything and another one which returns a number
Do you mean an Oracle procedure ?
An Oracle function can return only a single value to the caller. The return value is strongly typed via the RETURN keyword, which means you can only return a number.
The error message you are talking about is thrown in case of an exception. It is not a return value.
Quote:
i need to capture both
and pass it on to unix shell script
...
If you leave out the exception section from the function, then maybe something like this -
should assign the returned number or the error message to x.
Here <login_credentials> are your Oracle login credentials in the form username/password@SID and <your_function> is the invocation of your function that returns a number.
HTH,
tyler_durden
Last edited by durden_tyler; 04-05-2010 at 10:58 AM..
As Durden indicates functions typically return a value. Even in languages such as Perl which can return arrays and lists they still return these values as a single entity.
With that said I'd like to ask a question. Do you know if the error value comes back from STDERR because the Oracle command fails?
Rather than waiting for an answer I'll assume the above to be the case where an error message is written to STDERR and the command returns a non-zero value as status. Here's some test code. The first script, 'test9a.sh', calls a second script, 'test9b.sh', which can return an error. The error is returned if you specify 'error' as the first parameter to the first script.
Try it out to see if these scripts emulate your situation.
Last edited by m1xram; 04-06-2010 at 08:39 PM..
Reason: Add scripts and answer
Hi
I am pretty confused in returning and capturing multiple values
i have defined a function which should return values "total, difference"
i have used as
#!/usr/bin/ksh
calc()
{
total=$1+$2
echo "$total"
diff=$2-$1
echo "$diff"
}
I have invoked this function as
calc 5 8
Now i... (2 Replies)
Hi,
I want to execute an oracle function from unix script so for that I created a sample oracle function as below:
create or replace
function test_fn(test_date out varchar2)
RETURN varchar2
IS
BEGIN
select to_char(sysdate,'DD-MON-YY') into test_date from dual;
return test_date;... (5 Replies)
Hi
i am calling a stored procedure from unix shell like this call
test_proc('0002','20100218');
the stored procedure was giving output
like this dbms_output.put_line(' processed earlier');
i want to see the output in the unix shell where i called.
Thanks
barani (6 Replies)
Hi All
In my script, I can call on several functions. I have a logging function that is called by any of these functions. What I would like is some way of identifying which function I am using and pass this to the log function as some parameter.
Is there some built in command or way of... (3 Replies)
hi
how can I return multiple values from a C function. I tried the following:
#include <stdio.h>
void foo(int id, char *first_name, char *last_name)
{
/*
this is just an example to illustrate my problem... real code makes
use of the "id" parameter.
*/
first_name = (char... (8 Replies)
Hi all,
I am very new to BASH shell programming. I need to return an integer from a function to the caller function. I did this:
but it keeps giving me wrong return:
Can someone help me out here, please?
Thanks (2 Replies)
Hello Experts
how do I get a output value from Oracle to Unix . Please give me sample Unix codings
Scenario (Filne name : test.sh <argument > )
---------
1. Passing argument from unix to sql
2. connecting to Oracle & from oracle get output
3. Pass the output to ... (3 Replies)
I have two string returning function in ESQL/C
char *segment_name(lbuffer)
char *lbuffer;
{.....
and
char *get_bpdvalue(f_name)
char *f_name;
{......
both declared above main()
char *get_bpdvalue();
char *segment_name();
my problem is segment_name works on sprintf and strcpy... (5 Replies)