title: Insert data into nested table
body: <p>May I please have some help to Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table: </p>
  
  <pre><code>CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT
  (
    PONo NUMBER,
    CUST_ref REF Customer_objtyp,
    OrderDate DATE,
    ShipDate DATE,
    LineItemList_ntab LineItemList_ntabtyp,
    ShipToAddr_obj Address_objtyp
  )
  /
  </code></pre>
  
  <p>The LineItemList_ntab is a nested table.</p>
  
  <p>Here is the create code for LineItemList_ntabtyp:</p>
  
  <pre><code>CREATE TYPE LineItem_objtyp AS OBJECT (
    LineItemNo NUMBER,
    Stock_ref REF StockItem_objtyp,
    Quantity NUMBER,
    Discount NUMBER
  )
  /
  
  CREATE TYPE LineItemList_ntabtyp AS TABLE of LineItem_objtyp
  /
  </code></pre>
  
  <p>Here is some insert code that does work:</p>
  
  <pre><code>INSERT INTO PurchaseOrder_objtab
  SELECT 1008, REF(C),
  SYSDATE, '12-MAY-1999',
  LineItemList_ntabtyp(),
  NULL
  FROM Customer_objtab C 
  WHERE C.CustNo = 1;
  </code></pre>
  
  <p>In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.</p>
  
  <p>Here is some of the code I have tried to insert values with:</p>
  
  <pre><code>INSERT INTO PurchaseOrder_objtab
  SELECT 1008, REF(C),
  SYSDATE, '12-MAY-1999',
  LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004,
  NULL
  FROM Customer_objtab C 
  WHERE C.CustNo = 1;
  
  INSERT INTO PurchaseOrder_objtab
  SELECT 1008, REF(C),
  SYSDATE, '12-MAY-1999',
  INSERT INTO LineItemList_ntab SELECT 1, REF(StckItem),10,1 FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004, 
  NULL
  FROM Customer_objtab C 
  WHERE C.CustNo = 1;
  </code></pre>
  
  <p>The first insert statement above produces the following error:</p>
  
  <blockquote>
    <p>SQL Error: ORA-00933: SQL command not properly ended</p>
  </blockquote>
  
  <p>The second insert statement above produces the following error:</p>
  
  <blockquote>
    <p>SQL Error: ORA-00936: missing expression</p>
  </blockquote>
  
  <p>May I please have some help to successfully insert values into the LineItemList_ntab nested table?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I have this code to insert the data:</p>
  
  <pre><code>INSERT INTO PurchaseOrder_objtab
  SELECT 
  1008, 
  REF(C),
  SYSDATE, 
  '12-MAY-1999',
  (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004),
  NULL
  FROM Customer_objtab C 
  WHERE C.CustNo = 1;
  </code></pre>
  
  <p>I am now getting the following error:</p>
  
  <blockquote>
    <p>SQL Error: ORA-22979: cannot INSERT object view REF or user-defined REF
    22979. 00000 -  "cannot INSERT object view REF or user-defined REF"
    *Cause:    Attempt to insert an object view REF or user-defined REF in a
               REF column created to store system generated REF values"
    *Action:   Make sure the REF to be inserted is not from an object view
               or from a user-defined REF column</p>
  </blockquote>
  
  <p>I am pretty sure that this error is because I may have created the table incorrectly, here is my table create code for the PurchaseOrder_objtab :</p>
  
  <pre><code>CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp(
  PRIMARY KEY (PONo),
  FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)
  OBJECT ID PRIMARY KEY
  
    NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab(
  (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))
  ORGANIZATION INDEX COMPRESS)
  RETURN AS LOCATOR
  /
  </code></pre>
  
  <p>Here is the code to create the nested table:</p>
  
  <pre><code>CREATE TYPE LineItemList_ntabtyp AS TABLE of LineItem_objtyp
  /
  </code></pre>
  
  <p><strong>UPDATE2</strong></p>
  
  <p>This code successfully adds information to the table:</p>
  
  <pre><code>INSERT INTO PurchaseOrder_objtab
  SELECT 1009, REF(C),
  SYSDATE, '10-MAY-1999',
  LineItemList_ntabtyp(),
  NULL
  FROM Customer_objtab C 
  WHERE C.CustNo = 1;
  </code></pre>
  
  <p>So the error has something to do with the LineItemList_ntabtyp.</p>
  
  <p>Here is the table creation code:</p>
  
  <pre><code>CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp(
  PRIMARY KEY (PONo),
  FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)
  OBJECT ID PRIMARY KEY
  
  NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab((PRIMARY KEY(NESTED_TABLE_ID,LineItemNo)) ORGANIZATION INDEX COMPRESS) RETURN AS LOCATOR/
  </code></pre>
  
  <p>I am sure the error has something to do with the OBJECT IDENTIFIER IS PRIMARY KEY for the above table.</p>
  
  <p><strong>UPDATE3</strong></p>
  
  <p>Thank you for the code. How about inserting more than one value into the nested table.</p>
  
  <p>Your line of code for one item is:</p>
  
  <pre><code>(SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
  NULL)
  </code></pre>
  
  <p>How about if I would like to add two items, both StockNo 1004 and 1005? Is it something like this:</p>
  
  <pre><code>(SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004) AND (SELECT LineItemList_ntabtyp(LineItem_objtyp(2, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1005),
  NULL)
  </code></pre>
  
  <p><strong>UPDATE4</strong></p>
  
  <p>If, I add three items to the nested table, does the "FROM DUAL" statement stay as "FROM DUAL", or does it change to something reflecting three items? Also, does the "UNION ALL" statement appear as follows:</p>
  
  <pre><code>(
  SELECT LineItemList_ntabtyp(
      SELECT LineItem_objtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab WHERE StockNo = 1004         
      UNION ALL 
      SELECT LineItem_objtyp(2, REF(StckItem), 10, 1) FROM Stock_objtab WHERE StockNo = 1005 
      UNION ALL 
      SELECT LineItem_objtyp(3, REF(StckItem), 10, 1) FROM Stock_objtab WHERE StockNo = 1006
  )
  FROM DUAL
  )
  </code></pre>