Oracle SQL create identity column in nested table

Question

I'm making a table which contains nested table:

create DOCUMENT as OBJECT (
    DOC_ID NUMBER,
    DESCRIPTION VARCHAR(1000));   

create type documents_t is table of DOCUMENT;

create table projects (
    ID NUMBER GENERATED ALWAYS AS IDENTITY ,
    DOCUMENTS documents_t)
    NESTED TABLE DOCUMENTS STORE AS documents_nested(
        (PRIMARY KEY(nested_table_id, DOC_ID)) ORGANIZATION INDEX);

This works ok, but I can't seem to find how to make the nested table's primary key as identity column. any suggestions? Thanks

Answer 1

Please find the code snippet,

CREATE SEQUENCE seq_documents
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

CREATE OR REPLACE TYPE documents_q AS OBJECT
(
   doc_id      NUMBER,
   description VARCHAR2(1000),
   CONSTRUCTOR FUNCTION documents_q(p_description VARCHAR2) RETURN SELF AS RESULT
);

CREATE OR REPLACE TYPE BODY documents_q AS
   CONSTRUCTOR FUNCTION documents_q(p_description VARCHAR2) RETURN SELF AS RESULT IS
   BEGIN
      self.doc_id      := seq_documents.nextval;
      self.description := p_description;
      RETURN;
   END;
END;
/

CREATE TYPE documents_t AS TABLE OF documents_q;

create table projects (
    id NUMBER GENERATED ALWAYS AS IDENTITY ,
    documents documents_t)
    NESTED TABLE documents STORE AS documents_nested(
        (PRIMARY KEY(nested_table_id, doc_id))  ORGANIZATION INDEX);

INSERT INTO PROJECTS(documents) VALUES (documents_t(documents_q('Description One'),documents_q('Description Two')));
        
SELECT * FROM projects;

Please let me know if it gives you the solution. Thank you