Home » SQL & PL/SQL » SQL & PL/SQL » Composite Object (Oracle 9i)
Composite Object [message #412498] Thu, 09 July 2009 14:06 Go to next message
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 #412500 is a reply to message #412498] Thu, 09 July 2009 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is dv_ar a dense array?

Regards
Michel
Re: Composite Object [message #412501 is a reply to message #412498] Thu, 09 July 2009 14:15 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
06530, 00000, "Reference to uninitialized composite"
// *Cause:  An object, LOB, or other composite was referenced as a
//          left hand side without having been initialized.
// *Action: Initialize the composite with an appropriate constructor
//          or whole-object assignment.
//
Re: Composite Object [message #412504 is a reply to message #412500] Thu, 09 July 2009 14:24 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
It is a sparse array
Re: Composite Object [message #412505 is a reply to message #412498] Thu, 09 July 2009 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>It is a sparse array
The sun rises in the East.
Oracle throws an error.
So are you closer to a solution knowing it is a spare array.
I am not.
Re: Composite Object [message #412506 is a reply to message #412501] Thu, 09 July 2009 14:30 Go to previous messageGo to next message
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 #412507 is a reply to message #412505] Thu, 09 July 2009 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's my fault.
There are 2 errors.
Sparse array will return ORa-1403 "no data found" not ORA-06530.
For this one, you have to give a complete initialization of or_wholesaler.

Regards
Michel
Re: Composite Object [message #412508 is a reply to message #412505] Thu, 09 July 2009 14:33 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
the datatype is a nested table but nothing is deleted.
Re: Composite Object [message #412510 is a reply to message #412498] Thu, 09 July 2009 14:36 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I have a nested object giving me error.
perhaps you should reconsider using objects you don't know how to manipulate correctly.

What value added do the nested objects contribute to the application solution?
What do nest objects achieve that can not be done with vanilla tables?
Re: Composite Object [message #412511 is a reply to message #412508] Thu, 09 July 2009 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Composite Object [message #412524 is a reply to message #412511] Thu, 09 July 2009 15:27 Go to previous message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Thanks Michel, that worked Surprised
Previous Topic: SQL
Next Topic: Generate multiple file names with fixed format
Goto Forum:
  


Current Time: Wed Dec 07 22:05:12 CST 2016

Total time taken to generate the page: 0.18715 seconds