Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge in stored procedure Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 6:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
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.
Post #1592154
Posted Monday, July 14, 2014 6:38 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:13 AM
Points: 747, Visits: 1,315
what error are you getting
Post #1592157
Posted Monday, July 14, 2014 8:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
Are you working in Oracle or SQL Server?


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1592192
Posted Monday, July 14, 2014 10:38 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
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.
Post #1592407
Posted Tuesday, July 15, 2014 8:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1592610
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse