SQL Plus嵌套关系结构插入报错ORA-00947：值不足求助

Hey there, let's break down why you're hitting that ORA-00947 error and how to fix it!

The Root Cause #

That error pops up because your CUSTOMER table (backed by your custom object types) has more attributes than you're providing values for in your INSERT statement. Since you're trying to nest orders under customer accounts, your CUSTOMER type almost certainly includes a nested table attribute (of type ORDER_TAB) alongside the name and zip code—but your current insert only passes those two values, leaving out the order-related data the table expects.

First, Let's Confirm the Full Type & Table Setup #

I'm guessing your complete type definitions look something like this (building on the snippet you shared):

CREATE TYPE ORDERS AS OBJECT (
    ORDER_NO CHAR(5),
    ORDER_DATE DATE,
    TOTAL NUMBER
);
/
CREATE TYPE ORDER_TAB AS TABLE OF ORDERS;
/
CREATE TYPE CUSTOMER_TYPE AS OBJECT (
    CUST_NAME VARCHAR2(100),
    ZIP CHAR(5),
    CUST_ORDERS ORDER_TAB  -- This is the missing attribute you weren't providing
);
/
CREATE TABLE CUSTOMER OF CUSTOMER_TYPE
NESTED TABLE CUST_ORDERS STORE AS CUST_ORDERS_TAB;  -- Required to store nested table data

If you run DESCRIBE CUSTOMER; in SQL Plus, you'll see all three attributes listed, which confirms exactly why your original insert was short on values.

Correct INSERT Statements #

You need to provide a value for the nested table attribute—either with actual order data, or an empty table if the customer has no orders yet.

Option 1: Insert a Customer with Existing Orders

Use the object constructors to build the nested table and its contained order objects:

INSERT INTO CUSTOMER VALUES(
    'John Smith',
    '90210',
    ORDER_TAB(
        ORDERS('O0001', DATE '2024-01-15', 150.50),
        ORDERS('O0002', DATE '2024-02-20', 220.00)
    )
);
/
COMMIT;

Option 2: Insert a Customer with No Orders

If the customer doesn't have any orders yet, pass an empty ORDER_TAB instance:

INSERT INTO CUSTOMER VALUES('John Smith','90210', ORDER_TAB());
/
COMMIT;

Quick SQL Plus Tip #

Make sure each of your type creation statements ends with a standalone / on a new line—this tells SQL Plus to execute the PL/SQL block correctly, which is an easy detail to miss when working with object types.

内容的提问来源于stack exchange，提问作者James