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

Are you only looking for solution with making the nested table id as "identity column" ? — Sujitmohanty30, Jul 22 '20 at 15:17
Yes, I need the primary key column in the nested table (column "DOC_ID") to be also identity column. — Izik, Jul 22 '20 at 15:26
I mean i don't find such possibilities. we can achieve the same by user defined constructor also and using sequence. if you want I can provide one example. — Sujitmohanty30, Jul 22 '20 at 15:28
Do you mean using both constructor and sequence? or can I do it only with constructor? — Izik, Jul 22 '20 at 16:09
With constructor only but using sequence inside it to generate value for id. — Sujitmohanty30, Jul 22 '20 at 16:16

score 1 · Accepted Answer · answered Jul 22 '20 at 17:42

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

Thank you greatly for the example. it does work. Though I wished Oracle would make it simpler. — Izik, Jul 23 '20 at 07:50
It's my pleasure:). Yes we should keep looking if there is any such solution already available in Oracle and will update then here. — Sujitmohanty30, Jul 23 '20 at 08:59

Izik · Answer 2 · 2020-07-25T17:03:14.977

If you want another solution without constructor (because using constructor adds more maintenance)

I took @Sujitmohanty30 idea and used the sequence in the insert statement. It makes code more easy to maintain, but force you to use sequence on insert and insert one item at each query (because "sequence.nextval" don't get incremented in same query):

CREATE SEQUENCE documents_seq NOCACHE;
/
CREATE TYPE document_type AS OBJECT (
   doc_id      NUMBER,
   description VARCHAR2(1000)
);
/
CREATE TYPE documents AS TABLE OF document_type;
/
create table projects_docs (
    id NUMBER GENERATED ALWAYS AS IDENTITY ,
    docs documents)
    NESTED TABLE docs STORE AS docs_nested(
        (PRIMARY KEY(nested_table_id, doc_id)) ORGANIZATION INDEX); 
 /
INSERT INTO projects_docs (docs) VALUES(
    documents(document_type(documents_seq.nextval, 'doc')));
/
select p.id, d.* from projects_docs p, table(p.docs) d;

Oracle SQL create identity column in nested table

2 Answers2