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

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.