テクノロジー：PL/SQL

文字列の操作
Steven Feuerstein著

PL/SQLの理解と利用に関するシリーズ記事のパート3

すべてのアプリケーションにはデータが必要です。これは少々当たり前に感じられますね。アプリケーションはほとんどの場合、データベース表の上層に構築されます。データベース表はさまざまな種類のデータで溢れています。そして、PL/SQLであっても他の言語であっても、開発者が記述するプログラムはそのデータを操作します。そのため、PL/SQLでサポートされるさまざまなデータ型と、それらのデータ型の操作方法を知ることが非常に重要です。

クイズにチャレンジ

PL/SQLの基礎に関する著者の記事では、記事の中で説明した情報の知識をテストするクイズを毎回出題しています。このクイズは次に示す他、PL/SQL Challenge（plsqlchallenge.com ）にも掲載されます。PL/SQL Challengeは、PL/SQL言語のオンライン・クイズを提供するWebサイトです。この記事でクイズを読んで回答した場合、正解について次のパートで確認できます。一方、PL/SQL Challengeでクイズに挑戦した場合は、O’Reilly Media（oreilly.com）から電子書籍を選ぶチャンスに応募できます。

質問1

次のブロックを実行すると、どのように表示されるでしょうか。


  
BEGIN
   sys.DBMS_OUTPUT.put_line (
      INSTR ('steven feuerstein'
           , 'e'
           , -1
           , 2));
END;

質問2

「文字列にリテラル値を代入する場合に、そのリテラル値には一重引用符を含めることはできない」。これは正しいでしょうか、誤っているでしょうか。

質問3

次のブロックを実行すると、どのように表示されるでしょうか。 


  
BEGIN
   DBMS_OUTPUT.put_line (
      'REPLACE='
      || REPLACE ('steven feuerstein'
                , 'e'
                , NULL));
   DBMS_OUTPUT.put_line (
      'TRANSLATE='
      || TRANSLATE ('steven feuerstein'
                  , 'e'
                  , NULL));
END;

ご想像のとおり、データ型について学習すべきことは大量にあり、この1つの記事でそのすべての知識を紹介することはできません。したがってまずは、もっともよく使用されるデータ型の1つである、文字列について取り上げます。会社名、住所情報、説明文などが文字列であり、そのような文字列を使用しないデータベース表やプログラムはほとんどありません。結果的に、たいていの場合は次の操作を行う必要があります。 

  • 文字列の変数や定数を宣言する

  • 文字列の内容を操作する（一部の文字列の削除、複数の文字列の連結など）

  • PL/SQLプログラムとデータベース表の間で文字列データを移動させる

この記事では、PL/SQLプログラムで文字列の操作を始める際に必要となる情報を提供します。

文字列とは

文字列は文字データとも呼ばれ、ある特定の文字セットから選択した記号のシーケンスを表します。言いかえれば、文字列内の記号は、"A"や"B"などの英字で構成されることもあれば、"字串"などの中国語の文字で構成されることもあります。

PL/SQLには次の3種類の文字列があります。

固定長文字列：固定長文字列の右側は、宣言内で指定した長さまで空白でパディングされます。パディングの実際の様子については、"文字列変数の宣言"を参照してください。

可変長文字列：可変長文字列では最大長を指定しますが（最大長は32,767未満とする必要があります）、パディングは行われません。

キャラクタ・ラージ・オブジェクト（CLOB）：CLOBは最大128テラバイトを格納できる可変長文字列です。

文字列はリテラルまたは変数です。文字列リテラルは、一重引用符で囲みます。 


  
'This is a string literal'

文字列リテラルの内部に一重引用符を埋め込む必要がある場合は、2つの一重引用符を並べることで入力できます。 


  
'This isn''t a date'

また、文字"q"を使用して、代替的なリテラル終端文字を示すこともできます。


  
q'[This isn't a date]'

文字列変数は、文字列データ型を使用して宣言した後に値を代入した識別子です（値としてリテラルまたは式を使用できます）。

文字列変数の宣言

