The three methods available to access LOBs in Pro*C/C++ are:

• The DBMS_LOB package inside PL/SQL blocks.

• OCI (Oracle Call Interface) function calls.

• Embedded SQL statements.

The SQL statements are designed to give users a functional equivalent to the PL/SQL interface while avoiding the complexity of the OCI interface.

The following table compares LOB access by OCI function calls in Pro*C/C++, PL/SQL, and embedded SQL statements in Pro*C/C++. Empty boxes indicate missing functionality.

Purpose

This statement appends a LOB value at the end of another LOB.

Syntax

EXEC SQL [AT [:]database] LOB APPEND :src TO :dst ;

Host Variables

src (IN)

An internal LOB locator uniquely referencing the source LOB.

dst (IN OUT)

An internal LOB locator uniquely referencing the destination LOB.

Usage Notes

The data is copied from the source LOB to the end of the destination LOB, extending the destination LOB up to a maximum of 4 Gigabytes. If the LOB is extended beyond 4 Gigabytes, an error will occur.

The source and destination LOBs must already exist and the destination LOB must be initialized.

Both the source and destination LOBs must be of the same internal LOB type. It is an error to have enabled LOB buffering for either type of locator.

Purpose

Assigns a LOB or BFILE locator to another.

Syntax

EXEC SQL [AT [:]database] LOB ASSIGN :src to :dst ;

Host Variables

src (IN)

LOB or BFILE locator source copied from.

dst (IN OUT)

LOB or BFILE locator copied to.

Usage Notes

After the assignment, both locators refer to the same LOB value. The destination LOB locator must be a valid initialized (ALLOCATEd) locator.

For internal LOBs, the source locator's LOB value is copied to the destination locator's LOB value only when the destination locator is stored in the table. For Pro*C/C++, issuing a FLUSH of an object containing the destination locator will copy the LOB value.

An error is returned when a BFILE locator is assigned to an internal LOB locator and vice-versa. It is also an error if the src and dst LOBs are not of the same type.

If the source locator is for an internal LOB that was enabled for buffering, and the source locator has been used to modify the LOB value through the LOB Buffering Subsystem, and the buffers have not been FLUSHed since the WRITE, then the source locator cannot be assigned to the destination locator. This is because only one locator for each LOB can modify the LOB value through the LOB Buffering Subsystem.

Purpose

Close an open LOB or BFILE.

Syntax

EXEC SQL [AT [:]database] LOB CLOSE :src ;

Host Variables

src (IN OUT)

The locator of the LOB or BFILE to be closed.

Usage Notes

It is an error to close the same LOB twice either with different locators or with the same locator. For external LOBs, no error is produced if the BFILE exists but has not been opened.

It is an error to COMMIT a transaction before closing all previously opened LOBs. At transaction ROLLBACK time, all LOBs that are still open will be discarded without first being closed.

Purpose

Copy all or part of a LOB value into a second LOB.

Syntax

EXEC SQL [AT [:]database] LOB COPY :amt FROM :src [AT :src_offset]
   TO :dst [AT :dst_offset] ;

Host Variables

amt (IN)

The maximum number of bytes for BLOBs, or characters for CLOBs and NCLOBs, to copy.

src (IN)

The locator of the source LOB.

src_offset (IN)

This is the number of characters for CLOB or NCLOB, and the number of bytes for a BLOB, starting from 1 at the beginning of the LOB.

dst (IN)

The locator of the destination LOB.

dst_offset (IN)

The destination offset. Same rules as for src_offset.

Usage Notes

If the data already exists at the destination's offset and beyond, it is overwritten with the source data. If the destination's offset is beyond the end of the current data, zero-byte fillers (BLOBs) or spaces (CLOBs) are written into the destination LOB from the end of the current data to the beginning of the newly written data from the source.

The destination LOB is extended to accommodate the newly written data if it extends beyond the current length of the destination LOB. It is a runtime error to extend this LOB beyond 4 Gigabytes.

It is also an error to try to copy from a LOB that is not initialized.

Both the source and destination LOBs must be of the same type. LOB buffering must not be enabled for either locator.

The amt variable indicates the maximum amount to copy. If the end of the source LOB is reached before the specified amount is copied, the operation terminates with ORA-22993 error.

