| ORA-06530: Reference to uninitialized composite [message #419260] |
Thu, 20 August 2009 13:56  |
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 #419266 is a reply to message #419260] |
Thu, 20 August 2009 14:22  |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #419271 is a reply to message #419268] |
Thu, 20 August 2009 14:57 |
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 #419304 is a reply to message #419294] |
Fri, 21 August 2009 01:43 |
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 #419306 is a reply to message #419305] |
Fri, 21 August 2009 02:02 |
|
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
 |
Kevin Meade
Messages: 2103
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
