17.00 - Example: Splitting a CLOB Value in Half and Inserting the Pieces into Two Different Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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.