Microsoft SQL Server Database

Using SQLCODE Function

2 min read

Sep 8, 2020 12:00:00 AM

The idea of this post is to show how to handle errors using SQLCODE function. That function shows the code error for the latest exception when running a PL/SQL code. Here is a small example of code that raises an exception when two or more consecutive ORA-02292 errors occur.

Setting Up the Test Schema and Parent-Child Relationship

--Create a Table to capture how many times ORA-02292 errors occurred.  CREATE TABLE tcount (x int); -- table to count the number of 2292 errors    --Parent Table  create table orders (order_id int, total_sales int);  CREATE UNIQUE INDEX orders_pk on orders (order_id);  ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (order_id) ENABLE;    --Child Table  CREATE TABLE products  ( product_id numeric(10) not null,  supplier_id numeric(10) not null,  order_id int,  CONSTRAINT fk_orders  FOREIGN KEY (order_id)  REFERENCES orders(order_id)  );    --Add data in Parent and Child Tables  insert into orders values (1,1);  insert into products values (1,1,1);  commit;    --Verify the data on those Tables  select * from orders;  select * from products;

Implementing the Exception Handling Procedure

The core of this logic resides in using the SQLCODE function within the EXCEPTION block to identify the specific error code and act upon it.

--Create the procedure to Delete data from Parent table and force the error ORA-02292  CREATE OR REPLACE PROCEDURE delete_order  (order_id_in IN NUMBER)  IS  vtest2292 int := 0;  BEGIN   delete from orders where order_id = order_id_in;   commit;     -- if the delete instruction runs fine the tcount table is truncated   EXECUTE IMMEDIATE ('truncate table tcount');    EXCEPTION   WHEN OTHERS THEN   DECLARE   error_code NUMBER := SQLCODE;   BEGIN   IF error_code = -2292 THEN   DBMS_OUTPUT.PUT_LINE('ERROR 2292!!!!!!!!!!!!!'); -- error found   insert into tcount values (1);   commit;     select count(1) into vtest2292 from tcount;     IF vtest2292 >= 2 then   DBMS_OUTPUT.PUT_LINE('ERROR 2292 >x2!!!!!!!!!!!!!!'); -- two or more consecutive errors found   raise_application_error (-20001,'Two or more ORA-2292 were occurred deleting an order.');   END IF;   ELSE   raise_application_error (-20002,'An ERROR has occurred deleting an order.');   END IF;   END;  END;  /

Automating and Monitoring the Error Detection Job

We can use DBMS_SCHEDULER to automate the process and monitor the database alert log to see the handler in action.

--Job to run the procedure delete_order and confirm that just after the second consecutive execution the error will be written in alert log  BEGIN   DBMS_SCHEDULER.create_job (   job_name => 'job_delete_order',   job_type => 'PLSQL_BLOCK',   job_action => 'begin delete_order(1); end;',   start_date => SYSTIMESTAMP,   repeat_interval => 'FREQ=HOURLY;BYMINUTE=0; interval=1;',   enabled => TRUE);  END;  /    --Running the job job_delete_order to force the error, we have to monitor the database alert log to confirm that just after two executions the error will be visible there  set serveroutput on  select count(1) from tcount;  exec dbms_scheduler.run_job('job_delete_order',FALSE);  select count(1) from tcount;    tail -f alert_<database_name>.log

Understanding and Resolving the ORA-02292 Violation

The text associated with the ORA-02292 error is “integrity constraint (%s.%s) violated – child record found.” This issue can be fixed by deleting the child rows first, and afterward deleting the parent row. Another option is to modify the foreign key constraint to delete child rows when a parent row is deleted by setting up the “Foreign Key with Delete Cascade” option, but with SQLCODE function we can capture the error and handle it as needed.