To make a temporary LOB permanent, the COPY statement must be used to explicitly COPY the temporary LOB into a permanent one.

Purpose

Creates a temporary LOB.

Syntax

EXEC SQL [AT [:]database] LOB CREATE TEMPORARY :src ;

Host Variables

src (IN OUT)

Before execution, when IN, src is a LOB locator previously ALLOCATEd.

After execution, when OUT, src is a LOB locator that will point to a new empty temporary LOB.

Usage Notes

After successful execution, the locator points to a newly created temporary LOB that resides on the database server independent of a table. The temporary LOB is empty and has zero length.

At the end of a session, all temporary LOBs are freed. READs and WRITEs to temporary LOBs never go through the buffer cache.

Purpose

Disables LOB buffering for the LOB locator.

Syntax

EXEC SQL [AT [:]database] LOB DISABLE BUFFERING :src ;

Host Variable

src (IN OUT)

An internal LOB locator.

Usage Notes

This statement does not support BFILEs. Subsequent reads or writes will not be done through the LBS.

Purpose

Enables LOB buffering for the LOB locator.

Syntax

EXEC SQL [AT [:]database] LOB ENABLE BUFFERING :src ;

Host Variable

src (IN OUT)

An internal LOB locator.

Usage Notes

This statement does not support BFILEs. Subsequent reads and writes are done through the LBS.

Purpose

Erases a given amount of LOB data starting from a given offset.

Syntax

EXEC SQL [AT [:]database] LOB ERASE :amt FROM :src [AT :src_offset] ;

Host Variables

amt (IN OUT)

The input is the number of bytes or characters to erase. The returned output is the actual number erased.

src (IN OUT)

An internal LOB locator.

src_offset (IN)

The offset from the beginning of the LOB, starting from 1.

Usage Notes

This statement does not support BFILEs.

After execution, amt returns the actual number of characters/bytes that were erased. The actual number and requested number will differ if the end of the LOB value is reached before erasing the requested number of characters/bytes. If the LOB is empty, amt will indicate that 0 characters/bytes were erased.

For BLOBs, erasing means zero-byte fillers overwrite the existing LOB value. For CLOBs, erasing means that spaces overwrite the existing LOB value.

Purpose

Closes all BFILES opened in the current session.

Syntax

EXEC SQL [AT [:]database] LOB FILE CLOSE ALL ;

Usage Notes

If there are any open files in the session whose closure has not been handled properly, you can use the FILE CLOSE ALL statement to close all files opened in the session, and resume file operations from the beginning.

Purpose

Sets DIRECTORY alias and FILENAME in a BFILE locator.

Syntax

EXEC SQL [AT [:]database] LOB FILE SET :file
    DIRECTORY = :alias, FILENAME = :filename ;

Host Variables

file (IN OUT)

BFILE locator where the DIRECTORY alias and FILENAME is set.

alias (IN)

DIRECTORY alias name to set.

filename (IN)

The FILENAME to set.

Usage Notes

The given BFILE locator must be first ALLOCATEd prior to its use in this statement.

Both the DIRECTORY alias name and FILENAME must be provided.

The maximum length of the DIRECTORY alias is 128 bytes. The maximum length of the FILENAME is 255 bytes.

The only external datatypes supported for use with the DIRECTORY alias name and FILENAME attributes are CHARZ, STRING, VARCHAR, VARCHAR2 and CHARF.

It is an error to use this statement with anything but an external LOB locator.

Purpose

Writes this LOB's buffers to the database server.

Syntax

EXEC SQL [AT [:]database] LOB FLUSH BUFFER :src [FREE] ;

Host Variables

src (IN OUT)

Internal LOB locator.

Usage Notes

Writes the buffer data to the database LOB in the server from the LOB referenced by the input locator.

LOB buffering must have already been enabled for the input LOB locator.

The FLUSH operation, by default, does not free the buffer resources for reallocation to another buffered LOB operation. However, if you want to free the buffer explicitly, you can include the optional FREE keyword to so indicate.

Purpose

Free the temporary space for the LOB locator.

Syntax

EXEC SQL [AT [:]database] LOB FREE TEMPORARY :src ;

Host Variable

src (IN OUT)

The LOB locator pointing to the temporary LOB.

Usage Notes

The input locator must point to a temporary LOB. The output locator is marked not initialized and can be used in subsequent LOB statements.

Purpose

Copy all or a part of a BFILE into an internal LOB.

Syntax

EXEC SQL [AT [:]database] LOB LOAD :amt FROM FILE :file [AT :src_offset] INTO
    :dst [AT :dst_offset] ;

Host Variables

amt (IN)

Maximum number of bytes to be loaded.

file (IN OUT)

The source BFILE locator.

src_offset (IN)

The number of bytes offset from the beginning of the file, starting from 1.

dst (IN OUT)

The destination LOB locator which can be BLOB, CLOB, be NCLOB.

dst_offset (IN)

The number of bytes (for BLOBs) or characters (CLOBs and NCLOBs) from the beginning of the destination LOB where writing will begin. It starts at 1.

Usage Notes

The data is copied from the source BFILE to the destination internal LOB. No character set conversions are performed when copying the BFILE data to a CLOB or NCLOB. Therefore, the BFILE data must already be in the same character set as the CLOB or NCLOB in the database.

The source and destination LOBs must already exist. If the data already exists at the destination's start position, it is overwritten with the source data. If the destination's start position is beyond the end of the current data, zero-byte fillers (BLOBs) or spaces (CLOBs and NCLOBs) are written into the destination LOB. The fillers are written to the destination LOB from the end of the data to the beginning of the newly written data from the source.

The destination LOB is extended to accommodate the newly written data if it extends beyond the current length of the destination LOB. It is an error to extend this LOB beyond 4 Gigabytes.

It is also an error to copy from a BFILE that is not initialized.

The amount parameter indicates the maximum amount to load. If the end of the source BFILE is reached before the specified amount is loaded, the operation terminates with ORA-22993 error.

Purpose

Open a LOB or BFILE for read or read/write access.

Syntax

EXEC SQL [AT [:]database] LOB OPEN :src [ READ ONLY | READ WRITE ] ;

Host Variables

src (IN OUT)

LOB locator of the LOB or BFILE.

Usage Notes

The default mode in which a LOB or BFILE can be OPENed is for READ ONLY access.

For internal LOBs, being OPEN is associated with the LOB, not the locator. Assigning an already OPENed locator to another locator does not count as OPENing a new LOB. Instead, both locators refer to the same LOB. For BFILEs, being OPEN is associated with the locator.

Only 32 LOBs can be OPEN at any one time. An error will be returned when the 33rd LOB is OPENed.

There is no support for writable BFILEs. Therefore, when you OPEN a BFILE in READ WRITE mode, an error is returned.

It is also an error to open a LOB in READ ONLY mode and then attempt to WRITE to the LOB.

Purpose

Reads all or part of a LOB or BFILE into a buffer.

Syntax

EXEC SQL [AT [:]database] LOB READ :amt FROM :src [AT :src_offset]
   INTO :buffer [WITH LENGTH :buflen] ;

Host Variables

amt (IN OUT)

The input is the number of characters or bytes to be read. The output is the actual number of characters or bytes that were read.

If the amount of bytes to be read is larger than the buffer length it is assumed that the LOB is being READ in a polling mode. On input if this value is 0, then the data will be read in a polling mode from the input offset until the end of the LOB.

The number of bytes or characters actually read is returned in amt. If the data is read in pieces, amt will always contain the length of the last piece read.

When the end of a LOB is reached an ORA-1403: no data found error will be issued.

When reading in a polling mode, the application must invoke the LOB READ repeatedly to read more pieces of the LOB until no more data is left. Control the use of the polling mode with the NOT FOUND condition in a WHENEVER directive to catch the ORA-1403 error.

src (IN)

The LOB or BFILE locator.

src_offset (IN)

This is the absolute offset from the beginning of the LOB value from which to start reading. For character LOBs it is the number of characters from the beginning of the LOB. For binary LOBs or BFILEs it is the number of bytes. The first position is 1.

buffer (IN/OUT)

A buffer into which the LOB data will be read. The external datatype of the buffer is restricted to only a few types depending on the type of the source LOB. The maximum length of the buffer depends on the external datatype being used to store the LOB value. The following table summarizes the legal external datatypes and their corresponding maximum lengths categorized by source LOB type:

buflen (IN)

Specifies the length of the given buffer when it cannot be determined otherwise.

