Hello Experts,
I need to create Advance Queue table in Oracle. I need to store payload which would have nested details. However when I am getting below error while executing CREATE_QUEUE_TABLE:
Error:
ORA-22913: must specify table name for nested table column or attribute
ORA-06512: at “SYS.DBMS_AQADM”, line 81
ORA-06512: at line 2
Execute Block:
begin
DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table => ‘sau_q_tab’
, queue_payload_type => ‘sau_person_o_type’
);
end;
Object Type details:
CREATE OR REPLACE TYPE sau_person_o_type as object (
person_name varchar2(30),
person_assets sau_asset_t_type,
CONSTRUCTOR FUNCTION sau_person_o_type /nested table type/
RETURN SELF AS RESULT);
/
CREATE OR REPLACE PUBLIC SYNONYM sau_person_o_type FOR dcom.sau_person_o_type;
CREATE OR REPLACE TYPE BODY sau_person_o_type IS
CONSTRUCTOR FUNCTION sau_person_o_type
RETURN SELF AS RESULT
IS
BEGIN
RETURN;
END;
END;
/
CREATE OR REPLACE TYPE sau_asset_o_type as object (asset_id number,
asset_name varchar2(30),
CONSTRUCTOR FUNCTION sau_asset_o_type
RETURN SELF AS RESULT);
/
CREATE OR REPLACE PUBLIC SYNONYM sau_asset_o_type FOR dcom.sau_asset_o_type;
CREATE OR REPLACE TYPE BODY sau_asset_o_type IS
CONSTRUCTOR FUNCTION sau_asset_o_type
RETURN SELF AS RESULT
IS
BEGIN
RETURN;
END;
END;
/
CREATE OR REPLACE TYPE sau_asset_t_type AS TABLE OF sau_asset_o_type;
/