CREATE function f4001 return varchar2
is
begin
   return rpad('*', 4100, '*');
end;


select f4001() from dual;

doesn't work: sql buffer to small

therefore to assign a sql variable I use that:

variable buffer varchar(32000);
execute :buffer = f4001();

I can print it:

print buffer

but I can't prompt it. Or use itas an argument for a powershell function:

 host powershell echo '&buffer'

for that reason I have to use 'define' or 'column'.

define : I have'nt find a way to give the result of a pl/sql function with define. All I can do is:

 define buffer= cte

column: work only for varchar(N) with N>4000.

  column buffer new_value buffer
  select f3999 as buffer from dual

OK

  select f4001 as buffer from dual

buffer to small

CodePudding user response：

Replacing varchar2 type with clob did helped me to get rid o "buffer too small" problem.

So, in other words, try this:

CREATE or replace function f4001 return clob ...

and

variable buffer clob;

CodePudding user response：

The CREATE FUNCTION documentation says:

The database derives the length, precision, or scale of the return value from the environment from which the function is called.

Your environment is SQL, and - in it - varchar2 is restricted to 4000 characters.

What to do?

• If your database version is 12c or higher, talk to DBA and see whether they can set MAX_STRING_SIZE to EXTENDED (then max size is 32767 and not 4000), or
• Switch to CLOB datatype