Oracle PL/SQL procedure does not execute insert

Question

I have a procedure, in TOAD, that supposed to insert a new record after checking the record is not exists. It creates successfully:

CREATE OR REPLACE PROCEDURE Set_Mod ( 
    p_TypeID IN NUMBER, p_LinkID IN NUMBER
    ) 
AS
    v_isExists NUMBER := 0;
    v_query varchar2(200);
BEGIN

    SELECT TypeID 
    INTO v_isExists
    FROM myTable
    WHERE  LinkID = p_LinkID 
    AND TypeID =  p_TypeID;

    IF (v_isExists = 0) THEN
       v_query := 'INSERT INTO myTable ( TypeID, LinkID ) VALUES (' || p_TypeID || ',' || p_LinkID || ')';

       EXECUTE IMMEDIATE v_query;   

    END IF;

END;

/

I am trying to run the procedure using this block:

    BEGIN
         Set_Mod( 1, 1 );

    END;
/

I get these script outputs in TOAD:

Procedure created.
PL/SQL procedure successfully completed.

BUT no any insertions. It doesn't work. Any ideas where is the problem?

It looks lie your goal is to find out if a record exists already, then insert a row only if one does not exist. Read the answer below on how to fix what's wrong, but also consider a combined-key UNIQUE index on the two columns so that you can never put two rows in with the same TypeID, LinkID values. -- Then you can trap the duplication exception and perhaps do an update instead of an insert? — JasonInVegas
– JasonInVegas, Commented May 18, 2017 at 18:05

JasonInVegas · Accepted Answer · 2017-05-18 18:00:49Z

1

So the logic in your proc is (sort of) sound, but your test logic is not:

When you call Set_Mod(1,1) the answers will either be v_isExists = 1 if the query returns a row or the PL/SQL Exception NO_DATA_FOUND if no row is returned.

Because you do not trap this exception, the Procedure completes but no insert happens... v_isExists = 0 will not be true except I suppose if you call Set_Mod(0,0).

So, please review this documentation on handling PL/SQL exceptions in Procedures and search SO for PL/SQL Exception Handling for more details.

answered May 18, 2017 at 18:00

JasonInVegas

3912 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JasonInVegas Over a year ago

I'd also suggest that you do NOT use the execute immediate but instead put the insert statement into the exception handler and make it all transactional.

Al007 Over a year ago

I either need to use your solution or change my query to select count(*) and it works. In fact, in this case, I will never face duplicated insertion situation. Thanks

Collectives™ on Stack Overflow

Oracle PL/SQL procedure does not execute insert

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related