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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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.