PL/SQLプログラムで文字列を操作するには、文字列値を格納するための変数を宣言します。文字列変数を宣言するには、Oracle Databaseが提供する多数の文字列データ型（CHAR、NCHAR、VARCHAR2、NVARCHAR2、CLOB、NCLOBなど）の1つを選択する必要があります。接頭辞"N"の付いたデータ型は、"各国語キャラクタ・セット"のデータ型です。これは、Unicode文字データを格納するために使用するデータ型であることを示します（Unicodeは汎用の文字コード体系です。これによって、単一のキャラクタ・セットを使用して、任意の言語情報を保存できます）。

可変長文字列を宣言するには、その文字列の最大長を指定する必要があります。次のコードでは、VARCHAR2データ型を使用して、会社名を格納する変数を宣言しています。この変数には（この宣言においては）100文字を超える文字列を格納できません。 


  
DECLARE   
		l_company_name VARCHAR2(100);

最大長はかならず指定する必要があります。最大長を指定していない場合、Oracle Databaseで次のようなコンパイル・エラーが発生します。 


  
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)

固定長文字列を宣言するには、CHARデータ型を使用します。 


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

CHARを使用する場合は（VARCHAR2とは異なり）、固定長変数に最大長を指定する必要はありません。長さの制約を指定しない場合は、最大長1が自動的に使用されます。つまり、次の2つの宣言は同じものです。 


  
DECLARE    
		l_yes_or_no1 CHAR(1) := 'Y';  
		l_yes_or_no2 CHAR := 'Y';

長さが2以上のCHAR変数を宣言した場合は、その変数にどのような値を代入しても、指定した最大長まで自動的に空白でパディングされます。

最後に、キャラクタ・ラージ・オブジェクトを宣言するには、CLOBデータ型を使用します。この場合、最大長は指定しません。文字列の長さは、データベース・ブロック・サイズに基づいて、Oracle Databaseにより自動的に判定されます。次に例を示します。 


  
DECLARE     
		l_lots_of_text CLOB;

では、プログラムで使用するデータ型をどのように決めれば良いでしょうか。次にいくつかのガイドラインを挙げます。 

  • 文字列の文字数が32,767を超える可能性がある場合は、CLOB（またはNCLOB）データ型を使用する。

  • 文字列に代入する値がかならず固定長となる場合は、CHAR（またはNCHAR）を使用する。たとえば、米国社会保障番号の場合、文字列が常に同じ形式と長さ（NNN-NN-NNNN）になる。

  • それ以外（したがって大部分）の場合は、VARCHAR2データ型（Unicodeデータを扱う場合はNVACHAR2）を使用する。

かならず固定長となる文字列以外のデータにCHARデータ型を使用すると、予期せず望まれない結果となる可能性があります。次のブロックについて考えてみます。このブロックには、可変長文字列と固定長文字列の両方が含まれています。 


  
DECLARE
  l_variable VARCHAR2 (10) := 'Logic';
  l_fixed    CHAR (10) := 'Logic';
BEGIN
  IF l_variable = l_fixed
  THEN
   DBMS_OUTPUT.put_line ('Equal');
  ELSE
   DBMS_OUTPUT.put_line ('Not Equal');
  END IF;
END;

一見すると、実行後に"Equal"という語が表示されるだろうと思えますが、これは間違いです。実際には"Not Equal"と表示されます。これは、l_fixedの値は長さが10になるまで空白でパディングされているためです。次のブロック例のパディングについてはどうでしょうか。このブロックでは"Not Equal"と表示されると予想できるでしょう。


  
BEGIN
  IF 'Logic' = 'Logic     '
  THEN
    DBMS_OUTPUT.put_line ('Equal');
  ELSE
    DBMS_OUTPUT.put_line ('Not Equal');
  END IF;
END;

したがって、CHARデータ型は、変数の型、データベース列、パラメータのいずれで使用する場合でも、慎重になる必要があります。

変数の宣言後は、値の代入、値の変更、その変数に格納された文字列に対する操作などを実行できます。文字列の操作には、文字列ファンクションや文字列演算子を使用します。

これ以降は、VARCHAR2データ型に焦点を当てて説明します。

文字列の組込みファンクションの使用

変数に文字列を代入した後、通常はその文字列の内容の分析、何らかの方法での値の変更、他の文字列との連結などの操作を行う必要があります。Oracle Databaseは、そのようなすべての要件に対応する各種の組込みファンクションを提供しています。これらのファンクションのうち、よく使用されるものについて見ていきましょう。

