The Curious Case of the Missing PL/SQL Procedure

I have to run files of SQL on databases for developers almost every day. These files often create or recreate PL/SQL packages. Any errors usually go back to the developers for correction as I am not a PL/SQL expert. Yesterday I ran some of these files then a tester asked me why a screen had stopped working. The screen was calling a PL/SQL package which I had just recreated for the developer. I will call it package_b. It had compilation errors and these were causing the screen to fail. The developer was on leave so I decided to have a look at the problem. I listed the compilation errors and could see that they happened when package_b tried to call a new procedure in another package. I will call the other package package_a and I will call the new procedure procedure_a2. I looked for compilation errors in package_a but there were none. I looked at the description for package_a but procedure_a2 was not mentioned. This was because the developer had provided a new body for package_a but no new header. The new header was found and package_a was recompiled. It was then possible to recompile package_b and the tester’s screen started to work again. I have reproduced this problem below on Oracle 11.2.0.2.7:

 

I created package_a and showed that its description contained procedure_a1:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  end package_a;

  4  /

 

Package created.

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  end package_a;

  7  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

I created package_b containing procedure_b1. This procedure called package_a.procedure_a1. Then I ran package_b.procedure_b1 successfully:

 

SQL> create or replace package package_b is

  2  procedure procedure_b1;

  3  end package_b;

  4  /

 

Package created.

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  end procedure_b1;

  6  end package_b;

  7  /

 

Package body created.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

 

I added procedure_a2 to package_a but did not change its header. Then I described it but could only see procedure_a1:

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  procedure procedure_a2 is

  7  begin

  8  null;

  9  end procedure_a2;

10  end package_a;

11  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

Then I changed package_b to call package_a.procedure_a2 but it failed to compile:

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  package_a.procedure_a2;

  6  end procedure_b1;

  7  end package_b;

  8  /

 

Warning: Package Body created with compilation errors.

 

SQL> show errors

Errors for PACKAGE BODY PACKAGE_B:

 

LINE/COL

-------------------------------------------------------

ERROR

-------------------------------------------------------

5/1

PL/SQL: Statement ignored

 

5/11

PLS-00302: component 'PROCEDURE_A2' must be declared

 

SQL>

 

I changed the header for package_a. Then its description included procedure_a2:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  procedure procedure_a2;

  4  end package_a;

  5  /

 

Package created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

PROCEDURE PROCEDURE_A2

 

SQL>

 

This allowed me to compile package_b and run package_b.procedure_b1 again:

 

SQL> alter package package_b compile

  2  /

 

Package altered.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

DBMS_UTILITY.ANALYZE_SCHEMA

I read about dbms_utility recently and decided to give it a try. According to the documentation for Oracle 10.2, the analyze_schema procedure is obsolete (it says you should use dbms_stats.gather_schema_stats instead, which seems reasonable). However, when I tried dbms_utility.analyze_schema on that version, it seemed to work OK. I will try to look at some other dbms_utility subprograms in future posts:

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant create session, create table to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table table_list as

  2  select * from all_tables

  3  /

 

Table created.

 

SQL> select last_analyzed from user_tables

  2  where table_name = 'TABLE_LIST'

  3  /

 

LAST_ANAL

---------

 

 

SQL> exec dbms_utility.analyze_schema('ANDREW', 'ESTIMATE');

 

PL/SQL procedure successfully completed.

 

SQL> select last_analyzed from user_tables

  2  where table_name = 'TABLE_LIST'

  3  /

 

LAST_ANAL

---------

11-APR-12

 

SQL>

Bug 445628

This was tested on an Oracle 9 database. Oracle logged this as bug 445628 in 1997. Then they closed it saying it was not a bug. Either way, it's interesting so here is a worked example. First create two schemas, one to create a stored procedure and another to own a table:

SQL> conn / as sysdba
Connected.
SQL> create user code_owner identified by code_owner
  2  /

User created.

SQL> grant create session, create procedure to code_owner
  2  /

Grant succeeded.

SQL> create user table_owner identified by table_owner
  2  default tablespace user_data
  3  quota 1m on user_data
  4  /

User created.

SQL> grant create session, create table to table_owner
  2  /

Grant succeeded.

SQL>

Now login as code_owner, create a stored procedure and allow table_owner to execute it:

SQL> conn code_owner/code_owner
Connected.
SQL> create procedure do_nothing as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

SQL> grant execute on do_nothing to table_owner
  2  /

Grant succeeded.

SQL>

Now login as table_owner and execute code_owner's stored procedure:

SQL> conn table_owner/table_owner
Connected.
SQL> exec code_owner.do_nothing();

PL/SQL procedure successfully completed.

SQL>

So far so good but if table_owner creates a table with the same name as the schema whose code he wants to execute, that code stops working. I guess this is because Oracle thinks that he is trying to execute his own table:

SQL> create table code_owner (col1 number)
  2  /

Table created.

SQL> exec code_owner.do_nothing();
BEGIN code_owner.do_nothing(); END;

                 *
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00302: component 'DO_NOTHING' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

The solution is to drop the table:

SQL> drop table code_owner
  2  /

Table dropped.

SQL>

Then the code starts to work again:

SQL> exec code_owner.do_nothing();

PL/SQL procedure successfully completed.

SQL>