How to use table input parameter for MERGE command in Oracle Stored Procedure

Question

I am currently trying to write a stored procedure that inserts or updates multiple rows into my database. I pass the rows via a table input parameter, but I am stuck on how to pass those rows to the MERGE-command.

This command is working fine:

MERGE INTO dbo.EntryTable a
    USING (SELECT 'abc' "keyColumn", 'def' "valueColumn" FROM DUAL) b
    ON (a."keyColumn" = b."keyColumn")
    WHEN MATCHED THEN UPDATE SET a."valueColumn" = b."valueColumn"
    WHEN NOT MATCHED THEN
    INSERT ("keyColumn","valueColumn") 
    VALUES(b."keyColumn",b."valueColumn);

To get this into a stored procedure I created a table type:

CREATE OR REPLACE TYPE entry_type AS OBJECT
(
"keyColumn" NVARCHAR2(3), 
"valueColumn" NVARCHAR2(3)
);

CREATE OR REPLACE TYPE entry_type_list AS TABLE OF entry_type;

But as soon as I try to use it in a stored procedure like this:

CREATE OR REPLACE PROCEDURE set_entry_list (entries entry_type_list) AS
BEGIN
    MERGE INTO dbo.EntryTable a
    USING (SELECT * FROM entry_type_list) b
    ON (a."keyColumn" = b."keyColumn")
    WHEN MATCHED THEN UPDATE SET a."valueColumn" = b."valueColumn"
    WHEN NOT MATCHED THEN
    INSERT ("keyColumn","valueColumn") 
    VALUES(b."keyColumn",b."valueColumn);
END;

I am getting errors like this when creating the stored procedure:

LINE/COL ERROR

---

3/5 PL/SQL: SQL Statement ignored

4/26 PL/SQL: ORA-00942: table or view does not exist

I tried to find documentation on how to do this but I am currently out of ideas where to look.

Answer 1

If you are using 11g, you would still need the table operator in your query

SELECT * FROM TABLE( entry_type_list )

So your MERGE statement would be something like

MERGE INTO dbo.EntryTable a
USING (SELECT * FROM table( entry_type_list ) ) b
ON (a."keyColumn" = b."keyColumn")
WHEN MATCHED THEN UPDATE SET a."valueColumn" = b."valueColumn"
WHEN NOT MATCHED THEN
INSERT ("keyColumn","valueColumn") 
VALUES(b."keyColumn",b."valueColumn);