multiple out paramaters [message #185660] |
Wed, 02 August 2006 22:52 |
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 #185869 is a reply to message #185660] |
Thu, 03 August 2006 22:35 |
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
|
|
|
|
|