- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Return value from Oracle function
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2009 12:33 AM
тАО03-12-2009 12:33 AM
Return value from Oracle function
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-13-2009 03:06 AM
тАО03-13-2009 03:06 AM
Re: Return value from Oracle function
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-13-2009 03:36 AM
тАО03-13-2009 03:36 AM
Re: Return value from Oracle function
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-13-2009 08:10 AM
тАО03-13-2009 08:10 AM
Re: Return value from Oracle function
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-13-2009 08:23 AM
тАО03-13-2009 08:23 AM