2

I have a function in PLSQL, that operates with strings and return VARCHAR2 type.

One of the variables in this function is

result_key     VARCHAR2 (4000) := '';

When it work with a big amount of data I get ORA-06502: PL/SQL: numeric or value error: character string buffer too small error.

Seems like I have to extend my result_key variable. The only solution I see is to declare result_key as

result_key     VARCHAR2 (8000) := '';

I want to know if I can do it without declaring fixed size of result_key.

Sergei Podlipaev
  • 1,331
  • 1
  • 14
  • 34
  • Since 11g R1 you can make your `varchar2` variable sizes "dynamic" by defining the variable with a maximum length: `varchar2(32767)`. See http://stackoverflow.com/q/24831972/272735 for details. However 32767 bytes is the hard limit. If your data won't fit the limit then you have to switch to `clob`. – user272735 Feb 05 '16 at 04:41

2 Answers2

5

PL/SQL allows varchar2() types to have up to 32,767 bytes (see here). This may be sufficient for your purposes. Note the limit for data stored in Oracle tables is 4,000 (see here). (In my opinion, the different lengths seems like a cruel practical joke, although I understand the underlying reasons.)

In general, the type for longer strings is CLOB -- character larger binary objects. These behave a lot like char/varchar and they can be stored in tables as well as PL/SQL variables. The one downside is that the simplest way to look at the value is to copy them to a varchar2() in PL/SQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
4

For varchar2 variables in PL/SQL you have to declare its length. Note tho that its actual length is always the real one, as they use only as much space as they need. For example

result_key     VARCHAR2 (4000) := 'test';

will have length of 4, not 4000. So if you're going to use big strings and not sure about their size (but sure that they won't hit the limit) - go with the max possible 32767 which Gordon already mentioned. But keep in mind that PL/SQL can handle way bigger strings than SQL (4000) so be careful when trying to mix those, for example inserting variables into tables. If you're not sure about their maximum size and it's possible that they'll exceed that - go with CLOBs.

But again when using CLOBS and copying them into varchar2, to avoid the error you already know, always use dbms_lob.substr(), for example:

dbms_lob.substr(clob_name, [limit], [offset]);
dbms_lob.substr(clob_name, 4000, 1);

in this example - extract first 4000 chars from clob_name

Jemo
  • 117
  • 1
  • 7