Usage Notes

A BFILE must already exist on the database server and it must have been opened using the input locator. The database must have permission to read the file and you, the user, must have read permission on the directory.

It is an error to try to read from an un-initialized LOB or BFILE.

The length of the buffer is determined this way:

• From buflen, when the WITH LENGTH clause is present.

• In the absence of the WITH LENGTH clause, the length is determined by treating the buffer host variable in OUT mode.

Purpose

Truncates the LOB value.

Syntax

EXEC SQL [AT [:]database] LOB TRIM :src TO :newlen ;

Host Variables

src (IN OUT)

LOB locator for internal LOB.

newlen (IN)

The new length of the LOB value.

Usage Notes

This statement is not for BFILES. The new length cannot be greater than the current length, or an error is returned.

Purpose

Writes the contents of a buffer to a LOB.

Syntax

EXEC SQL [AT [:]database] LOB WRITE [APPEND] [ FIRST | NEXT | LAST | ONE ]
    :amt FROM :buffer [WITH LENGTH :buflen] INTO :dst [AT :dst_offset] ;

Host Variables

amt (IN OUT)

The input is the number of characters or bytes to be written.

The output is the actual number of characters or bytes that is written.

When writing using a polling method, amt will return the cumulative total length written for the execution of the WRITE statement after a WRITE LAST is executed. If the WRITE statement is interrupted, amt will be undefined.

buffer (IN)

A buffer from which the LOB data is written.

dst (IN OUT)

The LOB locator.

dst_offset (IN)

The offset from the beginning of the LOB (counting from 1), in characters for CLOBs and NCLOBs, in bytes for BLOBs.

buflen (IN)

The buffer length when it cannot be calculated in any other way.

Usage Notes

If LOB data already exists, it is overwritten with the data stored in the buffer. If the offset specified is beyond the end of the data currently in the LOB, zero-byte fillers or spaces are inserted into the LOB.

Specifying the keyword APPEND in the WRITE statement causes the data to automatically be written to the end of the LOB. When APPEND is specified, the destination offset is assumed to be the end of the LOB. It is an error to specify the destination offset when using the APPEND option in the WRITE statement.

The buffer can be written to the LOB in one piece (using the ONE orientation which is the default) or it can be provided piece-wise using a standard polling method.

Polling is begun by using FIRST, then NEXT to write subsequent pieces. The LAST keyword is used to write the final piece that terminates the write.

Using this piece-wise write mode, the buffer and the length can be different in each call if the pieces are of different sizes and from different locations.

If the total amount of data passed to Oracle is less than the amount specified by the amt parameter after doing all the writes, an error results.

The same rules apply for determining the buffer length as in the READ statement.

Purpose

This is a statement that is equivalent to several OCI and PL/SQL statements (which is why it is saved for last). Use the LOB DESCRIBE SQL statement to retrieve attributes from a LOB. This capability is similar to OCI and PL/SQL procedures. The LOB DESCRIBE statement has this format:

Syntax

EXEC SQL [AT [:]database] LOB DESCRIBE :src GET attribute1 [{, attributeN}]
   INTO :hv1 [[INDICATOR] :hv_ind1] [{, :hvN [[INDICATOR] :hv_indN] }] ;

where an attribute can be any of these choices:

CHUNKSIZE | DIRECTORY | FILEEXISTS | FILENAME | ISOPEN | ISTEMPORARY | LENGTH

Host variables

src (IN)

The LOB locator of an internal or external LOB.

hv1 ... hvN ... (OUT)

The host variables that receive the attribute values, in the order specified in the attribute name list.

hv_ind1 ... hv_indN ... (OUT)

Optional host variables that receive the indicator NULL status in the order of the attribute name list.

This table describes the attributes, which LOB it is associated with, and the C types into which they should be read:

Usage Notes

Indicator variables should be declared short. After execution has completed, sqlca.sqlerrd[2] contains the number of attributes retrieved without error. If there was an execution error, the attribute at which it occurred is one more than the contents of sqlca.sqlerrd[2].

DESCRIBE Example

Here is a simple Pro*C/C++ example that extracts the DIRECTORY and FILENAME attributes of a given BFILE:

The oci.h header file is needed for the proper type resolution and compilation of the following OCIBFileLocator declaration:

