oracle-databaseobject-oriented-databasenested-table

add values to nested table from another table (oracle)

I am trying to insert values to a nested table with an object of another table. This is what I'm trying (Sorry, I'm new working with dbs):

INSERT INTO Ocurrences (..., oSpace) VALUES  
(other inserts,
/* insert I don't know to do it to nested table oSpaces */
);

How I could add a value in oSpaces inserting an object from table Spaces?

Thanks.

Solution

  • Just use a collection of REFerences:

    INSERT INTO Ocurrences (
  CCase,
  /* ... Other column identifiers ..., */
  oSpaces
) VALUES (
  'abc',
  /* ... Other column values ..., */
  tSpace(
    (SELECT REF(s) FROM spaces s WHERE s.intcode='1')
  )
);

    db<>fiddle here

    As an aside, '20/02/2020' is not a DATE data-type, it is a string literal and relies on an implicit string-to-date conversion. This implicit conversion will fail if the user's NLS_DATE_FORMAT session parameter does not match your string's format and since any user can change their session parameters at any time then this is not something you should rely on.

    Instead, you should use: