Revisions to Insert data into nested table

added 477 characters in body

Source Link

edited May 21, 2013 at 7:15

812
3
15
24

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE:

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE 2:

If you're eager to insert more than one value into the nested table, use enumeration (if the values are written by hand) or subquery:

(
    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
    ) 
    FROM DUAL
)

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE:

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE:

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE 2:

If you're eager to insert more than one value into the nested table, use enumeration (if the values are written by hand) or subquery:

(
    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
    ) 
    FROM DUAL
)

deleted 3 characters in body

Source Link

edited May 16, 2013 at 8:45

whatsupbros

812
3
15
24

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE:

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtabtypPurchaseOrder_objtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE:

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtabtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE:

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

added 531 characters in body

Source Link

edited May 16, 2013 at 8:15

whatsupbros

812
3
15
24

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

It should work.UPDATE:

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtabtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

It should work..

You should use a type constructor in a select subquery to fill the nested table in your occasion:

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 WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE:

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtabtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

Source Link

answered May 15, 2013 at 15:16

whatsupbros

812
3
15
24

Loading

Collectives™ on Stack Overflow

Return to Answer