topic Re: Return value from Oracle function in Operating System - HP-UX

Return value from Oracle function

MAYIANAN — Thu, 12 Mar 2009 07:33:46 GMT

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.

Re: Return value from Oracle function

Malcolm Leckie — Fri, 13 Mar 2009 10:06:25 GMT

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

Re: Return value from Oracle function

Hein van den Heuvel — Fri, 13 Mar 2009 10:36:04 GMT

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.

Re: Return value from Oracle function

MAYIANAN — Fri, 13 Mar 2009 15:10:19 GMT

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.

Re: Return value from Oracle function

MAYIANAN — Fri, 13 Mar 2009 15:23:31 GMT

Already replied