General
cancel
Showing results for 
Search instead for 
Did you mean: 

Return value from Oracle function

MAYIANAN
Advisor

Return value from Oracle function

Hi,

I am calling a oracle function in unix. I have tried like

retval=`echo "declare var number
begin var:=function();
dbms_output.put_line(var);
end;" |sqlplus -s `

I have dbms out inside the function also. the retval has all the outputs in it. Is there any other way to capture the return value alone. select function() from dual; is not working since function is doing DML operation.
4 REPLIES
Malcolm Leckie
Occasional Advisor

Re: Return value from Oracle function

Hi

Your problem comes from not enabling the output from sqlplus. The following script will do what you want and enables and disables all the bits you should do by default

retval=`echo "set serveroutput on
set echo off verify off pages 0 feedback off heading off
execute dbms_output.put_line(f_number());
exit;" |sqlplus -s `
echo $retval


The various SET options are documented in the Oracle SQL*Plus manual http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1011230

Malcolm
Hein van den Heuvel
Honored Contributor

Re: Return value from Oracle function

Do I understand you correctly that you the output from is indeed happening and captured in 'retval' as a piece of string.

Now you want a second, seperated value returned. Right? Well, for a limited range of values you can use the 'exit xxx' in sqlplus and pick that up in the shell in '$?'

You may want to combine that with "whenever sqlerror exit xxx"

Google for "sqlplus exit status" surely will render useful hints !? Had you not tried that first?

hth,
Hein.

MAYIANAN
Advisor

Re: Return value from Oracle function

Thanks guyz for your reply.

Malcolm Leckie, I have enabled the ouput from sqlplus. But not written in my sample code. I am having many dbms_out from the function and i am getting all the ouputs along with return code in this retval

Hein van den Heuvel, I could not able to use whenever sqlerror. Since i need to capture the value that the function returns. $? just says the status of previous statement execution right and it will not return the value that oracle function returns.

I have handled the situation like redirecting all the output from function along with return code like dbms_output_put_line('RETCODE'||retval) and from shellscript i grep for RETCODE and used the retval.
MAYIANAN
Advisor

Re: Return value from Oracle function

Already replied