Home » SQL & PL/SQL » SQL & PL/SQL » How to cast collection in table
How to cast collection in table [message #667295] Wed, 20 December 2017 01:17 Go to next message
purnima1
Messages: 74
Registered: June 2014
Member
Hi Team,

I have one table which has some rows. I am trying to access it using collection but getting an error when compiling procedure .

attached is the insert and DDL for table

create or replace procedure purnima_survivor as

cursor c is select * from survivor_ship ;
type t is table of c%rowtype index by binary_integer;
tab t ;

--gold_record_table gold_record_tab;
--input_record_table input_record_tab;

begin 

open c;
fetch c bulk collect into tab;
close c ;


if tab.count > 0 then 

 -- select cast(collect (tab) as input_record_table) from dual ;

   
   for cur_rec in ( /*select s.EMAILID,s.work_phone,RANK() OVER (ORDER BY (CASE 
             WHEN SOURCE1 IN  ('GCOM','ELOQUA','PWS') THEN 1 
             WHEN SOURCE1 ='CRM' THEN 2 END
           ) ,
             (CASE WHEN NVL(EMAIL_FLAG,'N')='N' AND NVL(ACTIVE_FLAG,'N')='Y' THEN 1 -----NOT FLAGGED/ACTIVE 
                 WHEN NVL(EMAIL_FLAG,'N')='N' AND NVL(ACTIVE_FLAG,'N')='N'THEN 2--------NOT FLAGGED/INACTIVE 
                 WHEN NVL(EMAIL_FLAG,'N')='Y' AND NVL(ACTIVE_FLAG,'N')='Y'THEN 3-------FLAGED/ACTIVE
                 WHEN NVL(EMAIL_FLAG,'N')='Y' AND NVL(ACTIVE_FLAG,'N')='N'THEN 4 ---NOT FLAGGED/INACTIVE 
            END
            ) ,
            update_date Desc 
           ,SEQ_NUM Desc
           ) rank_val
        from table(tab)s*/
        SELECT *  FROM   TABLE(tab)
        
        ) 
        
     LOOP
      DBMS_OUTPUT.put_line(cur_rec.EMAILID || ' : ' || cur_rec.work_phone);--|| ' : ' ||cur_rec.rank_val);
    END LOOP;   
                         
 end if ;

end ;


compilation error

[Error] PLS-00382 (36: 32): PLS-00382: expression is of wrong type
[Error] ORA-22905 (36: 26): PL/SQL: ORA-22905: cannot access rows from a non-nested table item
[Error] PLS-00364 (41: 28): PLS-00364: loop index variable 'CUR_REC' use is invalid




Re: How to cast collection in table [message #667300 is a reply to message #667295] Wed, 20 December 2017 03:16 Go to previous messageGo to next message
cookiemonster
Messages: 13264
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't use PL/SQL types as tables like that.
You can use SQL types (created with create or replace type)
Re: How to cast collection in table [message #667340 is a reply to message #667295] Thu, 21 December 2017 11:14 Go to previous messageGo to next message
bala.b@outlook.com
Messages: 4
Registered: December 2017
Junior Member
create or replace package purnima_survivor_spec is
Procedure purnima_survivor;
cursor c is select * from survivor_ship ;
type rec is record (SEQ_NUM NUMBER, 
	"SOURCE1" VARCHAR2(200 BYTE), 
	"EMAILID" VARCHAR2(100 BYTE), 
	"EMAIL_FLAG" VARCHAR2(2 BYTE), 
	"ACTIVE_FLAG" VARCHAR2(10 BYTE), 
	"UPDATE_DATE" DATE, 
	"WORK_PHONE" VARCHAR2(100 BYTE));
type t is table of rec;-- index by binary_integer;
tab t ;
Function fnc_tab_order return t pipelined;
end;
/
create or replace Package body purnima_survivor_spec as

Procedure purnima_survivor is


--gold_record_table gold_record_tab;
--input_record_table input_record_tab;

begin 

open c;
fetch c bulk collect into tab;
close c ;


if tab.count > 0 then 

 -- select cast(collect (tab) as input_record_table) from dual ;

   
   for cur_rec in ( /*select s.EMAILID,s.work_phone,RANK() OVER (ORDER BY (CASE 
             WHEN SOURCE1 IN  ('GCOM','ELOQUA','PWS') THEN 1 
             WHEN SOURCE1 ='CRM' THEN 2 END
           ) ,
             (CASE WHEN NVL(EMAIL_FLAG,'N')='N' AND NVL(ACTIVE_FLAG,'N')='Y' THEN 1 -----NOT FLAGGED/ACTIVE 
                 WHEN NVL(EMAIL_FLAG,'N')='N' AND NVL(ACTIVE_FLAG,'N')='N'THEN 2--------NOT FLAGGED/INACTIVE 
                 WHEN NVL(EMAIL_FLAG,'N')='Y' AND NVL(ACTIVE_FLAG,'N')='Y'THEN 3-------FLAGED/ACTIVE
                 WHEN NVL(EMAIL_FLAG,'N')='Y' AND NVL(ACTIVE_FLAG,'N')='N'THEN 4 ---NOT FLAGGED/INACTIVE 
            END
            ) ,
            update_date Desc 
           ,SEQ_NUM Desc
           ) rank_val
        from table(tab)s*/
        SELECT *  FROM   TABLE(fnc_tab_order)
        
        ) 
        
     LOOP
       DBMS_OUTPUT.put_line(cur_rec.EMAILID || ' : ' || cur_rec.work_phone);--|| ' : ' ||cur_rec.rank_val);
    END LOOP;   
                         
 end if ;
end purnima_survivor;
Function fnc_tab_order return t pipelined IS
lv_Tab t := tab;
BEGIN
   IF lv_Tab.count<>0 THEN
      FOR I IN lv_Tab.First..lv_Tab.Last LOOP
        pipe ROW(lv_Tab(i));
      END LOOP;
   END IF;
   RETURN;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('EXCEPTION in fnc_tab_order :'||SQLERRM);
END fnc_tab_order;
end purnima_survivor_spec;

Check this... it might help you Smile
Re: How to cast collection in table [message #667352 is a reply to message #667295] Thu, 21 December 2017 22:17 Go to previous messageGo to next message
bala.b@outlook.com
Messages: 4
Registered: December 2017
Junior Member
create or replace package purnima_survivor_spec is
Procedure purnima_survivor;
cursor c is select * from survivor_ship ;
type rec is record (SEQ_NUM NUMBER, 
	"SOURCE1" VARCHAR2(200 BYTE), 
	"EMAILID" VARCHAR2(100 BYTE), 
	"EMAIL_FLAG" VARCHAR2(2 BYTE), 
	"ACTIVE_FLAG" VARCHAR2(10 BYTE), 
	"UPDATE_DATE" DATE, 
	"WORK_PHONE" VARCHAR2(100 BYTE));
type t is table of rec;-- index by binary_integer;
tab t ;
Function fnc_tab_order return t pipelined;
end;
/
create or replace Package body purnima_survivor_spec as

Procedure purnima_survivor is


--gold_record_table gold_record_tab;
--input_record_table input_record_tab;

begin 

open c;
fetch c bulk collect into tab;
close c ;


if tab.count > 0 then 

 -- select cast(collect (tab) as input_record_table) from dual ;

   
   for cur_rec in ( /*select s.EMAILID,s.work_phone,RANK() OVER (ORDER BY (CASE 
             WHEN SOURCE1 IN  ('GCOM','ELOQUA','PWS') THEN 1 
             WHEN SOURCE1 ='CRM' THEN 2 END
           ) ,
             (CASE WHEN NVL(EMAIL_FLAG,'N')='N' AND NVL(ACTIVE_FLAG,'N')='Y' THEN 1 -----NOT FLAGGED/ACTIVE 
                 WHEN NVL(EMAIL_FLAG,'N')='N' AND NVL(ACTIVE_FLAG,'N')='N'THEN 2--------NOT FLAGGED/INACTIVE 
                 WHEN NVL(EMAIL_FLAG,'N')='Y' AND NVL(ACTIVE_FLAG,'N')='Y'THEN 3-------FLAGED/ACTIVE
                 WHEN NVL(EMAIL_FLAG,'N')='Y' AND NVL(ACTIVE_FLAG,'N')='N'THEN 4 ---NOT FLAGGED/INACTIVE 
            END
            ) ,
            update_date Desc 
           ,SEQ_NUM Desc
           ) rank_val
        from table(tab)s*/
        SELECT *  FROM   TABLE(fnc_tab_order)
        
        ) 
        
     LOOP
       DBMS_OUTPUT.put_line(cur_rec.EMAILID || ' : ' || cur_rec.work_phone);--|| ' : ' ||cur_rec.rank_val);
    END LOOP;   
                         
 end if ;
end purnima_survivor;
Function fnc_tab_order return t pipelined IS
lv_Tab t := tab;
BEGIN
   IF lv_Tab.count<>0 THEN
      FOR I IN lv_Tab.First..lv_Tab.Last LOOP
        pipe ROW(lv_Tab(i));
      END LOOP;
   END IF;
   RETURN;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('EXCEPTION in fnc_tab_order :'||SQLERRM);
END fnc_tab_order;
end purnima_survivor_spec;
It maybe help to you Smile
Re: How to cast collection in table [message #667353 is a reply to message #667352] Thu, 21 December 2017 22:29 Go to previous message
BlackSwan
Messages: 26182
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/WHEN_OTHERS
Previous Topic: CLOB to file
Next Topic: Find where exception occurred in oracle procedure
Goto Forum:
  


Current Time: Mon Oct 15 06:22:45 CDT 2018