Chạy DDL trong Thủ tục Oracle - ORA-06508 - Làm thế nào để biên dịch lại trong thời gian chạy?

Trong trường hợp này, cũng như nhiều trường hợp khác, sẽ tốt hơn nếu tạo một gói thay vì một loạt các thủ tục độc lập. Các gói phá vỡ chuỗi phụ thuộc. Đây là một ví dụ:

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

    Table created

1. Các thủ tục độc lập

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

   Thủ tục được tạo

   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  /
   

và chúng tôi đang nhận được

     ORA-06508: PL/SQL: could not find program unit being called

1. Bưu kiện

   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;
     8  
     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;
    18  
    19  end;
    20  /
   
    Package body created
   
   
    SQL> begin
      2    test_pkg.proc1;
      3    test_pkg.proc2;
      4  end;
      5  /
   
      0
   
      PL/SQL procedure successfully completed
   

alter procedure PX3 compile; -- not "recompile"

Điều này hoạt động như mong đợi:

set serveroutput ON

create table X as select 42 A from DUAL;

create procedure PX3
as
  a int;
begin
  select max(A) into a from X;
  dbms_output.put_line(a);
end;
/

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

create procedure PX1
as
begin
  PX2;
end;
/

begin
  PX1;
end;
/

drop procedure PX1;
drop procedure PX2;
drop procedure PX3;
drop table X;

Nếu tôi không nhầm thì Oracle 9 nghiêm ngặt hơn về sự phụ thuộc và tính vô hiệu so với Oracle> = 10.

Tôi đã gặp một vài trường hợp trong Oracle 9 về các thủ tục và / hoặc gói thực hiện DDL trên các bảng mà chúng phụ thuộc vào. Tôi đã đi đến kết luận rằng giải pháp thiết thực nhất là làm cho họ không phụ thuộc vào những bảng đó. Tức là thay vì thực hiện SQL tĩnh

select COL1 into my_var from MY_CHANGING_TABLE ...;

làm điều đó như SQL động

execute immediate 'select COL1 from MY_CHANGING_TABLE ...' into my_var;