Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06530: Reference to uninitialized composite
ORA-06530: Reference to uninitialized composite [message #305406] Mon, 10 March 2008 12:42 Go to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Hi,

I require some help with VArrays !

I ran the following sample Varray code and it worked perfectly fine:

DECLARE
TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20);
phone_nos phone_no_tab ;
BEGIN
phone_nos := phone_no_tab();
phone_nos.EXTEND(2);
phone_nos(1) := '0117 942 2508';
DBMS_OUTPUT.PUT_LINE('phone_no(1) is '||phone_nos(1));
END;

I then created an Object Type and tried to run the same code as this:

TYPE STRUCT_JOB_DETAILS AS OBJECT
(
APPL_NO NUMBER (10),
S_FNAME VARCHAR2 (32),
S_MI VARCHAR2 (32),
S_LNAME VARCHAR2 (32),
APPL_DATE DATE,
DESCRIPTION VARCHAR2 (100),
S_UCID VARCHAR2 (10)
);

1 DECLARE
2 TYPE phone_no_tab IS VARRAY(6) OF STRUCT_JOB_DETAILS;
3 phone_nos phone_no_tab ;
4 BEGIN
5 phone_nos := phone_no_tab();
6 phone_nos.EXTEND(2);
7 phone_nos(1).APPL_NO := 0117;
8 DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is '||phone_nos(1).APPL_NO);
9 END;


But this gives me the following error: Sad

ORA-06530: Reference to uninitialized composite
ORA-06512: at line 7


Even if i rewrite the code as below there is no difference:

DECLARE
TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
phone_nos phone_no_tab ;
BEGIN
phone_nos := phone_no_tab(NULL);
phone_nos.EXTEND(2);
phone_nos(1).APPL_NO := 0117;
DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is '||phone_nos(1).APPL_NO);
END;


I have trying to resolve this for a week now ! I have no idea wats wrong ???? Confused
Require some assistance ! please !!!!
Thanks.

