In sqlplus how how to assign the result of PL/SQL function to a variable if the result if varchar2(n) with n> n

Pierre-olivier Gendraud 0
Viewed 23
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

sqlplus oracle
2 Answers

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;
ekochergin0

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
Littlefoot0
Related