ORA-06572: Function %s has out arguments

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error is returned when you are (typically) trying to execute a user-defined function inside a SQL statement and the function contains some parameter defined either as OUT or as IN/OUT.

ORA-06572: Function CLOB_TO_BLOB has out arguments

Most built-in functions do not use OUT parameters however it is perfectly acceptable to build packaged functions where such type of parameters are present, if not even required, to increase performance and reduce temporary memory allocation.

Although in the specific case of this CLOB_TO_BLOB function, the p_clob parameter has been declared as IN OUT but there isn't a NOCOPY declaration following it, which looks odd to me, normally the IN OUT declaration is a requirement for NOCOPY.

The trade-off is in that any attempt of using such functions in a SQL statement fails with the aforementioned error.

So, what if you need to iterate the function for each row of a given set?

There are two possibilities:

• one is to convert the implicit cursor (the SQL statement) into an explicit cursor (FOR cursor LOOP).
• the second one is to write a wrapper function;

implicit cursor, raising the error:

create table my_blobs(id number, tgt_blob blob)
/
create table my_clobs(id number, src_clob clob)
/

insert into my_blobs(id, tgt_blob)
select id, wwv_flow_utilities.clob_to_blob(src_clob, 'AL32UTF8')
FROM my_clobs;

ORA-06572: Function CLOB_TO_BLOB has out arguments

explicit cursor:

DECLARE
myblob blob;
BEGIN
FOR cur_clob in (SELECT id, src_clob FROM my_clobs) LOOP
myblob := wwv_flow_utilities.clob_to_blob(cur_clob.src_clob, 'AL32UTF8');
insert into my_blobs(id, tgt_blob) values(cur_clob.id, myblob);
END LOOP;
END;

or a wrapper function:

create or replace
function wrap_c2b (
p_clob in clob,
p_charset in varchar2)
return blob
as
tmp_clob clob := p_clob;
begin
return wwv_flow_utilities.CLOB_TO_BLOB(tmp_clob, p_charset);
end;

insert into my_blobs(id, tgt_blob)
select id, wrap_c2b(src_clob, 'AL32UTF8')
FROM my_clobs;

The wrapper function approach may cause performance degradation owing to the extra copies of the CLOBs causes by the passing of the source CLOB by value, which results in a plethora of temporary LOBs, so use it only if necessary (i.e. in your development environment, not on a production server...).

Usage of the explicit cursor seems to me a much more sensible way of using database resources, especially if the relevant parameter(s) of the function have been declared as NOCOPY.

See message translations for ORA-06572 and search additional resources