CODEWITHSUNDEEP
plsqltoad
Al007
Al007

Reputation: 383

Oracle PL/SQL procedure does not execute insert

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?

Upvotes: 0

Views: 1498

Answers (1)

JasonInVegas
JasonInVegas

Reputation: 391

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.

Upvotes: 1

Related Questions