CODEWITHSUNDEEP
oracle-databaseobject-oriented-databasenested-table
Yorwe
Yorwe

Reputation: 53

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.

Upvotes: 0

Views: 246

Answers (1)

MT0
MT0

Reputation: 167991

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:

  • A date literal DATE '2020-02-20'; or
  • An explicit conversion TO_DATE('20/02/2020', 'DD-MM-YYYY').

Upvotes: 1

Related Questions