Oracle program too large (codegen operands) error

Question

I'd like to build shapes in Oracle from several thousand points, but upon running the created code, I get the error:

ORA-06550: program too large (codegen operands)

What limit am I hitting? How can I overcome on it?
A similar code to reproduce the error (it runs or fails in a minute):

declare
  s clob;
begin
  s := '
    declare
       type t_x is table of number index by pls_integer;
       x t_x;
       varr sdo_ordinate_array;
    begin
    ';
  for i in 1..23000 loop --21825: ok, 21850: error
     s := s || 'x('||to_char(i)||') := 46.709864 + '||to_char(i)||'/23000;';
  end loop;

  s := s || '
      varr := sdo_ordinate_array();
      varr.extend(x.count);

      for i in 1 .. x.count loop
         varr(i) := x(i);
      end loop;
    end;';
  execute immediate s;
end;

For 10g these are the parameters that can cause that message if they are breached. https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/limits.htm. — BriteSponge, Sep 01 '17 at 13:18
Yes, I've found this as well. But which of them relate to the question above? — lmocsi, Sep 01 '17 at 15:43

Wernfried Domscheit · Answer 1 · 2017-09-01T13:30:38.527

1

Is there a reason why you put everything into a single dynamic statement? It looks really strange.

Try it similar to this:

declare
    varr sdo_ordinate_array;
begin
    varr := sdo_ordinate_array();
    for i in 1..23000 loop
        varr.extend;
        varr(i) := 46.709864 + i/23000;
    end loop;   

end;

I don't know your real code but you can also return value from execute immediate as this example. Perhaps it can simplify your problem.

DECLARE
    type t_x is table of number index by pls_integer;
    x t_x;
    str varchar2(100);
BEGIN
    FOR i in 10..20 LOOP
        str := i|| ' * 2';
        EXECUTE IMMEDIATE 'BEGIN :ret := '||str||'; END;' USING OUT x(i);
        dbms_output.put_line(x(i));
    END LOOP;
END;

edited Sep 01 '17 at 13:30

answered Sep 01 '17 at 13:22

Wernfried Domscheit

54,457
9
76
110

The reason why everything is put in the clob is that I have different coordinates for every element of the array. These are in a file, and the code above was the simplest form to emulate the original problem. The original problem is the code inside the clob. – lmocsi Sep 01 '17 at 15:48
If you really need this (which I still doubt) then you have to split your code into several packages. Do you still use Oracle 10g? In newer releases it is almost impossible to exceed the limit. – Wernfried Domscheit Sep 01 '17 at 15:59
This problem arose on 12c.But I just wanted an anonym plsql block, to load the data. The below code (with bulk collect) solves this problem. – lmocsi Sep 02 '17 at 19:09

score 0 · Accepted Answer · answered Sep 01 '17 at 13:15

It seems, that bulk collect can be sort of a solution to this problem. The following code runs 10x longer, but does not give an error:

declare
  s clob;
begin
  s := '
    declare
       type t_x is table of number index by pls_integer;
       x t_x;
       varr sdo_ordinate_array;
    begin
       select coord 
       bulk collect into x
       from (';
  for i in 1..23000 loop --21825: ok, 21850: error
     s := s || 'select '||to_char(i)||' rn, 46.709864 + '||to_char(i)||'/23000 coord from dual union all'||chr(10);
  end loop;

  s := s || '
      select null,null from dual
      )
      where rn is not null
      order by rn;

      varr := sdo_ordinate_array();
      varr.extend(x.count);

      for i in 1 .. x.count loop
         varr(i) := x(i);
      end loop;
    end;';
  execute immediate s;
end;

Oracle program too large (codegen operands) error

2 Answers2