Home » SQL & PL/SQL » SQL & PL/SQL » Passing Data between Procedures
Passing Data between Procedures [message #1967] Thu, 13 June 2002 16:00 Go to next message
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

Report message to a moderator

Re: Passing Data between Procedures [message #1968 is a reply to message #1967] Thu, 13 June 2002 16:17 Go to previous messageGo to next message
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;

Report message to a moderator

Re: Passing Data between Procedures [message #1975 is a reply to message #1967] Thu, 13 June 2002 23:27 Go to previous messageGo to next message
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;

Report message to a moderator

Re: Passing Data between Procedures [message #1979 is a reply to message #1967] Fri, 14 June 2002 08:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
EXCELLENT INPUT.
I could have never ever never thought such kind of a logic. I m learning from todd

Report message to a moderator

Re: Passing Data between Procedures [message #1982 is a reply to message #1975] Fri, 14 June 2002 08:47 Go to previous messageGo to next message
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.

Report message to a moderator

Re: Passing Data between Procedures [message #1987 is a reply to message #1982] Fri, 14 June 2002 09:57 Go to previous message
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;

Report message to a moderator

Previous Topic: SQL PLUS W Error - Urgent
Next Topic: Extract numbers from a string
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 13:27:24 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.