Merge in stored procedure

  • 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.

  • what error are you getting

  • 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
  • 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.

  • 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
  • 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