执行依赖嵌套表的PL/SQL存储过程触发ORA-00904无效标识符报错问题

• 列名匹配错误：你在创建Orders主表时，嵌套表对应的实际列名是Items，order_items是你自定义的嵌套表集合类型的名称，并非表中存在的列。你在存储过程的INSERT语句列清单里错误将类型名当成列名填写，Oracle在表结构中找不到名为order_items的列，因此抛出ORA-00904无效标识符错误。
• 手动执行的INSERT能正常运行，是因为你写的是不带列清单的全值插入语句，Oracle默认按照建表时的列顺序匹配传入值，没有触发列名合法性校验，因此没有暴露这个列名书写错误。
• 现有存储过程的参数设计与嵌套表结构不匹配：当前入参全部是单条子订单的散标量值，既无法支持同个主单下插入多条子订单的业务场景，也和你手动验证时支持多子项插入的逻辑不对齐。

调整两处核心逻辑即可正常运行：

1. 将INSERT语句列清单中错写的order_items替换为表中真实存在的嵌套表列名Items
2. 优化存储过程入参，直接传入预定义的order_items嵌套表集合类型，替代原来拆分的单条散标量子项参数，匹配嵌套表的设计逻辑，同时支持一次插入任意数量的子订单

修正后的存储过程代码：

CREATE OR REPLACE PROCEDURE insert_new_order
(
    p_order_id IN INT, 
    p_order_num IN CHAR,
    p_name IN CHAR,
    p_email IN CHAR,
    p_address IN VARCHAR2, 
    p_city IN VARCHAR2,
    p_province IN VARCHAR2,
    p_postcode IN VARCHAR2,
    p_telephone IN NUMBER, 
    p_total IN NUMBER,
    p_order_date IN DATE,
    p_items IN order_items
)
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE ('Insert attempted');

    INSERT INTO Orders (
        order_id, order_number, billing_name, billing_email, 
        billing_address, billing_city, billing_province, 
        billing_postcode, billing_telephone, billing_total, 
        order_date, Items
    )
    VALUES (
        p_order_id, p_order_num, p_name, p_email, 
        p_address, p_city, p_province, 
        p_postcode, p_telephone, p_total, 
        p_order_date, p_items
    );

    COMMIT;
    DBMS_OUTPUT.PUT_LINE ('Insert succeeded');

EXCEPTION
    WHEN OTHERS THEN   
        DBMS_OUTPUT.PUT_LINE ('Insert rejected');
        DBMS_OUTPUT.PUT_LINE ('SQL Error Code:  ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE ('SQL Error Message:  ' || SQLERRM); 
        ROLLBACK;
END;
/

修正后的存储过程调用示例，和你手动执行的插入逻辑完全等价：

BEGIN
    insert_new_order(
        p_order_id => 1,
        p_order_num => 'P396XT47',
        p_name => 'Test',
        p_email => 'test@test.co.uk',
        p_address => 'test',
        p_city => 'test',
        p_province => 'test',
        p_postcode => 'DL99 9UU',
        p_telephone => 123456789,
        p_total => 57.78,
        p_order_date => TO_DATE('19-apr-2021', 'DD-MON-YYYY'),
        p_items => order_items(
            order_item(1, 1, 1, 1, '3026143053', 1, 'Brand new', 53.49, 0),
            order_item(2, 1, 2, 2, '3029608429', 1, 'Brand new', 1.99, 2.00)
        )
    );
END;
/

补充说明 #

• 如果业务场景确实只需要一次插入单条子订单，也可以保留原有的散标量入参设计，但必须同样修正INSERT的列名错误，将列清单中的order_items改为Items。但这种设计扩展性差，无法支撑同主单多子项的业务需求，不推荐。
• Oracle默认不区分未加双引号的标识符大小写，因此只要列名没有在创建时用双引号强制指定大小写，使用Items、ITEMS都可以被正常识别。

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