複数の文字列の連結：頻繁に必要となる基本的な文字列操作の1つが、複数の文字列を組み合わせる（連結する）ことです。PL/SQLには次の2つの文字列連結方法があります。 

  • CONCAT組込みファンクション

  • ||（連結）演算子

 CONCATファンクションは2つの文字列を引数として受け取り、それらの2つの文字列を"接着して"返します。一方、連結演算子も2つの文字列を連結しますが、次の例から分かるように、3つ以上の文字列を組み合わせる場合に、より簡単に使用できます。 


  
DECLARE
  l_first  VARCHAR2 (10) := 'Steven';
  l_middle VARCHAR2 (5) := 'Eric';
  l_last   VARCHAR2 (20)
              := 'Feuerstein';
BEGIN
  /* Use the CONCAT function */
  DBMS_OUTPUT.put_line (
     CONCAT ('Steven', 'Feuerstein'));
  /* Use the || operator */
  DBMS_OUTPUT.put_line (
      l_first
      || ' '
      || l_middle
      || ' '
      || l_last);
END;
/

このブロックの出力は次のとおりです。 


  
StevenFeuerstein  
Steven Eric Feuerstein

  私の経験では、CONCATファンクションを見る機会はあまりありませんが、||演算子はPL/SQL開発者により、ほぼ全般的に使用されています。

CONCATまたは||に渡された文字列のいずれかがNULLか‘’（長さがゼロの文字列）の場合、単純にNULLを除く文字列が返されます。両方の文字列がNULLの場合は、NULLが返されます。

文字列の大文字小文字の変更：文字列の大文字小文字を変更するために、次の3つの組込みファンクションを使用できます。 

  • UPPER：すべての文字を大文字に変更する。

  • LOWER：すべての文字を小文字に変更する。

  • INITCAP：それぞれの語の1文字目を大文字に変更する（文字列は空白またはアルファベット以外の文字によって区切られる）。

 リスト1に、これらの大文字小文字変更ファンクションの使用例を示します。

コード・リスト1：大文字小文字変更ファンクションの例 


  
SQL> DECLARE
  2     l_company_name   VARCHAR2 (25) := 'oraCLE corporatION';
  3  BEGIN
  4     DBMS_OUTPUT.put_line (UPPER (l_company_name));
  5     DBMS_OUTPUT.put_line (LOWER (l_company_name));
  6     DBMS_OUTPUT.put_line (INITCAP (l_company_name));
  7  END;
  8  /

ORACLE CORPORATION
oracle corporation
Oracle Corporation

文字列の一部分の抽出：よく使用される文字列用の組込みファンクションとして、SUBSTRがあります。SUBSTRは、ある文字列から部分文字列を抽出するために使用します。SUBSTRをコールするときには、文字列、求める部分文字列の開始位置、部分文字列の文字数を指定します。

リスト2に、SUBSTRファンクションの使用例を示します。

コード・リスト2：SUBSTRファンクションの例 


  
DECLARE
   l_company_name   VARCHAR2 (6) := 'Oracle';
BEGIN
   /* Retrieve the first character in the string */
   DBMS_OUTPUT.put_line (
              SUBSTR (l_company_name, 1, 1));
   /* Retrieve the last character in the string */
   DBMS_OUTPUT.put_line (
              SUBSTR (l_company_name, -1, 1));
   /* Retrieve three characters, 
            starting from the second position.*/
   DBMS_OUTPUT.put_line (
              SUBSTR (l_company_name, 2, 3));
   /* Retrieve the remainder of the string,
            starting from the second position.*/
   DBMS_OUTPUT.put_line (
              SUBSTR (l_company_name, 2));
END;
/

このブロックの出力は次のとおりです。 


  
O
e
rac
racle

この例から分かるように、SUBSTRファンクションでは、部分文字列の負の開始位置を指定できます。この場合、Oracle Databaseでは文字列の末尾から先頭に向かってカウントされます。第3引数（部分文字列の文字数）を指定しない場合は、指定した位置以降のすべての文字列が自動的に返されます。

