Oracle Magazine - September/October 2011

Working with Strings

Part 3 in a series of articles on understanding and using PL/SQL

ORACLE DATABASE

Every application needs data. That seems rather obvious, doesn’t it? An application is almost always built on top of database tables. Those tables are full of different kinds of data. And the programs you write—whether they are in PL/SQL or another language—manipulate that data. It is, therefore, extremely important for you to be aware of the different datatypes supported by PL/SQL and how you can work with those datatypes.

As you might expect, there is an awful lot to learn about datatypes, and not all of that knowledge can fit into a single article. So I will start with one of the most common types of data: strings. Very few database tables and programs do not contain strings—strings such as a company name, address information, descriptive text, and so on. As a result, you quite often need to do the following: •;Declare string variables and constants

•;Manipulate the contents of a string (remove characters, join together multiple strings, and so on)

•;Move string data between PL/SQL pro-
grams and database tables
This article gives you the information you
need to begin working with strings in your
PL/SQL programs.

What;Is;a;strIng?

I-HUA CHEN

A string, also referred to as character data, is a sequence of selected symbols from a particular set of characters. In other words, the symbols in a string might consist of English letters, such as “A” or “B.” They might also consist of Chinese characters, such as 字串. There are three kinds of strings in PL/SQL: Fixed-length;strings.;The string is right-padded with spaces to the length specified in the declaration. (See the next section, “Declaring String Variables,” to see padding in action.)

Variable-length;strings.;A maximum length for the string is specified (and it must be no greater than 32,767), but no padding takes place.

Character;large;objects;(CLOBs). CLOBs are variable-length strings that can be up to 128 terabytes.

Strings can be literals or variables. A string literal begins and ends with a single quotation mark:

'This is a string literal'

If you need to embed a single quote inside a string literal, you can type in two single quotes right next to one another, as in:

'This isn''t a date'

You can also use the “q” character to indicate an alternative terminating character for the literal:

q'[This isn't a date]'

A string variable is an identifier declared with a string datatype and then assigned a value (which could be a literal or an expression).

DeCLar Ing;str Ing;VarIaBLes

To work with strings in your PL/SQL programs, you declare variables to hold the string values. To declare a string variable, you must select from one of the many string datatypes Oracle Database offers, including CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes that are prefixed with an ”N“ are “national character set” datatypes, which means they are used to store Unicode character data. (Unicode is a universal encoded character set that can store information in any language using a single character set.)

To declare a variable-length string, you

must provide the maximum length of that string. The following code declares a variable, using the VARCHAR2 datatype, that will hold a company name, which cannot (in this declaration) have more than 100 characters:

DECLARE l_company_name VARCHAR2(100);

You must provide the maximum length; if you leave it out, Oracle Database raises a compile error, as shown below:

SQL> DECLARE

2 l_company_name VARCHAR2;

3 BEGIN

4 l_company_name := 'Oracle Corporation';

5 END;

6 / l_company_name VARCHAR2;

*

ERROR at line 2: ORA-06550: line 2, column 21: PLS-00215: String length constraints must be in range ( 1 .. 32767)

To declare a fixed-length string, use the CHAR datatype:

DECLARE l_yes_or_no CHAR( 1) := 'Y';

With CHAR (unlike with VARCHAR2) you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length of 1. In other words, the two declarations below are identical: