ORA-06512中的小片段

Question

我在大学的小练习上遇到了麻烦。我应该捕捉到一个异常，ORA-02292是特定的，但由于某种原因，sqldeveloper在第14行给了我一个ORA-06512，这是我引发应用程序错误的地方。下面是代码：

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;

正如我所说的，sqldeveloepr告诉我，在第14行有一个ORA-06512，我不知道这是怎么可能的。

第14行是：

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

顺便说一下。

希望你们能帮我。

谢谢!

Answer 1

开发程序员定义的异常

检查你的EXCEPTION块。您遇到的错误：ORA-06512警告代码中存在未处理的错误。我从您的OP中重新安排了PL/SQL块，以查看可以得到什么样的结果：

[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;

最初发布PL/SQL (编辑)

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

查询1

SELECT * FROM log_table
   ORDER BY log_date desc

结果

|                     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 |

如果您想首先阅读一些有关Oracle数据库异常处理的背景知识，请尝试从Oracle文档：处理PL/SQL错误中引用。

通常，如果您正在处理Oracle定义的异常，如：

• NO_DATA_FOUND
• DIVIDE_BY_ZERO
• TOO_MANY_ROWS
• DIVIDE_BY_ZERO

要触发异常，所要做的就是对项目进行编程以提交错误。例如，在代码正文中，如果您想强制执行“除以零”异常，只需尝试为代码中的变量分配一个除以零的计算：

v_example_test:= 1500 / 0;

当尝试执行此指令时，Oracle将失败，它将退出代码，进入在代码底部定义的EXCEPTION PL/SQL块。

有关使用命名的程序员定义的异常的其他提示，如pl/sql代码块中的提示：

v_error_constraint

上面的链接为开发和测试自定义、程序员定义的异常提供了以下格式：

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];