1 Reply Latest reply: Nov 15, 2011 12:37 PM by Solomon Yakobson RSS

    How to insert into a table with a nested table which refer to another table

    900349
      Hello everybody,

      As the title of this thread might not be very understandable, I'm going to explain it :

      In a context of a library, I have an object table about Book, and an object table about Subscriber.
      In the table Subscriber, I have a nested table modeling the Loan made by the subscriber.
      And finally, this nested table refers to the Book table.

      Here the code concerning the creation of theses tables :

      Book :
      create or replace type TBook as object
      *(*
      number int,
      title varchar2(50)
      *);*

      Loan :
      create or replace type TLoan as object
      *(*
      book ref TBook,
      loaning_date date
      *);*
      create or replace type NTLoan as table of TLoan;

      Subscriber :
      create or replace type TSubscriber as object
      *(*
      sub_id int,
      name varchar2(25)
      loans NTLoan
      *);*


      Now, my problem is how to insert into a table of TSubscriber... I tried this query, without any success...

      insert into OSubscriber values
      *(1, 'LEVEQUE', NTLoan(*
      select TLoan(ref(b), '10/03/85') from OBook b where b.number = 1)
      *)*

      Of course, there is an occurrence of book in the table OBook with the number attribute 1.

      Oracle returned me this error :

      SQL error : ORA-00936: missing expression
      00936. 00000 - "missing expression"

      Thank you for your help
        • 1. Re: How to insert into a table with a nested table which refer to another table
          Solomon Yakobson
          1) NUMBER is a reserved word - you can't use it as identifier:
          SQL> create or replace type TBook as object
            2  (
            3  number int,
            4  title varchar2(50)
            5  );
            6  /
          
          Warning: Type created with compilation errors.
          
          SQL> show err
          Errors for TYPE TBOOK:
          
          LINE/COL ERROR
          -------- -----------------------------------------------------------------
          0/0      PL/SQL: Compilation unit analysis terminated
          3/1      PLS-00330: invalid use of type name or subtype name
          2) Subquery must be enclosed in parenthesis:
          SQL> create table OSubscriber of TSubscriber
            2  nested table loans store as loans
            3  /
          
          Table created.
          
          SQL> create table OBook of TBook
            2  /
          
          Table created.
          
          SQL> insert
            2    into OBook
            3    values(
            4           1,
            5           'No Title'
            6          )
            7  /
          
          1 row created.
          
          SQL> commit
            2  /
          
          Commit complete.
          
          SQL> insert into OSubscriber
            2    values(
            3           1,
            4           'LEVEQUE',
            5           NTLoan(
            6                  (select TLoan(ref(b),DATE '1985-10-03') from OBook b where b.num = 1)
            7                 )
            8          )
            9  /
          
          1 row created.
          
          SQL> select  *
            2    from  OSubscriber
            3  /
          
              SUB_ID NAME
          ---------- -------------------------
          LOANS(BOOK, LOANING_DATE)
          -------------------------------------------------------------------------------------------------------
                   1 LEVEQUE
          NTLOAN(TLOAN(000022020863025C8D48614D708DB5CD98524013DC88599E34C3D34E9B9DBA1418E49F1EB2, '03-OCT-85'))
          
          
          SQL> 
          SY.