0

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.

Community
  • 1
  • 1
Christoph Sonntag
  • 4,459
  • 1
  • 24
  • 49

1 Answers1

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);
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • When did Oracle make the use of `table()` redundant? I completely missed that ;) –  Nov 08 '19 at 14:55
  • @a_horse_with_no_name - 12.2. I'm sure there's a better reference but there is the "no clause needed" aside about it in this article. https://blogs.oracle.com/oraclemagazine/pipelined-table-functions – Justin Cave Nov 08 '19 at 15:08