Merge in stored procedure

  • masoudk1990

    SSCommitted

    Points: 1651

    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.

  • twin.devil

    SSC-Insane

    Points: 22208

    what error are you getting

  • Luis Cazares

    SSC Guru

    Points: 183496

    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

    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: 183496

    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

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

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