#include <oci.h>
...
OCIBFileLocator *bfile ;
char directory[31], filename[256] ;
short d_ind, f_ind ;

Finally, select a BFILE locator from some LOB table and perform the DESCRIBE:

EXEC SQL ALLOCATE :bfile ;
EXEC SQL SELECT a_bfile INTO :bfile FROM lob_table WHERE ... ;
EXEC SQL LOB DESCRIBE :bfile
   GET DIRECTORY, FILENAME INTO :directory:d_ind, :filename:f_ind ;

Indicator variables are only valid for use with the DIRECTORY and FILENAME attributes. These attributes are character strings whose values may be truncated if the host variable buffers used to hold their values aren't large enough. When truncation occurs, the value of the indicator will be set to the original length of the attribute.

This program, lobdemo1.pc, illustrates several LOB embedded SQL statements. The source code is in the demo directory. The application uses a table named license_table whose columns are social security number, name, and a CLOB containing text summarizing driving offenses. Several simplified SQL operations of a typical motor vehicle department are modeled.

The possible actions are:

• Add new records.

• List records by social security number.

• List information in a record, given a social security number.

• Append a new traffic violation to an existing CLOB's contents.

/***************************************************************************
  
  SCENARIO: 
  
  We consider the example of a database used to store driver's
  licenses. The licenses are stored as rows of a table containing
  three columns: the sss number of a person, his name in text and the 
  text summary of the info found in his license.

  The sss number is the driver's unique social security number.

  The name is the driver's given name as found on his ID card.

  The text summary is a summary of the information on the driver,
  including his driving record, which can be arbitrarily long and may
  contain comments and data regarding the person's driving ability. 

  APPLICATION OVERVIEW:

  This example demonstrate how a Pro*C client can handle the new LOB
  datatypes through PL/SQL routines. Demonstrated are mechanisms for
  accessing and storing lobs to tables and manipulating LOBs through
  the stored procedures available through the dbms_lob package.

****************************************************************************/

/***************************************************************************

   To run the demo:

   1. Execute the script, lobdemo1c.sql in SQL*Plus
   2. Precompile using Pro*C/C++
        proc lobdemo1 user=scott/tiger sqlcheck=full
   3. Compile/Link (This step is platform specific)

****************************************************************************/

/*** The following will be added to the creation script for this example ***
 *** This code can be found in lobdemo1c.sql				 ***

connect scott/tiger;

set serveroutput on;

Rem Make sure database has no license_table floating around

drop table license_table;

Rem ABSTRACTION:
Rem A license table reduces the notion of a driver's license into three 
Rem distinct components - a unique social security number (sss), 
Rem a name (name), and a text summary of miscellaneous information.

Rem IMPLEMENTATION:
Rem Our implementation follows this abstraction

create table license_table(
  sss char(9),
  name varchar2(50),
  txt_summary clob);

insert into license_table 
	values('971517006', 'Dennis Kernighan', 
	'Wearing a Bright Orange Shirt - 31 Oct 1996');

insert into license_table 
	values('555001212', 'Eight H. Number', 
	'Driving Under the Influence - 1 Jan 1997');

insert into license_table 
 	values('010101010', 'P. Doughboy', 
	'Impersonating An Oracle Employee - 10 Jan 1997');

insert into license_table
 	values('555377012', 'Calvin N. Hobbes', 
	'Driving Under the Influence - 30 Nov 1996');

select count(*) from license_table;

Rem Commit to save
commit;

****************************************************************************/

/**************************
 * Begin lobdemo1.pc code *
 **************************/

#define EX_SUCCESS       0
#define EX_FAILURE       1

#ifndef STDIO
# include <stdio.h>
#endif /* STDIO */

#ifndef SQLCA_ORACLE
# include <sqlca.h>
#endif /* SQLCA_ORACLE */

#ifndef OCI_ORACLE
# include <oci.h>
#endif /* OCI_ORACLE */

#include <time.h>
#include <string.h>
#include <stdlib.h>
#include <ctype.h>

#ifndef LOBDEMO1_ORACLE
# include "lobdemo1.h"
#endif /* LOBDEMO1_ORACLE */

/***********
 * Defines *
 ***********/
#define SSS_LENGTH 12
#define NAME_LENGTH 50 /* corresponds with max length of name in table */
#define BUFLEN 1024
#define MAXCRIME 5
#define DATELENGTH 12

/***********
 * Globals *
 ***********/

char *CrimeList[MAXCRIME]={ "Driving Under the Influence", 
			    "Grand Theft Auto", 
			    "Driving Without a License",
			    "Impersonating an Oracle Employee",
			    "Wearing a Bright Orange Shirt" };

char curdate[DATELENGTH];

/*********************** 
 * Function prototypes *
 ***********************/

#if defined(__STDC__)
  void GetDate( void );
  void PrintSQLError( void );
  void Driver( void );
  void ListRecords( void );
  void PrintCrime( OCIClobLocator *a_clob );
  void GetRecord( void );
  void NewRecord( void );
  char *NewCrime( void );
  void GetName( char *name_holder );
  void AppendToClob( OCIClobLocator *a_clob, char *charbuf );
  void AddCrime( void );
  void ReadClob( OCIClobLocator *a_clob );
  boolean GetSSS( char *suggested_sss );
#else
  void GetDate();
  void PrintSQLError( );
  void Driver( );
  void ListRecords( );
  void PrintCrime(/* OCIClobLocator *a_clob */);
  void GetRecord( );
  void NewRecord( );
  char *NewCrime( );
  void GetName(/* char *name_holder */);
  void AppendToClob(/* OCIClobLocator *a_clob, char *charbuf */);
  void AddCrime();
  boolean GetSSS(/* char *suggested_sss */);
#endif

/* 
 * NAME
 *   GetDate
 * DESCRIPTION
 *   Get date from user
 * LOB FEATURES
 *   none
 */ 

void GetDate()
{
  time_t now;

  now = time(NULL);
  strftime(curdate, 100, " - %d %b %Y", localtime(&now));
}

main()
{
  char * uid = "scott/tiger";

  EXEC SQL WHENEVER SQLERROR DO PrintSQLError();

  printf("Connecting to license database account: %s \n", uid);
  EXEC SQL CONNECT :uid;
  
  GetDate();

  printf("\t*******************************\n");
  printf("\t* Welcome to the DMV Database *\n");
  printf("\t*******************************\n\n");
  printf("Today's Date is%s\n", curdate);

  Driver();

  EXEC SQL COMMIT RELEASE;

  return (EX_SUCCESS);
}

/* 
 * NAME
 *   Driver
 * DESCRIPTION
 *   Command Dispatch Routine
 * LOB FEATURES
 *   none
 */ 

void Driver()
{
  char choice[20];
  boolean done = FALSE;

  while (!done)
  {
    printf("\nLicense Options:\n");
    printf("\t(L)ist available records by SSS number\n");
    printf("\t(G)et information on a particular record\n");
    printf("\t(A)dd crime to a record\n");
    printf("\t(I)nsert new record to database\n");
    printf("\t(Q)uit\n");
    printf("Enter your choice: ");

    fgets(choice, 20, stdin);
    switch(toupper(choice[0]))
    {
    case 'L':
      ListRecords();
      break;
    case 'G':
      GetRecord();
      break;
    case 'A':
      AddCrime();
      break;
    case 'I': 
      NewRecord();
      break;
    case 'Q':
      done = TRUE;
      break;
    default:
      break;
    }
  }
}

/* 
 * NAME
 *   ListRecords
 * DESCRIPTION
 *   List available records by sss number
 * LOB FEATURES
 *   none
 */ 

void ListRecords()
{
  char *select_sss = "SELECT SSS FROM LICENSE_TABLE";
  char sss[10];

  EXEC SQL PREPARE sss_exec FROM :select_sss;
  EXEC SQL DECLARE sss_cursor CURSOR FOR sss_exec;
  EXEC SQL OPEN sss_cursor;

  printf("Available records:\n");

  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      EXEC SQL FETCH sss_cursor INTO :sss;
      printf("\t%s\n", sss);
    }
  EXEC SQL WHENEVER NOT FOUND CONTINUE;

  EXEC SQL CLOSE sss_cursor;
}


/* 
 * NAME
 *   PrintCrime
 * DESCRIPTION
 *   Tests correctness of clob
 * LOB FEATURES
 *   OCIlobRead and OCILobGetLength
 */

