Skip to content

Insight and analysis of technology and business strategy

Using SQLCODE Function

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 .
--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;
 
 --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;
 /
 
 --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
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.

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!

dba-cloud-services
Upcoming-Events-banner