ORA-06512 in small snippet

Question

I'm having trouble with a small college exercise. I'm supposed to catch an exception, ORA-02292 to be specific, and I do, but for some reason sqldeveloper gives me an ORA-06512 at line 14, which is where I raise the application error. Here's the code:

Declare

  v_job_id job.job_id%TYPE := &Job_ID;
  v_error_constraint exception;
  PRAGMA EXCEPTION_INIT(v_error_constraint, -2292);

Begin

  Delete from job
    Where job_id = v_job_id;

Exception

  When v_error_constraint then
   raise_application_error(-20001, 'You are trying to delete a job associated to an employee.');

End;

As I said, sqldeveloepr tells me that there's an ORA-06512 at line 14, which I can't understand how that's possible.

Line 14 is:

raise_application_error(-20001, 'You are trying to delete a job associated to an employee.');

by the way.

Hope you guys can help me.

Thanks!

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Developing Programmer-Defined Exceptions

CHECK your EXCEPTION block. The error you encountered: ORA-06512 Warns that there are errors in the code that are not handled. I rearranged the PL/SQL block from your OP to see what kind of results I could get:

[SQL Fiddle][1]

**Oracle 11g R2 Schema Setup**:
-- Stack Overflow Exception Handling

create table job
   ( job_id number  primary key,
     job_name varchar2(200),
     create_date date  
   );

create table employee
   ( ename varchar2(40),
     depno number,
     job_assignment number,
     hire_date date,
        constraint fk_job
        foreign key (job_assignment)
        references job (job_id)
   );

-- Our server does not have DBMS_OUTPUT enabled.  This log table
-- will serve as our programs and their outputs for review.

create table log_table
   ( log_date date,
     message varchar2(2000)
);

-- This utility procedure is *highly* reusable.  I added this
--  to the solution as a substitute for DBMS_OUTPUT feedback
--  messages.  Be sure to query the "log_table" table each time
--  you run a test that calls this procedure.

CREATE PROCEDURE log_the_msg ( msg IN log_table.message%TYPE )
IS
   PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
   INSERT INTO log_table( log_date, message )
   VALUES(sysdate, msg);
   COMMIT;

END;

insert into job( job_id, job_name, create_date )
 values( 100, 'NINJA', to_date('01/01/2014','MM/DD/YYYY'));

insert into job( job_id, job_name, create_date )
 values( 200, 'RESEARCHER', to_date('01/12/2014','MM/DD/YYYY'));

insert into job( job_id, job_name, create_date )
 values( 300, 'SECURITY', to_date('03/05/2014','MM/DD/YYYY'));

insert into job( job_id, job_name, create_date )
 values( 400, 'ADMINISTRATIVE', to_date('04/15/2014','MM/DD/YYYY'));

insert into job( job_id, job_name, create_date )
 values( 500, 'FACILITIES', to_date('04/21/2014','MM/DD/YYYY'));

commit;

insert into employee( ename, depno, job_assignment, hire_date )
   values('SPLINTER', 20, 100, to_date('03/15/2014','MM/DD/YYYY')); 

insert into employee( ename, depno, job_assignment, hire_date )
   values('MENDEL', 20, 100, to_date('03/16/2014','MM/DD/YYYY')); 

insert into employee( ename, depno, job_assignment, hire_date )
   values('RAFAEL', 10, 100, to_date('03/15/2014','MM/DD/YYYY')); 

insert into employee( ename, depno, job_assignment, hire_date )
   values('DONATELLO', 30, 200, to_date('03/17/2014','MM/DD/YYYY')); 

insert into employee( ename, depno, job_assignment, hire_date )
   values('MICHELANGELO', 20, 200, to_date('03/10/2014','MM/DD/YYYY')); 

commit;

Originally Posted PL/SQL Block (edited)

-- Originally Posted PL/SQL Block

Declare

  v_deleted_job_id   job.job_id%TYPE:= 100;
  v_error_constraint exception;
  pragma exception_init(v_error_constraint, -2292);

Begin

  DELETE FROM job
   WHERE job_id = v_deleted_job_id;
  COMMIT;

--Confirmation Checkpoint:
  log_the_msg('Ran PL/SQL Code Block to Completion.');

Exception

   When v_error_constraint then
      log_the_msg(SQLERRM);
      raise_application_error(-20001, 
         'You are trying to delete a job associated to an employee.');

End;

Query 1:

SELECT * FROM log_table
   ORDER BY log_date desc

Results:

|                     LOG_DATE |                                                                             MESSAGE |
|------------------------------|-------------------------------------------------------------------------------------|
| April, 22 2014 09:22:46+0000 | ORA-02292: integrity constraint (USER_4_AF378.FK_JOB) violated - child record found |
| April, 22 2014 09:22:20+0000 | ORA-02292: integrity constraint (USER_4_AF378.FK_JOB) violated - child record found |

if you want to read first some background on Oracle database exception handling, try this reference from Oracle Documentation: Handling PL/SQL Errors.

Usually, if you are dealing with Oracle defined exceptions such as:

NO_DATA_FOUND
DIVIDE_BY_ZERO
TOO_MANY_ROWS
DIVIDE_BY_ZERO

All you have to do to trigger the exception is to program your project to commit the error. For example, in the body of your code, if you wanted to force a "divide by zero" exception, simply try assigning a divide-by-zero calculation to a variable within your code:

v_example_test:= 1500 / 0;

Oracle will fail when trying to execute this instruction and it will exit out of the code into the EXCEPTION PL/SQL block defined at the bottom of your code.

Some additional tips on working with named, programmer-defined exceptions such as the one in your pl/sql code block:

v_error_constraint

The link above suggests the following format for developing and testing custom, programmer-defined exceptions:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
    IS
    [declaration_section]

    exception_name EXCEPTION;

BEGIN
    executable_section
    RAISE exception_name;

EXCEPTION
    WHEN exception_name THEN
       [statements]
  
    WHEN OTHERS THEN
       [statements]

END [procedure_name];

ORA-06512 in small snippet

1 Answers1

Developing Programmer-Defined Exceptions