Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Unanswered: my "IF NOT EXISTS" procedure doesn't work. Please help

    Hi eveyone,
    I have whitten this stored procedure in Oracle 8.1 and it just I can't get it to work.

    CREATE OR REPLACE PROCEDURE "myUser"."myProcedure"
    (x NUMBER,y NUMBER)
    IS

    IF NOT EXISTS (SELECT * FROM myUSER.myTable WHERE myTable.x=myProcedure.x AND myTable.y=myProcedure.y)
    BEGIN
    INSERT INTO myTable VALUES(myProcedure.x , myProcedure.y);
    END;

    Any help whould be appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    The NOT EXISTS is a SQL function, you can't use it in PL/SQL in that manner. This should get you going....

    declare
    nCount number;
    begin
    select count(*)
    into nCount
    from MyTable
    where x = inX AND
    y = inY;
    if nCount = 0 then
    insert ....
    end if;
    end;

    Hth
    Bill

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: my "IF NOT EXISTS" procedure doesn't work. Please help

    Originally posted by bramtram
    Hi eveyone,
    I have whitten this stored procedure in Oracle 8.1 and it just I can't get it to work.

    CREATE OR REPLACE PROCEDURE "myUser"."myProcedure"
    (x NUMBER,y NUMBER)
    IS

    IF NOT EXISTS (SELECT * FROM myUSER.myTable WHERE myTable.x=myProcedure.x AND myTable.y=myProcedure.y)
    BEGIN
    INSERT INTO myTable VALUES(myProcedure.x , myProcedure.y);
    END;

    Any help whould be appreciated.
    An alternative would be:

    CREATE OR REPLACE PROCEDURE myUser.myProcedure
    (x NUMBER,y NUMBER)
    IS
    INSERT INTO myTable VALUES(myProcedure.x , myProcedure.y);
    EXCEPTION
    -- Ignore failed attempt to re-insert same row
    WHEN DUP_VAL_ON_INDEX THEN NULL;
    END;

    BTW, I strongly suggest you drop those double quotes around the procedure name. They make the name case sensitive, so that it can only ever be invoked as "myProcedure" not myProcedure or myprocedure or MYPROCEDURE.

  4. #4
    Join Date
    Jun 2003
    Posts
    4

    Re: my "IF NOT EXISTS" procedure doesn't work. Please help

    Originally posted by andrewst
    An alternative would be:

    CREATE OR REPLACE PROCEDURE myUser.myProcedure
    (x NUMBER,y NUMBER)
    IS
    INSERT INTO myTable VALUES(myProcedure.x , myProcedure.y);
    EXCEPTION
    -- Ignore failed attempt to re-insert same row
    WHEN DUP_VAL_ON_INDEX THEN NULL;
    END;

    BTW, I strongly suggest you drop those double quotes around the procedure name. They make the name case sensitive, so that it can only ever be invoked as "myProcedure" not myProcedure or myprocedure or MYPROCEDURE.

    This worked for me. Thank you. I have one more question. How to return a message to the calling program/procedure whenever EXCEPTION is reached? Thanks.

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Bramtram,

    I'm sorry but you need to read some Oracle docs, maybe even programming docs. There is no substitute for reading the docs, no matter how many forums you may go on.

    On a lighter (more helpful) note, this may help (based on Tony's response - assuming you have a unique index/primary key on the x/y columns)....

    create or replace FUNCTION myUser.myProcedure
    (x number,y number) RETURN NUMBER
    is
    insert into myTable values(myProcedure.x , myProcedure.y);
    RETURN 1;
    exception
    -- Ignore failed attempt to re-insert same row
    when dupe_val_on_index then
    RETURN 0;
    WHEN OTHERS THEN
    RETURN -1;
    end;

    Changes to original in CAPS.

    In PL/SQL you would change the call to

    DECLARE
    Result NUMBER(1);
    BEGIN
    Result := myUser.myProcedure( 1,2 );
    IF Result = 1 THEN
    -- Success
    DoSomething();
    ELSIF Result = 0 THEN
    -- Failed - already in table
    DoSomethingElse();
    ELSIF Result = -1 THEN
    -- Some other oracle error?
    DoSomethingElseElse();
    .........
    etc........
    .........

    Note the inclusion of the EXCEPTION WHEN OTHERS. Just because an exception was raised doesn't mean it was because of a duplicate key. A function returning with no value can cause other problems, so it's worth always including it.

    If you're not calling it from PL/SQL then you need to check the docs for your client side tool/language etc on how to call and determine the return type of a function.

    Hth
    Bill
    Last edited by billm; 06-04-03 at 22:11.

  6. #6
    Join Date
    Jun 2003
    Posts
    4
    Originally posted by billm
    Hi Bramtram,

    I'm sorry but you need to read some Oracle docs, maybe even programming docs. There is no substitute for reading the docs, no matter how many forums you may go on.

    On a lighter (more helpful) note, this may help (based on Tony's response - assuming you have a unique index/primary key on the x/y columns)....

    create or replace FUNCTION myUser.myProcedure
    (x number,y number) RETURN NUMBER
    is
    insert into myTable values(myProcedure.x , myProcedure.y);
    RETURN 1;
    exception
    -- Ignore failed attempt to re-insert same row
    when dupe_val_on_index then
    RETURN 0;
    WHEN OTHERS THEN
    RETURN -1;
    end;

    Changes to original in CAPS.

    In PL/SQL you would change the call to

    DECLARE
    Result NUMBER(1);
    BEGIN
    Result := myUser.myProcedure( 1,2 );
    IF Result = 1 THEN
    -- Success
    DoSomething();
    ELSIF Result = 0 THEN
    -- Failed - already in table
    DoSomethingElse();
    ELSIF Result = -1 THEN
    -- Some other oracle error?
    DoSomethingElseElse();
    .........
    etc........
    .........

    Note the inclusion of the EXCEPTION WHEN OTHERS. Just because an exception was raised doesn't mean it was because of a duplicate key. A function returning with no value can cause other problems, so it's worth always including it.

    If you're not calling it from PL/SQL then you need to check the docs for your client side tool/language etc on how to call and determine the return type of a function.

    Hth
    Bill
    Bill,
    Thank you for your "wise advice" however your code didn't work in either of your replies, so I suggest you test it before posting it and giving advice(however sensible it may be) to other people.
    Cheers,
    Bramtram.

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Yes, I'm sure it wouldn't work, do you have the following objects...?

    myUser (schema)
    myTable (Table)
    DoSomething (procedure)
    DoSomethingElse (procedure)
    DoSomethingElseElse (procedure)

    probably not. In which case it'll fail. If you do have those objects then I'll have to give up my day job. Also you might have to add the '/' character to the end of the code to make it run as is, sorry I forgot to mention that. Do you need help loading SqlPlus too?

    Anyway, enough of this, my apologies for suggesting you should read the docs and for forgetting to wipe for you.

    Regards
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •