CODEWITHSUNDEEP
sqloracle-database
nassim
nassim

Reputation: 1553

Oracle SQL3 3 nesting tables insert problem

I'm making an SQL3 script where I create types and then create tables and nested tables. The prolem I'm getting happens when I want to insert a row where it says :

ORA-00932: inconsistent datatypes: expected UDT got CHAR

this is an online reproduction of the problem where the types and tables are already created, you can try to insert: https://www.tutorialspoint.com/oracle_terminal_online.php?fbclid=IwAR0GgaLe2_GvGsEb80eB-D0uKDSDJDr1WNBPiK3mHQqpJQrtfacQ1cf03NA

the following is the types creation script 

CREATE TYPE T_Personne ;
/

CREATE TYPE T_SET_Tag AS TABLE OF Varchar2(30);
/

CREATE TYPE T_Message AS OBJECT (
    Texte Varchar2(500),
    DateEcrit Date,
    Tags T_SET_Tag
);
/

CREATE TYPE T_SET_Message AS TABLE OF T_Message;
/

CREATE TYPE T_Contact AS OBJECT(
    Per REF T_Personne,
    Depuis Date
);
/

CREATE TYPE T_SET_Contact AS TABLE OF T_Contact;
/

CREATE OR REPLACE TYPE T_Personne AS OBJECT (
    Prenom Varchar2(30),
    Suit T_SET_Contact,
    Ecrit T_SET_Message
);
/

this one is the table creation script 

CREATE TABLE TAB_Personne OF T_Personne
    NESTED TABLE Suit STORE AS TAB_suit,
    NESTED TABLE Ecrit STORE AS TAB_ecrit(
        NESTED TABLE Tags STORE AS TAB_Tags
    );

and finally the script I'm using to insert my new rows 

INSERT INTO TAB_Personne VALUES( 
    'Baam',
    T_SET_Contact(),
    T_SET_Message()
);

INSERT INTO TAB_Personne VALUES( 
    'Rachel',
    T_SET_Contact(
        (SELECT REF(P)
        FROM TAB_Personne P
        WHERE P.Prenom='Baam'), 
        to_date('01/01/2018', 'dd/mm/yyyy')
    ),
    T_SET_Message(
        'Paris candidat aux jeux Olympiques 2022',
        to_date('01/06/2019', 'dd/mm/yyyy'),
        T_SET_Tag('JM2022')
    )
);

the error message I'm getting is

1 row created.

  'Paris candidat aux jeux Olympiques 2022',      *

ERROR at line 10:

ORA-00932: inconsistent datatypes: expected UDT got CHAR

I'd be glad if anyone can guide me through this, thanks.

Upvotes: 2

Views: 145

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

You are specifying a T_SET_Message collection but you then need T_Message objects within that; you're supplying the attributes of that object type, not an actual object of the type. The first element in the (non-)collection is a string, hence the error you get - you've supplied a string ('Paris...') when it's expecting to see a UDT (T_Message('Paris...', ...)).

You are also doing the same thing with the T_SET_Contact collection.

You need to wrap your current attributes in object constructors; so this works:

INSERT INTO TAB_Personne VALUES( 
    'Rachel',
    T_SET_Contact(
        T_Contact(
            (SELECT REF(P)
            FROM TAB_Personne P
            WHERE P.Prenom='Baam'), 
            to_date('01/01/2018', 'dd/mm/yyyy')
        )
    ),
    T_SET_Message(
        T_Message(
            'Paris candidat aux jeux Olympiques 2022',
            to_date('01/06/2019', 'dd/mm/yyyy'),
            T_SET_Tag('JM2022')
        )
    )
);

db<>fiddle

Upvotes: 2

Related Questions