Hello Gurus,
Request you help in this.
I am getting this error ORA-00902: invalid datatype when updating using table of record type variable plsql
create table cdt_phone_handset (
cdt_phone_id number primary key,
cdt_phone_name varchar2(100),
cdt_phone_manufacturer varchar2(100),
purchase_dt date,
quantity number
);
insert into
cdt_phone_handset
values
(109010, 'CISCO 701', 'CSCO', sysdate, 5);
insert into
cdt_phone_handset
values
(109011, 'ATT 901', 'ATT', sysdate, 15);
insert into
cdt_phone_handset
values
(109012, 'ATT 699', 'ATT', sysdate, 90);
--------------------------------------------------------------------------------
CREATE OR replace PACKAGE test_api
AS
TYPE cdt_phone_rec IS RECORD (
cdt_phone_id NUMBER,
cdt_phone_name VARCHAR2(100),
cdt_phone_manufacturer VARCHAR2(100) );
TYPE cdt_phone_tab
IS TABLE OF CDT_PHONE_REC INDEX BY BINARY_INTEGER;
PROCEDURE test1;
END test_api;
------------------------------------------------------------------------------------------
CREATE OR replace PACKAGE BODY test_api
AS
PROCEDURE Test1
IS
l_cdt_phones CDT_PHONE_TAB;
BEGIN
SELECT cdt_phone_id,
cdt_phone_name,
cdt_phone_manufacturer
bulk collect INTO l_cdt_phones
FROM cdt_phone_handset
WHERE cdt_phone_manufacturer = 'ATT'; -- get the ATT manufactured phones
dbms_output.Put_line (' Count ->'
|| l_cdt_phones.count);
dbms_output.Put_line('Iterate the Phones');
FOR cur_rec IN (SELECT *
FROM TABLE(l_cdt_phones)) LOOP
dbms_output.Put_line(' cdt_phone_id -> '
|| cur_rec.cdt_phone_id
|| ' : '
|| ' cdt_phone_name -> '
|| cur_rec.cdt_phone_name
|| ' : '
|| ' cdt_phone_manufacturer -> '
|| cur_rec.cdt_phone_manufacturer);
END LOOP; -- this loop works fine
UPDATE cdt_phone_handset --this updates fails ORA-00902: invalid datatype,
SET purchase_dt = SYSDATE + 2
WHERE cdt_phone_id IN (SELECT cdt_phone_id
FROM TABLE(l_cdt_phones));
END;
END test_api;
------------------------------------------------------------------------------
Please tell me what i am doing wrong , the code compiles fine but fails at run time.Please help.
Cannot get whats wrong here. i have reduced the problem to show the error.
Is it because i have declared table type in package in plsql , do i have to declare it at schema level using create type statement ?
Thank you for your help & patience, help will be appreciated
oracle version Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production