Wednesday, September 26, 2012

ORA-04053, ORA-00604 and ORA-02019

I had a problem which produced the three errors shown above. I looked them up in Google where it was suggested that I should check my database link, tnsnames.ora file, listener.ora file and anything else related to connecting one database to another. None of it made any difference. A colleague then worked out the cause of the problem and I have recreated it in the simple example shown below. First I created a user in a remote Oracle 10 database:

SQL> show user
USER is "SYS"
SQL> create user remote_user
  2  identified by remote_password
  3  /

User created.

SQL> grant create session to remote_user
  2  /

Grant succeeded.

SQL>

Then I created a link owner in a local Oracle 11 database:
 
SQL> show user
USER is "SYS"
SQL> create user link_user
  2  identified by link_password
  3  quota 1m on users
  4  /

User created.

SQL> grant
  2  create session,
  3  create table,
  4  create database link,
  5  create procedure to link_user
  6  /

Grant succeeded.

SQL>

Next I created another user in the local database:

USER is "SYS"
SQL> create user local_user
  2  identified by local_password
  3  /

User created.

SQL> grant create session
  2  to local_user
  3  /

Grant succeeded. 

SQL> 

I connected to the local database as the link owner and created a table and a link to the remote database. Then I created a procedure to insert the contents of the DUAL table from the remote database into the table in the local database. I also allowed the other user in the local database to execute this procedure. I ran the procedure and checked that an X had been inserted in the table. Next I modified the table's structure. This invalidated the procedure. I ran the procedure again. Oracle noticed that it was invalid and recompiled it so it was valid afterwards. I confirmed that there were then two rows in the table. Finally I modified the table's structure again so the procedure was invalid. This process of invalidating procedures, if the objects they access change, is standard Oracle functionality. The automatic recompilation is normal Oracle behaviour too. I explained it recently here albeit in French: 

SQL> show user
USER is "LINK_USER"
SQL> create table link_table
  2  (col1 varchar2(1))
  3  /

Table created.

SQL> create database link remote_database
  2  connect to remote_user
  3  identified by remote_password
  4  using 'MRMDPT1'
  5  /

Database link created.

SQL> create procedure link_procedure as
  2  begin
  3  insert into link_table
  4  select * from dual@remote_database;
  5  end;
  6  /

Procedure created.

SQL> grant execute on link_procedure to local_user
  2  /

Grant succeeded.

SQL> exec link_procedure;

PL/SQL procedure successfully completed.

SQL> select * from link_table
  2  /

C
-
X

SQL> alter table link_table modify
  2  (col1 varchar2(2))
  3  /

Table altered.

SQL> select status
  2  from user_objects
  3  where object_name = 'LINK_PROCEDURE'
  4  /

STATUS
-------
INVALID

SQL> exec link_procedure;

PL/SQL procedure successfully completed.

SQL> select status
  2  from user_objects
  3  where object_name = 'LINK_PROCEDURE'
  4  /

STATUS
-------
VALID

SQL> select * from link_table
  2  /

CO
--
X
X

SQL> alter table link_table modify
  2  (col1 varchar2(3))
  3  /

Table altered.

SQL> select status
  2  from user_objects
  3  where object_name = 'LINK_PROCEDURE'
  4  /

STATUS
-------
INVALID

SQL>

Then it all went wrong. I connected as the other user in the local database and tried to execute the procedure. You might think that Oracle would have done another automatic recompilation but it didn't. Instead, it displayed the three errors shown in the title. So, if this happens to you, try looking for invalid objects and recompile any you find. If there are too many, you could use utlrp as shown here:

SQL> show user
USER is "LOCAL_USER"
SQL> exec link_user.link_procedure;
BEGIN link_user.link_procedure; END;

*
ERROR at line 1:
ORA-04052: error occurred when looking up remote
object REMOTE_USER.DUAL@REMOTE_DATABASE.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-02019: connection description for remote database
not found

SQL>

To confirm that I had diagnosed the problem correctly, I connected as link_user and recompiled the procedure. Then I connected as local_user and ran the procedure successfully. Finally, I reconnected as link_user and confirmed that the table then had three rows, each with a letter X:

SQL> conn link_user/link_password
Connected.
SQL> alter procedure link_procedure compile
  2  /

Procedure altered.

SQL> conn local_user/local_password
Connected.
SQL> exec link_user.link_procedure;

PL/SQL procedure successfully completed.

SQL> conn link_user/link_password
Connected.
SQL> select * from link_table
  2  /

COL
---
X
X
X

SQL>

2 comments:

Anonymous said...

What was remote_dependencies_mode in the local database?

Andrew Reid said...

I cannot remember. I will revisit the example and get back to you.