Passing Data between Procedures [message #1967] |
Thu, 13 June 2002 16:00 |
Rob
Messages: 70 Registered: January 2000
|
Member |
|
|
I have the tables and procedures listed below. I'm trying to load the POOL_MAPPING table using the 2 procedures below. I want to pass in the GROUP_NAME from the new POOL_MAPPING table into the nghierarchy procedure to generate and return data to the NG_HIERARCHY colmn of the POOL_MAPPING table.
Can this be done with a subquery?
NI_USER_DATA
col RELATED_USER_DATA_LINK
col PARENT_KEY
col DATA_VALUE
NI_NAMED_GROUP
col NAMED_GROUP_PK
col PARENT_GROUP
col GROUP_NAME
col GROUP_LEVEL
POOL_MAPPING
col GROUP_NAME
col NG_HIERARCHY
col CSA
col MC_POOL
col RATE_CENTER
create or replace procedure
sp_pool_mapping AS
BEGIN
insert into pool_mapping select ng.group_name, t210.data_value csa, t201.data_value mc_pool, t215.data_value rate_center
from ni_named_group ng,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 210) t210,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 201) t201,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 215) t215
where t210.parent_key (+)= ng.named_group_pk
and t201.parent_key (+)= ng.named_group_pk
and t215.parent_key (+)= ng.named_group_pk
and ng.group_level = '4';
end;
create or replace procedure
namedGroupHierarchy
(GROUP_NAME IN VARCHAR2, return_value OUT varchar2)
IS
ngh varchar2(512);
begin for r in (select GROUP_NAME || '/' GROUP_NAME from NI_NAMED_GROUP connect by prior PARENT_GROUP = NAMED_GROUP_PK) loop
ngh := r.GROUP_NAME || ngh;
end loop;
ngh := substr(ngh, 1, length(ngh) - 1);
DBMS_OUTPUT.enable;
dbms_output.put_line( ngh );
end;
I'm hoping the POOL_MAPPING table will look like this:
GROUP_NAME NG_HIERARCHY CSA MC_POOL RATE_CENTER
------------- ---------------- -------- ---------------
SEATTLE SEATTLE/WA/USA SEATTLE_WA ASEATTL WA-SEATTLE
CHICAGO CHICAGO/IL/USA CHICAGO_IL ACHICAG IL-CHICAGO
|
|
|
Re: Passing Data between Procedures [message #1968 is a reply to message #1967] |
Thu, 13 June 2002 16:17 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Rob, I would suggest starting with creating a function to return the hierarchy string.
create or replace function f_ng_hierarchy(
p_group_name in ni_named_group.group_name%type)
return varchar2
is
v_result varchar2(512);
begin
for r in (select group_name
from ni_named_group
start with group_name = p_group_name
connect by prior parent_group = named_group_pk) loop
v_result := v_result || '/' || r.group_name;
end loop;
return (substr(v_result, 2));
end;
/
It may need some tweaking, but that is the general idea. Then either call this function directly in your insert into/select statement, or if you just want to update certain rows, maybe wrap the update statement in a procedure:
update pool_mapping
set ng_hierarchy = f_ng_hierarchy(group_name)
where group_name = p_some_group_name;
|
|
|
Re: Passing Data between Procedures [message #1975 is a reply to message #1967] |
Thu, 13 June 2002 23:27 |
Rob
Messages: 70 Registered: January 2000
|
Member |
|
|
Thanks for the response! Can I do a Fetch Into from the funtion to populate the ng_hierarchy column? How would I go about calling the function from this procedure?
create or replace procedure
sp_pool_mapping AS
BEGIN
insert into pool_mapping select ng.group_name, t210.data_value csa, t201.data_value mc_pool, t215.data_value rate_center
from ni_named_group ng,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 210) t210,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 201) t201,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 215) t215
where t210.parent_key (+)= ng.named_group_pk
and t201.parent_key (+)= ng.named_group_pk
and t215.parent_key (+)= ng.named_group_pk
and ng.group_level = '4';
end;
|
|
|
|
Re: Passing Data between Procedures [message #1982 is a reply to message #1975] |
Fri, 14 June 2002 08:47 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Sure, that would be the most efficient place to do it if you need to store this value.
insert into pool_mapping
select ng.group_name, f_ng_hierarchy(ng.group_name), t210.data_value, t201.data_value, t215.data_value ...
I would also mention though that I would not recommend actually even storing this value. By storing it, you are taking an image of the hierarchy and, in essence, freezing it. If the hierarchy changes, your column is now out of date. Now I realize that city/state/country information isn't likely to change, but the principle is the same.
Instead, how about just calling the function in a select statement when you read your pool_mapping table?
select group_name, f_ng_hierarchy(group_name), ...
from pool_mapping;
That way the hierarchy is not stored and the display value (with all the formatting) can be dynamically retrieved when needed. The performance hit of the extra query should be negligible.
|
|
|
Re: Passing Data between Procedures [message #1987 is a reply to message #1982] |
Fri, 14 June 2002 09:57 |
Rob
Messages: 70 Registered: January 2000
|
Member |
|
|
I am seeing this error PLS-00222: no function with name 'F_NG_HIERARCHY' exists in this scope.
It looks like I am not declaring the function correctly. I am using TOAD and can see the function compiled correctly and is in the right instance.
create or replace procedure
sp_pool_mapping
AS
f_ng_hierarchy varchar2(512);
BEGIN
insert into pool_mapping (GROUP_NAME, NG_HIERARCHY, CSA, MC_POOL, RATE_CENTER) select ng.group_name, f_ng_hierarchy(ng.group_name), t210.data_value csa, t201.data_value mc_pool, t215.data_value rate_center
from ni_named_group ng,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 210) t210,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 201) t201,
(select parent_key, data_value from ni_user_data
where related_user_data_link = 215) t215
where t210.parent_key (+)= ng.named_group_pk
and t201.parent_key (+)= ng.named_group_pk
and t215.parent_key (+)= ng.named_group_pk
and ng.group_level = '4';
end;
|
|
|