Working with Strings（使用Oracle字符串）(七)

from the string. */

DBMS_OUTPUT.put_line (

TRANSLATE ('S1t2e3v4e56n'

, 'A1234567890'

, 'A'));

END;

/

Now, “A” is replaced with “A” and the remaining characters in the string are replaced with NULL, so the string “Steven” is then displayed.

Good to Know

Beyond awareness of the basic properties of strings in PL/SQL and built-in functions, you can benefit by keeping the following 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:

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 adifferent error, which you can see below:

SQL> CREATE TABLE small_varchar2

2 (

3 string_value VARCHAR2 (2)

4 )

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.

-------------------------------

present by dylan.