ORA-06502 while dbms_sql.execute(<anonymous block>) with out-binding

Question

I got some trouble with executing dynamic SQL with binding variables (in and out) via dbms_sql. I always get an ORA-06502 but cannot get the reason. I could reduce the SQL-snippet so far to know that the out parameter occurs the error.

declare
  l_cur_id NUMBER;
  l_sql    VARCHAR2(100) := 'begin :1 := ''test''; end;';
  l_res    VARCHAR2(100);
  l_dbms   NUMBER;
begin
  l_cur_id := dbms_sql.open_cursor;
  dbms_sql.parse(l_cur_id, l_sql, dbms_sql.native);
  dbms_sql.bind_variable(l_cur_id, '1', l_res);
  l_dbms := dbms_sql.execute(l_cur_id); -- ORA here
  dbms_sql.close_cursor(l_cur_id);
exception
  when others then
    dbms_sql.close_cursor(l_cur_id);
    raise;
end;
/

In-parameters work fine. I'm using Oracle Database 12 Enterprise Edition Release 12.1.0.2.0

Do I have to config the out-parameter in another way? I'm thankful for any help.

Answer 1

You haven't specified a size for the bind variable. By default it uses the current length of the variable; from the documentation:

Parameter	Description
out_value_size	Maximum expected OUT value size, in bytes, for the VARCHAR2, RAW, CHAR OUT or IN/OUT variable. If no size is given, then the length of the current value is used. This parameter must be specified if the value parameter is not initialized.

As that variable is by default initialised as null, that length is zero; which is the same as saying it isn't initialised. So it errors when it tries to assign the four-character 'test' to to the zero-character variable.

You also need to call dbms_sql.variable_value to get retrieve the out bind variable value.

If you initialised l_res with a value at least as long as anything you might assign inside the dynamic block then it would work:

declare
  ...
  l_res    VARCHAR2(100) := 'xxxx';
  ...
begin
  ...
  dbms_sql.bind_variable(l_cur_id, '1', l_res);
  l_dbms := dbms_sql.execute(l_cur_id);
  dbms_sql.variable_value(l_cur_id, '1', l_res);
  ...
end;
/

but that obviously ideal, as really you'd need to supply a value 100-chars long, and remember to adjust that if the length changed later; so instead specify the length in the bind call:

declare
  ...
  l_res    VARCHAR2(100);
  ...
begin
  ...
  dbms_sql.bind_variable(l_cur_id, '1', l_res, 100);
  l_dbms := dbms_sql.execute(l_cur_id);
  dbms_sql.variable_value(l_cur_id, '1', l_res);
  ...
end;
/

db<>fiddle