Re: oo database and deref

In article <8g0ld0$r3v$1_at_nnrp1.deja.com>,   Thomas Edisson <smudo202_at_my-deja.com> wrote:
> I use the following statements to create object tables:
>
> CREATE OR REPLACE TYPE Player_type AS OBJECT (
> ID INTEGER,
> Title VARCHAR2(20),
> FirstName VARCHAR2(30),
> SurName VARCHAR2(30)
> );
> /
>
> CREATE TABLE Player_table OF Player_type (
> ID NOT NULL,
> SurName NOT NULL,
> PRIMARY KEY (ID)
> );
>
> In player_table ID's are running from 1 to 1000.
> When I tried to deref tuples, I get errors. Here
> are the commands:
>
> declare
> myplayer REF player_type;
> mp player_type;
> begin
> select ref(a) into myplayer from player_table a where id = 220;
> select deref(myplayer) into mp from player_table;
> end;
> /
>
> ERROR at line 1:
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at line 6
>
> What am I doing wrong? Is there a mistake I don't see?
> Any commends are appreciated!
> Thx in advance,
> Tom
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

the line:

> select deref(myplayer) into mp from player_table;

is selecting the deref of myplayer FROM EVERY ROW ni the player_table. try:

select deref(myplayer) into mp from DUAL;

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.