Home » SQL & PL/SQL » SQL & PL/SQL » Composite Object (Oracle 9i)
Composite Object [message #412498] |
Thu, 09 July 2009 14:06  |
chintu00
Messages: 91 Registered: February 2007 Location: NJ, US
|
Member |
|
|
I have a nested object giving me error. can someone pls help.
CREATE OR REPLACE TYPE OBJFCST
AS OBJECT (JAN NUMBER(18,2),FEB NUMBER(18,2),MAR NUMBER(18,2),APR NUMBER(18,2),MAY NUMBER(18,2),JUN NUMBER(18,2),
JUL NUMBER(18,2),AUG NUMBER(18,2),SEP NUMBER(18,2),OCT NUMBER(18,2),NOV NUMBER(18,2), DEC NUMBER(18,2),
Q1 NUMBER(18,2), Q2 NUMBER(18,2), Q3 NUMBER(18,2),Q4 NUMBER(18,2), TOTAL NUMBER(18,2));
/
create or replace type wholesaler
as object (dept number,
supp number,
channel varchar2(20),
basis varchar2(6),
cy_actuals objfcst,
ly_actuals objfcst,
fcst_object objfcst
)
/
CREATE OR REPLACE TYPE wholesaler_array
as table of wholesaler
CREATE OR REPLACE TYPE OBJDEPTVENDOR
AS OBJECT (dept NUMBER(4), vendor NUMBER(10));
/
CREATE OR REPLACE TYPE dept_vend_array
AS table OF OBJDEPTVENDOR
/
1 declare
2 dv_ar dept_vend_array := dept_vend_array(OBJDEPTVENDOR(3,244));
3 or_wholesaler wholesaler_array := wholesaler_array();
4 j integer := 0;
5 begin
6 for i in dv_ar.first..dv_ar.last
7 loop
8 or_wholesaler.extend;
9 j:= j+1;
10 or_wholesaler(j).dept := dv_ar(i).dept;
11 or_wholesaler(j).supp := dv_ar(i).vendor;
12 or_wholesaler(j).channel := 'Delivery';
13 or_wholesaler(j).basis := 'GPURC';
14 end loop;
15* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at line 10
|
|
|
|
|
|
|
Re: Composite Object [message #412506 is a reply to message #412501] |
Thu, 09 July 2009 14:30   |
chintu00
Messages: 91 Registered: February 2007 Location: NJ, US
|
Member |
|
|
I tried the initialization as below but doesn't help.
or_wholesaler wholesaler_array := wholesaler_array(wholesaler(0,0,0,0,objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
)
);
|
|
|
|
|
|
Re: Composite Object [message #412511 is a reply to message #412508] |
Thu, 09 July 2009 14:42   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> create or replace type x1 as object (f1 integer, f2 varchar2(10));
2 /
Type created.
SQL> create or replace type x2 as object (c1 integer, c2 varchar2(10), c3 x1);
2 /
Type created.
SQL> create or replace type tx2 is table of x2;
2 /
Type created.
SQL> declare
2 c tx2 := tx2();
3 begin
4 for i in 1..3 loop
5 c.extend;
6 c(c.last).c1 := 0;
7 c(c.last).c2 := 'A';
8 end loop;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at line 7
SQL> 5
5* c.extend;
SQL> c/;/; c(c.last):=x2(null,null,x1(null,null));/
5* c.extend; c(c.last):=x2(null,null,x1(null,null));
SQL> l
1 declare
2 c tx2 := tx2();
3 begin
4 for i in 1..3 loop
5 c.extend; c(c.last):=x2(null,null,x1(null,null));
6 c(c.last).c1 := 0;
7 c(c.last).c2 := 'A';
8 end loop;
9* end;
SQL> /
PL/SQL procedure successfully completed
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Thu Feb 13 19:14:55 CST 2025
|