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 Go to next message
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 Go to previous messageGo to next message
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 #144919 is a reply to message #144916] Fri, 28 October 2005 18:28 Go to previous messageGo to next message
pbrelin
Messages: 2
Registered: October 2005
Location: Walnut Creek, CA
Junior Member
Super, that did it.

Thanks!
Re: ORA-06530: Reference to uninitialized composite [message #145798 is a reply to message #144916] Sun, 06 November 2005 20:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3701
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 #145802 is a reply to message #145798] Sun, 06 November 2005 21:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8008
Registered: November 2002
Location: California, USA
Senior Member
scott@ORA92> create or replace TYPE msgRecordTyp AS OBJECT
  2    (r_msgid   VARCHAR2(50),
  3  	r_trackid VARCHAR2(50),
  4  	r_test	  VARCHAR2(50));
  5  /

Type created.

scott@ORA92> create or replace TYPE msgTableArrayTyp AS VARRAY(500) OF msgRecordTyp;
  2  /

Type created.

scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> DECLARE
  2    msgTab msgTableArrayTyp := msgTableArrayTyp();
  3  BEGIN
  4    FOR a IN
  5  	 -- first_cursor:
  6  	(SELECT * FROM dept)
  7    LOOP
  8  	 FOR b IN
  9  	   -- second_cursor:
 10  	   (SELECT * FROM emp
 11  	    WHERE  deptno = a.deptno
 12  	    AND    job = 'CLERK')
 13  	 LOOP
 14  	   -- array assignment:
 15  	   msgTab.EXTEND;
 16  	   msgTab(msgTAb.LAST) := msgRecordTyp (a.dname, b.ename, b.job);
 17  	 END LOOP;
 18  	 FOR c IN
 19  	   -- third_cursor:
 20  	   (SELECT * FROM emp
 21  	    WHERE  deptno = a.deptno
 22  	    AND    job = 'SALESMAN')
 23  	 LOOP
 24  	   -- array assignment:
 25  	   msgTab.EXTEND;
 26  	   msgTab(msgTAb.LAST) := msgRecordTyp (a.dname, c.ename, c.job);
 27  	 END LOOP;
 28    END LOOP;
 29    OPEN :g_ref FOR
 30    SELECT * FROM TABLE (CAST (msgTab AS msgTableArrayTyp));
 31  END;
 32  /

PL/SQL procedure successfully completed.

scott@ORA92> COLUMN r_msgid   FORMAT A15
scott@ORA92> COLUMN r_trackid FORMAT A15
scott@ORA92> COLUMN r_test    FORMAT A15
scott@ORA92> PRINT :g_ref

R_MSGID         R_TRACKID       R_TEST
--------------- --------------- ---------------
ACCOUNTING      MILLER          CLERK
RESEARCH        SMITH           CLERK
RESEARCH        ADAMS           CLERK
SALES           JAMES           CLERK
SALES           ALLEN           SALESMAN
SALES           WARD            SALESMAN
SALES           MARTIN          SALESMAN
SALES           TURNER          SALESMAN

8 rows selected.


Re: ORA-06530: Reference to uninitialized composite [message #145810 is a reply to message #145802] Sun, 06 November 2005 23:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3701
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 #145831 is a reply to message #145818] Mon, 07 November 2005 02:08 Go to previous messageGo to next message
oraclenewbie2005
Messages: 5
Registered: November 2005
Junior Member
I have uploaded the file and my desired result is also included in the file (at the end).

Thanks for your help
  • Attachment: test.sql
    (Size: 9.01KB, Downloaded 515 times)
Re: ORA-06530: Reference to uninitialized composite [message #145946 is a reply to message #145831] Mon, 07 November 2005 17:25 Go to previous messageGo to next message
rleishman
Messages: 3701
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 #145951 is a reply to message #145946] Mon, 07 November 2005 18:29 Go to previous messageGo to next message
oraclenewbie2005
Messages: 5
Registered: November 2005
Junior Member
Thanks Ross.

According to the definition of the table, it will not return 2 rows for both 2nd and 3rd cursor because both always return 1 row. There is no relationship between the 2nd and 3rd cursor, but both cursors are related to the 1st cursor.

Thanks
Re: ORA-06530: Reference to uninitialized composite [message #145966 is a reply to message #145951] Mon, 07 November 2005 20:51 Go to previous messageGo to next message
rleishman
Messages: 3701
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 #146289 is a reply to message #145966] Wed, 09 November 2005 14:05 Go to previous messageGo to next message
oraclenewbie2005
Messages: 5
Registered: November 2005
Junior Member
Thanks
I got it work now
Re: ORA-06530: Reference to uninitialized composite [message #176036 is a reply to message #144914] Tue, 06 June 2006 08:32 Go to previous messageGo to next message
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 #176058 is a reply to message #176036] Tue, 06 June 2006 12:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8008
Registered: November 2002
Location: California, USA
Senior Member
What you posted works. You need to post the whole function, including the part that does not work, along with a copy and paste of how you are using the function that causes the error. You should also post any related items, such as the structure of games.
Re: ORA-06530: Reference to uninitialized composite [message #176115 is a reply to message #144914] Wed, 07 June 2006 01:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8008
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 #176335 is a reply to message #144914] Wed, 07 June 2006 23:40 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Excellent! Thank you Barbara...
Re: ORA-06530: Reference to uninitialized composite [message #176381 is a reply to message #176315] Thu, 08 June 2006 02:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8008
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 #176495 is a reply to message #176381] Thu, 08 June 2006 09:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8008
Registered: November 2002
Location: California, USA
Senior Member
I don't know what your overall goal is or whether this is a simplified piece of something larger and needs to be returned in this format, but if all you are trying to do is obtain a numerical ranking there are easier ways, for example:

SCOTT@10gXE> SELECT sub_id, score,
  2  	    rank () over (order by score desc) as ranking,
  3  	    score_date
  4  from   game_scores
  5  where  game_id = 1
  6  order  by score desc
  7  /

    SUB_ID      SCORE    RANKING SCORE_DATE
---------- ---------- ---------- -------------------
        24  999910000          1 2006-06-08 07:18:35
        25  999909999          2 2006-06-08 07:18:35

SCOTT@10gXE>

Re: ORA-06530: Reference to uninitialized composite [message #176562 is a reply to message #144914] Thu, 08 June 2006 14:47 Go to previous messageGo to next message
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

Re: ORA-06530: Reference to uninitialized composite [message #176610 is a reply to message #176562] Thu, 08 June 2006 21:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8008
Registered: November 2002
Location: California, USA
Senior Member
Calculate the rank in an inner subquery, then limit the rows in an outer query:

SCOTT@10gXE> SELECT * FROM game_scores
  2  /

   GAME_ID     SUB_ID      SCORE SCORE_DATE
---------- ---------- ---------- -------------------
         1         23 1000000000 2006-06-06 19:47:37
         1         24  999910000 2006-06-08 19:47:37
         1         25  999909999 2006-06-08 19:47:37

SCOTT@10gXE> SELECT sub_id, score, ranking, score_date
  2  FROM   (SELECT sub_id, score,
  3  		    rank () over (order by score desc) as ranking,
  4  		    score_date
  5  	     from   game_scores
  6  	     order  by score desc)
  7  WHERE  TRUNC (score_date) = TRUNC (SYSDATE)
  8  AND    ROWNUM <= 10
  9  /

    SUB_ID      SCORE    RANKING SCORE_DATE
---------- ---------- ---------- -------------------
        24  999910000          2 2006-06-08 19:47:37
        25  999909999          3 2006-06-08 19:47:37

SCOTT@10gXE> 



Re: ORA-06530: Reference to uninitialized composite [message #176660 is a reply to message #144914] Fri, 09 June 2006 02:04 Go to previous message
smora
Messages: 59
Registered: May 2006
Member
Thanks Barbara, it's much quicker with the rank analytic fucntion. I had never paid much attention to it, only used the sum () over () occasionally. I really appreciate all your help...
Previous Topic: pl/sql stored procedure tuning
Next Topic: XML Parsing - Process Slow down
Goto Forum:
  


Current Time: Fri Nov 28 12:51:12 CST 2014

Total time taken to generate the page: 0.09816 seconds