Home » SQL & PL/SQL » SQL & PL/SQL » Proc to return a REF CURSOR using dynamic SQL (Oracle 10g)
Proc to return a REF CURSOR using dynamic SQL [message #426272] Wed, 14 October 2009 11:53 Go to next message
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 #426274 is a reply to message #426272] Wed, 14 October 2009 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
Please explain why it is urgent for ME to solve this problem for you.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Proc to return a REF CURSOR using dynamic SQL [message #426276 is a reply to message #426274] Wed, 14 October 2009 12:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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?
Re: Proc to return a REF CURSOR using dynamic SQL [message #426320 is a reply to message #426272] Wed, 14 October 2009 23:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Seems to me you want this:

declare
   vsql varchar2(32000);
   rc1 sys_refcursor;
begin
   vsql := '<any select statement you can build here>';
   open rc1 for vsql;
end;
/


Basic refcursor stuff. Notice that the refcursor was opened using a variable that contains a select statement. Older versions of Oracle do not support this, but any currently supported version of Oracle does.

Good luck, Kevin
Re: Proc to return a REF CURSOR using dynamic SQL [message #426380 is a reply to message #426277] Thu, 15 October 2009 03:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just build up a list of join conditions at the same time as you build up the list of data sources.
Previous Topic: Timeout Error
Next Topic: Connect_by error
Goto Forum:
  


Current Time: Wed Sep 28 04:01:57 CDT 2016

Total time taken to generate the page: 0.11272 seconds