別の文字列内の文字列の検索：ある文字列が別の文字列内のどの位置にあるか（およびある文字列が別の文字列内に存在するか）を判定するには、INSTRファンクションを使用します。INSTRは4つの引数を受け取ります。 

  • 検索対象の文字列（必須）。

  • 目的の部分文字列（必須）。

  • 検索の開始位置（オプション）。これが負の値の場合は、文字列の末尾からカウントされる。値を指定しない場合は、文字列の先頭から検索される（つまり、開始位置が1になる）。

  • N個目に出現する部分文字列を検索すること（オプション）。値を指定しない場合は、最初に出現する部分文字列が検索される。

 リスト3に、INSTRファンクションの使用例を示します。

コード・リスト3：INSTRファンクションの例 


  
BEGIN
   /* Find the location of the first "e" */
   DBMS_OUTPUT.put_line (
      INSTR ('steven feuerstein', 'e'));
      
   /* Find the location of the first "e" starting from position 6 */ 
   DBMS_OUTPUT.put_line (
      INSTR ('steven feuerstein'
           , 'e'
           , 6));
           
   /* Find the location of the first "e" starting from the 6th position from
      the end of string and counting to the left.*/
   DBMS_OUTPUT.put_line (
      INSTR ('steven feuerstein'
           , 'e'
           , -6));
           
   /* Find the location of the 3rd "e" starting from the 6th position from
      the end of string.*/
   DBMS_OUTPUT.put_line (
      INSTR ('steven feuerstein'
           , 'e'
           , -6
           , 3));
END;
/

このブロックの出力は次のとおりです。 


  
3
9
11 
5

INSTRは非常に柔軟で便利なユーティリティです。ある部分文字列が文字列内に存在するかを判定するために簡単に使用できます。この判定を行い、ブールを返すファンクションを次に示します。 


  
CREATE OR REPLACE FUNCTION 
is_in_string (
  string_in      IN VARCHAR2
 ,substring_in   IN VARCHAR2)
  RETURN BOOLEAN
IS
BEGIN
  RETURN INSTR (string_in
              , substring_in) > 0;
END is_in_string;
/

文字列を空白（またはその他の文字）でパディング：CHARデータ型の使用についてすでに警告したとおり、Oracle Databaseでは文字列値は宣言内に指定した最大長まで空白でパディングされます。

一方、おもにレポート生成の際に、文字列の先頭より前か末尾の後に、空白（またはその他の文字）を配置したい場合もあります。そのような状況に対応するため、Oracle DatabaseではLPADとRPADを提供しています。

これらのファンクションをコールするときには、文字列をパディングする長さと、パディングに使用する文字または文字列を指定します。パディング文字列を指定しない場合のデフォルトは、空白を使用したパディングです。

リスト4に、LPADおよびRPADパディング・ファンクションの使用例を示します。

コード・リスト4：パディング・ファンクションの例 


  
DECLARE
   l_first   VARCHAR2 (10) := 'Steven';
   l_last    VARCHAR2 (20) := 'Feuerstein';
   l_phone   VARCHAR2 (20) := '773-426-9093';
BEGIN
   /* Indent the subheader by 3 characters */
   DBMS_OUTPUT.put_line ('Header');
   DBMS_OUTPUT.put_line (
      LPAD ('Sub-header', 13, '.'));

   /* Add "123" to the end of the string, until the 20 character is reached.*/
   DBMS_OUTPUT.put_line (
      RPAD ('abc', 20, '123'));

   /* Display headers and then values to fit within the columns.*/
   DBMS_OUTPUT.put_line (
     /*1234567890x12345678901234567890x*/
      'First Name Last Name            Phone');
   DBMS_OUTPUT.put_line (
         RPAD (l_first, 10)
      || ' '
      || RPAD (l_last, 20)
      || ' '
      || l_phone);
END;

このブロックの出力は次のとおりです。 


  
Header
...Sub-header
abc12312312312312312
First Name Last Name            Phone
Steven     Feuerstein           773-426-9093

文字列内の文字の置換：Oracle Databaseでは、文字列内の1文字以上の文字列を選択的に変更するための多数のファンクションを提供しています。たとえば、テキストがブラウザで適切に表示されるように、文字列内のすべての空白をHTMLの空白（" "）に置換する必要があります。このようなニーズに対応する2つのファンクションがあります。 

  • REPLACE：文字のセットまたはパターンを別のセットに置換する。

  • TRANSLATE：個々の文字を変換または置換する。

