Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06530: Reference to uninitialized composite
ORA-06530: Reference to uninitialized composite [message #419260] Thu, 20 August 2009 13:56 Go to next message
beckyduffy
Messages: 4
Registered: August 2009
Location: UK
Junior Member
I have a problem with using Oracle types and arrays

I have created 2 Oracle types as shown

CREATE OR REPLACE
type volume_rec as object (
VOL_STRT_DTM DATE
,VOL_END_DTM DATE
,VOL_QTY NUMBER(26,13) )
/

CREATE OR REPLACE
TYPE vol_rec_tab
AS VARYING ARRAY (1000) OF VOLUME_REC
/

Now I have a store procedure

CREATE FUNCTION volumes RETURN vol_rec_tab
IS
vrec_updates vol_rec_tab;

l_rc number := 0;

cursor c_vols IS
SELECT * from latest_volumes;
BEGIN
vrec_updates := vol_rec_tab();

FOR v_vols in c_vols LOOP
vrec_updates.extend;

l_rec_counter := l_rec_counter + 1;

vrec_updates(l_rc).vol_strt_dtm := v_vols.vol_strt_dtm;
vrec_updates(l_rc).vol_end_dtm := v_vols.vol_strt_dtm;
vrec_updates(l_rc).vol_qty := v_vols.vol_qty;

END LOOP;
END;


But when I try and run it I get an error ORA-06530: Reference to uninitialized composite and I can't work out how to iitialise it

I am running like this

declare
v_rec vol_rec_tab;
begin
v_rec := etm_interface_pkg.etm_biztalk_api;
end;
/


Please help - this one is driving me crazy!
Re: ORA-06530: Reference to uninitialized composite [message #419262 is a reply to message #419260] Thu, 20 August 2009 14:08 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Please post a few more times so we can be sure you are serious about this issue.

>I have a problem with using Oracle types and arrays
Perhaps then you should avoid trying to use them.
Re: ORA-06530: Reference to uninitialized composite [message #419263 is a reply to message #419262] Thu, 20 August 2009 14:18 Go to previous messageGo to next message
beckyduffy
Messages: 4
Registered: August 2009
Location: UK
Junior Member
Thanks for your 'helpful'response. I hope someone else can be more encouraging.
Re: ORA-06530: Reference to uninitialized composite [message #419266 is a reply to message #419260] Thu, 20 August 2009 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE 
  2  type volume_rec as object (
  3  VOL_STRT_DTM DATE
  4  ,VOL_END_DTM DATE
  5  ,VOL_QTY NUMBER(26,13) )
  6  /

Type created.

SQL> 
SQL> CREATE OR REPLACE 
  2  TYPE vol_rec_tab
  3  AS VARYING ARRAY (1000) OF VOLUME_REC
  4  /

Type created.

SQL> CREATE FUNCTION volumes RETURN vol_rec_tab
  2  IS
  3  vrec_updates vol_rec_tab;
  4  
  5  l_rc number := 0;
  6  
  7  cursor c_vols IS
  8  SELECT * from latest_volumes;
  9  BEGIN
 10  vrec_updates := vol_rec_tab();
 11  
 12  FOR v_vols in c_vols LOOP 
 13  vrec_updates.extend;
 14  
 15  l_rec_counter := l_rec_counter + 1;
 16  
 17  vrec_updates(l_rc).vol_strt_dtm := v_vols.vol_strt_dtm; 
 18  vrec_updates(l_rc).vol_end_dtm := v_vols.vol_strt_dtm; 
 19  vrec_updates(l_rc).vol_qty := v_vols.vol_qty; 
 20  
 21  END LOOP;
 22  END;
 23  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION VOLUMES:
LINE/COL
--------------------------------------------------------------------
ERROR
--------------------------------------------------------------------
8/1
PL/SQL: SQL Statement ignored
8/15
PL/SQL: ORA-00942: table or view does not exist
15/1
PL/SQL: Statement ignored
15/1
PLS-00201: identifier 'L_REC_COUNTER' must be declared
17/1
PL/SQL: Statement ignored
17/36
PLS-00364: loop index variable 'V_VOLS' use is invalid
18/1
PL/SQL: Statement ignored
18/35
PLS-00364: loop index variable 'V_VOLS' use is invalid
19/1
PL/SQL: Statement ignored
19/31
PLS-00364: loop index variable 'V_VOLS' use is invalid

Maybe we could help if we could reproduce what you had. This is not the case.
Post a working Test case: create table and insert statements along with the result you want with these data.

Nefore please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Thu, 20 August 2009 14:22]

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite [message #419267 is a reply to message #419263] Thu, 20 August 2009 14:23 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
there are a few hits... Try this one http://www.orafaq.com/forum/t/54324/0/
Re: ORA-06530: Reference to uninitialized composite [message #419268 is a reply to message #419260] Thu, 20 August 2009 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By the way, your function does not return any value, I doubt it compiles.

Regards
Michel
Re: ORA-06530: Reference to uninitialized composite [message #419271 is a reply to message #419268] Thu, 20 August 2009 14:57 Go to previous messageGo to next message
beckyduffy
Messages: 4
Registered: August 2009
Location: UK
Junior Member
OK Try again
    CREATE OR REPLACE
       type volume_rec2 as object (
      VOL_STRT_DTM                             DATE
    , VOL_END_DTM                               DATE
    , VOL_QTY                                   NUMBER(26,13) )
SQL> /
 
Type created.

    CREATE OR REPLACE
    TYPE vol_rec_tab2
    AS VARYING ARRAY (1000) OF VOLUME_REC2
SQL> /

Type created.

  CREATE TABLE LATEST_VOLUMES2
  ( VOL_STRT_DTM              DATE
  , VOL_END_DTM               DATE
 , VOL_QTY                   NUMBER(26,13) )
SQL> /

Table created.

SQL> INSERT INTO LATEST_VOLUMES2 VALUES (SYSDATE -5, SYSDATE-4, 32);

1 row created.

SQL> INSERT INTO LATEST_VOLUMES2 VALUES (SYSDATE -4, SYSDATE-3, 37);

1 row created.

    CREATE OR REPLACE FUNCTION volumes RETURN vol_rec_tab2
    IS
      vrec_updates vol_rec_tab2;
      l_rc number := 0;
      cursor c_vols IS
        SELECT * from latest_volumes2;
    BEGIN
      vrec_updates := vol_rec_tab2();
      FOR v_vols in c_vols LOOP
       vrec_updates.extend;
       l_rc := l_rc + 1;
       vrec_updates(l_rc).vol_strt_dtm := v_vols.vol_strt_dtm;
       vrec_updates(l_rc).vol_end_dtm := v_vols.vol_strt_dtm;
       vrec_updates(l_rc).vol_qty := v_vols.vol_qty;
     END LOOP;
    RETURN vrec_updates;
  END;
SQL> /


But when I come to execute I get the problem

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  v_rec vol_rec_tab2;
  3  begin
  4  v_rec := volumes;
  5* end;
SQL> /
1
2
declare
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "VPPEXTRACT.VOLUMES", line 14
ORA-06512: at line 4


[Mod-Edit: Frank added [code] tags to improve readability of code-segments]

[Updated on: Fri, 21 August 2009 01:16] by Moderator

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite [message #419294 is a reply to message #419271] Fri, 21 August 2009 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
OK Try again

OK, try again formatting your post as requested.

Regards
Michel
Re: ORA-06530: Reference to uninitialized composite [message #419304 is a reply to message #419294] Fri, 21 August 2009 01:43 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
your function should be changed like this and then try

CREATE OR REPLACE FUNCTION volumes RETURN vol_rec_tab2
    IS
      vrec_updates vol_rec_tab2;
      l_rc number := 0;
      cursor c_vols IS
        SELECT * from latest_volumes2;
    BEGIN
      vrec_updates := vol_rec_tab2();
      FOR v_vols in c_vols LOOP
       vrec_updates.extend;
       l_rc := l_rc + 1;
       vrec_updates(l_rc) := volume_rec2(null,null,null);
       vrec_updates(l_rc).vol_strt_dtm := v_vols.vol_strt_dtm;
       vrec_updates(l_rc).vol_end_dtm := v_vols.vol_strt_dtm;
       vrec_updates(l_rc).vol_qty := v_vols.vol_qty;
     END LOOP;
    RETURN vrec_updates;
  END;






Re: ORA-06530: Reference to uninitialized composite [message #419305 is a reply to message #419304] Fri, 21 August 2009 01:48 Go to previous messageGo to next message
beckyduffy
Messages: 4
Registered: August 2009
Location: UK
Junior Member
Thanks bonker and too all the others who replied to me personally rather than incur the wrath of Michel or the Black Swan.
Re: ORA-06530: Reference to uninitialized composite [message #419306 is a reply to message #419305] Fri, 21 August 2009 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
beckyduffy wrote on Fri, 21 August 2009 08:48
Thanks bonker and too all the others who replied to me personally rather than incur the wrath of Michel or the Black Swan.

There is no wrath. I saw that you profile mention "Data Migration Consultant", if you write your report and make your migration like you posted I am scared for your customer.
Learning to adapt to your environment and well and proper explain and report is likely the most important skills for a consultant.
I'm proud to show you your weakness then you can improve yourself. There are different ways to help.

Regards
Michel

[Updated on: Fri, 21 August 2009 02:02]

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite [message #419522 is a reply to message #419260] Sun, 23 August 2009 21:31 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Michel is pointing out you need to observe the proper niceties in our formus. We are all busy poeple with jobs. We participate in these forums because we want to share our wisdom and experience with others who need it. But if you can't take the time to make sure code you post is formatted and is testable, then why should we take our time to give you an answer when there are a hundred other people who also want an answer to some problem and they ARE willing to read forum guidelines when told, and to learn how to format code when posting it here, and to make sure their posted examples work. EH?

HERE IS THE SOLUTION TO YOUR PROBLEM.

The collection you are using is not initialized.

  3  vrec_updates vol_rec_tab;

should read as

  3  vrec_updates vol_rec_tab := vol_rec_tab();

this causes the collection to initialize to an empty set. Once intialized to an empty set, records can be created and elements filled in.

Failing to do this requires you to use whole object assignment when setting the collection.

select cast(multiset(
                     select vol_strt_dtm,vol_end_dtm,vol_qty
                     from latests_volumes2
                    ) as vol_rec_tab
           )
into vrec_updates
from dual
;

but this is not what you are doing as you are looping through data which is way less efficient unless you need to actually modify the data in some way that cannot be done in your sql. But what cannot be done in sql? Some thing maybe but most stuff can so there is almost never a reason to do looping.

In addition you are assigning values incorrectly to your collection type. You must assign then entire row (e.g. you must assign all values of an array entry at one time), not pieces. Pieces can be assigned to an object variable but not a collection variable.

Here is a corrected version of your code.

CREATE OR REPLACE FUNCTION volumes RETURN vol_rec_tab2
    IS
      vrec_updates vol_rec_tab2 := vol_rec_tab2();
      l_rc number := 0;
      cursor c_vols IS
        SELECT * from latest_volumes2;
    BEGIN
      FOR v_vols in c_vols LOOP
        vrec_updates.extend;
        vrec_updates(vrec_updates.last) := volume_rec2 (v_vols.vol_strt_dtm,v_vols.vol_strt_dtm,v_vols.vol_qty);
      END LOOP;
      RETURN vrec_updates;
    END;
/


Notice how we are assigning a complete row to an instance of your array.

Here also is the WHOLE OBJECT ASSIGNMENT variation.

CREATE OR REPLACE FUNCTION volumes RETURN vol_rec_tab2
    IS
      vrec_updates vol_rec_tab2 := vol_rec_tab2();
    BEGIN
      select cast(multiset(
                           select vol_strt_dtm,vol_end_dtm,vol_qty
                           from LATEST_VOLUMES2
                          ) as vol_rec_tab2
                 )
      into vrec_updates
      from dual
      ;
      RETURN vrec_updates;
    END;
/

Please try the following;

1) make the fix I note above and prove your stuff works
2) google whole object assignment in oracle plsql and learn what it means and how to do it
3) rewrite your routine to use whole object assignemt and see how much faster it goes


As one last example of issues with posting. I presume from your last post that you may have gotten this answer as a private message from someone else? If so shame on them and shame on you. What good is this thread if in the end you got an answer and then decided to hide it from everyone else because you failed to post the anwer in this thread? Very selfish.

If I am mistaken, then let my answer here be my appology.

Good luck, Kevin

[Updated on: Sun, 23 August 2009 21:53]

Report message to a moderator

Previous Topic: ora 12034 view log is younger than last refresh
Next Topic: oracle transpose
Goto Forum:
  


Current Time: Sat Dec 03 06:18:31 CST 2016

Total time taken to generate the page: 0.11422 seconds