Stack Exchange
PL/SQL MERGE INTO Compilation Errors
I want to use PL/SQL MERGE to perform an UPSERT on a single table defined as 
DECLARE

v_create LONG;

v_drop LONG;

TABLE_DOES_NOT_EXIST exception;  
PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942); -- ORA-00942

TABLE_NAME VARCHAR2(30) := 'B2BOWNER.SSC_Page_Map'; 

BEGIN

   BEGIN

       v_drop := 'DROP TABLE ' || TABLE_NAME; 

       EXECUTE IMMEDIATE v_drop;  

       EXCEPTION 

           WHEN TABLE_DOES_NOT_EXIST THEN NULL;

   END;

v_create := 'CREATE TABLE ' ||  TABLE_NAME || ' (
                PAGE_ID_NBR   NUMERIC(10)   NOT NULL Check(Page_ID_NBR > 0),
                PAGE_TYPE     VARCHAR2(50)  NOT NULL, 
                PAGE_DCPN     VARCHAR2(100) NOT NULL,
                PRIMARY KEY(Page_ID_NBR, Page_Type))';

EXECUTE IMMEDIATE v_create; 

COMMIT WORK;

COMMIT COMMENT 'Create Table'; 

EXCEPTION

    WHEN OTHERS THEN

        IF SQLCODE = -955 THEN

            NULL; -- suppresses ORA-00955 exception
        ELSE

            RAISE;
        END IF;

END;

using the following attempt at PL/SQL MERGE as follows
CREATE OR REPLACE FUNCTION B2BOWNER.F_SSC_Page_Map_Update(
                            p_page_id   IN B2BOWNER.SSC_Page_Map.Page_ID_NBR%TYPE, 
                            p_page_type IN B2BOWNER.SSC_Page_Map.Page_Type%TYPE, 
                            p_page_dcpn IN B2BOWNER.SSC_Page_Map.Page_Dcpn%TYPE)

RETURN INTEGER

IS

   TABLE_DOES_NOT_EXIST exception;  
   PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942); -- ORA-00942

BEGIN

  MERGE INTO SSC_Page_Map d
  USING (SELECT PAGE_ID_NBR, PAGE_TYPE from dual)
  ON(d.PAGE_ID_NBR = p_page_id AND d.PAGE_TYPE = p_page_type)
  WHEN MATCHED THEN UPDATE SET d.PAGE_ID_NBR= p_page_id, d.PAGE_TYPE=p_page_type
  WHEN NOT MATCHED THEN INSERT (PAGE_ID_NBR, PAGE_TYPE, PAGE_DCPN) VALUES (p_page_id,    p_page_type, p_page_dcpn); 

   COMMIT WORK; 

   COMMIT COMMENT 'Modifying Type and Description Only';

   RETURN 0;

   EXCEPTION

    WHEN TABLE_DOES_NOT_EXIST THEN

        RETURN -1;

    WHEN DUP_VAL_ON_INDEX THEN

        RETURN -2;

    WHEN INVALID_NUMBER THEN

        RETURN -3;

    WHEN OTHERS THEN

        RETURN -4;

END F_SSC_Page_Map_Update;

SHOW ERRORS PROCEDURE B2BOWNER.F_SSC_Page_Map_Update;

GRANT EXECUTE ON B2BOWNER.F_SSC_Page_Map_Update TO B2B_USER_DBROLE

I am getting a 
Warning: compiled but with compilation errors
No errors.
Grant complete.

It details out as
[Warning] ORA-24344: success with compilation error
16/31   PL/SQL: ORA-00904: "PAGE_TYPE": invalid identifier
15/4    PL/SQL: SQL Statement ignored
(2: 0): Warning: compiled but with compilation errors

How do I write the MERGE so I can compile the PL/SQL function without errors?

Shouldn't that be THEN INSERT (d.PAGE_ID_NBR, d.PAGE_TYPE, d.PAGE_DCPN) ...?

It looks to me like the MERGE statement should be:
MERGE INTO SSC_PAGE_MAP d
  USING (SELECT p_page_id AS PAGE_ID_NBR, p_page_type AS PAGE_TYPE from dual)
    ON(d.PAGE_ID_NBR = p_page_id AND
       d.PAGE_TYPE = p_page_type)
  WHEN MATCHED THEN
    UPDATE SET d.PAGE_DCPN = p_page_dcpn
  WHEN NOT MATCHED THEN
    INSERT
      (PAGE_ID_NBR, PAGE_TYPE,   PAGE_DCPN)
    VALUES
      (p_page_id,   p_page_type, p_page_dcpn);

Share and enjoy.
