PLS-00382: expression is of wrong type
I are in the midst of testing stored procedures after Oracle10gR2 Upgrade from Oracle8i (8.1.7.4). On Oracle10g, I notice that "Initializing implicitly during direct assignment" have problems with due to the more stringent datatype checks for Collections.
The code in Part A) parse ok in Oracle8i but not Oracle10g.
- It fail with "PLS-00382: expression is of wrong type" at Line#15 "Obj_LLI_OSOURCE := curvar_OSOURCE". It is trying to assign a collection to cursor, curvar_OSOURCE. curvar_OSOURCE is a cursor rowtype for nested table, lli_transport_order(Line#5).
The setup of tables and collection objects are in Part B-D)
I hope someone who have found themselves in same predication and solutions can help.
Thanks in advance
Lim
--
A)
declare
PL_Rec_LLI_OSOURCE_TAB LLI_OSOURCE_TAB := LLI_OSOURCE_TAB ();
Obj_LLI_OSOURCE LLI_OSOURCE_OBJ;
--Line#5 cursor cur_OSOURCE is select * from the(select ORDER_SOURCE from lli_transport_order where rownum <1);
curvar_OSOURCE cur_OSOURCE%rowtype;
begin
curvar_OSOURCE.ORDER_NO := '123';
curvar_OSOURCE.SEQNO := 1;
curvar_OSOURCE.RECORD_TYPE := 'OS';
curvar_OSOURCE.COMPANY_ID := 'ID';
curvar_OSOURCE.COMPANY_NAME := 'COMPANY_NAME';
curvar_OSOURCE.IN_COMPANY_ID := 'ID';
curvar_OSOURCE.IN_COMPANY_NAME := 'COMPANY_NAME';
--Line#15 Obj_LLI_OSOURCE := curvar_OSOURCE;
for i in cur_OSOURCE loop
dbms_Output.put_line('test');
end loop;
end;
/
--
B)
CREATE OR REPLACE TYPE lli_osource_tab IS TABLE OF LLI_OSOURCE_OBJ
/
--
C)
CREATE OR REPLACE TYPE lli_osource_obj AS OBJECT
(ORDER_NO VARCHAR2(15),
SEQNO NUMBER,
RECORD_TYPE VARCHAR2(2),
COMPANY_ID VARCHAR2(10),
COMPANY_NAME VARCHAR2(50),
IN_COMPANY_ID VARCHAR2(10),
IN_COMPANY_NAME VARCHAR2(50))
/
--
D)
CREATE TABLE "LLI_TRANSPORT_ORDER" ("ORDER_NO" VARCHAR2(15), "SEQNO" NUMBER,
"ORDER_SOURCE" "LLI_OSOURCE_TAB")
NESTED TABLE "ORDER_SOURCE" STORE AS "NESTED_LLI_OSOURCE_TAB1" RETURN
AS VALUE ;