PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585826] |
Thu, 30 May 2013 04:15 |
prejib
Messages: 126 Registered: March 2009 Location: India
|
Senior Member |
|
|
While executing the attached .sql , I am getting the bleow error . Please help me solve the issue
SQL> @salary.sql
Package created.
No errors.
Warning: Procedure created with compilation errors.
Errors for PROCEDURE TEST.CHANGE_SALARY_FOR:
LINE/COL ERROR
-------- -----------------------------------------------------------------
32/10 PL/SQL: Statement ignored
32/10 PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be
declared
-
Attachment: salary.sql
(Size: 2.15KB, Downloaded 2224 times)
|
|
|
|
|
Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585835 is a reply to message #585831] |
Thu, 30 May 2013 04:49 |
prejib
Messages: 126 Registered: March 2009 Location: India
|
Senior Member |
|
|
SQL> show user
USER is "TEST"
SQL> create or replace PACKAGE test.compensation_rules
IS
FUNCTION adjusted_compensation (
employee_id_in IN
employees.employee_id%TYPE,
pct_increase_in IN NUMBER)
RETURN
employees.salary%TYPE
;
END compensation_rules;
/
show errors; 2 3 4 5 6 7 8 9 10 11
Package created.
SQL> SQL>
No errors.
SQL> create or replace PROCEDURE test.change_salary_for (
2 dept_in IN employees.department_id%TYPE
3 , pct_increase_in IN NUMBER
4 , fetch_limit_in IN PLS_INTEGER
5 )
6 IS
7 bulk_errors exception;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
8 9 CURSOR employees_cur
IS
10 11 SELECT employee_id, salary FROM employees WHERE department_id = dept_in;
12
13 TYPE employee_tt IS TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER;
14 15 employee_ids employee_tt;
16 17 TYPE salary_tt IS TABLE OF employees.salary%TYPE INDEX BY BINARY_INTEGER;
18
19 salaries salary_tt;
20
PROCEDURE log_errors IS PRAGMA AUTONOMOUS_TRANSACTION;
21 22 BEGIN
23 FOR error_rec IN (SELECT * FROM err$_employees)
LOOP
24 25 q$error_manager.register_error (
26 error_code_in => error_rec.ora_err_number$
27 , name1_in => 'EMPLOYEEE_ID'
28 , value1_in => error_rec.employee_id
29 , name2_in => 'PCT_INCREASE'
30 , value2_in => pct_increase_in
31 , name3_in => 'NEW_SALARY'
32 , value3_in => error_rec.salary
33 );
34 END LOOP;
35
36 DELETE FROM err$_employees;
37 38 COMMIT;
39 END log_errors;
40 BEGIN
OPEN employees_cur;
41 42
LOOP
43 44 FETCH employees_cur
BULK COLLECT INTO employee_ids, salaries LIMIT fetch_limit_in;
45
46 FOR indx IN 1 .. employee_ids.COUNT
LOOP
47 48 salaries (indx) := compensation_rules.adjusted_compensation (
49 employee_id_in => employee_ids (indx)
50 , pct_increase_in => pct_increase_in
51 );
END LOOP;
52 53
54 FORALL indx IN 1 .. employee_ids.COUNT()
55 UPDATE employees SET salary = salaries (indx) WHERE employee_id = employee_ids (indx)
56 57
LOG ERRORS REJECT LIMIT UNLIMITED;
58
log_errors ();
59 60
61 EXIT WHEN employee_ids.COUNT() < fetch_limit_in;
62 END LOOP;
END change_salary_for; 63 64
65 /
show errors;
Warning: Procedure created with compilation errors.
SQL>
Errors for PROCEDURE TEST.CHANGE_SALARY_FOR:
LINE/COL ERROR
-------- -----------------------------------------------------------------
25/10 PL/SQL: Statement ignored
25/10 PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be
declared
|
|
|
|
|
|
|
|
|