リスト5に、これら2つの文字置換用組込みファンクションの例を示します。1文字を置換する場合は、REPLACEとTRANSLATEの結果は同じです。一方、複数の文字を置換する場合は、REPLACEとTRANSLATEの動作は異なります。この例では、REPLACEをコールすると、"abc"が"123"に置換されます。しかし、文字列内にある、このパターン（"abc"）に当てはまらない個々の文字（a、b、c）はいずれも置換されません。

コード・リスト5：文字置換ファンクションの例 


  
DECLARE
   l_name   VARCHAR2 (50) := 'Steven Feuerstein';
BEGIN
   /* Replace all e's with the number 2.Since you are replacing a single 
      character, you can use either REPLACE or TRANSLATE.*/
   DBMS_OUTPUT.put_line (
      REPLACE (l_name, 'e', '2'));
   DBMS_OUTPUT.put_line (
      TRANSLATE (l_name, 'e', '2'));

   /* Replace all instances of "abc" with "123" */
   DBMS_OUTPUT.put_line (
      REPLACE ('abc-a-b-c-abc'
             , 'abc'
             , '123'));
   /* Replace "a" with "1", "b" with "2", "c" with "3".*/ 
   DBMS_OUTPUT.put_line (
      TRANSLATE ('abc-a-b-c-abc'
               , 'abc'
               , '123'));
END;
/

このブロックの出力は次のとおりです。 


  
St2v2n F2u2rst2in
St2v2n F2u2rst2in
123-a-b-c-123
123-1-2-3-123

一方、TRANSLATEをコールすると、個々の文字のそれぞれが、同じ位置のまま第3引数の文字に置換されます。

一般的に、文字列パターンを置換する必要がある場合は常にREPLACEを使用してください。一方、TRANSLATEは、文字列内の各文字を置換する必要がある場合に適しています。

文字列からの文字の削除：LPADとRPADは与え、TRIM、LTRIM、RTRIMは取り除きます。これらのトリム・ファンクションは、文字列の先頭（左）または末尾（右）から文字列を削除するために使用します。リスト6に、RTRIMとLTRIMの両方の例を示します。

コード・リスト6：LTRIMファンクションとRTRIMファンクションの例 


  
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;

このブロックの出力は次のとおりです。 


  
This sentence has too many periods 
		1   

この例では、RTRIMによりすべてのピリオドが削除されます。第2引数でトリムする文字（または文字列）をピリオドとして指定しているためです。また、LTRIMのコールの例は、複数の文字をトリムするように指定できることを示しています。この例では、文字列bの先頭からすべての英字と空白をトリムするように指定し、この指定どおりの出力が得られます。

RTRIMでもLTRIMでも、デフォルトの動作は、文字列の先頭または末尾から空白をトリムすることです。RTRIM(a)と指定することは、RTRIM(a,’ ‘)と指定することと同じです。LTRIM(a)とLTRIM(a,’ ‘)にも同じことが言えます。

その他のトリム・ファンクションとして、純粋なTRIMがあります。次のブロックから分かるように、TRIMの動作はLTRIMやRTRIMとは少し異なります。 


  
DECLARE
   x   VARCHAR2 (30)
          := '.....Hi there!.....';
BEGIN
   DBMS_OUTPUT.put_line (
      TRIM (LEADING '.'FROM x)); 
   DBMS_OUTPUT.put_line (
      TRIM (TRAILING '.'FROM x));
   DBMS_OUTPUT.put_line (
      TRIM (BOTH '.'FROM x));

   --The default is to trim 
   --from both sides
   DBMS_OUTPUT.put_line (
      TRIM ('.'FROM x));

   --The default trim character 
   --is the space:
   DBMS_OUTPUT.put_line (TRIM (x));
END;

  このブロックの出力は次のとおりです。 


  
 Hi there!.....
    .....Hi there!
    Hi there!
    Hi there!
    .....Hi there!.....

  TRIMを使用すれば、文字列の片側から、もしくは両側からトリムできます。ただし、削除する文字として指定できるのは1文字のみです。たとえば、次のように記述することはできません。 


  
TRIM(BOTH ',.;' FROM x)

  複数の文字を文字列の先頭と末尾の両方から削除する必要がある場合は、RTRIMとLTRIMの両方を使用する必要があります。 


  
