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 |
|
purnima1
Messages: 79 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 #667340 is a reply to message #667295] |
Thu, 21 December 2017 11:14 |
|
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
|
|
|
Re: How to cast collection in table [message #667352 is a reply to message #667295] |
Thu, 21 December 2017 22:17 |
|
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
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 06:28:05 CDT 2024
|