Proc to return a REF CURSOR using dynamic SQL [message #426272] |
Wed, 14 October 2009 11:53  |
dkrishnachaitanya
Messages: 3 Registered: October 2009 Location: CALIFORNIA
|
Junior Member |
|
|
Hi,
I need to create a stored procedure (as part of a PKG lets say 'IC_FILE_ROUTE_PKG') which should return a REF CURSOR as below.
it should take the following inputs:
group_name ('processor' as you see in the below query),
skill_values_int as PLSQL table (1,1,17,0,0 as you see in the below query),
skill_name as PLSQL table ('loan_type','financial_type','language_type','piggy_back','tx_a6' as you see in the below query)
--> remaining all values in the query are hard-coded (so dont worry about them)
--> the values in skill_values_int are corresponding to the values in skill_name, so will have equal counts.
The difficult part is, it has to build the following query dynamically based on the count of values in skill_values_int/skill_name (For Ex, the following query has 5 inline views skill1,skill2, skill3, SKILL4, SKILL5 for the 5 values in skill_values_int/skill_name). Also, if you observe, WHERE conditions will also increase based on the count of values in skill_values_int/skill_name.
Please help, need this very urgently.
Thank you,
Krishna
select skilled_users.user_name, skilled_users.user_threshold_percent
from (SELECT ALL bac.*
FROM WPRT1SID.bac_user_sp bac
,(SELECT ALL dm_repeating.users_names
FROM WPRT1SID.dm_group_sp dm_group, dm_group_rp dm_repeating
WHERE ((dm_group.group_name = 'processor'))
AND dm_repeating.r_object_id = dm_group.r_object_id) processors
,(SELECT ALL dmc_wq_user_skill.user_name
FROM WPRT1SID.dmc_wq_user_skill_sp dmc_wq_user_skill
WHERE ( dmc_wq_user_skill.r_object_id IN (
SELECT r_object_id
FROM WPRT1SID.dmc_wq_user_skill_r
WHERE skill_values_int =
1)
AND (dmc_wq_user_skill.skill_info_id =
(select dmc_wq_skill_info.r_object_id from WPRT1SID.dmc_wq_skill_info_sp dmc_wq_skill_info where dmc_wq_skill_info.skill_name='loan_type')
)
) ) skill1
,(SELECT ALL dmc_wq_user_skill.user_name
FROM WPRT1SID.dmc_wq_user_skill_sp dmc_wq_user_skill
WHERE ( dmc_wq_user_skill.r_object_id IN (
SELECT r_object_id
FROM WPRT1SID.dmc_wq_user_skill_r
WHERE skill_values_int =
1)
AND (dmc_wq_user_skill.skill_info_id =
(select dmc_wq_skill_info.r_object_id from WPRT1SID.dmc_wq_skill_info_sp dmc_wq_skill_info where dmc_wq_skill_info.skill_name='financial_type')
)
) ) skill2
,(SELECT ALL dmc_wq_user_skill.user_name
FROM WPRT1SID.dmc_wq_user_skill_sp dmc_wq_user_skill
WHERE ( dmc_wq_user_skill.r_object_id IN (
SELECT r_object_id
FROM WPRT1SID.dmc_wq_user_skill_r
WHERE skill_values_int =
17)
AND (dmc_wq_user_skill.skill_info_id =
(select dmc_wq_skill_info.r_object_id from WPRT1SID.dmc_wq_skill_info_sp dmc_wq_skill_info where dmc_wq_skill_info.skill_name='language_type')
)
)) skill3
,(SELECT ALL dmc_wq_user_skill.user_name
FROM WPRT1SID.dmc_wq_user_skill_sp dmc_wq_user_skill
WHERE ( dmc_wq_user_skill.r_object_id IN (
SELECT r_object_id
FROM WPRT1SID.dmc_wq_user_skill_r
WHERE skill_values_int =
0)
AND (dmc_wq_user_skill.skill_info_id =
(select dmc_wq_skill_info.r_object_id from WPRT1SID.dmc_wq_skill_info_sp dmc_wq_skill_info where dmc_wq_skill_info.skill_name='piggy_back')
)
)) skill4
,(SELECT ALL dmc_wq_user_skill.user_name
FROM WPRT1SID.dmc_wq_user_skill_sp dmc_wq_user_skill
WHERE ( dmc_wq_user_skill.r_object_id IN (
SELECT r_object_id
FROM WPRT1SID.dmc_wq_user_skill_r
WHERE skill_values_int =
0)
AND (dmc_wq_user_skill.skill_info_id =
(select dmc_wq_skill_info.r_object_id from WPRT1SID.dmc_wq_skill_info_sp dmc_wq_skill_info where dmc_wq_skill_info.skill_name='tx_a6')
)
)) skill5
,(SELECT ALL user_ident
FROM WPRT1SID.file_routing_user_oooffice
WHERE ( (from_date_utc <=
TO_DATE ('09/10/2009 3:57:13','DD/MM/YYYY HH24:MI:SS')
)
AND (to_date_utc >= TO_DATE ('09/10/2009 0:0:0','DD/MM/YYYY HH24:MI:SS')
)
)
) ooo
WHERE (bac.user_state = 0)
AND (200000. <= bac.user_max_loan_amt)
AND bac.user_name = processors.users_names
AND bac.user_name = skill1.user_name
AND bac.user_name = skill2.user_name
AND bac.user_name = skill3.user_name
AND bac.user_name = skill4.user_name
AND bac.user_name = skill5.user_name
AND bac.user_name = ooo.user_ident(+)
AND ooo.user_ident is null
) skilled_users
where
(skilled_users.user_threshold_percent * 100) / 100 >
(SELECT ALL COUNT (bac_loan.r_object_id)
FROM WPRT1SID.bac_loan_sp bac_loan
WHERE ( (bac_loan.processor = skilled_users.user_name)
AND (bac_loan.processing_status_type < 3)
)
AND ( bac_loan.i_has_folder = 1
AND bac_loan.i_is_deleted = 0
))
|
|
|
|
Re: Proc to return a REF CURSOR using dynamic SQL [message #426276 is a reply to message #426274] |
Wed, 14 October 2009 12:04   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@Blackswan - do please leave post moderation to the moderators - there's a good chap. If you could try to include some sort of actual help in your posts, that'd make a pleasant change too.
@dkrishnachaitanya - whatis the actual problem that you're hitting.
It looks like you should be able to step through your lists of Skill_values_int and Skill_names, and for each one build a string like:
,(SELECT dmc_wq_user_skill.user_name
FROM WPRT1SID.dmc_wq_user_skill_sp dmc_wq_user_skill
WHERE ( dmc_wq_user_skill.r_object_id IN (SELECT r_object_id
FROM WPRT1SID.dmc_wq_user_skill_r
WHERE skill_values_int = <skill_values_int value>)
AND (dmc_wq_user_skill.skill_info_id = (select dmc_wq_skill_info.r_object_id
from WPRT1SID.dmc_wq_skill_info_sp dmc_wq_skill_info
where dmc_wq_skill_info.skill_name='<Skill_names value>')
)
) ) skill<counter value>
Then you concatenate these strings together, include the top part of the query, adn away you go.
|
|
|
Re: Proc to return a REF CURSOR using dynamic SQL [message #426277 is a reply to message #426276] |
Wed, 14 October 2009 12:14   |
dkrishnachaitanya
Messages: 3 Registered: October 2009 Location: CALIFORNIA
|
Junior Member |
|
|
Thanks for the reply JRowbottom.
Please observe the following WHERE conditions in the query:
AND bac.user_name = skill1.user_name
AND bac.user_name = skill2.user_name
AND bac.user_name = skill3.user_name
AND bac.user_name = skill4.user_name
AND bac.user_name = skill5.user_name
I need to include these conditions dynamically based on the count of values in skill_values_int/skill_name. how would i do that using dynamic sql?
|
|
|
|
|