?
Solved

Return 2 data values (integer, varchar2) from Oracle stored Function

Posted on 2007-03-28
6
Medium Priority
?
888 Views
Last Modified: 2010-08-05
I'm new developer in Oracle. So please bear if it's too basic question and would appreciate your patience in answering.   My requirement is to get 2 parameter (Integer,varchar2)values back from Oracle Stored Function. Could someone give me an example on how to accomplish this.
      
0
Comment
Question by:vasudev_v
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18809493
A function only supports one return value.  However, a stored procedure supports multiple output parameters.
0
 
LVL 15

Accepted Solution

by:
OMC2000 earned 150 total points
ID: 18809577
You could convert your integer value to string and concatenate it with your string with some seprater character,  '#' for instance, then in caller procedure or function you could extract both values with instr and substr builtin functions
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18809606
You can return a delimited character string something like:
----------------------
create or replace function myFunc return varchar2
is
      someNumber number := 1;
      someString varchar2(20) := 'some string';
begin
      return to_char(someNumber) || ':' || someString;
end;
/

show errors

select myFunc() from dual;



0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 35

Expert Comment

by:johnsone
ID: 18810824
Just to clarify, out parameters are supported in both functions and procedures.

create function test_func (out_var out varchar2) return number as
begin
out_var := 'test';
return(1);
end;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18811464
True but you can't use a function with an out parameter as part of a select statement.  You have to use it within PL/SQL itself:
----------------------------
var bob varchar2(100)

-- fails
select test_func(:bob) from dual;

--works
declare
      myString varchar2(10);
      myNumber number;
begin
      myNumber := test_func(myString);
      dbms_output.put_line('String: ' || myString);
      dbms_output.put_line('Number: ' || myNumber);
end;
/
0
 
LVL 35

Expert Comment

by:johnsone
ID: 18815518
Not sure of the original intention for use.  Just wanted to point out that it is possible to use out parameters in a function.  May not be practical in this case.

As a more complex way to return more than one value, a ref cursor is possible and can be used in a select statement.

create or replace package test_pack is
 type rcur is ref cursor;
 function test_func return rcur;
end;
/

create or replace package body test_pack is
 function test_func return rcur is
   return_cursor rcur;
 begin
   open return_cursor for select 1, 'test' from dual;
   return return_cursor;
 end;
end;
/
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question