Take the 2-minute tour ×

I have an oracle procedure which calls few other procedures.


Now what happens is that PROCEDURE2 is performing DDL (EXECUTE IMMEDIATE 'DDL PROCESS' ) on some dependent tables being used in subsequent procedures 3 and onwards. This runs fine until it hits PROCEDURE3 where it throws ORA-06508 - Program / Unit being called not found.

We can not modify it not to use DDL, We have to use them. I have tried recompiling after DDL statements but it does not work (ALTER PROCEDURE PROCEDURE3 RECOMPILE) neither in PROCEDURE1 or in PROCEDURE2.

I doubt that PROCEDURE1 itself becomes invalid and compiling that on runtime will cause endless loop. I have tried, does not works.

So I need to use some trick that can make these procedures valid again on runtime after DDL is done on dependent objects.

Thanks in advance for whoever participates to answer.

share|improve this question
you should try to call the procedures from anonymous bloks. –  Florin Ghita Dec 6 '12 at 7:40
@user1881540: What ddl changes you are doing,like creating a table ? and using that table in procedure 3 .Please post the code. –  Gaurav Soni Dec 6 '12 at 8:43
add comment

3 Answers

up vote 2 down vote accepted

In this case, as in many other cases, would be better to create a package instead of a bunch of stand-alone procedures. Packages break dependency chain. Here is an example:

    SQL> create table tb_t(
     2    col1 number
     3  )
     4  ;

    Table created
  1. Stand-alone procedures

    SQL> create or replace procedure Proc1
      2  is
      3  begin
      4    execute immediate 'alter table tb_t add (col2 number)';
      5  end;
      6  /

    Procedure created

    SQL> create or replace procedure Proc2
     2  is
     3    l_var number;
     4  begin
     5    select count(*)
     6      into l_var
     7      from tb_t;
     8    dbms_output.put_line(to_char(l_var));
     9  end;
     10  /
     Procedure created
     SQL> begin
      2    proc1;
      3    proc2;
      4  end;
      5  /

and we are getting

  1. Package

    SQL> create or replace package test_pkg
      2  is
      3    procedure proc1;
      4    procedure proc2;
      5  end;
      6  /
    Package created
    SQL> create or replace package body test_pkg
      2  is
      3    procedure proc1
      4    is
      5    begin
      6      execute immediate 'alter table tb_t add (col3 number)';
      7    end;
      9    procedure proc2
     10    is
     11     l_var number;
     12    begin
     13      select count(*)
     14        into l_var
     15        from tb_t;
     16      dbms_output.put_line(to_char(l_var));
     17    end;
     19  end;
     20  /
     Package body created
     SQL> begin
       2    test_pkg.proc1;
       3    test_pkg.proc2;
       4  end;
       5  /
       PL/SQL procedure successfully completed
share|improve this answer
What if I do not want to create a package ? –  user1881540 Dec 6 '12 at 9:20
Can you find another approach on this one. –  user1881540 Dec 6 '12 at 9:21
@user1881540 What if I do not want to create a package. Why not? If you really really do not want to create a package, you can use execute immediate statement (execute immediate begin proc1; end;(for each procedure in a pl/sql block)), which breaks dependency tracking as well. Take a look at this. But it would be better to use a package. –  Nicholas Krasnov Dec 6 '12 at 10:54
Yepp, That works ! Thank You. –  user1881540 Dec 6 '12 at 13:33
add comment
alter procedure PX3 compile; -- not "recompile"

This works as expected:

set serveroutput ON

create table X as select 42 A from DUAL;

create procedure PX3
  a int;
  select max(A) into a from X;

create procedure PX2
  execute immediate 'alter table X add (B int)';
  execute immediate 'alter procedure PX3 compile';

create procedure PX1


drop procedure PX1;
drop procedure PX2;
drop procedure PX3;
drop table X;
share|improve this answer
This does not works. I have already tried using the compile statement –  user1881540 Dec 6 '12 at 9:20
Well, the code I provided works when put into a file testcase.sql and executed in SQL*Plus while connected to an Oracle 9 server. Does it work on your system? If it works, what are you doing differently in your code? –  colemar Dec 6 '12 at 11:48
add comment

If I am not mistaken, Oracle 9 is more strict about dependency and invalidation compared to Oracle >=10.

I had a few cases in Oracle 9 of procedures and/or packages doing DDL on tables they depend on. I came to the conclusion that the most practical solution is to make them not dependent on those tables. I.e. instead of doing static SQL

select COL1 into my_var from MY_CHANGING_TABLE ...;

do it as dynamic SQL

execute immediate 'select COL1 from MY_CHANGING_TABLE ...' into my_var;
share|improve this answer
add comment

Your Answer


