SQL DEVELOPER NESTED TABLE PROBLEM

Josxlu

Hi everyone

Im spanish and im searching for help because im having a problem with my database in order to insert some values and i dont know how to solve it.

The thing is i have hierarchy. In the top i have a entity call 'Library' and one Nested Table call 'Room' this entity 'Room' which is is a Nested table from 'Library' is divided in two subclasses 'Comun_Room' and 'Study_Room'. Well i hope it is clear from now. Now i have a class called 'Booking' and it is associated with 'Study Room' which means in the class of 'Booking' i have a reference to the 'Study Room'.

I just want to know how when i insert values into 'Booking' which is the order to reference the 'Study_Room's that i have created in 'Library'.

I have tried this: SELECT REF(s) FROM ROOM s WHERE ID_ROOM=1; (The Id_Room =1 it is because there is a study_room associated with that id).

But its not working.

I dont know if i have explained myself in a good way but im a little desperate and that's why i have writing something here.

Thanks for reading and for the help.

***Moderator action (Timo): moved from SQL Developer to SQL & PL/SQL space.***

odie_63

My problem is i dont know how to implement the part of Reserva and Ejemplar because they are aggregation of a subclasses belonging to a nested table of Library.

I don't understand where the problem is.

Just declare Reserva_ntabtyp and Ejemplar_ntabtyp as regular collections, not table of REFs :

CREATE TYPE Reserva_ntabtyp AS TABLE OF Reserva_objtyp;CREATE TYPE Ejemplar_ntabtyp AS TABLE OF Ejemplar_objtyp;

You only have to declare TABLE of REFs if you plan to store Reserva_objtyp (or Ejemplar_objtyp) instances in a separate object table.

Do you want to do that?

If not, your existing CREATE TABLE statement should work, Oracle will take care of creating nested tables (with system-generated names) for Reservas and Ejemplares :

CREATE TABLE Biblioteca_objtab OF Biblioteca_objtyp(  Id_biblioteca PRIMARY KEY) NESTED TABLE salas STORE AS Sala_ntab( (PRIMARY KEY (NESTED_TABLE_ID, Id_sala)));

Sample insert statement :

insert into Biblioteca_objtabvalues (  Biblioteca_objtyp(    Id_biblioteca => 1  , salas         => Sala_ntabtyp(                       SalaComun_objtyp(                         Id_Sala    => 1                       , nombre     => '123456789'                       , ejemplares => Ejemplar_ntabtyp(                                         Ejemplar_objtyp(num_registro => 12345)                                       , Ejemplar_objtyp(num_registro => 67890)                                       )                       )                     , SalaEstudio_objtyp(                         Id_Sala  => 2                       , num_sala => 99                       , reservas => Reserva_ntabtyp(                                       Reserva_objtyp(Id_reserva => 123)                                     , Reserva_objtyp(Id_reserva => 567)                                     )                       )                     )  ));

Edit:

If you require custom namings, as you did for SALA_NTAB, you can specify them like this :

CREATE TABLE Biblioteca_objtab OF Biblioteca_objtyp(  Id_biblioteca PRIMARY KEY) NESTED TABLE salas STORE AS Sala_ntab(    ( PRIMARY KEY (NESTED_TABLE_ID, Id_sala) )    NESTED TABLE treat(object_value as SalaEstudio_objtyp).reservas STORE AS Reserva_ntab (         ( PRIMARY KEY (NESTED_TABLE_ID, Id_reserva) )     )    NESTED TABLE treat(object_value as SalaComun_objtyp).ejemplares STORE AS Ejemplar_ntab (         ( PRIMARY KEY (NESTED_TABLE_ID, num_registro) )     ));

odie_63

cb07c1f9-f6f2-49b9-bbe1-67cb460538e2 wrote:I dont know if i have explained myself in a good way

Well, from your explanation, it seems like you're using an Object-Relational implementation.

Do you confirm?

If so, it might be even clearer if you could post DDLs of your objects and storage tables, along with sample data.

I just want to know how when i insert values into 'Booking' which is the order to reference the 'Study_Room's that i have created in 'Library'.I have tried this: SELECT REF(s) FROM ROOM s WHERE ID_ROOM=1; (The Id_Room =1 it is because there is a study_room associated with that id).

If you're actually using OR, then it depends on how you defined the object identifier in the ROOM table.

If it's PK-based, assuming on ID_ROOM, you can use MAKE_REF function, e.g. to build a REF value to ID_ROOM=1 row  :

MAKE_REF(room, 1)

Otherwise, you'll have to get the REF using a separate SQL (sub)query, just like what you showed.

Paulzip

Well i hope it is clear from now.

No.

As Odie says, it looks like an Object Relational situation, however, we can't realistically understand how things fully hang together from a loose description, we need to see the structure of your tables.

Please post the DDL for the tables, their relationships and some sample data via insert script.  From that it should be easy to help you.

Josxlu

Hello again!

First of all thank you so much to answer me guys i don't know how this web exactly works but i'm learning and i'm going to try to answer you much more deeply and with photos.

I'm going to ask to your questions:

It is an Object-Relational Implementation?

Yeah this is a Object-Relational implementation of a database of a University Library.

It is a project for a work of my University, this means i am a beginner in all of this database, programming in oracle... so there concepts that i don't manage very well, sorry if i'm not very exactly a some point.

Well i'm going to try to explain my problem again and with the real data and DDL images.

I have advanced a little bit more with the problem and i have seen it is more deep than i thought it isn't just related with the INSERT. It is related with the implementation. The thing is i have the next UML DIAGRAM (The part that is given me problems, i post it for you to give a mental image):

