Merge in stored procedure

Question

Post reply

Merge in stored procedure

masoudk1990

SSCommitted

Points: 1651
More actions
July 14, 2014 at 6:25 am

#290893

Hello guys, may you help me why my merge in stored procedure have exception?
CREATE OR REPLACE PROCEDURE mySP
(
pID IN NUMBER
, pCurrent_Level_ID IN NUMBER
, pParent_Level_ID IN NUMBER
, pTitle IN VARCHAR2
, pOrganization_Unit IN NUMBER
, outmessage OUT VARCHAR2
, outresult OUT NUMBER
) AS
BEGIN
----------------validation
outresult := -1;
IF pTitle IS NULL THEN
outmessage := 'Please insert title';
RETURN;
END IF;
IF pOrganization_Unit IS NULL THEN
outmessage := 'Please determine your organization unit';
RETURN;
END IF;
-----------end validation
-----------if exists update else insert
MERGE INTO myTable USING dual ON --I have Error here sql statement ignored
(Id = pid)
when matched then
UPDATE myTable SET Title = pTitle,Organization_Unit = pOrganization_Unit
WHERE ID = pID;
WHEN not matched then
INSERT INTO myTable(ID,Current_Level_ID,Parent_Level_ID,Title,Organization_Unit)
VALUES (Commision_Commodity_Group_Id.nextval,pCurrent_Level_ID,pParent_Level_ID,pTitle,pOrganization_Unit)
outMessage := 'Success';
outNumber := 0;
return;
EXCEPTION
WHEN OTHERS THEN
outMessage := 'Error : ' || SQLERRM;
outNumber := -1;
return;
END mySP;
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
September 6, 2022 at 7:03 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Umer Akram

SSC-Insane

Points: 22209

July 14, 2014 at 6:38 am

#1729115

what error are you getting

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Are you working in Oracle or SQL Server?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

masoudk1990 SSCommitted Points: 1651 More actions · Answer 3

Sorry I think I asked my question in wrong section. I just searched for 'oracle' with ctrl + F through my web browser and I'm in here.

I working with oracle through sqlDeveloper software. The error I get is "sql statement ignored".

I searched for this and I found that "Merge" statement should be in beginning of procedure or you have to put it in a string and execute it with equivalent of sp_execute in oracle.

I tried all this methods and I still get "sql statement ignored" error.

By the way is it okay to ask oracle questions in this forum or I must register in another forums for my oracle questions?

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

There's no problem for us if you post it here. However, this site is focused on SQL Server and you might not get the best answers. You should try an Oracle forum.

I'm not great at Oracle, but aren't you missing a table on MERGE statement?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2