Home » SQL & PL/SQL » SQL & PL/SQL » multiple out paramaters
multiple out paramaters [message #185660] Wed, 02 August 2006 22:52 Go to next message
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
Member
Hi all

I have a function that I want to use to return some data. The function has multiple out parameters, and this is causing me a few headaches. The function is as follows...

FUNCTION f_get_next_anniversary_date(p_assignment_id    IN  NUMBER,
                                     p_effective_date   IN  DATE,
                                     p_absence_category IN  VARCHAR2,
                                     p_anv_start_date   OUT NOCOPY DATE,
                                     p_message          OUT NOCOPY VARCHAR2,
                                     p_error            OUT NOCOPY VARCHAR2) RETURN DATE
IS
...




I think in order to call this function I need to do so inside a block

eg

declare
v_anv_start_date DATE;
v_message VARCHAR2(100);
v_error VARCHAR2(100);
v_ret_val varchar2(100);
begin
v_ret_val := xxpay_leave_accrual_pk.f_get_next_anniversary_date(201,trunc(sysdate),'AUAL',v_anv_start_date,v_message, v_error);
end;


however I am not really sure how to
a) pick which of the out parameters I am going to use to set v_ret_val
b) how to then use v_ret_val to return some data to a SQL query. Eventually what I am trying to do here is be able to call this function to return v_anv_start_date from an SQL query...

This approach is only my best guess at what I need to do in order to get this data, so may well be incorrect!

thanks in advance

cameron



Re: multiple out paramaters [message #185676 is a reply to message #185660] Thu, 03 August 2006 01:18 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What I feel You are On Utter confusion ...
Thats why you are trying to do big mistakes .

1. It is tru that A function can have Out poarameter.
   If you are going to use this function in an an SQL quey ,  
   It   wont allow you to use OUT parameter.

2. Though the f_get_next_anniversary_date's return type is DATE 
You are using v_ret_val (The variable you used to collect the 
return value) is of varcahr2 type.  


Thumbs Up
Rajuvan



[Updated on: Thu, 03 August 2006 01:18]

Report message to a moderator

Re: multiple out paramaters [message #185869 is a reply to message #185660] Thu, 03 August 2006 22:35 Go to previous messageGo to next message
crmoffat
Messages: 33
Registered: November 2005
Location: Australia
Member
cheers Rajuvan


think I figured out what I need to do (excuse the non helpful names in the functions below...was just playing around when I created them)

created this object and table of objects...

create or replace TYPE xxoss_r_ann_date AS OBJECT (
assign_id number,
anv_date date,
message varchar2(1000),
m_error varchar2(1000)
)


then this

create or replace type xxoss_r_ann_dates as table of xxoss_r_ann_date


then created this function to 'populate' the objects

create or replace function xxoss_r_test_object_v2(p_date in date, p_leave_type in varchar2) return xxoss_r_ann_dates
as
v_anv_start_date DATE;
v_message VARCHAR2(1000);
v_error VARCHAR2(1000);
x_ret_val varchar2(100);
xxran xxoss_r_ann_dates := xxoss_r_ann_dates();
begin

for x in (SELECT * FROM per_assignments_f WHERE p_date BETWEEN effective_start_date
          and effective_end_date
          and primary_flag = 'Y'
          and assignment_type = 'E')

loop

x_ret_val := xxpay_leave_accrual_pk.f_get_next_anniversary_date(x.assignment_id,p_date,p_leave_type,v_anv_start_date,v_message, v_error);
xxran.extend;
xxran(xxran.count) := xxoss_r_ann_date(x.assignment_id,v_anv_start_date, v_message, v_error);

end loop;


return xxran;
end;




for my purposes works fine, can call this function to populate
xxoss_r_ann_dates and then use that as a sub query.

cheers

cameron
Re: multiple out paramaters [message #185887 is a reply to message #185869] Fri, 04 August 2006 01:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


For Using this in the SQL statemets(Or In subquries) , You need to Use "Table Function with CAST" . The followung link may help you for a quick reference.

http://www.databasejournal.com/features/oracle/article.php/3352091

Thumbs Up
Rajuvan.
Re: multiple out paramaters [message #185903 is a reply to message #185887] Fri, 04 August 2006 03:25 Go to previous message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
Except you don't generally need to use CAST Wink
Previous Topic: Collections Error
Next Topic: Problem with Greek CharSet
Goto Forum:
  


Current Time: Sun Dec 08 06:08:02 CST 2024