pl/sql时出现这个问题怎么解决啊,错误ORA
发布网友
发布时间：2022-04-30 00:35
我来回答
共2个回答
懂视网
时间：2022-04-30 04:57
DUP_VAL_ON_INDEX ORA-00001 You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE ORA-00051 You were waiting for a resource and you timed out.
TRANSACTION_BACKED_OUT ORA-00061 The remote portion of a transaction has rolled back.
INVALID_CURSOR ORA-01001 You tried to reference a cursor that does not yet exist. This may have happened because you‘ve executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON ORA-01012 You tried to execute a call to Oracle before logging in.
LOGIN_DENIED ORA-01017 You tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND ORA-01403 You tried one of the following:
You executed a SELECT INTO statement and no rows were returned.
You referenced an uninitialized row in a table.
You read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS ORA-01422 You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE ORA-01476 You tried to divide a number by zero.
INVALID_NUMBER ORA-01722 You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR ORA-06500 You ran out of memory or memory was corrupted.
PROGRAM_ERROR ORA-06501 This is a generic "Contact Oracle support" message because an internal problem was encountered.
VALUE_ERROR ORA-06502 You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
CURSOR_ALREADY_OPEN ORA-06511 You tried to open a cursor that is already open. 以上的例子中有关于命名的异常的使用方法。这里不再说了。
BEGIN
Execution section
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line (‘A SELECT...INTO did not return any row.‘);
END;
b：未命名系统异常：
这些系统异常没有名字，这些异常不经常出现，这些异常有错误代码和关联信息。
有两种方式处理未命名的异常：
方法一： 使用WHEN OTHERS 异常处理
方法二：给一个异常 关联异常代码和名称 ，然后像命名异常一样使用它。
方法一异常没有目标性，下面说明方法二：
使用Pragma 调用 EXCEPTION_INIT关联一个预定义的oracle错误号到程序定义的异常
下面是个demo：
DECLARE
exception_name EXCEPTION;
PRAGMA
EXCEPTION_INIT (exception_name, Err_code);
BEGIN
Execution section
EXCEPTION
WHEN exception_name THEN
handle the exception
END;
先声明异常名称，然后调用EXCEPTION_INIT函数绑定错误号码和异常名称， 绑定后这个异常名称就可以像命名式异常那样用了。
例如：
[sql]
SQL> DECLARE
2 e_insert_excep EXCEPTION; --定义异常名称
3 PRAGMA EXCEPTION_INIT(e_insert_excep,-01400); -- 关联异常名称和异
常号
4 BEGIN
5 INSERT INTO departments (department_id,department_name) VALUES(280,N
ULL);
6 EXCEPTION
7 WHEN e_insert_excep THEN
8 DBMS_OUTPUT.PUT_LINE(‘INSERT OPERATION FAILED‘);
9 DBMS_OUTPUT.PUT_LINE(SQLERRM);
10 END;
11 /
INSERT OPERATION FAILED
ORA-01400: 无法将 NULL 插入 ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")
PL/SQL 过程已成功完成。
首先定义一个 e_insert_excep异常名称， 然后调用 EXCEPTION_INIT函数绑定这个异常名称，最后出现异常输出错误信息。
SQLERRM的信息如下： ORA-01400: 无法将 NULL 插入 ("HR"."DEPARTMENTS"."DEPARTMENT_NAME")
需要关联 数字和自定义的错误名称；
两个变量： SQLCODE 错误代码
SQLERRM 错误内容
当抛出异常的时候，这两个变量会被自动填充，可以获取这两个的值来判断
错误：
3：用户自定义异常：
先看三个例子再解释：
ex1：
[sql]
SQL> DECLARE
2 v_deptno NUMBER := 500;
3 v_name VARCHAR2(20) :=‘Testing‘;
4 e_invalid_department EXCEPTION;
5 BEGIN
6 UPDATE departments SET department_name = v_name
7 WHERE department_id = v_deptno;
8
9 IF SQL%NOTFOUND THEN
10 RAISE e_invalid_department;
11 END IF;
12 COMMIT;
13 EXCEPTION
14 WHEN e_invalid_department THEN
15 DBMS_OUTPUT.PUT_LINE(‘No such department id‘);
16 END;
17 /
No such department id
PL/SQL 过程已成功完成。
关于RAISE_APPLICATION_ERROR 过程；
语法：
raise_application_error(error_number,message[,{TRUE|FALSE}]);
可以通过这个过程定义一个自己的异常号和 异常信息
注意： error_number是介于： -20000..-20999的数字，message是一个
字符串最大长度为2k
[sql]
SQL> DECLARE
2 v_deptno NUMBER := 500;
3 v_name VARCHAR2(20) := ‘Testing‘;
4 e_invalid_department EXCEPTION; --定义一个异常
5 PRAGMA EXCEPTION_INIT(e_invalid_department,-20188); --把异常和异常
号绑定；
6 BEGIN
7 UPDATE departments
8 SET department_name =v_name
9 WHERE department_id = v_deptno;
10
11 IF SQL%NOTFOUND THEN
12
13 RAISE_APPLICATION_ERROR(-20188,‘I write my error message here !‘);
14 END IF;
15 COMMIT;
16 EXCEPTION
17 WHEN e_invalid_department THEN
18 DBMS_OUTPUT.PUT_LINE(SQLCODE || ‘--->‘ || SQLERRM);
19 END;
20
21 /
-20188--->ORA-20188: I write my error message here !
PL/SQL 过程已成功完成。
说明：RAISE_APPLICATION_ERROR ( )
RAISE_APPLICATION_ERROR是一个内建的存储过程，这个存储过程可以显示用户
自定义的错误信息和错误号 ，这些错误号是Oracle开放出来供开发者用的，
范围为：-20000 and -20999
当使用RAISE_APPLICATION_ERROR的时候，所以以前的事务不提交，自动回滚。
语法格式如下：
RAISE_APPLICATION_ERROR (error_number, error_message);
使用RAISE_APPLICATION_ERROR步骤如下：
1：首先在Declaration区域自定义一个exception，
2：在特定的逻辑规则下出现 Raise 用户自定义的exception
3：最后 catch 这个异常，在捕获后使用RAISE_APPLICATION_ERROR这个过程链接
自定义的错误号和错误信息。
重点： 关于异常的传播机制：
比较以下三个例子就会明白异常的传播机制：
ex1：
[sql]
CREATE OR REPLACE PROCEDURE add_more_departments(p_name1 VARCHAR2, p_mgr1 NUMBER, p_loc1 NUMBER,p_name2 VARCHAR2, p_mgr2 NUMBER, p_loc2 NUMBER) IS
BEGIN
INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(departments_seq.NEXTVAL,p_name1,p_mgr1,p_loc1);
DBMS_OUTPUT.PUT_LINE(‘Add Dept: ‘ || p_name1);
INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(departments_seq.NEXTVAL,p_name2,p_mgr2,p_loc2);
DBMS_OUTPUT.PUT_LINE(‘Add Dept: ‘ || p_name2);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Err: adding dept:‘);
END;
/
CREATE OR REPLACE PROCEDURE create_more_departments
IS
BEGIN
add_more_departments(‘Media‘,100,1800,‘Editing‘,99,1800);
END;
/
BEGIN
create_more_departments;
END;
这个例子中 99这条记录在表中本来就有，现在再次进行插入，会冲突报错
执行结果：以上两个insert 只有第一个插入进去了，第二个插入失败。
执行结果如下：
SQL> /
Add Dept: Media
Err: adding dept:
PL/SQL 过程已成功完成。
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
340 Media
已选择28行。
只有第一条被插入
ex2：
在做第二个例子前先把上次的实验结果删除掉：
delete from departments where department_id >270;
select department_id,department_name from departments order by 1;
[sql]
CREATE OR REPLACE PROCEDURE add_more_departments(p_name1 VARCHAR2, p_mgr1 NUMBER, p_loc1 NUMBER,p_name2 VARCHAR2, p_mgr2 NUMBER, p_loc2 NUMBER) IS
BEGIN
INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(departments_seq.NEXTVAL,p_name1,p_mgr1,p_loc1);
DBMS_OUTPUT.PUT_LINE(‘Add Dept: ‘ || p_name1);
INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(departments_seq.NEXTVAL,p_name2,p_mgr2,p_loc2);
DBMS_OUTPUT.PUT_LINE(‘Add Dept: ‘ || p_name2);
END;
/
CREATE OR REPLACE PROCEDURE create_more_departments
IS
BEGIN
add_more_departments(‘Media‘,100,1800,‘Editing‘,99,1800);
END;
/
BEGIN
create_more_departments;
END;
执行结果如下：
SQL> BEGIN
2 create_more_departments;
3 END;
4 /
Add Dept: Media
BEGIN
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (HR.DEPT_MGR_FK) - 未找到父项关键字
ORA-06512: 在 "HR.ADD_MORE_DEPARTMENTS", line 6
ORA-06512: 在 "HR.CREATE_MORE_DEPARTMENTS", line 4
ORA-06512: 在 line 2
查询结果：
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
这次没有异常处理，把异常处理去掉了。
这次的执行结果： 两条记录一条记录也没有插入进去。
ex3：
delete from departments where department_id >270;
select department_id,department_name from departments order by 1;
[sql]
CREATE OR REPLACE PROCEDURE add_more_departments(p_name1 VARCHAR2, p_mgr1 NUMBER, p_loc1 NUMBER,p_name2 VARCHAR2, p_mgr2 NUMBER, p_loc2 NUMBER) IS
BEGIN
INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(departments_seq.NEXTVAL,p_name1,p_mgr1,p_loc1);
DBMS_OUTPUT.PUT_LINE(‘Add Dept: ‘ || p_name1);
INSERT INTO departments(department_id,department_name,manager_id,location_id)VALUES(departments_seq.NEXTVAL,p_name2,p_mgr2,p_loc2);
DBMS_OUTPUT.PUT_LINE(‘Add Dept: ‘ || p_name2);
END;
/
CREATE OR REPLACE PROCEDURE create_more_departments
IS
BEGIN
add_more_departments(‘Media‘,100,1800,‘Editing‘,99,1800);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Errors have happend‘);
END;
/
BEGIN
create_more_departments;
END;
执行结果：
SQL> BEGIN
2 create_more_departments;
3 END;
4 /
Add Dept: Media
Errors have happend
PL/SQL 过程已成功完成。
查询结果：
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
400 Media
这次把异常处理放在了最外面：调用的最外面：
执行结果如下：
执行成功的第一条语句被成功插入，第二条被捕获。
PL/SQL异常处理方法
标签：
热心网友
时间：2022-04-30 02:05
你的机器上应该是安装了多个Oracle产品，默认的sqlplus和PL/SQL Developer使用了不同路径下的tnsname。
这个问题有两种解决方法：
1.配置每个Oracle产品的$ORACLE_HOME/NETWORK/ADMIN/TNSNAME.ORA
2.配置PL/SQL Developer使用的tnsname
打开PL/SQL Developer，点击“Cancel”进入“PLS/SQL Developer(Not logged on)”，选择Tools >> Preferences 打开Preferences窗口，选择 Connection >> Oracle Home下拉框，选择tnsname。
