-1

I am new to Oracle.
I have a table in Oracle which has 4 columns Period, Open_Flag,Creation_Dt,Updated_By. The Period column is the Primary key of the table.
enter image description here
I have created a proc which will check the value of period from input parameter in the table, if its existing, the value of Open_flag has to be updated else a new record shall be inserted.
create or replace PROCEDURE PROC_REF_SAP_PERIOD( V_PERIOD IN NUMBER,V_OPEN_FLAG IN VARCHAR2,V_CREATION_DT IN DATE,V_UPDATED_BY IN VARCHAR2) AS BEGIN MERGE INTO REF_SAP_PERIOD T USING (SELECT * FROM REF_SAP_PERIOD WHERE PERIOD=V_PERIOD )S ON (T.PERIOD=S.PERIOD ) WHEN MATCHED THEN UPDATE SET OPEN_FLAG = V_OPEN_FLAG --WHERE PERIOD=V_PERIOD AND CREATION_DT=V_CREATION_DT AND UPDATED_BY=V_UPDATED_BY WHEN NOT MATCHED THEN INSERT (PERIOD,OPEN_FLAG,CREATION_DT,UPDATED_BY) VALUES (V_PERIOD,V_OPEN_FLAG,V_CREATION_DT,V_UPDATED_BY); END;
The issue is that the Update is working well in this case, however, the insert is not working.
Please help.

Meen
  • 119
  • 3
  • 15
  • 2
    Please, text [not screenshots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). Say I want to help you; if you had posted some text, I could easily copy the code, try and edit it; if you post an image, I simply vote to close the question. – Aleksej Dec 06 '16 at 14:26
  • Sorry @Aleksej ...I have now updated the question. – Meen Dec 07 '16 at 04:08

1 Answers1

1

You are merging table with itself, filtered by period. Obviously, it will never see your non-existent values in itself.

Try this line instead of your USING line:

using (select V_PERIOD "period" from dual)S
Alexander Anikin
  • 1,108
  • 6
  • 14