[Updated on: Mon, 10 March 2008 12:44]

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite [message #305413 is a reply to message #305406] Mon, 10 March 2008 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I executed this on Apex (http://apex.oracle.com, version 10.2.0.3):
DECLARE
TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
phone_nos phone_no_tab ;
BEGIN
phone_nos := phone_no_tab();
phone_nos.EXTEND(2);
phone_nos(1).APPL_NO := 0117;
DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is '||phone_nos(1).APPL_NO);
END;

And got:
phone_nos(1).APPL_NO is 117

So it correct.
What it your version (at least 3 decimals)?

Regards
Michel

[Updated on: Mon, 10 March 2008 12:58]

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite [message #305415 is a reply to message #305406] Mon, 10 March 2008 13:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Trust me I did the same and I was about the reply but I tried something else to be honest I don't know why it is behaving like that. Anyways my findings :
  1  DECLARE
  2  TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
  3  phone_nos phone_no_tab ;
  4  BEGIN
  5  phone_nos := phone_no_tab();
  6  phone_nos.EXTEND(2);
  7  --phone_nos(1).APPL_NO := 0117;
  8  phone_nos(1).s_fname := 'Hello World';
  9  DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is '||phone_nos(1).APPL_NO);
 10  DBMS_OUTPUT.PUT_LINE('phone_nos(1).s_fname is '||phone_nos(1).s_fname);
 11* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at line 8



  1  DECLARE
  2  TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
  3  phone_nos phone_no_tab ;
  4  BEGIN
  5  phone_nos := phone_no_tab();
  6  phone_nos.EXTEND(2);
  7  phone_nos(1).APPL_NO := 0117;
  8  --phone_nos(1).s_fname := 'Hello World';
  9  DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is '||phone_nos(1).APPL_NO);
 10  DBMS_OUTPUT.PUT_LINE('phone_nos(1).s_fname is '||phone_nos(1).s_fname);
 11* END;
SQL> /
phone_nos(1).APPL_NO is 117
phone_nos(1).s_fname is

PL/SQL procedure successfully completed.


  1  DECLARE
  2  TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
  3  phone_nos phone_no_tab ;
  4  BEGIN
  5  phone_nos := phone_no_tab(struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL));
  6  phone_nos.EXTEND(2);
  7  phone_nos(1).APPL_NO := 0117;
  8  phone_nos(1).s_fname := 'Hello World';
  9  DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is '||phone_nos(1).APPL_NO);
 10  DBMS_OUTPUT.PUT_LINE('phone_nos(1).s_fname is '||phone_nos(1).s_fname);
 11* END;
SQL> /
phone_nos(1).APPL_NO is 117
phone_nos(1).s_fname is Hello World

PL/SQL procedure successfully completed.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


Cheers

Raj

P.S : Version updated

[Updated on: Mon, 10 March 2008 13:02]

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite [message #305416 is a reply to message #305406] Mon, 10 March 2008 13:05 Go to previous messageGo to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Hey !

For me it does not seem to be running at all..
The error keeps popping up !

Anyways my version:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Re: ORA-06530: Reference to uninitialized composite [message #305417 is a reply to message #305415] Mon, 10 March 2008 13:08 Go to previous messageGo to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Raj,

Even after including these lines its the same error msg for me !
but i know this happens at times !

8 --phone_nos(1).s_fname := 'Hello World';
9 DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is ||phone_nos(1).APPL_NO);
10 DBMS_OUTPUT.PUT_LINE('phone_nos(1).s_fname is '||phone_nos(1).s_fname);

Re: ORA-06530: Reference to uninitialized composite [message #305418 is a reply to message #305416] Mon, 10 March 2008 13:12 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Technically speaking these are two different codes
Quote:

TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20);


Famous Tom Kyte's quotes.
Quote:
You are comparing apple with oranges
First one is a Array of struct_job_details and the second one is Array of Varchar2. So they are different and hence initialisation has to be different. To be honest we should be getting the same error as you did but I am not sure why we didn't get the error.

Regards

Raj
Re: ORA-06530: Reference to uninitialized composite [message #305420 is a reply to message #305417] Mon, 10 March 2008 13:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Initialise the collection like I did in my post and copy/paste like what I did. How to do it read the forum guidelines.

Regards

Raj
Re: ORA-06530: Reference to uninitialized composite [message #305421 is a reply to message #305406] Mon, 10 March 2008 13:16 Go to previous messageGo to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Hmmm..
Ya they surely are 2 totally different things !

And the fact that the code is working for u guys is puzzling me..
The only thing here that seems to be different are the Oracle versions.

I dunno exactly if that is the cause.. I have no idea !
Re: ORA-06530: Reference to uninitialized composite [message #305423 is a reply to message #305406] Mon, 10 March 2008 13:30 Go to previous messageGo to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Raj,

I executed the sequence of steps as u have done!
This code seems to be working perfectly fine !!!!

DECLARE
	TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
	phone_nos phone_no_tab ;
BEGIN
	[B]phone_nos := phone_no_tab(struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL));[/B]
	phone_nos.EXTEND(2);
	phone_nos(1).APPL_NO := 0117;
	phone_nos(1).s_fname := 'Hello World';
	DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is '||phone_nos(1).APPL_NO);
	DBMS_OUTPUT.PUT_LINE('phone_nos(1).s_fname is '||phone_nos(1).s_fname);
END;


Thanks a lot !!!!!!!!!!!!!!!!!!!!!!!!!!!
This is really very very helpful...

But how did it work for u w/o the initialization in the first place !!!!
Re: ORA-06530: Reference to uninitialized composite [message #305424 is a reply to message #305423] Mon, 10 March 2008 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe 10g improvements.

Regards
Michel
Re: ORA-06530: Reference to uninitialized composite [message #305427 is a reply to message #305423] Mon, 10 March 2008 13:49 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am initialising it. But in the following code I am not initalising the collection before extending it. Because internally it gets translated to malloc and calloc (C Functions). So you have to be more explicit when dealing with types. The only reason what I can think of is the way they allocate memory structures internally might have changed between 9i and 10g.
SQL> DECLARE
  2  TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
  3  phone_nos phone_no_tab ;
  4  BEGIN
  5  --phone_nos := phone_no_tab();
  6  phone_nos.EXTEND(2);
  7  phone_nos(1).APPL_NO := 0117;
  8  --phone_nos(1).s_fname := 'Hello World';
  9  DBMS_OUTPUT.PUT_LINE('phone_nos(1).APPL_NO is '||phone_nos(1).APPL_NO);
 10  DBMS_OUTPUT.PUT_LINE('phone_nos(1).s_fname is '||phone_nos(1).s_fname);
 11  END;
 12  /
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 6



If you are C programmer try this. First example is an unitialised collection.

$ cat test.c
#include <stdio.h>
void main()
{
  char *p;
  strcpy(p,"Hello world");
  printf("%s",p);
}
$cc test.c
$ a.out
Bus error(coredump)

cat test.c
#include <stdio.h>
void main()
{
   char *p;
   p=(char *)malloc(10);
   strcpy(p,"Hello world");
  printf("%s",p);
  free(p);
}

$ a.out
Hello world$ 

All these are my guess or assumptions. I could be totally wrong as well.

Regards

Raj
Re: ORA-06530: Reference to uninitialized composite [message #305436 is a reply to message #305406] Mon, 10 March 2008 14:49 Go to previous messageGo to next message
drink25up
Messages: 11
Registered: March 2008
Junior Member
Thanks a lot for that explanation !!!

However, my prob seems to be coming back now !
I changed the code a bit !
N i am back to the same error Sad

DECLARE
    TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
    phone_nos phone_no_tab ;
    i NUMBER := 0;
BEGIN
    phone_nos := phone_no_tab(struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL));
    --phone_nos.extend(6);
    FOR i IN 1.. 5
    LOOP
        phone_nos.extend;
        DBMS_OUTPUT.PUT_LINE ('i = ' || i); 
        phone_nos(i).APPL_NO := i;
        phone_nos(i).s_fname := 'Hello World ' || i;
        DBMS_OUTPUT.PUT_LINE ('phone_nos(i).APPL_NO is ' || phone_nos(i).APPL_NO);
        DBMS_OUTPUT.PUT_LINE ('phone_nos(i).s_fname is ' || phone_nos(i).s_fname);
    END LOOP;   
END;


ORA-06530: Reference to uninitialized composite
ORA-06512: at line 12

Its been initialized. Why does this error reappear now ??
Re: ORA-06530: Reference to uninitialized composite [message #305449 is a reply to message #305436] Mon, 10 March 2008 16:13 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
My apologies. I overlooked what you have written. What you have declared is a fixed size array. That's the reason why you are hitting that error and you have initialise it in one single go. Extend method do extend it but you have to initalize it before you can populate. So my suggestion is remove the varray and use nested table. It's lot more convenient and easier.

SQL> l
  1  DECLARE
  2      TYPE phone_no_tab IS VARRAY(10) OF STRUCT_JOB_DETAILS;
  3      phone_nos phone_no_tab ;
  4      i NUMBER := 0;
  5  BEGIN
  6      phone_nos := phone_no_tab(struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
  7                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
  8                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
  9                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 10                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 11                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 12                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 13                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 14                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL),
 15                                struct_job_details(NULL,NULL,NULL,NULL,NULL,NULL,NULL));
 16      FOR i IN 1.. 10
 17      LOOP
 18          DBMS_OUTPUT.PUT_LINE ('i = ' || i);
 19          phone_nos(i).APPL_NO := i;
 20          phone_nos(i).s_fname := 'Hello World ' || i;
 21          DBMS_OUTPUT.PUT_LINE ('phone_nos(i).APPL_NO is ' || phone_nos(i).APPL_NO);
 22          DBMS_OUTPUT.PUT_LINE ('phone_nos(i).s_fname is ' || phone_nos(i).s_fname);
 23          dbms_output.put_line('Count is : ' || phone_nos.count);
 24      END LOOP;
 25* END;
SQL> /
i = 1
phone_nos(i).APPL_NO is 1
phone_nos(i).s_fname is Hello World 1
Count is : 10
i = 2
phone_nos(i).APPL_NO is 2
phone_nos(i).s_fname is Hello World 2
Count is : 10
i = 3
phone_nos(i).APPL_NO is 3
phone_nos(i).s_fname is Hello World 3
Count is : 10
i = 4
phone_nos(i).APPL_NO is 4
phone_nos(i).s_fname is Hello World 4
Count is : 10
i = 5
phone_nos(i).APPL_NO is 5
phone_nos(i).s_fname is Hello World 5
Count is : 10
i = 6
phone_nos(i).APPL_NO is 6
phone_nos(i).s_fname is Hello World 6
Count is : 10
i = 7
phone_nos(i).APPL_NO is 7
phone_nos(i).s_fname is Hello World 7
Count is : 10
i = 8
phone_nos(i).APPL_NO is 8
phone_nos(i).s_fname is Hello World 8
Count is : 10
i = 9
phone_nos(i).APPL_NO is 9
phone_nos(i).s_fname is Hello World 9
Count is : 10
i = 10
phone_nos(i).APPL_NO is 10
phone_nos(i).s_fname is Hello World 10
Count is : 10

PL/SQL procedure successfully completed.

Regards

Raj

[Updated on: Mon, 10 March 2008 16:21]

Report message to a moderator

Previous Topic: Creating Procedure error
Next Topic: How to Splilit The String Into Single Column using Comma As Delimiter ?
Goto Forum:
  


Current Time: Thu Dec 08 20:20:23 CST 2016

Total time taken to generate the page: 0.09587 seconds