CODEWITHSUNDEEP
sqloracle-databasenested-table
Izik
Izik

Reputation: 948

Oracle SQL create identity column in nested table

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

Upvotes: 1

Views: 364

Answers (2)

Izik
Izik

Reputation: 948

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;

Upvotes: 0

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

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

Upvotes: 1

Related Questions