For Loop Execute Immediate Enhancement – Small Bug

Hi,

Oracle added a lot of For Loop enhancement on 21c. All supported on 23c and 26ai of course. One of them is dynamic sql (execute immediate) support in For Loop. I prefer for loop instead of “while loop” because it is more clear and mostly easier to read. here is an example:

create or replace package pkg_X as 
  x number;
end;
/

Package PKG_X compiled

create or replace package body pkg_x as
  procedure sp_1 as
    type t_arr is table of varchar2(128) index by pls_integer;
    x_arr t_arr;
    type t_rec is record(con_id number, name varchar2(128));
    xx number;
  begin
    for r t_rec in (execute immediate 'select con_id, name from v$pdbs') loop
      x_arr(r.con_id) := r.name;
    end loop;
  end;
end;
/

Package Body PKG_X compiled

create or replace package pkg_X as

x number;

end;

Package PKG_X compiled

create or replace package body pkg_x as

procedure sp_1 as

type t_arr is table of varchar2(128) index by pls_integer;

x_arr t_arr;

type t_rec is record(con_id number, name varchar2(128));

xx number;

begin

for r t_rec in (execute immediate 'select con_id, name from v$pdbs') loop

x_arr(r.con_id) := r.name;

end loop;

end;

Package Body PKG_X compiled

this is just an example. of course this code does not need dynamic sql but I just wanted to demonstrate the issue. So the package is compiled without an issue. By the way I am running this code on 26ai (23.26.0).

Problem starts when you add another procedure with a DATE column selection. (Interesting right?)

create or replace package body pkg_x as
  procedure sp_1 as
    type t_arr is table of varchar2(128) index by pls_integer;
    x_arr t_arr;
    type t_rec is record(con_id number, name varchar2(128));
    xx number;
  begin
    for r t_rec in (execute immediate 'select con_id, name from v$pdbs') loop
      x_arr(r.con_id) := r.name;
    end loop;
  end;

  procedure sp_2 as 
    x_date date;
  begin
    select max(created) into x_date from user_objects;
  end;
end;
/

LINE/COL ERROR
--------- -------------------------------------------------------------
16/5 PLS-00801: internal error [*** ASSERT at file pdw4.c, line 1040; Cannot coerce from type 25 to type 12; PKG_X__TEST_USER__B__323913[16, 5]]

create or replace package body pkg_x as

procedure sp_1 as

type t_arr is table of varchar2(128) index by pls_integer;

x_arr t_arr;

type t_rec is record(con_id number, name varchar2(128));

xx number;

begin

for r t_rec in (execute immediate 'select con_id, name from v$pdbs') loop

x_arr(r.con_id) := r.name;

end loop;

end;

procedure sp_2 as

x_date date;

begin

select max(created) into x_date from user_objects;

end;

LINE/COL ERROR

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

16/5 PLS-00801: internal error [*** ASSERT at file pdw4.c, line 1040; Cannot coerce from type 25 to type 12; PKG_X__TEST_USER__B__323913[16, 5]]

so we got an Internal error. It points ” PKG_X__TEST_USER__B__323913[16, 5]” line 16 which is “select max(created)…”. It is not related with table or column.

if you switch order of the procedures (put sp_2 above the sp_1) then it is compiled normally! Another interesting point is, it is happening when you select column like DATE, Timestamp etc but not on number or varchar2 column.

create or replace package body pkg_x as
  procedure sp_1 as
    type t_arr is table of varchar2(128) index by pls_integer;
    x_arr t_arr;
    type t_rec is record(con_id number, name varchar2(128));
    xx number;
  begin
    for r t_rec in (execute immediate 'select con_id, name from v$pdbs') loop
      x_arr(r.con_id) := r.name;
    end loop;
  end;

  procedure sp_2 as 
    x_object_id number;
  begin
    select max(object_id) into x_object_id from user_objects;
  end;
end;
/

Package Body PKG_X compiled

create or replace package body pkg_x as

procedure sp_1 as

type t_arr is table of varchar2(128) index by pls_integer;

x_arr t_arr;

type t_rec is record(con_id number, name varchar2(128));

xx number;

begin

for r t_rec in (execute immediate 'select con_id, name from v$pdbs') loop

x_arr(r.con_id) := r.name;

end loop;

end;

procedure sp_2 as

x_object_id number;

begin

select max(object_id) into x_object_id from user_objects;

end;

Package Body PKG_X compiled

so it is compiled again. as I said, varchar2 is also working. in any case there is a small compiler bug in here. I hope it will be fixed soon. I would like to use new features as much as possible.

wish you a healthy good life. stay positive in this era of AI…

Leave a Reply Cancel reply