CODEWITHSUNDEEP
oracle-databaseplsqloracle12c
Popplar
Popplar

Reputation: 279

Returning boolean as an out parameter from stored procedure?

Is this a valid way to return a boolean as an output parameter from the store procedure? To return true if everything went OK or raise an exception when an error occurred.

PROCEDURE STUFF (VAL1 IN NUMBER, VAL2 IN NUMBER, RESULT OUT BOOLEAN)
IS
BEGIN
    INSERT INTO TABLE_1 (A_COLUMN) VALUES (VAL1);
    INSERT INTO TABLE_2 (B_COLUMN) VALUES (VAL2);
    COMMIT;
    RESULT := TRUE;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
//Do stuff;
RAISE;
END;

Upvotes: 0

Views: 3362

Answers (2)

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

I would take it a step further and suggest that you do not "hide" an exception and then return instead a status code, whether it be integer, string or boolean.

Instead, rely on the default error propagation flow of PL/SQL and document exceptions that could be raised in your program.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

Should be fine if you use it properly. Also, it is a good practice to use %TYPE for parameters that refer to columns.

DECLARE 
    v_result BOOLEAN; 
    PROCEDURE Stuff (val1   IN table_1.a_column%TYPE, 
                     val2   IN table_2.b_column%TYPE, 
                     result OUT BOOLEAN) 
    IS 
    BEGIN 
        INSERT INTO table_1 (a_column) VALUES (val1); 
        INSERT INTO table_2 (b_column) VALUES (val2); 

        COMMIT; 

        result := TRUE; 
    EXCEPTION 
      WHEN OTHERS THEN 
                 ROLLBACK; 

                 --Do stuff; 
                 RAISE; 
    END; 
BEGIN 
    Stuff(1, 2, v_result); 

    IF v_result THEN 
      dbms_output.put_line('SUCCESS'); 
    -- Do other stuff 
    END IF; 
END; 

/ 

SUCCESS

Upvotes: 3

Related Questions