void PrintCrime(a_clob)
  OCIClobLocator *a_clob; 
{ 
  ub4 lenp; 

  printf("\n");
  printf("=====================\n");
  printf(" CRIME SHEET SUMMARY \n");
  printf("=====================\n\n");

  EXEC SQL LOB DESCRIBE :a_clob GET LENGTH INTO :lenp;

  if(lenp == 0) /* No crime on file */
    {
      printf("Record is clean\n");
    }
  else
    {
      ub4 amt = lenp;
      varchar *the_string = (varchar *)malloc(2 + lenp);

      the_string->len = (ub2)lenp;      

      EXEC SQL WHENEVER NOT FOUND CONTINUE;
      EXEC SQL LOB READ :amt
        FROM :a_clob INTO :the_string WITH LENGTH :lenp;
         
      printf("%.*s\n", the_string->len, the_string->arr);
      free(the_string);
    }
}

/* 
 * NAME
 *   GetRecord
 * DESCRIPTION
 *   Get license of single individual
 * LOB FEATURES
 *   allocate and select of blob and clob
 */ 

void GetRecord()
{
  char sss[SSS_LENGTH];
  
  if(GetSSS(sss) == TRUE)
    {
      OCIClobLocator *license_txt;
      char name[NAME_LENGTH]={'\0'};
      
      EXEC SQL ALLOCATE :license_txt;

      EXEC SQL SELECT name, txt_summary INTO :name, :license_txt 
	FROM license_table WHERE sss = :sss;
      
      printf("========================================================\n\n");
      printf("NAME: %s\tSSS: %s\n", name, sss);
      PrintCrime(license_txt);
      printf("\n\n========================================================\n");

      EXEC SQL FREE :license_txt;
    }
  else
    {
      printf("SSS Number Not Found\n");
    }
}

/* 
 * NAME
 *   NewRecord
 * DESCRIPTION
 *   Create new record in database
 * LOB FEATURES
 *   EMPTY_CLOB() and OCILobWrite
 */ 

void NewRecord()
{
  char sss[SSS_LENGTH], name[NAME_LENGTH] = {'\0'};
  
  if(GetSSS(sss) == TRUE)
    {
      printf("Record with that sss number already exists.\n");
      return;
    }
  else
    {
      OCIClobLocator *license_txt;
      
      EXEC SQL ALLOCATE :license_txt;
      
      GetName(name);

      EXEC SQL INSERT INTO license_table 
	VALUES (:sss, :name, empty_clob());

      EXEC SQL SELECT TXT_SUMMARY INTO :license_txt FROM LICENSE_TABLE
	WHERE SSS = :sss;

      printf("========================================================\n\n");
      printf("NAME: %s\tSSS: %s\n", name, sss);
      PrintCrime(license_txt);
      printf("\n\n========================================================\n");

      EXEC SQL FREE :license_txt;
    }
}

/* 
 * NAME
 *   NewCrime
 * DESCRIPTION
 *   Query user for new crime
 * LOB FEATURES
 *   None
 */ 

char *NewCrime()
{
  int  SuggestedCrimeNo;
  int  i;
  char crime[10];

  printf("Select from the following:\n");
  for(i = 1; i <= MAXCRIME; i++)
    printf("(%d) %s\n", i, CrimeList[i-1]);

  printf("Crime (1-5): ");
  fgets(crime, 10, stdin);
  SuggestedCrimeNo = atoi(crime);

  while((SuggestedCrimeNo < 1) || (SuggestedCrimeNo > MAXCRIME))
    {
      printf("Invalid selection\n");
      printf("Crime (1-5): ");
      fgets(crime, 10, stdin);
      SuggestedCrimeNo = atoi(crime);
    }
  
  return CrimeList[SuggestedCrimeNo-1];
}

/* 
 * NAME
 *   AppendToClob
 * DESCRIPTION
 *   Append String charbuf to a Clob in the following way:
 *   if the contents of the clob a_clob were <foo> and the
 *   contents of charbuf were <bar>, after the append a_clob
 *   will contain: <foo>\n<bar> - <curdate>
 *   where <curdate> is today's date as obtained by the
 *   GetDate procedure.
 * LOB FEATURES
 *   OCILobWrite
 * NOTE
 *   Potentially, charbuf can be a very large string buffer.
 *   Furthermore, it should be noted that lobs and lob
 *   performance were designed for large data. Therefore, 
 *   users are encouraged to read and write large chunks of
 *   data to lobs. 
 */ 

