Forum Stats

  • 3,769,478 Users
  • 2,252,969 Discussions
  • 7,875,044 Comments

Discussions

SQL DEVELOPER NESTED TABLE PROBLEM

Josxlu
Josxlu Member Posts: 11
edited May 23, 2020 3:11AM in SQL & PL/SQL

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.***

Josxlu

Best Answer

  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    edited May 21, 2020 4:36PM Accepted Answer
    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) )     ));
    JosxluJosxlu
«1

Answers

  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    edited May 21, 2020 8:16AM
    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.

    JosxluJosxlu
  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited May 21, 2020 9:57AM
    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.

    JosxluJosxlu
  • Josxlu
    Josxlu Member Posts: 11
    edited May 21, 2020 11:35AM

    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):

    pastedImage_0.png

    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
    Josxlu Member Posts: 11
    edited May 21, 2020 11:37AM

    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
    Josxlu Member Posts: 11
    edited May 21, 2020 11:37AM

    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
    Paulzip Member Posts: 8,494 Blue Diamond
    edited May 21, 2020 11:50AM

    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
  • Josxlu
    Josxlu Member Posts: 11
    edited May 21, 2020 11:52AM

    Yeah It has to be implemented in O.R

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited May 21, 2020 12:42PM

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

    Josxlu
  • Josxlu
    Josxlu Member Posts: 11
    edited May 21, 2020 12:52PM

    Okey thank you so much!

  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    edited May 21, 2020 4:36PM Accepted Answer
    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) )     ));
    JosxluJosxlu