Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06530: Reference to uninitialized composite
| ORA-06530: Reference to uninitialized composite [message #144914] |
Fri, 28 October 2005 17:55  |
pbrelin
Messages: 2
Registered: October 2005
Location: Walnut Creek, CA
|
Junior Member |
|
|
Hi, I can figure this one out.
Givin this:
create or replace TYPE msgRecordTyp IS OBJECT (
r_msgid VARCHAR2(50),
r_trackid VARCHAR2(50)
);
/
create or replace TYPE msgTableArrayTyp AS VARRAY(500) OF msgRecordTyp;
/
And then running this:
declare
msgTab msgTableTyp := msgTableTyp( msgRecordTyp(null, null) );
v_msgid varchar2(20) := 'msgid value';
v_trackid varchar2(20) := 'trackid value';
begin
--
msgTab( 1 ).r_msgid := v_msgid;
msgTab( 1 ).r_trackid := v_trackid;
dbms_output.put_line( 'Print msgid: ' || msgTab(1).r_msgid );
dbms_output.put_line( 'Print trackid: ' || msgTab(1).r_trackid );
dbms_output.put_line( 'Table count: ' || msgTab.count );
dbms_output.put_line( chr(9) );
--
msgTab.extend;
dbms_output.put_line( 'Extended a second time' );
dbms_output.put_line( 'Table count: ' || msgTab.count );
msgTab( 2 ).r_msgid := 'msgid value';
dbms_output.put_line( 'assign msgid value to msgTab' );
msgTab( 2 ).r_trackid := 'trackid value';
end;
I get:
Print msgid: msgid value
Print trackid: trackid value
Table count: 1
Extended a second time
Table count: 2
declare
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at line 16
Why can I not assign the 2nd set of values to the 2nd position in my collection?
Any ideas? Thenks in advance...
~paul
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #144916 is a reply to message #144914] |
Fri, 28 October 2005 18:03  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello,
personally, i'd use this syntax (i.e. the object type directly into
the table type), i.e.
SQL> declare
2 msgTab msgTableArrayTyp := msgTableArrayTyp();
3 v_msgid varchar2(20) := 'msgid value';
4 v_trackid varchar2(20) := 'trackid value';
5 begin
6 msgTab.EXTEND;
7 msgTab(1) := msgRecordTyp(v_msgid, v_trackid);
8 msgTab.EXTEND;
9 msgTab(2) := msgRecordTyp(v_msgid, v_trackid);
10 end;
11 /
PL/SQL procedure successfully completed.
Regards
|
|
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #145798 is a reply to message #144916] |
Sun, 06 November 2005 20:33 |
oraclenewbie2005
Messages: 5
Registered: November 2005
|
Junior Member |
|
|
Hi,
If I have created an object type with more than 30 elements
create or replace TYPE msgRecordTyp IS OBJECT (
r_msgid VARCHAR2(50),
r_trackid VARCHAR2(50),
r_test VARCHAR2(50),
...
...
...
...
);
/
create or replace TYPE msgTableArrayTyp AS VARRAY(500) OF msgRecordTyp;
/
And there are 3 cursors. I need to populate data to this array "msgTableArrayTyp" with data from 3 cursors. The FOR loop is as follows;
FOR a IN first_cursor LOOP
FOR b IN second_cursor LOOP
<array assignment>
END LOOP;
FOR c IN third_cursor LOOP
<array assignment>
END LOOP;
END LOOP;
In this case, if I use the method you provide, how can I append element on the same array without getting the "wrong argument" error. Or there is another method I can use in this case? I am using Oracle 9i.
Thanks a lot
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #145800 is a reply to message #145798] |
Sun, 06 November 2005 20:57 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Rather than hard-coding the array index, use the LAST method to find the highest index number.
FOR a IN first_cursor LOOP
FOR b IN second_cursor LOOP
msgTab.EXTEND;
msgTab(msgTab.LAST) := msgRecordTyp(a.msgid, b.trackid);
END LOOP;
FOR c IN third_cursor LOOP
msgTab.EXTEND;
msgTab(msgTab.LAST) := msgRecordTyp(a.msgid, b.trackid);
END LOOP;
END LOOP;
_____________
Ross Leishman
|
|
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #145810 is a reply to message #145802] |
Sun, 06 November 2005 23:14 |
oraclenewbie2005
Messages: 5
Registered: November 2005
|
Junior Member |
|
|
Thanks for your prompt reply. I forgot to mention, in my case, the object that I created has 30 elements.
I need to populate the first 10 from the first cursor, the second 10 from the second cursor and the last 10 from the third cursor to the array. The reason is that these data was stored in 3 different tables.
In this case, how can I do it?
Thanks a lot!
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #145818 is a reply to message #145810] |
Mon, 07 November 2005 00:41 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You haven't given us enough to go on. How do the records in the 1st cursor relate to those in the 2nd and 3rd cursor.
Give us
- CREATE TABLE and INSERT statements to build test data
- CURSOR definitions for all 3 cursors
- CREATE TYPE statements
- Your desired outputs
Try to cut it down to just the key fields to keep it manageable.
_____________
Ross Leishman
|
|
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #145946 is a reply to message #145831] |
Mon, 07 November 2005 17:25 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Sorry, there's not enough information here. Your example includes only one loop through the 2nd and 3rd cursors for each row returned from the first cursor.
What's not clear to me is how the rows in the 2nd and 3rd cursor are matched up into rows if they return 2 or more rows each.
eg. For a given row (A) in the first cursor, the 2nd cursor returns 2 rows: (B1) and (B2), and the 3rd cursor returns 2 rows (C1) and (C2). Is there some relationship that will allow us to match the B rows to the C rows?
(A) (B1) (C1)
(A) (B2) (C2)
If not, do we perform a cartesian product?
(A) (B1) (C1)
(A) (B1) (C2)
(A) (B2) (C1)
(A) (B2) (C2)
or treat each one separately?
(A) (B1)
(A) (B2)
(A) (C1)
(A) (C2)
_____________
Ross Leishman
|
|
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #145966 is a reply to message #145951] |
Mon, 07 November 2005 20:51 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
In which case, you need only 1 cursor:
CURSOR test_ord (p_premise_id VARCHAR2) IS
SELECT
oo.ORDER_NUM OrderNumber,
oo.acct_num acct_num,
to_char(oo.completed, 'hh24:mi:ss') OrderCompletionDate,
to_char(oo.completed, 'yyyy-mm-dd') OrderCompletionTime,
oo.FunctionCd1,
aos.latest_assignment LatestAssignmentId,
m0.mobile0_data1,
m0.mobile0_data2,
m0.mobile0_data3,
m1.mobile1_data1,
m1.mobile1_data2,
m1.mobile1_data3
FROM test_order oo, test_asn aos,
test_report sr, test_mobile0 m0,
test_report sr1, test_mobile1 m1
WHERE oo.premise = p_premise
AND oo.order_id = aos.for_order;
AND sr.assignment = aos.latest_assignment
AND sr.report_type =
(SELECT srd.report_id FROM test_report_def srd
WHERE LOWER(srd.NAME) = 'mobile0')
AND m0.mobile0_id(+) = sr.record_num
AND sr1.assignment = aos.latest_assignment
AND sr1.report_type =
(SELECT srd.report_id FROM test_report_def srd
WHERE LOWER(srd.NAME) = 'mobile1')
AND m1.mobile1_id(+) = sr1.record_num;
This now returns all of the values you need to fill your object.
Some tips to smarten it up:
1. Convert the joins to v9i ANSI joins.
2. Change the SELECT to use the test_row() constructor, so that it returns the object without having to instantiate it in PL/SQL
3. BULK COLLECT the SQL into a test_row_array() collection
_____________
Ross Leishman
[Updated on: Mon, 07 November 2005 20:52] Report message to a moderator |
|
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #176036 is a reply to message #144914] |
Tue, 06 June 2006 08:32 |
smora
Messages: 59
Registered: May 2006
|
Member |
|
|
Im doing something similar to the original post, only in a PL/SQL function. Im getting the ORA-06530: Reference to uninitialized composite error. Any ideas would be appreciated.
Using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
SQL> CREATE OR REPLACE TYPE leaderboard_obj AS OBJECT(
2 l_sub_id NUMBER(10),
3 l_score NUMBER(10),
4 l_rank NUMBER(2),
5 l_date DATE
6 );
7 /
Type created.
Elapsed: 00:00:01.71
SQL>
SQL> CREATE OR REPLACE TYPE leader_array AS VARRAY(10) OF leaderboard_obj;
2 /
Type created.
And then trying to use the type in a function:
create or replace function leaderboard_daily
(p_game_id IN games.game_id%TYPE)
RETURN leader_array
AS
v_leaderboard leader_array := leader_array();
loop_index NUMBER(2) := 1;
...
...
The function gets created fine, but when i try to run it i get the ORA-06530: Reference to uninitialized composite error.
Thank you.
|
|
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #176115 is a reply to message #144914] |
Wed, 07 June 2006 01:19 |
smora
Messages: 59
Registered: May 2006
|
Member |
|
|
Game and game_score tables:
SQL> desc games
Name Null? Type
----------------------------------------- -------- -------------
GAME_ID NOT NULL NUMBER(4)
GAME_NAME VARCHAR2(20)
GAME_LAUNCH_DATE DATE
GAME_RATING NUMBER(3,1)
SQL> desc game_scores
Name Null? Type
----------------------------------------- -------- -------------
SUB_ID NOT NULL NUMBER(10)
GAME_ID NOT NULL NUMBER(4)
SCORE NOT NULL NUMBER(10)
SCORE_DATE NOT NULL DATE
The full function is:
create or replace function leaderboard_daily
(p_game_id IN games.game_id%TYPE)
RETURN leader_array
AS
v_leaderboard leader_array := leader_array();
loop_index NUMBER(2) := 1;
CURSOR LeaderboardCursor IS
select sub_id, score, score_date from hovrads.game_scores
where game_id = p_game_id
and to_char(score_date, 'YYYYMMDD') = to_char(sysdate, 'YYYYMMDD')
order by score desc;
v_ldrcursor LeaderboardCursor%ROWTYPE;
BEGIN
OPEN LeaderboardCursor;
LOOP
FETCH LeaderboardCursor INTO v_ldrcursor;
EXIT WHEN (LeaderboardCursor%NOTFOUND OR loop_index > 10);
dbms_output.put_line('Loop index: ' || loop_index);
dbms_output.put_line('Sub id: ' || v_ldrcursor.sub_id);
dbms_output.put_line('Score: ' || v_ldrcursor.score);
dbms_output.put_line('Score date: ' || v_ldrcursor.score_date);
v_leaderboard.EXTEND;
v_leaderboard(loop_index).l_sub_id := v_ldrcursor.sub_id;
v_leaderboard(loop_index).l_score := v_ldrcursor.score;
v_leaderboard(loop_index).l_date := v_ldrcursor.score_date;
-- v_leaderboard(loop_index).l_rank := 1;
loop_index := loop_index + 1;
END LOOP;
CLOSE LeaderboardCursor;
RETURN v_leaderboard;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Exception: ' || SQLCODE);
RETURN NULL;
WHEN OTHERS THEN
dbms_output.put_line('Exception: ' || SQLCODE);
RETURN NULL;
END leaderboard_daily;
/
This is what happens when i create and run it:
SQL> @C:\TEMP\leaderboard_daily.txt;
Function created.
SQL> select leaderboard_daily(1) from dual;
LEADERBOARD_DAILY(1)(L_SUB_ID, L_SCORE, L_RANK, L_DATE)
--------------------------------------------------------
Loop index: 1
Sub id: 24
Score: 999910000
Score date: 2006-06-07 08:46:47
Exception: -6530
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #176315 is a reply to message #176115] |
Wed, 07 June 2006 18:40 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- reproduction of exception:
SCOTT@10gXE> CREATE TABLE games
2 (GAME_ID NUMBER(4) NOT NULL,
3 GAME_NAME VARCHAR2(20),
4 GAME_LAUNCH_DATE DATE,
5 GAME_RATING NUMBER(3,1))
6 /
Table created.
SCOTT@10gXE> CREATE TABLE game_scores
2 (game_id NUMBER,
3 sub_id NUMBER,
4 score NUMBER,
5 score_date DATE)
6 /
Table created.
SCOTT@10gXE> INSERT INTO game_scores VALUES (1, 24, 999910000, SYSDATE)
2 /
1 row created.
SCOTT@10gXE> INSERT INTO game_scores VALUES (1, 25, 999909999, SYSDATE)
2 /
1 row created.
SCOTT@10gXE> CREATE OR REPLACE TYPE leaderboard_obj AS OBJECT(
2 l_sub_id NUMBER(10),
3 l_score NUMBER(10),
4 l_rank NUMBER(2),
5 l_date DATE
6 );
7 /
Type created.
SCOTT@10gXE> CREATE OR REPLACE TYPE leader_array AS VARRAY(10) OF leaderboard_obj;
2 /
Type created.
SCOTT@10gXE> create or replace function leaderboard_daily
2 (p_game_id IN games.game_id%TYPE)
3 RETURN leader_array
4
5 AS
6
7 v_leaderboard leader_array := leader_array();
8 loop_index NUMBER(2) := 1;
9
10 CURSOR LeaderboardCursor IS
11 select sub_id, score, score_date from game_scores
12 where game_id = p_game_id
13 and to_char(score_date, 'YYYYMMDD') = to_char(sysdate, 'YYYYMMDD')
14 order by score desc;
15
16 v_ldrcursor LeaderboardCursor%ROWTYPE;
17
18 BEGIN
19
20 OPEN LeaderboardCursor;
21 LOOP
22 FETCH LeaderboardCursor INTO v_ldrcursor;
23 EXIT WHEN (LeaderboardCursor%NOTFOUND OR loop_index > 10);
24
25 dbms_output.put_line('Loop index: ' || loop_index);
26 dbms_output.put_line('Sub id: ' || v_ldrcursor.sub_id);
27 dbms_output.put_line('Score: ' || v_ldrcursor.score);
28 dbms_output.put_line('Score date: ' || v_ldrcursor.score_date);
29
30 v_leaderboard.EXTEND;
31 v_leaderboard(loop_index).l_sub_id := v_ldrcursor.sub_id;
32 v_leaderboard(loop_index).l_score := v_ldrcursor.score;
33 v_leaderboard(loop_index).l_date := v_ldrcursor.score_date;
34 -- v_leaderboard(loop_index).l_rank := 1;
35
36 loop_index := loop_index + 1;
37
38 END LOOP;
39 CLOSE LeaderboardCursor;
40
41 RETURN v_leaderboard;
42
43 EXCEPTION
44
45 WHEN NO_DATA_FOUND THEN
46 dbms_output.put_line('Exception: ' || SQLCODE);
47 RETURN NULL;
48 WHEN OTHERS THEN
49 dbms_output.put_line('Exception: ' || SQLCODE);
50 RETURN NULL;
51
52 END leaderboard_daily;
53 /
Function created.
SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> select leaderboard_daily(1) from dual
2 /
LEADERBOARD_DAILY(1)(L_SUB_ID, L_SCORE, L_RANK, L_DATE)
----------------------------------------------------------------------------------------------------
Loop index: 1
Sub id: 24
Score: 999910000
Score date: 2006-06-07 16:35:21
Exception: -6530
-- minimal correction:
SCOTT@10gXE> create or replace function leaderboard_daily
2 (p_game_id IN games.game_id%TYPE)
3 RETURN leader_array
4
5 AS
6
7 v_leaderboard leader_array := leader_array();
8 loop_index NUMBER(2) := 1;
9
10 CURSOR LeaderboardCursor IS
11 select sub_id, score, score_date from game_scores
12 where game_id = p_game_id
13 and to_char(score_date, 'YYYYMMDD') = to_char(sysdate, 'YYYYMMDD')
14 order by score desc;
15
16 v_ldrcursor LeaderboardCursor%ROWTYPE;
17
18 BEGIN
19
20 OPEN LeaderboardCursor;
21 LOOP
22 FETCH LeaderboardCursor INTO v_ldrcursor;
23 EXIT WHEN (LeaderboardCursor%NOTFOUND OR loop_index > 10);
24
25 dbms_output.put_line('Loop index: ' || loop_index);
26 dbms_output.put_line('Sub id: ' || v_ldrcursor.sub_id);
27 dbms_output.put_line('Score: ' || v_ldrcursor.score);
28 dbms_output.put_line('Score date: ' || v_ldrcursor.score_date);
29
30 v_leaderboard.EXTEND;
31 v_leaderboard(loop_index) := leaderboard_obj (v_ldrcursor.sub_id,
32 v_ldrcursor.score,
33 loop_index,
34 v_ldrcursor.score_date);
35
36 loop_index := loop_index + 1;
37
38 END LOOP;
39 CLOSE LeaderboardCursor;
40
41 RETURN v_leaderboard;
42
43 EXCEPTION
44
45 WHEN NO_DATA_FOUND THEN
46 dbms_output.put_line('Exception: ' || SQLCODE);
47 RETURN NULL;
48 WHEN OTHERS THEN
49 dbms_output.put_line('Exception: ' || SQLCODE);
50 RETURN NULL;
51
52 END leaderboard_daily;
53 /
Function created.
SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> select leaderboard_daily(1) from dual
2 /
LEADERBOARD_DAILY(1)(L_SUB_ID, L_SCORE, L_RANK, L_DATE)
----------------------------------------------------------------------------------------------------
LEADER_ARRAY(LEADERBOARD_OBJ(24, 999910000, 1, '2006-06-07 16:35:21'), LEADERBOARD_OBJ(25, 999909999
, 2, '2006-06-07 16:35:21'))
Loop index: 1
Sub id: 24
Score: 999910000
Score date: 2006-06-07 16:35:21
Loop index: 2
Sub id: 25
Score: 999909999
Score date: 2006-06-07 16:35:21
SCOTT@10gXE> set serveroutput off
SCOTT@10gXE> select t.* from table (leaderboard_daily(1)) t
2 /
L_SUB_ID L_SCORE L_RANK L_DATE
---------- ---------- ---------- -------------------
24 999910000 1 2006-06-07 16:35:21
25 999909999 2 2006-06-07 16:35:21
SCOTT@10gXE>
|
|
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #176381 is a reply to message #176315] |
Thu, 08 June 2006 02:45 |
smora
Messages: 59
Registered: May 2006
|
Member |
|
|
Quick question: In the above example, you accessed the output of the function by using the 'TABLE' pseudo-function:
select t.* from table (leaderboard_daily(1)) t
If I execute the above statement multiple times concurrently with different parameters, will the multiple tables 't' created clash with each other? How do these multiple temporary tables 't' stay independent?
Thank you.
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #176494 is a reply to message #176381] |
Thu, 08 June 2006 09:08 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The multiple calls to table functions and usage of the table keyword will not clash. It is the same as if you were using any other functions. If you are going to use them in the same query, then you will need to supply different aliases, like t1 and t2, within that query. The online documentation says the following:
"Multiple Calls to Table Functions
Multiple invocations of a table function, either within the same query or in separate queries result in multiple executions of the underlying implementation. That is, in general, there is no buffering or reuse of rows.
For example,
SELECT * FROM TABLE(f(...)) t1, TABLE(f(...)) t2
WHERE t1.id = t2.id;
SELECT * FROM TABLE(f());
SELECT * FROM TABLE(f());"
|
|
|
|
|
|
| Re: ORA-06530: Reference to uninitialized composite [message #176562 is a reply to message #144914] |
Thu, 08 June 2006 14:47 |
smora
Messages: 59
Registered: May 2006
|
Member |
|
|
Thanks for all your help Barbara.
The sql u suggested would return the ranking for all the scores selected, essentially 1 to 10 (since we are returning the top 10 scores). I need the top 10 scores for the given day, but the rank of the score on an all time list of scores.
So assuming there was a score of 1000000000 on 2006-06-06, and we run the function on 2006-06-08, the table should look like:
SUB_ID SCORE RANKING SCORE_DATE
---------- ---------- ---------- -------------------
24 999910000 2 2006-06-08 07:18:35
25 999909999 3 2006-06-08 07:18:35
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 17 20:19:57 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
