Oracle Magazine - September/October 2011

points about long strings and maximum string sizes in mind.

When the string is too long. You must specify a maximum length when you declare a variable based on the VARCHAR2 type. What happens, then, when you try to assign a value to that variable whose length is greater than the maximum? Oracle Database raises the ORA-06502 error, which is also defined in PL/SQL as the VALUE_ERROR exception.

Here is an example of the exception being raised and propagated out of the block unhandled:

Code Listing 6: Examples of LTRIM and RTRIM functions

DECLARE a VARCHAR2 ( 40)

:= 'This sentence has too many periods....'; b VARCHAR2 ( 40) := 'The number 1';

BEGIN
DBMS_OUTPUT.put_line (
RTRIM (a, '.'));
DBMS_OUTPUT.put_line (
LTRIM (
b
, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '

|| 'abcdefghijklmnopqrstuvwxyz'));

END;

The output from this block is:

SQL> DECLARE

2 l_name VARCHAR2( 3); 3 BEGIN

4 l_name := 'Steven'; 5 END;

6 / DECLARE *

ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4

Here is a rewrite of the same block that traps the VALUE_ERROR exception:

SQL> DECLARE

2 l_name VARCHAR2 ( 3);

3 BEGIN

4 l_name := 'Steven';

5 EXCEPTION

6 WHEN VALUE_ERROR 7 THEN

8 DBMS_OUTPUT.put_line (

9 'Value too large!');

10 END;

11 / Value too large!

Interestingly, if you try to insert or update a value in a VARCHAR2 column of a database table, Oracle Database raises a different error, which you can see below:

SQL> CREATE TABLE small_varchar2

2 (

3 string_value VARCHAR2 ( 2)

4 )

This sentence has too many periods 1

5 / Table created.

SQL> BEGIN

2 INSERT INTO small_varchar2

3 VALUES ('abc');

4 END;

5 / BEGIN *

ERROR at line 1: ORA-12899: value too large for column "HR"."SMALL_VARCHAR2"."STRING_VALUE" (actual: 3, maximum: 2)

ORA-06512: at line 2

Different maximum sizes. There are a number of differences between SQL and PL/SQL for the maximum sizes for string datatypes. In PL/SQL, the maximum size for VARCHAR2 is 32,767 bytes, while in SQL the maximum is 4,000 bytes. In PL/SQL, the maximum size for CHAR is 32,767 bytes, while in SQL the maximum is 2,000 bytes.

Therefore, if you need to save a value from a VARCHAR2 variable in the column of a table, you might encounter the ORA-12899 error. If this happens, you have two choices:

•;Use SUBSTR to extract no more than 4,000 bytes from the larger string, and save that substring to the table. This option clearly has a drawback: you lose some of your data.

•;Change the datatype of the column from VARCHAR2 to CLOB. This way, you can save all your data.

In PL/SQL, the maximum size for CLOB is

128 terabytes, while in SQL the maximum is just ( 4 GB – 1) DB_BLOCK_SIZE.

THERE’S MORE TO DATA THAN STRINGS Character data plays a very large role in PL/SQL applications, but those same applications undoubtedly also rely on data of other types, especially numbers and dates. I will cover these datatypes in the next PL/SQL 101 article. 

Steven Feuerstein
(steven.feuerstein@
quest.com) is Quest
Software’s PL/SQL
evangelist. He has
published 10 books on
Oracle PL/SQL (O’Reilly Media) and is an Oracle
ACE director. More information is available at
stevenfeuerstein.com.