Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve values from PL/SQL Table Type in Procedure (10.2.0.1.0 )
How to retrieve values from PL/SQL Table Type in Procedure [message #433580] Thu, 03 December 2009 02:22 Go to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi,

I am facing difficulties to retrieve data from the Pl/sql table type in procedure output variable, Can any one suggest me for this.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0

Procedure :
-----------
create or replace PROCEDURE P_PER_COSTSAVING_COMP1
                                                        (   
                                                           IP_STDT DATE, 
                                                            IP_ENDDT DATE, 
                                                            IP_ENCTYPE VARCHAR2 ,
                                                            OP_COSTSAVINGCOMPDATASET OUT SYS_REFCURSOR
                                                        )   
AS 
        V_ERRCODE NUMBER;  
        V_ERRMSG VARCHAR2(4000);  
        V_USERNAME VARCHAR2(150);
        TYPE V_TYPE_PER_COST_COMP_RECORD IS RECORD (
                                            id NUMBER(10),
                                            UserName VARCHAR2(250),  
                                            user_idn NUMBER(10),  
                                            EpisodeType VARCHAR2(100),  
                                            FixedCost NUMBER(18,0),  
                                            CalcCost NUMBER(18,0),  
                                            TotalTime NUMBER(18,0),  
                                            DirectCost NUMBER(18,0),  
                                            InDirectCost NUMBER(18,0)
                                          ); 
        TYPE V_TABLE_PER_COST_COMP_TAB IS TABLE OF V_TYPE_PER_COST_COMP_RECORD;
        V_TABLE_PER_COST_COMP V_TABLE_PER_COST_COMP_TAB;
        
BEGIN
	select rownum as id,	
		UserName,  
		user_idn,  
		EpisodeType,  
		FixedCost, 
		CalcCost, 
		TotalTime, 
		DirectCost, 
		InDirectCost BULK COLLECT INTO V_TABLE_PER_COST_COMP
	from (
			Select  
				UserName,  
				user_idn,  
				EpisodeType,  
				Sum(FixedCost)as FixedCost, 
				Sum(CalcCost) as CalcCost, 
				Sum(TotalTime) as TotalTime, 
				Sum(DirectCost)as DirectCost, 
				Sum(InDirectCost)as InDirectCost 
			from
			(
				Select null as  username,user_idn,EpisodeType,FixedCost,CalcCost,
                                       TotalTime,0 as DirectCost,0 as InDirectCost 
				From 
			)tab1
			group by UserName,user_idn,    EpisodeType 
	) finaldataset;  

  IF V_TABLE_PER_COST_COMP.first IS NOT NULL
	THEN
		FOR i in V_TABLE_PER_COST_COMP.first..V_TABLE_PER_COST_COMP.last
		LOOP
			V_TABLE_PER_COST_COMP(i).id := V_TABLE_PER_COST_COMP(i).id;
    
			select NVL(e.last_name,'')||','||NVL(e.first_name,'') into V_USERNAME
			from sys_user  su    
			left outer join entity e on su.entity_idn=e.entity_idn 
			WHERE  su.user_idn = V_TABLE_PER_COST_COMP(i).user_idn;
		    
			V_TABLE_PER_COST_COMP(i).UserName := V_USERNAME;
              
			DBMS_OUTPUT.PUT_LINE(TO_CHAR(I)||'A '||V_TABLE_PER_COST_COMP(i).UserName);
		END LOOP;
  END IF;
     -- OPEN OP_COSTSAVINGCOMPDATASET FOR V_TABLE_PER_COST_COMP;
		
EXCEPTION
    WHEN OTHERS THEN
        V_ERRCODE :=SQLCODE;
        V_ERRMSG := SQLERRM;
        RAISE_APPLICATION_ERROR(-20001,TO_CHAR(V_ERRCODE)||V_ERRMSG);
END P_PER_COSTSAVING_COMP1;
/


Thanks,
Thani.

[Updated on: Thu, 03 December 2009 02:32]

Report message to a moderator

Re: How to retrieve values from PL/SQL Table Type in Procedure [message #433585 is a reply to message #433580] Thu, 03 December 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Remove your WHEN OTHERS clause
2/ Explain what is your problem EXACTLY

Regards
Michel
Re: How to retrieve values from PL/SQL Table Type in Procedure [message #433586 is a reply to message #433580] Thu, 03 December 2009 02:48 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Michel,

I need to bring the out put of table type dataset in OP_COSTSAVINGCOMPDATASET OUT SYS_REFCURSOR parameter.
which will be displayed in the Application.

SQL> create or replace PROCEDURE P_PER_COSTSAVING_COMP1
                                                        (   
                                                           IP_STDT DATE, 
                                                            IP_ENDDT DATE, 
                                                            IP_ENCTYPE VARCHAR2 ,
                                                            OP_COSTSAVINGCOMPDATASET OUT SYS_REFCURSOR
                                                        )   
AS 
        V_ERRCODE NUMBER;  
        V_ERRMSG VARCHAR2(4000);  
        V_USERNAME VARCHAR2(150);
        TYPE V_TYPE_PER_COST_COMP_RECORD IS RECORD (
                                            id NUMBER(10),
                                            UserName VARCHAR2(250),  
                                            user_idn NUMBER(10),  
                                            EpisodeType VARCHAR2(100),  
                                            FixedCost NUMBER(18,0),  
                                            CalcCost NUMBER(18,0),  
                                            TotalTime NUMBER(18,0),  
                                            DirectCost NUMBER(18,0),  
                                            InDirectCost NUMBER(18,0)
                                          ); 
        TYPE V_TABLE_PER_COST_COMP_TAB IS TABLE OF V_TYPE_PER_COST_COMP_RECORD;
        V_TABLE_PER_COST_COMP V_TABLE_PER_COST_COMP_TAB;
        
BEGIN
	select rownum as id,	
		UserName,  
		user_idn,  
		EpisodeType,  
		FixedCost, 
		CalcCost, 
		TotalTime, 
		DirectCost, 
		InDirectCost BULK COLLECT INTO V_TABLE_PER_COST_COMP
	from (
			Select  
				UserName,  
				user_idn,  
				EpisodeType,  
				Sum(FixedCost)as FixedCost, 
				Sum(CalcCost) as CalcCost, 
				Sum(TotalTime) as TotalTime, 
				Sum(DirectCost)as DirectCost, 
				Sum(InDirectCost)as InDirectCost 
			from
			(
				Select null as  username,user_idn,EpisodeType,FixedCost,CalcCost,
                                       TotalTime,0 as DirectCost,0 as InDirectCost 
				From 
			)tab1
			group by UserName,user_idn,    EpisodeType 
	) finaldataset;  

  IF V_TABLE_PER_COST_COMP.first IS NOT NULL
	THEN
		FOR i in V_TABLE_PER_COST_COMP.first..V_TABLE_PER_COST_COMP.last
		LOOP
			V_TABLE_PER_COST_COMP(i).id := V_TABLE_PER_COST_COMP(i).id;
    
			select NVL(e.last_name,'')||','||NVL(e.first_name,'') into V_USERNAME
			from sys_user  su    
			left outer join entity e on su.entity_idn=e.entity_idn 
			WHERE  su.user_idn = V_TABLE_PER_COST_COMP(i).user_idn;
		    
			V_TABLE_PER_COST_COMP(i).UserName := V_USERNAME;
              
			DBMS_OUTPUT.PUT_LINE(TO_CHAR(I)||'A '||V_TABLE_PER_COST_COMP(i).UserName);
		END LOOP;
  END IF;
     -- OPEN OP_COSTSAVINGCOMPDATASET FOR V_TABLE_PER_COST_COMP;
END P_PER_COSTSAVING_COMP1;
/


Thanks,
Thani.
Re: How to retrieve values from PL/SQL Table Type in Procedure [message #433590 is a reply to message #433586] Thu, 03 December 2009 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In order to do this you have to rewrite the code as a single query and open your output cursor for this query.

Regards
Michel
Re: How to retrieve values from PL/SQL Table Type in Procedure [message #433593 is a reply to message #433590] Thu, 03 December 2009 03:25 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could redefine your TYPE as an SQL type rather than a pl/sql type, and use TABLE(V_TABLE_PER_COST_COMP) to access it as a data source
Previous Topic: Sorting logic
Next Topic: sql restictions of 4000 charactes in in clause
Goto Forum:
  


Current Time: Mon Dec 05 08:47:07 CST 2016

Total time taken to generate the page: 0.10350 seconds