15.10 - Example: Splitting a CLOB Value in Half and Inserting the Pieces into Two Different Tables - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

The following example shows an SQL procedure used to split a CLOB in half and then insert the halves into two separate tables.

First the table definitions:

    CREATE TABLE tabc1 (
      a1 INTEGER, 
      b1 CLOB);
    CREATE TABLE tabc2 (
      a2 INTEGER, 
      b2 CLOB);

The following SQL code defines the procedure:

    CREATE PROCEDURE clobsplitter(IN    a1 INTEGER, 
                                  IN    a2 INTEGER, 
                                  INOUT b  CLOB )
          BEGIN
             DECLARE localclob CLOB;
             DECLARE len INTEGER;
             SET len = CHARACTERS(b);
             SET localclob = SUBSTR(b, 1, len/2);
             INSERT tabc1 (:a1, localclob);
             INSERT tabc2 (:a2, SUBSTR(b, len/2 + 1));
             SET b = localclob || SUBSTR(b, len/2 + 1);
          END;

The stages in the process followed by this procedure are as follows:

  1. The first half of CLOB b is assigned to local variable named localclob.
  2. The contents of CLOB are inserted into table tabc1.
  3. The second half of CLOB b is inserted into table tabc2 without using a local LOB variable.
  4. Local variable localclob is concatenated with the second half of b and assigned to b.
  5. The contents of b are returned to the application.

    If clobsplitter is called by another procedure, then b is passed to the other procedure.