ORA-00904 'invalid identifier' error using 'MERGE INTO' and 'SELECT FROM dual' on a DATE column

Question

I have the following PL/SQL stored procedure on an Oracle database:

PROCEDURE MyProcedure (
    p_id IN NUMBER
  , p_date IN DATE
  , p_num IN NUMBER) 
  AS
  BEGIN
    MERGE INTO MY_TABLE mytable
    USING (SELECT
            p_id,
            p_date,
            p_num
          FROM dual) temp
    ON (mytable.myid = temp.p_id AND mytable.mydate = temp.p_date)
  WHEN MATCHED THEN
    UPDATE SET
      DIFFERENCE = temp.p_num,
  WHEN NOT MATCHED THEN
    INSERT VALUES (
      MY_TABLEIDSEQ.NEXTVAL,
      temp.p_id,
      temp.p_date,
      temp.p_num);
END MyProcedure;

The MY_TABLE table is defined as follows:

CREATE TABLE "MY_DBO"."MY_TABLE" 
   (
     "MYTABLEID" NUMBER(38,0),
     "MYID" NUMBER(38,0),
     "MYDATE" DATE,
     "MYNUM" NUMBER(25,4)
   )

However, when I run the stored procedure with valid values for the input fields, I get the following error:

ORA-00904: "TEMP"."P_DATE": invalid identifier

ORA-06512: at "MY_DBO.MY_PKG", line 54

ORA-06512: at line 18

I've no idea what is causing this, any help would be greatly appreciated. Please note that the stored procedure begins on line 45 of MY_PKG.

I found a similar issue here, but the solution to change 'dual' to 'MY_TABLE' didn't seem to work for me.

PS: I am very new to Oracle :)

Answer 1

In the "select from dual" statement you used the input parameters of your stored procedures as values. Oracle indeed generates implicit names for these columns, which I cannot currently predict. But you could provide your own column names/alias names in that statement:

USING (SELECT
        p_id col_id,
        p_date col_date,
        p_num col_num
      FROM dual) temp

Those names are just examples. I would strongly encourage you to use unique names in such a case to prevent any ambiguities later on. You would need to replace any use of temp.* in your merge statement with the alias names you provided.