RTRIM(LTRIM(x,',.;'),',.;')

  また、TRANSLATEを使用して、文字をNULLに置換する、つまりNULLに"変換（translate）"することで、文字列から特定の文字を削除することもできます。ただし、この置換の指定方法については気をつけてください。たとえば、文字列からすべての数字（0～9）を削除するとします。そこで、まず試しに、次のようなブロックを記述します。 


  
BEGIN
   /* Remove all digits (0-9) 
      from the string.*/
   DBMS_OUTPUT.put_line (
      TRANSLATE ('S1t2e3v4e56n'
               , '1234567890'
               , ''));
END;
/

  しかし、このブロックを実行しても何も表示されません（つまりNULL文字列が表示されます）。これは、TRANSLATEに渡す引数のいずれかがNULL（または長さがゼロの文字列）の場合、このファンクションはNULL値を返すためです。

そのため、3つの引数すべてをNULL以外にする必要があります。つまり、第2引数と第3引数の先頭に、次のように自身に置換されるだけの文字を指定する必要があります。 


  
BEGIN
   /* Remove all digits (0-9) 
      from the string.*/
   DBMS_OUTPUT.put_line (
      TRANSLATE ('S1t2e3v4e56n'
               , 'A1234567890'
               , 'A'));
END;
/

  これで、"A"が"A"に置換され、文字列内のその他の文字はNULLに置換されるため、"Steven"という文字列が表示されるようになります。

役に立つ知識

PL/SQLの文字列の基本的なプロパティや組込みファンクションを把握する他に、長い文字列および文字列の最大サイズに関して次のポイントを覚えておくと役に立ちます。

文字列が長すぎる場合：VARCHAR2型の変数を宣言する際には、最大長を指定する必要があります。ここで、最大長よりも長い値をその変数に代入しようとするとどうなるでしょうか。その場合、Oracle DatabaseでORA-06502エラーが発生します。このエラーは、PL/SQLでもVALUE_ERROR例外として定義されています。

例外が発生し、未処理のままブロックの外部に伝播される例を次に示します。 


  
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

  この同じブロックを、VALUE_ERROR例外を捕捉するように書き直すと、次のようになります。 


  
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!

次のステップ

詳細情報 PL/SQLのデータ型 CHARとVARCHAR2の違い 組込みファンクション

 ダウンロード Oracle Database 11g

 テスト  PL/SQLの知識 >

その他の記事 PL/SQLの基礎、パート1とパート2  

興味深いことに、データベース表のVARCHAR2列に値を挿入するか値を更新しようとすると、Oracle Databaseでは異なるエラーが発生します。このエラーは次の例で確認できます。 


  
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

異なる最大サイズ：SQLとPL/SQLでは、文字列データ型の最大サイズが異なる場合が多くあります。PL/SQLでのVARCHAR2の最大サイズは32,767バイトですが、SQLでの最大サイズは4,000バイトです。また、PL/SQLでのCHARの最大サイズは32,767バイトですが、SQLでの最大サイズは2,000バイトです。

そのため、VARCHAR2変数の値を表の列に保存する必要がある場合に、ORA-12899エラーが発生することがあります。このエラーが発生した場合の対策は次の2つです。 

  • SUBSTRを使用して大きな文字列から4,000バイトの文字列を抽出し、その部分文字列を表に保存する。このオプションには、データの一部が消失するという明らかな欠点がある。

  • 列のデータ型をVARCHAR2からCLOBに変更する。この方法では、すべてのデータを保存できる。

PL/SQLでのCLOBの最大サイズは128テラバイトですが、SQLでの最大サイズは(4GB - 1) * DB_BLOCK_SIZEのみです。

文字列だけがデータにあらず

文字データはPL/SQLアプリケーションで非常に大きな役割を担いますが、それらの同じアプリケーションで、その他のデータ型、特に数値や日付を使用することは間違いありません。数値や日付のデータ型については、次のPL/SQLの基礎の記事で取り上げます。 

Steven Feuerstein（steven.feuerstein@quest.com）は、Quest SoftwareのPL/SQLエヴァンジェリストです。これまで、Oracle PL/SQLに関する著書（O’Reilly Media）を10冊発行しており、Oracle ACE Directorでもあります。詳細は、stevenfeuerstein.comをご覧ください。

 ご意見ご感想をお寄せください。