Home » SQL & PL/SQL » SQL & PL/SQL » Return a ref cursor for a PL/SQL table
Return a ref cursor for a PL/SQL table [message #217666] Sun, 04 February 2007 06:03 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi Experts,
I am facing a problem with returning values in a pl/sql table through a REF CURSOR. I want to return the value in my PL/SQL table back to .Net code through a REF CURSOR. I have code given below but the error comes as "PL/SQL: ORA-22905: cannot access rows from a non-nested table item"

Any idea, how can I implement the same? The oracle version is 10g. Also, I want the data to be in ordered way, i.e in whatever they are filled into the array structure.

DECLARE    
	CURSOR cur_rule_group_id IS
	SELECT org.rule_group_id rule_group_id, channel_ord.order_level 
		                FROM (SELECT channel_id, LEVEL order_level FROM channel_hierarchy  
									CONNECT BY PRIOR parent_channel = channel_id  
									AND PRIOR channel_id <> 0      
									START WITH channel_id = 113
							 ORDER BY LEVEL DESC
							 ) channel_ord, obr_rule_group org
						WHERE org.customer_set_id IS NULL
						AND org.channel_id = channel_ord.channel_id
						AND org.process_group = 1
						ORDER BY channel_ord.order_level DESC;

po_channel_rule_group  sys_refcursor;
				
	
	TYPE t_rule_condition IS RECORD
	         (lv_rule_id 				obr_rule_group_member.rule_id%TYPE,
	          lv_prerequisite_rule_id 	obr_rule_group_member.prerequisite_rule_id%TYPE,
	          lv_rule_name				obr_rule.rule_name%TYPE,
	          lv_rule_threshold			obr_rule.rule_threshold%TYPE,
	          lv_rule_start_date		obr_rule.rule_start_date%TYPE,
	          lv_rule_end_date			obr_rule.rule_end_date%TYPE,
	          lv_condition_id			obr_condition.condition_id%TYPE,
	          lv_comparison_operator	obr_condition.comparison_operator%TYPE,
	          lv_comparison_value		obr_condition.comparison_value%TYPE,
	          lv_condition_result		obr_condition.condition_result%TYPE,
	          lv_field_name				obr_field.field_name%TYPE,
	          lv_field_xpath			obr_field.field_xpath%TYPE,
	          lv_xpath_uri				obr_field.xpath_uri%TYPE,
	          lv_field_data_type		obr_field.field_data_type%TYPE,
	          lv_order_form_level		obr_rule.order_form_level%TYPE,
	          lv_rule_group_id			obr_rule_group_member.rule_group_id%TYPE
	         );

	TYPE typ_rule_condition IS TABLE OF t_rule_condition INDEX BY BINARY_INTEGER;
	v_typ_rule_condition typ_rule_condition;
	         	
	i PLS_INTEGER := 0;

	BEGIN

	FOR rec_rule_group_id IN cur_rule_group_id 
	LOOP
	
	  FOR rec_rule_condition IN 
	                       (SELECT grp.rule_id, grp.prerequisite_rule_id, rl.rule_name, rl.rule_threshold, rl.rule_start_date, rl.rule_end_date, 
								   cond.condition_id, cond.comparison_operator, cond.comparison_value, cond.condition_result,  
								   f.field_name, f.field_xpath, f.xpath_uri, f.field_data_type,  rl.order_form_level, grp.rule_group_id  
							INTO  v_typ_rule_condition(i)
							FROM   obr_rule_group_member grp
							      ,obr_rule rl
							      ,obr_condition cond
							      ,obr_field f
							WHERE grp.rule_group_id = rec_rule_group_id.rule_group_id
							AND rl.active_flag = 'Y'
							AND grp.rule_id = rl.rule_id  
							AND cond.rule_id = rl.rule_id	  
							AND cond.field_id = f.field_id  
							ORDER BY grp.execute_seq
							) 
	   LOOP
	   
	       i := i + 1;
	       v_typ_rule_condition(i) := rec_rule_condition;
	   
	   END LOOP;
		
		
		
	END LOOP;
	
	OPEN po_channel_rule_group 
	  FOR SELECT lv_rule_id, lv_prerequisite_rule_id, lv_rule_name, lv_rule_threshold, lv_rule_start_date, lv_rule_end_date,
                 lv_condition_id, lv_comparison_operator, lv_comparison_value, lv_condition_result, lv_field_name,
                 lv_field_xpath, lv_xpath_uri, lv_field_data_type, lv_order_form_level, lv_rule_group_id
          FROM TABLE(v_typ_rule_condition);
		
	
	END;	
/

[Updated on: Sun, 04 February 2007 06:10]

Report message to a moderator

Re: Return a ref cursor for a PL/SQL table [message #217693 is a reply to message #217666] Sun, 04 February 2007 20:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can only use the TABLE() function on a Nested Table Type that is declared on the database. Yours is a PL/SQL TYPE definition.

You will nead to create both the OBJECT TYPE (t_rule_condition) and the COLLECTION TYPE (typ_rule_condition) on the database, using CREATE OR REPLACE TYPE.

Ross Leishman
Re: Return a ref cursor for a PL/SQL table [message #217755 is a reply to message #217666] Mon, 05 February 2007 05:11 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

OK. so there is no way of doing it through PL/SQL type? If I use Global Temporaray table, will it be slower or have an performance impact?

Regards
Himanshu
Re: Return a ref cursor for a PL/SQL table [message #217844 is a reply to message #217755] Mon, 05 February 2007 19:43 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Probably not.
Previous Topic: make one query from multiple query
Next Topic: Doubt in MV refresh
Goto Forum:
  


Current Time: Sat Dec 10 03:14:03 CST 2016

Total time taken to generate the page: 0.08995 seconds