Home » SQL & PL/SQL » Client Tools » PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be (oracle 10g ,Solaris)
PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585826] Thu, 30 May 2013 04:15 Go to next message
prejib
Messages: 118
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 40 times)
Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585831 is a reply to message #585826] Thu, 30 May 2013 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 57642
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which line is line 32?
Use SQL*Plus and copy and paste your session, the WHOLE session including the error.

You get this message when either the object does not exist, either you have not access to it in the context you want to access it.
Remember that roles are not enabled in procedure.

Regards
Michel

Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585834 is a reply to message #585826] Thu, 30 May 2013 04:48 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
prejib wrote on Thu, 30 May 2013 11:15

LINE/COL ERROR
-------- -----------------------------------------------------------------
32/10 PL/SQL: Statement ignored
32/10 PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be
declared
[/code]


Hello,

What is the output of the following?

SELECT object_name, object_type, status 
FROM user_objects 
WHERE object_name = 'Q$ERROR_MANAGER';



Regards,
Dariyoosh
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 Go to previous messageGo to next message
prejib
Messages: 118
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

Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585837 is a reply to message #585835] Thu, 30 May 2013 04:54 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
AS I asked, what do you get if you run the following queries

To see whether the package exists
SELECT object_name, object_type, status 
FROM user_objects 
WHERE object_name = 'Q$ERROR_MANAGER';


To see whether the procedure in that package exists
SELECT procedure_name
FROM all_procedures
WHERE owner = USER and object_name = 'Q$ERROR_MANAGER';



Regards,
Dariyoosh
Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585841 is a reply to message #585835] Thu, 30 May 2013 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 57642
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 30 May 2013 11:39
...
You get this message when either the object does not exist, either you have not access to it in the context you want to access it.
Remember that roles are not enabled in procedure.

Regards
Michel

Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585842 is a reply to message #585837] Thu, 30 May 2013 05:36 Go to previous messageGo to next message
prejib
Messages: 118
Registered: March 2009
Location: India
Senior Member
Hi ,

The below object is not present in user or in 'sys' to grant privileges.please help me where can we find this ?
SQL> SELECT object_name, object_type, status
FROM user_objects
WHERE object_name = 'Q$ERROR_MANAGER';

no rows selected

SQL> 
Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585844 is a reply to message #585842] Thu, 30 May 2013 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 57642
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query in ALL_OBJECTS, if it is not there then either the object does not exist or you have not access to it.
It it is there, execute "set role none;" and execute again the query on ALL_OBJECTS. What happens?

Regards
Michel
Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585849 is a reply to message #585844] Thu, 30 May 2013 06:32 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
You can download Q$ERROR_MANAGER from Toad World
Re: PLS-00201: identifier 'Q$ERROR_MANAGER.REGISTER_ERROR' must be [message #585855 is a reply to message #585849] Thu, 30 May 2013 07:31 Go to previous message
prejib
Messages: 118
Registered: March 2009
Location: India
Senior Member
Thanks . Resolved the issue
Previous Topic: Number Data Type Column does not display number as it was entered
Next Topic: PL/SQL step by step Execution
Goto Forum:
  


Current Time: Wed Apr 23 19:02:59 CDT 2014

Total time taken to generate the page: 0.09639 seconds