add values to nested table from another table (oracle)-CodePudding

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

Create de first type and nested table:

CREATE OR REPLACE TYPE Space AS OBJECT(

    intCode VARCHAR(10),
    description VARCHAR(200),
    usage VARCHAR(300),
    meters NUMBER,
    controls tControl
    );

CREATE TABLE Spaces OF Space(intCode PRIMARY KEY)
NESTED TABLE controls STORE AS controlsList;
CREATE TYPE tSpace AS TABLE OF REF Space;

Then:

CREATE OR REPLACE TYPE Ocurrence AS OBJECT(
cCase VARCHAR(10),
registerDate DATE,
cstate NUMBER(1),
symptoms VARCHAR(300),
testDate DATE,
infectionInCampus VARCHAR(1), /*Y or N*/
cReferrer REF Referrer,
cTrackings trackingsArray,
cInformer REF Informer,
oSpaces tSpace,
cCloseContacts tCloseContacts
);
CREATE TABLE Ocurrences OF Ocurrence (cCase PRIMARY KEY)
NESTED TABLE cCloseContacts STORE AS closeContactsList
NESTED TABLE oSpaces STORE AS spaceList;

And I want to do an insert to table Ocurrences, and in the nested table oSpaces add an object from table Spaces. I have tried:

INSERT INTO Ocurrences (ccase, registerdate, cstate, symptoms, testdate,infectionincampus,creferrer,cinformer,ctrackings, oSpaces, cclosecontacts) VALUES  
('7',
'10/02/2020',
2,
'xxxxxx',
'xx/xx/xxxx',
'Y',
(SELECT REF (r) FROM referrers r WHERE r.internalid='1'),
(SELECT REF (i) FROM informers i WHERE i.NIF='1111111'),
trackingsArray(
tracking ('1', 2, '20/02/2020','11:30','12345679','xxxxxxxx'),
tracking ('2', 2, '20/02/2020','11:30','12345679','xxxxxxxx'))
/* 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.

CodePudding user response：

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').