void AppendToClob(a_clob, charbuf)
  OCIClobLocator *a_clob;
  char *charbuf;
{
  ub4 ClobLen, WriteAmt, Offset;
  int CharLen = strlen(charbuf);
  int NewCharbufLen = CharLen + DATELENGTH + 4; 
  varchar *NewCharbuf;
 
  NewCharbuf = (varchar *)malloc(2 + NewCharbufLen);

  NewCharbuf->arr[0] = '\n';
  NewCharbuf->arr[1] = '\0';
  strcat((char *)NewCharbuf->arr, charbuf);
  NewCharbuf->arr[CharLen + 1] = '\0';
  strcat((char *)NewCharbuf->arr, curdate);

  NewCharbuf->len = NewCharbufLen;

  EXEC SQL LOB DESCRIBE :a_clob GET LENGTH INTO :ClobLen;

  WriteAmt = NewCharbufLen;
  Offset = ClobLen + 1;

  EXEC SQL LOB WRITE ONE :WriteAmt FROM :NewCharbuf
    WITH LENGTH :NewCharbufLen INTO :a_clob AT :Offset;

  free(NewCharbuf);
}

/* 
 * NAME
 *   AddCrime
 * DESCRIPTION
 *   Add a crime to a citizen's crime file
 * LOB FEATURES
 *   OCILobWrite
 */ 

void AddCrime()
{
  char sss[SSS_LENGTH];

  if (GetSSS(sss) == TRUE)
    {
      OCIClobLocator *license_txt;
      char *crimebuf;
      char  name[NAME_LENGTH] = {'\0'};
      
      EXEC SQL ALLOCATE :license_txt;	
      
      EXEC SQL SELECT txt_summary INTO :license_txt FROM license_table
	WHERE sss = :sss FOR UPDATE; 

      crimebuf = NewCrime();

      printf("Added %s to CrimeList\n", crimebuf);
      AppendToClob(license_txt, crimebuf);

      EXEC SQL SELECT name INTO :name FROM license_table WHERE sss = :sss;

      printf("NAME: %s SSS: %s\n", name, sss);
      PrintCrime(license_txt);

      EXEC SQL COMMIT;
      EXEC SQL FREE :license_txt;
    }
  else
    {
      printf("SSS Number Not Found\n");
    }
}

/* 
 * NAME
 *   GetSSS
 * DESCRIPTION
 *   Fills the passed buffer with a client-supplied social security number
 *   Returns FALSE if sss does not correspond to any entry in the database,
 *   else returns TRUE
 * LOB FEATURES
 *   none
 */

boolean GetSSS(suggested_sss)
  char *suggested_sss;
{
  int count = 0;
  int i;

  printf("Social Security Number: ");
  fgets(suggested_sss, SSS_LENGTH, stdin);

  for(i = 0; ((suggested_sss[i] != '\0') && (i < SSS_LENGTH)); i++)
    {
      if(suggested_sss[i] == '\n') 
	suggested_sss[i]='\0';
    }

  EXEC SQL SELECT COUNT(*) INTO :count FROM license_table 
    WHERE sss = :suggested_sss;

  return (count != 0);
}

/* 
 * NAME
 *   GetName
 * DESCRIPTION
 *   Get name from user. 
 *   
 * LOB FEATURES
 *   none
 */

void GetName(name_holder)
  char *name_holder;
{
  int count=0;
  int i;

  printf("Enter Name: ");
  fgets(name_holder, NAME_LENGTH + 1, stdin);

  for(i = 0; name_holder[i] != '\0'; i++)
    {
      if(name_holder[i] == '\n') 
	name_holder[i]='\0';
    }

  return;
}

/* 
 * NAME
 *   PrintSQLError
 * DESCRIPTION
 *   Prints an error message using info in sqlca and calls exit.
 * COLLECTION FEATURES
 *   none
 */ 

void PrintSQLError()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("SQL error occurred...\n");
  printf("%.*s\n", (int)sqlca.sqlerrm.sqlerrml,
         (CONST char *)sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK RELEASE;
  exit(EX_FAILURE);
}