Okay the classes of the images are in spanish i translate it to you in order to understand the first explanation:

Biblioteca = Library

Sala=Room

Sala_estudio=Study_room

Sala_comun=Comun_room

Reserva=Booking

Ejemplar=Copy (this class wasnt included before this is like a COPY of a book store in the library, i included it because has the same problem that i want explain)

My problem is i dont know how to implement the part of Reserva and Ejemplar because they are aggregation of a subclasses belonging to a nested table of Library.

This is the code i have done in order to implement it but it is bad becuse i dont declare the NESTED TABLES of Ejemplar and Reserva because i dont know where declare it.

CREATE OR REPLACE TYPE Reserva_objtyp AS OBJECT (

            Id_reserva NUMBER(5),

);

CREATE TYPE Reserva_ntabtyp AS TABLE OF REF Reserva_objtyp;

CREATE TYPE Ejemplar_objtyp AS OBJECT (

            num_registro NUMBER(5),

);

CREATE TYPE Ejemplar_ntabtyp AS TABLE OF REF Ejemplar_objtyp;

CREATE TYPE Sala_objtyp AS OBJECT (

            Id_sala NUMBER (5),

) NOT FINAL NOT INSTANTIABLE;

CREATE TYPE SalaEstudio_objtyp UNDER Sala_objtyp (

            num_sala NUMBER(2),

            reservas Reserva_ntabtyp

);

CREATE TYPE SalaComun_objtyp UNDER Sala_objtyp (

     nombre VARCHAR2(20),

     ejemplares Ejemplar_ntabtyp

);

CREATE TYPE Sala_ntabtyp AS TABLE OF Sala_objtyp;

CREATE TYPE Biblioteca_objtyp AS OBJECT(

Id_biblioteca NUMBER(5),

salas Sala_ntabtyp

);

CREATE TABLE Biblioteca_objtab OF Biblioteca_objtyp(

     Id_biblioteca PRIMARY KEY,

)NESTED TABLE salas STORE AS Sala_ntab(

(PRIMARY KEY (NESTED_TABLE_ID, Id_sala),));

I hope with the image and this part of the code you could be able to understand the problem better thank you for the answers and i hope we can find a solution it will help me a lot.

Regards.

Josxlu

Hello! Thanks for the answer!

I have answer you below.

I didnt know how to reply 2 answers at the same time, that's why i write this message.

Regards.

Josxlu

Hello! Thanks for the answer!

I have answer you below.

I didnt know how to reply 2 answers at the same time, that's why i write this message.

Regards.

Paulzip

Does your solution need to be Object Relational (O.R.)?  Having worked on Object Relational DBs I can attest to them not being particularly developer friendly, especially for beginners and also particularly when schema evolution is involved.  Is O.R. part of the Uni assignment?

Josxlu

Yeah It has to be implemented in O.R

Paulzip

OK, if I get some time later I'll have a look at a solution.

Josxlu

Okey thank you so much!

odie_63

My problem is i dont know how to implement the part of Reserva and Ejemplar because they are aggregation of a subclasses belonging to a nested table of Library.

I don't understand where the problem is.

Just declare Reserva_ntabtyp and Ejemplar_ntabtyp as regular collections, not table of REFs :

CREATE TYPE Reserva_ntabtyp AS TABLE OF Reserva_objtyp;CREATE TYPE Ejemplar_ntabtyp AS TABLE OF Ejemplar_objtyp;

You only have to declare TABLE of REFs if you plan to store Reserva_objtyp (or Ejemplar_objtyp) instances in a separate object table.

Do you want to do that?

If not, your existing CREATE TABLE statement should work, Oracle will take care of creating nested tables (with system-generated names) for Reservas and Ejemplares :

CREATE TABLE Biblioteca_objtab OF Biblioteca_objtyp(  Id_biblioteca PRIMARY KEY) NESTED TABLE salas STORE AS Sala_ntab( (PRIMARY KEY (NESTED_TABLE_ID, Id_sala)));

Sample insert statement :

insert into Biblioteca_objtabvalues (  Biblioteca_objtyp(    Id_biblioteca => 1  , salas         => Sala_ntabtyp(                       SalaComun_objtyp(                         Id_Sala    => 1                       , nombre     => '123456789'                       , ejemplares => Ejemplar_ntabtyp(                                         Ejemplar_objtyp(num_registro => 12345)                                       , Ejemplar_objtyp(num_registro => 67890)                                       )                       )                     , SalaEstudio_objtyp(                         Id_Sala  => 2                       , num_sala => 99                       , reservas => Reserva_ntabtyp(                                       Reserva_objtyp(Id_reserva => 123)                                     , Reserva_objtyp(Id_reserva => 567)                                     )                       )                     )  ));

Edit:

If you require custom namings, as you did for SALA_NTAB, you can specify them like this :

CREATE TABLE Biblioteca_objtab OF Biblioteca_objtyp(  Id_biblioteca PRIMARY KEY) NESTED TABLE salas STORE AS Sala_ntab(    ( PRIMARY KEY (NESTED_TABLE_ID, Id_sala) )    NESTED TABLE treat(object_value as SalaEstudio_objtyp).reservas STORE AS Reserva_ntab (         ( PRIMARY KEY (NESTED_TABLE_ID, Id_reserva) )     )    NESTED TABLE treat(object_value as SalaComun_objtyp).ejemplares STORE AS Ejemplar_ntab (         ( PRIMARY KEY (NESTED_TABLE_ID, num_registro) )     ));