how to capture oracle function returning 2 values in unix Post: 302410201

Sponsored Content

Top Forums Shell Programming and Scripting how to capture oracle function returning 2 values in unix Post 302410201 by durden_tyler on Monday 5th of April 2010 09:21:57 AM

04-05-2010

Registered User

Quote:

Originally Posted by trichyselva

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 -

Code:

$
$ cat -n get_tvalue.sh
     1  #!/usr/bin/bash
     2  x=$(sqlplus -s /nolog <<EOF
     3  connect <login_credentials>
     4  set pages 0 feedback off
     5  var n varchar2(10)
     6  exec :n := <your_function>;
     7  print n
     8  exit
     9  EOF)
    10  echo "x => $x"
    11
$
$

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..

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

how to pass values from oracle sql plus to unix shell script

2. Programming

string returning function

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...

3. Shell Programming and Scripting

Values from Oracle to Unix

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 ...

4. Shell Programming and Scripting

returning from a function

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

5. Programming

returning multiple values from a function in C

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...

6. Shell Programming and Scripting

Returning the name of function used

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...

7. Shell Programming and Scripting

how to pass the values to unix shell from the oracle stored procedure.

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

8. Shell Programming and Scripting

unix capture oracle function error

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;...

9. Programming

Function Returning Pointer

Hi guys. how a functions such fdopen, ... can return pointer? are these functions use static memory(variables)?

10. Shell Programming and Scripting

Returning and capturing multiple return values from a function

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...

LEARN ABOUT PHP

oci_set_action

OCI_SET_ACTION(3)														 OCI_SET_ACTION(3)

oci_set_action - Sets the action name

SYNOPSIS

       bool oci_set_action (resource  $connection, string  $action_name)

DESCRIPTION

	Sets the action name for Oracle tracing.

	The action name is registered with the database when the next 'roundtrip' from PHP to the database occurs, typically when an SQL statement
       is executed.

	The action name can subsequently be queried from database administration views such as V$SESSION. It can be used for tracing and  monitor-
       ing such as with V$SQLAREA and DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE.

	The value may be retained across persistent connections.

PARAMETERS

	      o $connection
		-An Oracle connection identifier, returned by oci_connect(3), oci_pconnect(3), or oci_new_connect(3).

	      o $action_name
		- User chosen string up to 32 bytes long.

RETURN VALUES

	Returns TRUE on success or FALSE on failure.

EXAMPLES

       Example #1

	      Setting the action

	      <?php

	      $c = oci_connect('hr', 'welcome', 'localhost/XE');

	      // Record the action
	      oci_set_action($c, 'Friend Lookup');

	      // Code that causes a roundtrip, for example a query:
	      $s = oci_parse($c, 'select * from dual');
	      oci_execute($s);
	      oci_fetch_all($s, $res);

	      sleep(30);

	      ?>

	      // While the script is running, the administrator can see the actions
	      // being performed:

	      sqlplus system/welcome
	      SQL> select action from v$session;

NOTES

       Note

	      Oracle version requirement

	      This function is available when PHP is linked with Oracle Database libraries from version 10 g onwards.

       Tip

	      Performance

	      With  older  versions of OCI8 or the Oracle Database, the client information can be set using the Oracle DBMS_APPLICATION_INFO pack-
	      age. This is less efficient than using oci_set_client_info(3).

       Caution

	      Roundtrip Gotcha

	      Some but not all OCI8 functions cause roundtrips. Roundtrips to the database may not occur  with	queries  when  result  caching	is
	      enabled.

SEE ALSO

       oci_set_module_name(3), oci_set_client_info(3), oci_set_client_identifier(3).

PHP Documentation Group 													 OCI_SET_ACTION(3)

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

how to pass values from oracle sql plus to unix shell script

Discussion started by: trichyselva

2. Programming

string returning function

Discussion started by: jisc

3. Shell Programming and Scripting

Values from Oracle to Unix

Discussion started by: Krishnaramjis

4. Shell Programming and Scripting

returning from a function

Discussion started by: alirezan