4
Temporary LOBs

In this chapter we describe how to work with Temporary LOBs in terms of use cases. That is, we discuss each operation on a LOB (such as "See If a Temporary LOB is Open") in terms of a use case by that name. The table listing all the use cases is provided at the head of the chapter (see "Use Case Model: Internal Temporary LOBs"). A summary figure, "Use Case Model Diagram: Temporary LOBs", locates all the use cases in single drawing. If you are using the HTML version of this document, you can use this figure to navigate to the use case in which you are interested by clicking on the relevant use case title.

The individual use cases are themselves laid out as follows:

A figure that depicts the use case (see "Preface" for a description of how to interpret these diagrams).
A scenario that portrays one implementation of the use case in terms of the hypothetical multimedia application described above (see "An Example Application" in Chapter 1, "Introduction to Working With LOBs").
Code examples in each of the programmatic environments which can be utilized to implement the use case (see "Programmatic Environments for Operating on LOBs" in Chapter 1, "Introduction to Working With LOBs").

Use Case Model: Internal Temporary LOBs

Table 4-1 Use Case Model Overview: Internal Temporary LOBs

Use Case and Page

Create a Temporary LOB

See If a LOB is Temporary

Free a Temporary LOB

Load a Temporary LOB with Data from a BFILE

See If a Temporary LOB Is Open

Display the Temporary LOB Data

Read Data from a Temporary LOB

Read a Portion of the Temporary LOB (substr)

Compare All or Part of Two (Temporary) LOBs

See If a Pattern Exists in a Temporary LOB (instr)

Get the Length of a Temporary LOB

Copy All or Part of One (Temporary) LOB to Another

Copy a LOB Locator for a Temporary LOB

See If One LOB Locator for a Temporary LOB Is Equal to Another

See If a LOB Locator for a Temporary LOB Is Initialized

Get Character Set ID of a Temporary LOB

Get Character Set Form of a Temporary LOB

Append One (Temporary) LOB to Another

Write Append to a Temporary LOB

Write Data to a Temporary LOB

Trim the Temporary LOB Data

Erase Part of a Temporary LOB

Enable LOB Buffering for a Temporary LOB

Flush Buffer for a Temporary LOB

Disable LOB Buffering for a Temporary LOB

Use Case and Page
Create a Temporary LOB
See If a LOB is Temporary
Free a Temporary LOB
Load a Temporary LOB with Data from a BFILE
See If a Temporary LOB Is Open
Display the Temporary LOB Data
Read Data from a Temporary LOB
Read a Portion of the Temporary LOB (substr)
Compare All or Part of Two (Temporary) LOBs
See If a Pattern Exists in a Temporary LOB (instr)
Get the Length of a Temporary LOB
Copy All or Part of One (Temporary) LOB to Another
Copy a LOB Locator for a Temporary LOB
See If One LOB Locator for a Temporary LOB Is Equal to Another
See If a LOB Locator for a Temporary LOB Is Initialized
Get Character Set ID of a Temporary LOB
Get Character Set Form of a Temporary LOB
Append One (Temporary) LOB to Another
Write Append to a Temporary LOB
Write Data to a Temporary LOB
Trim the Temporary LOB Data
Erase Part of a Temporary LOB
Enable LOB Buffering for a Temporary LOB
Flush Buffer for a Temporary LOB
Disable LOB Buffering for a Temporary LOB

Figure 4-1 Use Case Model Diagram: Internal Temporary LOBs (part 1 of 2)

Figure 4-2 Use Case Model Diagram: Internal temporary LOBs (part 2 of 2)

Programmatic Environments

Note:
No Visual Basic or Java support for temporary LOBs is planned for the 8.1 time-frame.

Oracle8i supports the definition, creation, deletion, access, and update of temporary LOBs in PL/SQL (using the DBMS_LOB package), C/C++ (using PRO*C), and C (using the OCI).

These interfaces operate on temporary LOBs through locators in the same way that they do for permanent LOBs. Since temporary lobs are never part of any table, you cannot use SQL DML to operate on them. They must be manipulated using the DBMS_LOB package, the OCI, or the other programmatic interfaces.

SQL support for temporary LOBs is available in that temporary LOB locators can be used as IN values, with values accessed through a locator. Specifically, they can be used

as a value in a WHERE clause for INSERT, UPDATE, DELETE, or SELECT such as

SELECT pattern FROM composite_image WHERE temp_lob_pattern_id  = 
somepattern_match_function(lobvalue);

and

as a variable in a SELECT INTO... statement such as

SELECT PermanentLob INTO TemporaryLob_loc FROM Demo_tab WHERE Column1 := 1;

Note that selecting a permanent LOB into a temporary LOB locator will cause the temporary LOB locator to point to a permanent LOB. It does not cause a copy of the permanent LOB to be put in the temporary LOB.

Examining the use case model diagrams for temporary LOBs, and comparing it to the "Use Case Model Diagram: Internal Persistent LOBs (part 1 of 2)", and "Use Case Model Diagram: Internal Persistent LOBs (part 2 of 2)", you can see that you can utilize many of the same functions that apply to persistent LOBs for operating on temporary LOBs:

DBMS_LOB package PL/SQL procedures (Compare, Instr, Substr)
DBMS_LOB package PL/SQL procedures and corresponding OCI functions (Append, Copy, Erase, Getlength, Loadfromfile, Read, Trim, Write, WriteAppend).
OCI functions (OCILobAssign, OCILobLocatorIsInit, etc.).

In addition, you can use the ISTEMPORARY function to determine if a LOB is temporary based on its locator.

The Location of Temporary LOBs

Temporary LOBs are not stored permanently in the database like other data. The data is stored in temporary tablespaces, but is not stored in any tables. This means you can CREATE an internal temporary LOB (BLOB,CLOB, NCLOB) on the server independent of any table, but you cannot store that LOB. Since temporary LOBs are not associated with a table schema, there are no meanings to the terms "inline" and "out-of-line" for temporary LOBs. However, note that all temporary LOBs reside in the server; there is no support for client-side temporary LOBs.

The Lifetime and Duration of Temporary LOBs

The default lifetime of a temporary LOB is a session.

The interface for creating temporary LOBs includes a parameter that lets you specify the default scope of the life of the temporary LOB. By default, all temporary LOBs are deleted at the end of the session in which they were created. If a process dies unexpectedly or the database crashes, all temporary LOBs are deleted.

OCI users can group temporary LOBs together into a logical bucket. The OCIDuration will represent a store for temporary LOBs. There will be a default duration for every session into which temporary LOBs will be placed if the user doesn't specify a specific duration. The default duration will end when the user's session ends. Also, the user will be able to perform an OCIDuration operation which will cause all contents in the OCIDuration to be freed.

Memory Handling

Temporary LOBs are especially useful when you want to perform some transformational operation on a LOB -- such as morphing an image, or changing a LOB from one format to another -- and then return it to the database. In doing this you can utilize LOB Buffering support for temporary LOBs, you can specify CACHE/NOCACHE for each temporary LOB, and you can FREE an individual temporary internal LOB when you have no further need of it.

Your temporary tablespace is used to store the temporary LOB data. Data storage resources will be controlled by the DBA through control of a user's access to temporary tablespaces, and by the creation of different temporary tablespaces.

Memory usage will increase incrementally as the number of temporary LOBs grows. You can reuse temporary LOB space in your session by freeing temporary LOBs explicitly. Freeing one or more temporary LOBs does not result in all of the space being returned to the temporary tablespace for general re-consumption. Instead, it remains available for reuse in the session. If a process dies unexpectedly or the database crashes, the space for temporary LOBs is freed along with the deletion of the temporary LOBs. In all cases, when a user's session ends, space is returned to the temporary tablespace for general reuse.

We previously noted that if you perform a

SELECT permanent_lob INTO temporary_lob_locator FROM y_blah WHERE x_blah

the temporary_lob_locator will get overwritten with the permanent_lob's locator. This will result in creating a copy of the LOB pointed at by permanent_lob, and temporary_lob_locator will represent this newly created temporary LOB. Note that unless you had saved the temporary_lob's locator in another variable, you will lose track of the LOB that temporary_lob_locator originally pointed at before the SELECT INTO operation.

In this case the temporary LOB will not get implicitly freed. If you do not wish to waste space, you will explicitly free a temporary LOB before overwriting it with a permanent LOB locator.

Since CR and rollbacks will not be supported for temporary LOBs, you will have to free the temporary LOB and start over again if you run into an error.

Locators and Semantics

Creation of a temporary LOB instance by a user causes the engine to create, and return a locator to the LOB data. Temporary LOBs do not support any operations that are not supported for persistent LOB locators, but temporary LOB locators have certain specific features. For instance, when you perform the following query

SELECT permanent_lob INTO temporary_lob_locator FROM y_blah 
   WHERE x_blah := a_number;

temporary_lob_locator is overwritten with the permanent_lob's locator. This means that unless you have a copy of temporary_lob's locator that points to the temporary LOB that was overwritten, you no longer have a locator with which to access the temporary LOB.

Temporary LOBs adhere to value semantics in order to be consistent with permanent LOBs and to conform to the ANSI standard for LOBs. Since CR, undo, and versions are not generated for temporary LOBs, there may be an impact on performance if you assign multiple locators to the same temporary LOB because semantically each locator will have its own copy of the temporary LOB. Each time a user does an OCILobAssign, or the equivalent assignment in PL/SQL, the database will make a copy of the temporary LOB (although it may be done lazily for performance reasons). Each locator will point to its own LOB value. If one locator is used to create a temporary LOB, and another LOB locator is assigned to that temporary LOB using OCILobAssign, the database will copy the original temporary LOB and cause the second locator to point to the copy, not the original temporary LOB.

In order for multiple users to modify the same LOB, they must go through the same locator. Although temporary LOBs use value semantics, you can apply pseudo-reference semantics by using pointers to locators in OCI, and having multiple pointers to locators point to the same temporary LOB locator if necessary. In PL/SQL, you can have the same effect by passing the temporary LOB locator "by reference" between modules. This will help avoid using more than one locator per temporary LOB, and prevent these modules from making local copies of the temporary LOB.

Here are two examples of situations where a user will incur a copy, or at least an extra roundtrip to the server:

Assigning one temporary LOB to another.

DECLARE
  Va BLOB;
  Vb BLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(Vb,TRUE,DBMS_LOB.SESSION);
  DBMS_LOB.CREATETEMPORARY(Va,TRUE,DBMS_LOB.SESSION);
  Va := Vb;
END;

This will cause Oracle to create a copy of Vb and point the locator Va to it. We will also free the temporary LOB that Va used to point to.

Assigning one collection to another collection.

If a temporary LOB is an element in a collection and you assign one collection to another, you will incur copy overhead and free overhead for the temporary LOB locators that get updated. This is also true for the case where you assign an object type containing a temporary LOB as an attribute to another such object type, and they have temporary LOB locators that get assigned to each other because the object types have LOB attributes that are pointing to temporary LOB locators.
For information about with collections, see:

Oracle8i Concepts

Oracle8i Application Developer's Guide - Fundamentals

If your application involves several such assignments and copy operations of collections or complex objects, and you seek to avoid the above overheads, then persistent internal LOBs may be more suitable for such applications. More precisely: you should not use temporary LOBs inside collections or complex objects when you are doing assignments or copies of those collections or complex objects. Also, you should not select LOB values into temporary LOB locators.

You will incur overhead if you have a temporary LOB in a duration, you call OCIDurationEnd on that duration, and then subsequently reassign the locator for that temporary LOB to another LOB. Irrespective of whether there was a previous OCIDurationEnd call, Oracle will attempt to free the temporary LOB to which the locator pointed. Or if the user tries to access the temporary LOB with that locator they will incur an error. Once a user issues OCIDurationEnd, all temporary LOBs in that duration will be freed regardless of the fact that locators may still exist which used to refer to the now freed LOBs.

In PL/SQL, user-defined durations are not exposed. However, users may specify either session scope or call scopes using the predefined duration parameters dbms_lob.session, or dbms_lob.call.

User-defined OCIDurations can be created using the OCIDurationBegin call when the database is using the object option. The user can end the OCIDuration with a call to OCIDurationEnd. Any temporary LOBs that existed in the duration will be freed.

Security Issues with Temporary LOBs

Security is provided through the LOB locator. Only the user who created the temporary LOB can access it. Locators are not designed to be passed from one user's session to another. If you did manage to pass a locator from one session to another, you would no longer be able to access the temporary LOBs in the new session from the original session. By the same token, you would not be able to access a temporary LOB in the original session from the new (current) session to which the locator was migrated.

Temporary LOB lookup will be localized to each user's own session. Someone using a locator from another session would only be able to access LOBs within his own session that had the same lobid. Users of your application should not try to do this, but if they do, they will still not be able to affect anyone else's data.

Managing Temporary LOBs

Oracle keeps track of temporary LOBs per session, and provides a v$ view called v$temporary_lobs. From the session the application can determine which user owns the temporary LOBs. This table can be used by DBAs to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.

Create a Temporary LOB

Figure 4-3 Use Case Diagram: Create a temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

A temporary LOB will be empty when it is created.

Temporary LOBs do not support the empty_blob() or empty_clob() functions that are supported for permanent LOBs. The empty_blob() function specifies the fact that the LOB is initialized, but not populated with any data.

This example reads in a single video Frame from the Multimedia_tab table. Then it creates a temporary LOB so that we can use the temporary LOB to convert the video image from MPEG to JPEG format. The Temporary LOB which is created will be read through the CACHE, and it will be automatically cleaned up at the end of the user's session, if it is not explicitly freed sooner.

Example: Create a Temporary LOB Using PL/SQL (DBMS_LOB Package)

Note:
You may need to set up the following data structures for certain examples to work:
CREATE TABLE long_raw_tab (id number, long_raw_col long raw); INSERT INTO long_raw_tab VALUES (1,HEXTORAW('7D')); INSERT INTO multimedia_tab (clip_id,frame) SELECT id,TO_LOB(long_raw_col) FROM long_raw_tab;

DECLARE
   Dest_loc       BLOB;
   Src_loc        BLOB;
   Amount         INTEGER := 4000;
BEGIN
   SELECT Frame INTO Src_loc FROM Multimedia_tab WHERE Clip_ID = 1;
    /* Create a temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
   /* Copy the entire frame from the Src_loc to the Temporary Lob: */
   DBMS_LOB.COPY(Dest_loc,Src_loc,DBMS_LOB.GETLENGTH(Src_loc),1,1);
   DBMS_LOB.FREETEMPORARY(Dest_loc);
END;

Example: Create a Temporary LOB Using C (OCI)

/* This function reads in a single video Frame from the Multimedia_tab table.
  Then it creates a temporary LOB so that we can use the temporary LOB to 
  convert the video image from MPEG to JPEG format.. The Temporary LOB which is 
  created will be read through the CACHE, and it will be automatically cleaned 
  up at the end of the user's session, if it is not explicitly freed sooner. 
  This function returns 0 if it completes successfully, and -1 if it fails: */
sb4 select_and_createtemp (OCILobLocator *lob_loc, 
                           OCIError      *errhp, 
                           OCISvcCtx     *svchp,
                           OCIStmt       *stmthp,
                           OCIEnv        *envhp) 
{
  OCIDefine     *defnp1;
  OCIBind       *bndhp;
  text          *sqlstmt;
  int rowind =1;
  ub4 loblen = 0;
  OCILobLocator *tblob;

  printf ("in select_and_createtemp \n");
   
  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob,
                        (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0))
  {
    printf("failed in OCIDescriptor Alloc in select_and_createtemp \n");
    return -1;
  }

  /* Arbitrarily select where Clip_ID =1: */
  sqlstmt = (text *)
     "SELECT Frame FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE";

  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                     (ub4) strlen((char *)sqlstmt),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
      (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n");
      return -1;
  }

  /* Define for BLOB: */
  if (OCIDefineByPos(stmthp, 
             &defnp1, errhp, (ub4) 1, (dvoid *) &lob_loc, (sb4)0, 
             (ub2) SQLT_BLOB, (dvoid *) 0, (ub2 *) 0, 
             (ub2 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: Select locator: OCIDefineByPos()\n");
    return -1;
  }

  /* Execute the select and fetch one row: */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                     (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n");
    return -1;
  }

  if(OCILobCreateTemporary(svchp,
                            errhp, tblob, (ub2)0, SQLCS_IMPLICIT, 
                            OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                            OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return -1;
  }

  if (OCILobGetLength(svchp, errhp, lob_loc, &loblen) != 0)
  {
    printf("OCILobGetLength FAILED\n");
    return -1;
  }
  if (OCILobCopy(svchp, errhp, tblob,lob_loc,(ub4)loblen, (ub4) 1, (ub4) 1))
  {
    printf( "OCILobCopy FAILED \n");
  }   
  if(OCILobFreeTemporary(svchp,errhp,tblob))
  {
    printf ("FAILED: OCILobFreeTemporary call \n");
    return -1;
  }
    
  return 0;
}

Example: Create a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. CREATE-TEMPORARY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".

       01  BLOB1          SQL-BLOB.
       01  TEMP-BLOB      SQL-BLOB.
       01  LEN            PIC S9(9) COMP.
       01  D-LEN          PIC 9(9).
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       CREATE-TEMPORARY.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the CLOB locators: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.

           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC ORACLE OPTION (SELECT_ERROR=NO) END-EXEC.
           EXEC SQL 
                SELECT FRAME INTO :BLOB1
                FROM MULTIMEDIA_TAB 
                WHERE CLIP_ID = 1
           END-EXEC.
 
      * Get the length of the persistent BLOB: 
           EXEC SQL 
                LOB DESCRIBE :BLOB1
                GET LENGTH INTO :LEN
           END-EXEC.
     
      * Copy the entire length from persistent to temporary: 
           EXEC SQL 
                LOB COPY :LEN FROM :BLOB1 TO :TEMP-BLOB
           END-EXEC.

      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB
           END-EXEC.

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Create a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void createTempLOB_proc()
{
  OCIBlobLocator *Lob_loc, *Temp_loc;
  int Amount;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate the LOB Locators: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL ALLOCATE :Temp_loc;
  /* Create the Temporary LOB: */
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  EXEC SQL SELECT Frame INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1;
  /* Copy the full length of the source LOB into the Temporary LOB: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Amount;
  EXEC SQL LOB COPY :Amount FROM :Lob_loc TO :Temp_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  createTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

See If a LOB is Temporary

Figure 4-4 Use Case Diagram: See If a LOB is Temporary

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This is a generic example that queries whether the locator is associated with a temporary LOB or not.

Example: See If a LOB is Temporary Using PL/SQL (DBMS_LOB Package)

/* This is also an example of freeing a temporary LOB. First we test to make 
   sure that the LOB locator points to a temporary LOB, then we free it. 
   Otherwise, we issue an error: */
CREATE or REPLACE PROCEDURE freeTempLob_proc(Lob_loc IN OUT BLOB) IS
BEGIN
   /* Free the temporary LOB locator passed in. */
   /* First check to make sure that the locator is pointing to a temporary 
      LOB:*/
    IF DBMS_LOB.ISTEMPORARY(Lob_loc) = 1 THEN
       /* Free the temporary LOB locator: */
        DBMS_LOB.FREETEMPORARY(Lob_loc);
        DBMS_OUTPUT.PUT_LINE(' temporary LOB was freed');
    ELSE
        /* Print an error: */
        DBMS_OUTPUT.PUT_LINE(
           'Locator passed in was not a temporary LOB locator');
    END IF;
END;

Example: See If a LOB is Temporary Using C (OCI)

/* This function also frees a temporary LOB. It takes a locator as an argument,   
   checks to see if it is a temporary LOB, and if it is the function will free 
   the temporary LOB. Otherwise, it will print out a message saying the locator 
   wasn't a temporary LOB locator. This function returns 0 if it 
   completes successfully, and -1 otherwise: */ 

sb4 check_and_free_temp(OCILobLocator *tblob,
                        OCIError      *errhp, 
                        OCISvcCtx     *svchp,
                        OCIStmt       *stmthp, 
                        OCIEnv        *envhp)
{
  boolean is_temp;
  is_temp = FALSE;
 
  if (OCILobIsTemporary(envhp, errhp, tblob, &is_temp))
  {
    printf ("FAILED: OCILobIsTemporary call\n");
    return -1;
  }
  if(is_temp)
  {
      if(OCILobFreeTemporary(svchp, errhp, tblob))
      {
          printf ("FAILED: OCILobFreeTemporary call\n");
          return -1;
 
      }else
      {
          printf("Temporary LOB freed\n");
      }
  }else
  {
      printf("locator is not a temporary LOB locator\n");
  }
  return 0;
}

Example: See If a LOB is Temporary Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-ISTEMP.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
       01  TEMP-BLOB      SQL-BLOB.
       01  IS-TEMP        PIC S9(9) COMP.
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       CREATE-TEMPORARY.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.

           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Check if the LOB is temporary: 
           EXEC SQL 
                LOB DESCRIBE :TEMP-BLOB
                GET ISTEMPORARY INTO :IS-TEMP
           END-EXEC.
     
           IF IS-TEMP = 1
      *      Logic for a temporary LOB goes here
             DISPLAY "LOB is temporary."
           ELSE
      *      Logic for a persistent LOB goes here.
             DISPLAY "LOB is persistent."
           END-IF.

           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: See If a LOB is Temporary Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void lobIsTemp_proc()
{
  OCIBlobLocator *Temp_loc;
  int isTemporary = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Determine if the Locator is a Temporary LOB Locator: */
  EXEC SQL LOB DESCRIBE :Temp_loc GET ISTEMPORARY INTO :isTemporary;
  if (isTemporary)
    printf("Locator is a Temporary LOB locator\n");
  else
    printf("Locator is not a Temporary LOB locator \n");
  /* Note that in this example, isTemporary should be 1 (TRUE) */
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  lobIsTemp_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Free a Temporary LOB

Figure 4-5 Use Case Diagram: Free a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

A temporary LOB instance can only be destroyed by using OCI or the DBMS_LOB package by using the appropriate FREETEMPORARY or OCIDurationEnd or OCILOBFreeTemporary statements.

To make a temporary LOB permanent, the user must explicitly use the OCI or DBMS_LOB copy() command and copy the temporary LOB into a permanent one.

Example: Free a Temporary LOB Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure freeTempLob_proc is not part of the 
   DBMS_LOB package: */
CREATE or REPLACE PROCEDURE freeTempLob_proc(Lob_loc IN OUT BLOB) IS

BEGIN
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
   /* Use the temporary LOB locator here, then free it.*/
   /* Free the temporary LOB locator: */
   DBMS_LOB.FREETEMPORARY(Lob_loc);
   DBMS_OUTPUT.PUT_LINE('Temporary LOB was freed');
END;

Example: Free a Temporary LOB Using C (OCI)

/* This function creates a temporary LOB and then frees it: 
   This function returns 0 if it completes successfully, and -1 otherwise:  */ 

sb4 freeTempLob(OCIError      *errhp, 
                OCISvcCtx     *svchp,
                OCIStmt       *stmthp, 
                OCIEnv        *envhp)
{
  OCILobLocator *tblob;

  checkerr (errhp,OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob,
                                     (ub4)OCI_DTYPE_LOB, (size_t)0,
                                     (dvoid**)0));

  if(OCILobCreateTemporary(svchp,errhp,tblob,(ub2)0,SQLCS_IMPLICIT,
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE,
                           OCI_DURATION_SESSION))
  {
      (void) printf("FAILED:CreateTemporary():check_and_free_temp2\n");
      return -1;
  }

  if(OCILobFreeTemporary(svchp,errhp,tblob))
  {
      printf ("FAILED: OCILobFreeTemporary call in check_and_free_temp2\n");
      return -1;

  }else
  {
   printf("Temporary LOB freed in check_and_free_temp2\n");
  }
  return 0;
}

Example: Free a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. FREE-TEMPORARY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
        
       01  TEMP-BLOB      SQL-BLOB.
       01  IS-TEMP        PIC S9(9) COMP.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       FREE-TEMPORARY.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.

           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Do something with the temporary LOB here: 

      * Free the temporary LOB:
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Free a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void freeTempLob_proc()
{
  OCIBlobLocator *Temp_loc;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Do something with the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  freeTempLob_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Load a Temporary LOB with Data from a BFILE

Figure 4-6 Use Case Diagram: Load a LOB with data from a BFILE

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

In using the OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another. However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile operation to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing loadfromfile.

The example procedure assumes that there is an operating system source directory (AUDIO_DIR) that contains the LOB data to be loaded into the target LOB.

Example: Load a Temporary LOB with Data from a BFILE Using PL/SQL (DBMS_LOB Package)

DECLARE
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount         INTEGER := 4000;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
   /* Opening the BFILE is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.CLOSE(Dest_loc);
   /* Free the temporary LOB: */
   DBMS_LOB.FREETEMPORARY(Dest_loc);
END;

Example: Load a Temporary LOB with Data from a BFILE Using C (OCI)

/* Here is a section of code which shows how to create a temporary LOB, and load 
   the contents of a BFILE into the temporary LOB: */

sb4 load_temp(OCIError *errhp,
              OCISvcCtx *svchp,
              OCIStmt   *stmthp,
              OCIEnv    *envhp)
{
  OCILobLocator *bfile;
  int amount =100;
  OCILobLocator *tblob;

  printf("in load_temp\n"); 
  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob,
                        (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in load_temp\n");
    return -1;
  }
  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&bfile,
                        (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in load_temp\n");
    return -1;
  }

  /* Create a temporary LOB: */
  if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, 
                           SQLCS_IMPLICIT, OCI_TEMP_BLOB, 
                           OCI_ATTR_NOCACHE, OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return -1;
  }
  
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"), (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED in load_temp\n");
    return -1;
  }

   /* Opening the BFILE is mandatory: */
   if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))
  {
    printf( "OCILobFileOpen FAILED for the bfile load_temp \n");
    return -1;
  }
 
   /* Opening the LOB is optional: */
   if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return -1;
  }

  if(OCILobLoadFromFile(svchp,
         errhp,
         tblob,
         (OCILobLocator*)bfile,
         (ub4)amount,
         (ub4)1,(ub4)1))
  {
    printf( "OCILobLoadFromFile FAILED\n");
    return -1;
  } 

  /* Close the lobs: */
  if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile))
  {
    printf( "OCILobClose FAILED for bfile \n");
    return -1;
  }

  checkerr(errhp,(OCILobClose(svchp, errhp, (OCILobLocator *) tblob)));
  

  /* Free the temporary LOB now that we are done using it */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }
}

Example: Load a Temporary LOB with Data from a BFILE Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOAD-TEMPORARY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP VALUE 10.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       LOAD-TEMPORARY.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BFILE and BLOB locators: 
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
           
      * Close the LOBs: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.

      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.

      * And free the LOB locators: 
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Load a Temporary LOB with Data from a BFILE Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void loadTempLobFromBFILE_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount = 4096;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the BFILE is mandatory; */
  /* Opening the LOB is optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Load the data from the BFILE into the Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Temp_loc;
  EXEC SQL LOB CLOSE :Lob_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Temp_loc;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  loadTempLobFromBFILE_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

See If a Temporary LOB Is Open

Figure 4-7 Use Case Diagram: See If a Temporary LOB Is Open... 206

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This is a generic example takes a locator as input, creates a temporary LOB, opens it and tests if the LOB is open.

Example: See If a Temporary LOB Is Open Using PL/SQL

/* Note that the example procedure seeTempLOBIsOpen_proc is not part of the 
   DBMS_LOB package. This procedure takes a locator as input, creates a  
   temporary LOB, opens it and tests if the LOB is open. */
CREATE OR REPLACE PROCEDURE seeTempLOBIsOpen_proc(Lob_loc IN OUT BLOB, 
                                                  Retval OUT INTEGER) IS
BEGIN
   /* Create the temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
   /* See If the LOB is open: */
   Retval := DBMS_LOB.ISOPEN(Lob_loc);
  /* The value of Retval will be 1  if  the LOB is open. */
   /* Free the temporary LOB: */
   DBMS_LOB.FREETEMPORARY(Lob_loc);
END;

Example: See If a Temporary LOB Is Open Using C (OCI)

/* This function takes a locator and returns 0 if the function 
   completes successfully. The function prints out "Temporary LOB is open" or 
  "Temporary LOB is closed". It does not check whether or not the locator is 
   actually pointing to a temporary LOB or not, but the open or close test will 
   work either way. The function returns 0 if it completes 
   successfully, and -1 if it fails. */

sb4 seeTempLOBIsOpen (OCILobLocator *lob_loc,
                      OCIError      *errhp,
                      OCISvcCtx     *svchp,
                      OCIStmt       *stmthp,
                      OCIEnv        *envhp)
{
    boolean is_open = FALSE;
    OCILobLocator *tblob;
   

    printf("in seeTempLOBIsOpen \n");

    if(OCILobCreateTemporary(svchp, 
              errhp, 
              lob_loc,
              (ub2)0, 
              SQLCS_IMPLICIT, 
              OCI_TEMP_BLOB, 
              OCI_ATTR_NOCACHE, 
              OCI_DURATION_SESSION))
    {
        (void) printf("FAILED: CreateTemporary() \n");
        return -1;
    }
 
    if(OCILobIsOpen(svchp, errhp, lob_loc, &is_open))
    {
        printf("OCILobIsOpen FAILED\n");
        return -1;
    }
    if(is_open)
    {
        printf("Temporary LOB is open\n");
 
    }else
    {
        printf("Temporary LOB is closed\n");
 
    }

    if(OCILobFreeTemporary(svchp,errhp,tblob))
    {
        printf("OCILobFreeTemporary FAILED \n");
        return -1;
    }

    return 0;
}

Example: See If a Temporary LOB Is Open Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-ISOPEN.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".

       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP.
       01  IS-OPEN        PIC S9(9) COMP.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       TEMP-LOB-ISOPEN.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators:
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Open temporary LOB:
           EXEC SQL LOB OPEN :TEMP-BLOB READ ONLY END-EXEC.

           EXEC SQL 
                LOB DESCRIBE :TEMP-BLOB GET ISOPEN INTO :IS-OPEN
           END-EXEC.
           
           IF IS-OPEN = 1
      *       Logic for an open temporary LOB goes here:
              DISPLAY "Temporary LOB is OPEN."
           ELSE 
      *       Logic for a closed temporary LOB goes here:
              DISPLAY "Temporary LOB is CLOSED."
           END-IF.
        
      * Close the temporary LOB:
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.

      * Free the temporary LOB:
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.

      * And free the LOB locators: 
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: See If a Temporary LOB Is Open Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void tempLobIsOpen_proc()
{
  OCIBlobLocator *Temp_loc;
  int isOpen = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Open the Temporary LOB */
  EXEC SQL LOB OPEN :Temp_loc READ ONLY;
  /* Determine if the LOB is Open */
  EXEC SQL LOB DESCRIBE :Temp_loc GET ISOPEN INTO :isOpen;
  if (isOpen)
    printf("Temporary LOB is open\n");
  else
    printf("Temporary LOB is not open\n");
  /* Note that in this example, the LOB is Open so isOpen == 1 (TRUE) */
  /* Close the LOB */
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator */
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  tempLobIsOpen_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Display the Temporary LOB Data

Figure 4-8 Use Case Diagram: Display the Temporary LOB data

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

As an instance of displaying a LOB, our example stream-reads the image Drawing from the column object Map_obj onto the client-side in order to view the data.

Example: Display the Temporary LOB Data Using PL/SQL (DBMS_LOB Package)

/* The following function acceses the Washington_audio file, creates a temporary 
  LOB, loads some data from the file, and then reads it back and 
  displays it. */ 
DECLARE
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount         INTEGER := 128;
   Bbuf           RAW(128);
   Position       INTEGER :=1;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
   /* Opening the FILE  is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.LOADFROMFILE(Dest_loc,Src_loc,Amount);

   LOOP
      DBMS_LOB.READ (Dest_loc, Amount, Position, Bbuf);
      /* Display the buffer contents: */
      DBMS_OUTPUT.PUT_LINE('Result :'|| utl_raw.cast_to_varchar2(Bbuf));
      Position := Position + Amount;
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of data loaded into temp LOB');

    DBMS_LOB.CLOSE(Dest_loc);
    DBMS_LOB.FREETEMPORARY(Dest_loc);
    /* Closing the file is mandatory unless you close the files later: */
    DBMS_LOB.CLOSE(Src_loc);
END;

Example: Display the Temporary LOB Data Using C (OCI)

/* The following function acceses the Washington_audio file, creates a temporary 
  LOB, loads some data from the file, and then reads it back and 
  displays it. The reading is done in a streaming fashion. This function assumes 
  that the file specified is kept in the directory known by the directory alias 
  "AUDIO_DIR". It also assumes that the file is at least 14000 bytes long, which 
  is the amount specified to be read and loaded. These amounts are arbitrary for 
  this example. This function uses fprintf() to display the contents of the 
  file. This works well for text data, but you may wish to change the method for 
  binary data. For audio  data, you could, for instance, call an audio function.
  The function returns 0 if it completes successfully, and -1 if it fails. */

#define MAXBUFLEN 32767

sb4 display_file_to_lob( OCIError      *errhp,
                         OCISvcCtx     *svchp,
                         OCIStmt       *stmthp,
                         OCIEnv        *envhp)
{
  int rowind;
  char *binfile;
  OCILobLocator *tblob;
  OCILobLocator *bfile;


  ub4 amount = 14000;
  ub4 offset = 0;
  ub4 loblen = 0;
  ub4 amtp   = 0;
  sword retval;
  ub4 piece  = 1;
  ub4 remainder= 0;
  ub1 bufp[MAXBUFLEN];
  sb4 return_code = 0;
 
  (void) printf("\n===> Testing loading files into lobs and displaying 
them\n\n"); 
 
  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob,
                        (ub4) OCI_DTYPE_LOB,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }

  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,
                        (ub4) OCI_DTYPE_FILE,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }

  /* Create a temporary LOB: */
  if(OCILobCreateTemporary(svchp, errhp, tblob,(ub2)0, SQLCS_IMPLICIT, 
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
      (void) printf("FAILED: CreateTemporary() \n");
      return -1;
  } 


  if(OCILobFileSetName(envhp, errhp, &bfile, (text*)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),(text*)"Washington_audio",
                       (ub2)strlen("Wasthington_audio")))
  {
      printf("OCILobFileSetName FAILED\n");
      return_code = -1;
  }

  /* Open the BFILE: */
  if(OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_FILE_READONLY))
  {
     printf( "OCILobFileOpen FAILED \n");
     return_code = -1;
  }

  if(OCILobLoadFromFile(svchp,errhp,tblob,(OCILobLocator*)bfile,(ub4)amount,
                        (ub4)1,(ub4)1))
  {
      printf( "OCILobLoadFromFile FAILED\n");
      return_code = -1;
  }
 
  offset = 1;
  memset(bufp, '\0', MAXBUFLEN);
 
  retval = OCILobRead(svchp, errhp, tblob, &amtp, offset, 
                      (dvoid *) bufp, (amount < MAXBUFLEN ? amount : MAXBUFLEN), 
                      (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                      (ub2) 0, (ub1) SQLCS_IMPLICIT);

  printf("1st piece read from file is %s\n",bufp);
 
  switch (retval)
  {
    case OCI_SUCCESS:             /* Only one piece */
      (void) printf("stream read piece # %d \n", ++piece);
      (void)printf("piece read was %s\n",bufp);
      break;
    case OCI_FAILURE:
      /*  report_error();  function not shown here */
      break;
    case OCI_NEED_DATA:           /* There are 2 or more pieces */
      remainder = amount;
      printf("remainder is %d \n",remainder);
      do
      {
           memset(bufp, '\0', MAXBUFLEN);
           amtp = 0;
           remainder -= MAXBUFLEN;
           printf("remainder is %d \n",remainder);
           retval = OCILobRead(svchp, errhp, tblob,  &amtp, offset,
                               (dvoid *) bufp, (ub4) MAXBUFLEN, (dvoid *)0,
                               (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                               (ub2) 0, (ub1) SQLCS_IMPLICIT);
 
        /* The amount read returned is undefined for FIRST, NEXT pieces: */
            (void)fprintf(stderr,"stream read %d th piece, amtp = %d\n",
                          ++piece, amtp); 
            (void)fprintf(stderr,"piece  of length read was %d\n",
                strlen((const char*)bufp));
            (void)fprintf(stderr,"piece read was %s\n",bufp);
       } while (retval == OCI_NEED_DATA);
       break;
    default:
      (void) printf("Unexpected ERROR: OCILobRead() LOB.\n");
       break;
  }
  
   /* Close the audio file: */
  if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile))
  {
     printf( "OCILobFileClose FAILED\n");
     return_code =  -1;
  }
  /* clean up the temp LOB now that we are done with it */ 

  if(check_and_free_temp(tblob, errhp,  svchp,stmthp, envhp))
  {
      printf("check and free failed in load test\n");
      return_code = -1;
  }
  return return_code;
}

Example: Display the Temporary LOB Data Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. ONE-READ-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(9) VALUES "SAMP/SAMP".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  BUFFER2        PIC X(32767) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  ORASLNRD       PIC 9(4).
       01  ISTEMP         PIC S9(9) COMP.

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

           EXEC SQL VAR BUFFER2 IS LONG RAW(32767) END-EXEC.

       PROCEDURE DIVISION.
       ONE-READ-BLOB.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locator:
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL LOB CREATE TEMPORARY :TEMP-BLOB END-EXEC. 

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
  
      * Set up the directory and file information:
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "Washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.

           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

           EXEC SQL 
              LOB DESCRIBE :SRC-BFILE GET LENGTH INTO :AMT
           END-EXEC.

      * Open source BFILE and destination temporary BLOB:
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
 
      * Perform a single read:

           EXEC SQL 
                LOB READ :AMT FROM :TEMP-BLOB INTO :BUFFER2 
           END-EXEC.
          
           DISPLAY "Read ", BUFFER2, " from TEMP-BLOB".

       END-OF-BLOB.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB FREE TEMPORARY :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.
        
       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Display the Temporary LOB Data Using C++ (Pro*C/C++)

#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 1024

void displayTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "PHOTO_DIR", *Name = "Lincoln_photo";
  int Amount;
  struct {
    unsigned short Length;
    char Data[BufferLength];
  } Buffer;
  int Position = 1;
  /* Datatype Equivalencing is Mandatory for this Datatype */
  EXEC SQL VAR Buffer IS VARRAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Initialize the LOB Locators */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the LOBs is Optional */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;  
  /* Load a specified amount from the BFILE into the Temporary LOB */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Amount;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc AT :Position INTO :Temp_loc;
  /* Setting Amount = 0 will initiate the polling method */
  Amount = 0;
  /* Set the maximum size of the Buffer */
  Buffer.Length = BufferLength;
  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      /* Read a piece of the BLOB into the Buffer */
      EXEC SQL LOB READ :Amount FROM :Temp_loc INTO :Buffer;
      printf("Display %d bytes\n", Buffer.Length);
    }
  printf("Display %d bytes\n", Amount);
  /* Closing the LOBs is mandatory if you have opened them */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator */
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  displayTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Read Data from a Temporary LOB

Figure 4-9 Use Case Diagram: Read Data from a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Stream Read

The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled via polling or a callback.

When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4 gigabytes regardless of the starting offset and the amount of data in the LOB. You do not need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read.

For example, assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here's the OCI read call, excluding the initialization of the parameters:

#define MAX_LOB_SIZE 4294967295 
ub4  amount =  MAX_LOB_SIZE; 
ub4  offset = 1000; 
OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0)

When using polling mode, be sure to look at the value of the 'amount' parameter after each OCILobRead() call to see how many bytes were read into the buffer since the buffer may not be entirely full.

When using callbacks, the 'len' parameter, which is input to the callback, will indicate how many bytes are filled in the buffer. Be sure to check the 'len' parameter during your callback processing since the entire buffer may not be filled with data (see theOracle Call Interface Programmer's Guide.).

Scenario

Our example reads the data from a single video Frame.

Example: Read Data from a Temporary LOB Using PL/SQL (DBMS_LOB Package)

/* Note that PL/SQL does not support streaming reads. The OCI example will 
   illustrate streaming reads: */ 
DECLARE
  Dest_loc       BLOB;
  Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
  Amount         INTEGER := 4000;
  Bbuf           RAW(32767);
  Position       INTEGER :=1;
BEGIN
    DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
    /* Opening the FILE is mandatory: */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* Opening the LOB is optional: */
    DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
    DBMS_LOB.READ (Dest_loc, Amount, Position, Bbuf);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE(Src_loc);

Example: Read Data from a Temporary LOB Using C (OCI)

/* This is the same example as was shown for reading and displaying data from a   
   temporary LOB. This function takes the Washinton_audio file, opens that file 
   as a BFILE as input, loads that file data into a temporary LOB and then reads
   the data from the temporary LOB 5000 or less bytes at a time.  
   5000 bytes was an arbitrary maximum buffer length chosen for this example.
   The function returns 0 if it completes successfully, and -1 if it fails. */

#define MAXBUFLEN 32767

sb4 test_file_to_lob (OCILobLocator *lob_loc, 
                      OCIError      *errhp,
                      OCISvcCtx     *svchp,
                      OCIStmt       *stmthp,
                      OCIEnv        *envhp)
{
  int rowind;
  OCILobLocator *tblob;
  OCILobLocator *bfile;
  
  ub4 amount = 14000;
  ub4 offset =0;
  ub4   loblen = 0;
  ub4   amtp = 0;
  sword retval;
  ub4   piece = 1;
  ub4   remainder=0;
  ub1 bufp[MAXBUFLEN];
 
  (void) printf(
              "\n===> Testing loading files into lobs and displaying them\n\n"); 
 
 /* Create a temporary LOB: */
 if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, SQLCS_IMPLICIT, 
                          OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                          OCI_DURATION_SESSION))
 {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
 } 
 if(OCILobFileSetName(envhp, errhp, &bfile,(text*)"AUDIO_DIR",
                     (ub2)strlen("AUDIO_DIR"),
                     (text*)"Washington_audio",
                     (ub2)strlen("Washington_audio")))
  {
      printf("OCILobFileSetName FAILED\n");
      return -1;
  }
  if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_FILE_READONLY))
 {
     printf( "OCILobFileOpen FAILED \n");
     return -1;
 }
 if(OCILobLoadFromFile(svchp,errhp,tblob,(OCILobLocator*)bfile,(ub4)amount,
                       (ub4)1,(ub4)1))
 {
     printf( "OCILobLoadFromFile FAILED\n");
     return -1;
 }
 
 offset = 1;
 memset(bufp, '\0', MAXBUFLEN);
 
 retval = OCILobRead(svchp, errhp, tblob, &amtp, offset, (dvoid *) bufp,
                     (amount < MAXBUFLEN ? amount : MAXBUFLEN), (dvoid *)0,
                     (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                     (ub2) 0, (ub1) SQLCS_IMPLICIT);
  fprintf(stderr,"1st piece read from file is %s\n",bufp);
 
  switch (retval)
  {
    case OCI_SUCCESS:             /* Only one piece */
      (void) printf("stream read piece # %d \n", ++piece);
      (void)printf("piece read was %s\n",bufp);
      break;
    case OCI_FAILURE:
      /*  report_error();  function not shown here */
      break;
    case OCI_NEED_DATA:           /* There are 2 or more pieces */
      remainder = amount;
      fprintf(stderr,"remainder is %d \n",remainder);
      do
      {
           memset(bufp, '\0', MAXBUFLEN);
           amtp = 0;
           remainder -= MAXBUFLEN;
           fprintf(stderr,"remainder is %d \n",remainder);
 
            retval = OCILobRead(svchp, errhp, tblob, &amtp, offset,
                               (dvoid *) bufp,(ub4) MAXBUFLEN, (dvoid *)0,
                               (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                               (ub2) 0, (ub1) SQLCS_IMPLICIT);
 
        /* The amount read returned is undefined for FIRST, NEXT pieces: */
            (void)fprintf(stderr,"stream read %d th piece, amtp = %d\n",
                          ++piece, amtp); 
            (void)fprintf(stderr,
                "piece  of length read was %d\n",strlen((const  char *)bufp));
            (void)fprintf(stderr,"piece read was %s\n",bufp);
       } while (retval == OCI_NEED_DATA);
       break;
    default:
      (void) printf("Unexpected ERROR: OCILobRead() LOB.\n");
       break;
  }
  
   /* Close the audio file: */
  if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile))
  {
     printf( "OCILobFileClose FAILED\n");
     return -1;
  }

  /* Clean up the temp LOB now that we are done with it: */ 
  if(check_and_free_temp(lob_loc, errhp, svchp,stmthp, envhp))
  {
      printf("check and free failed in load test\n");
      return -1;
  }
  return 0;
}

Example: Read Data from a Temporary LOB Using COBOL (Pro*COBOL)

      IDENTIFICATION DIVISION.
       PROGRAM-ID. ONE-READ-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(9) VALUES "SAMP/SAMP".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  BUFFER2        PIC X(32767) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  ORASLNRD       PIC 9(4).
       01  ISTEMP         PIC S9(9) COMP.

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

           EXEC SQL VAR BUFFER2 IS LONG RAW(32767) END-EXEC.

       PROCEDURE DIVISION.
       ONE-READ-BLOB.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locator:
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL LOB CREATE TEMPORARY :TEMP-BLOB END-EXEC. 

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
  
      * Set up the directory and file information:
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "Washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.

           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

           EXEC SQL 
              LOB DESCRIBE :SRC-BFILE GET LENGTH INTO :AMT
           END-EXEC.

      * Open source BFILE and destination temporary BLOB:
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
 
      * Perform a single read:

           EXEC SQL 
                LOB READ :AMT FROM :TEMP-BLOB INTO :BUFFER2 
           END-EXEC.
          
           DISPLAY "Read ", BUFFER2, " from TEMP-BLOB".

       END-OF-BLOB.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB FREE TEMPORARY :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.
        
       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Read Data from a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 1024

void readTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Length, Amount;
  struct {
    unsigned short Length;
    char Data[BufferLength];
  } Buffer;
  /* Datatype Equivalencing is Mandatory for this Datatype */
  EXEC SQL VAR Buffer IS VARRAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Initialize the BFILE Locator */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Determine the Length of the BFILE */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  /* Allocate and Create the Temporary LOB */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Open the BFILE for Reading */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Load the BFILE into the Temporary LOB */
  Amount = Length;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;
  /* Close the BFILE */
  EXEC SQL LOB CLOSE :Lob_loc;
  Buffer.Length = BufferLength;
  EXEC SQL WHENEVER NOT FOUND DO break;
  while (TRUE)
    {
      /* Read a piece of the Temporary LOB into the Buffer */
      EXEC SQL LOB READ :Amount FROM :Temp_loc INTO :Buffer;
      printf("Read %d bytes\n", Buffer.Length);
    }
  printf("Read %d bytes\n", Amount);
  /* Free the Temporary LOB */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators */
  EXEC SQL FREE :Temp_loc;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  readTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Read a Portion of the Temporary LOB (substr)

Figure 4-10 Use Case Diagram: Read a portion of the Temporary LOB from the Table (substr)

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This example shows the operation in terms of reading a portion from sound-effect Sound.

Example: Read a Portion of the Temporary LOB (substr) Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure substringTempLOB_proc is not part of the 
   DBMS_LOB package. */
/* This example assumes the user has a 'Washington_audio' file in a
   directory which has a AUDIO_DIR alias */
CREATE or REPLACE PROCEDURE substringTempLOB_proc IS
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount         INTEGER := 32767;
   Bbuf           RAW(32767);
   Position       INTEGER :=128;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
   /* Opening the FILE  is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Opening the LOB is optional */
   DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
   Bbuf := DBMS_LOB.SUBSTR(Dest_loc, Amount, Position);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.CLOSE(Dest_loc);
END;

Example: Read a Portion of the Temporary LOB (substr) Using COBOL (Pro*COBOL)

      IDENTIFICATION DIVISION.
       PROGRAM-ID. ONE-READ-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(9) VALUES "SAMP/SAMP".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  BUFFER2        PIC X(32767) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  ORASLNRD       PIC 9(4).
       01  ISTEMP         PIC S9(9) COMP.

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

           EXEC SQL VAR BUFFER2 IS LONG RAW(32767) END-EXEC.

       PROCEDURE DIVISION.
       ONE-READ-BLOB.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locator
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL LOB CREATE TEMPORARY :TEMP-BLOB END-EXEC. 

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
  
      * Set up the directory and file information
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "Washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.

           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

           EXEC SQL 
              LOB DESCRIBE :SRC-BFILE GET LENGTH INTO :AMT
           END-EXEC.

      * Open source BFILE and destination temporary BLOB.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
 
      * Perform a single read

           EXEC SQL 
                LOB READ :AMT FROM :TEMP-BLOB INTO :BUFFER2 
           END-EXEC.
          
           DISPLAY "Read ", BUFFER2, " from TEMP-BLOB".

       END-OF-BLOB.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB FREE TEMPORARY :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.
        
       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Read a Portion of the Temporary LOB (substr) Using C++ (Pro*C/C++)

/* Pro*C/C++ lacks an equivalent embedded SQL form for the DBMS_LOB.SUBSTR()
   function.  However, Pro*C/C++ can interoperate with PL/SQL using
   anonymous PL/SQL blocks embedded in a Pro*C/C++ program as this example
   shows. */

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 4096

void substringTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Position = 1024;
  unsigned int Length;
  int Amount = BufferLength;
  struct {
    unsigned short Length;
    char Data[BufferLength];
  } Buffer;
  /* Datatype Equivalencing is Mandatory for this Datatype: */
  EXEC SQL VAR Buffer IS VARRAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Open the LOBs: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Determine the length of the BFILE and load it into the Temporary LOB: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length;
  EXEC SQL LOB LOAD :Length FROM FILE :Lob_loc INTO :Temp_loc;
  /* Invoke SUBSTR() on the Temporary LOB inside a PL/SQL block: */
  EXEC SQL EXECUTE
    BEGIN
      :Buffer := DBMS_LOB.SUBSTR(:Temp_loc, :Amount, :Position);
    END;
  END-EXEC;
  /* Process the Data in the Buffer. */
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources used by the locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  substringTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Compare All or Part of Two (Temporary) LOBs

Figure 4-11 Use Case Diagram: Compare All or Part of Two Temporary LOBs

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

The following example compares two frames from the archival table VideoframesLib_tab to see whether they are different and, depending on the result of comparison, inserts the Frame into the Multimedia_tab.

Example: Compare All or Part of Two (Temporary) LOBs Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure compareTwoTemporPersistLOBs_proc is not part 
   of the DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE compareTwoTemporPersistLOBs_proc IS
    Lob_loc1 BLOB;
    Lob_loc2 BLOB;
    Temp_loc BLOB;
    Amount   INTEGER := 32767;
    Retval   INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT Frame INTO Lob_loc1 FROM Multimedia_tab
        WHERE Clip_ID = 1;
    SELECT Frame INTO Lob_loc2 FROM Multimedia_tab
        WHERE Clip_ID = 2;
    /* Copy a frame into a temp LOB and convert it to a different format */
    /* before comparing the frames : */
    DBMS_LOB.CREATETEMPORARY(Temp_loc, TRUE, DBMS_LOB.SESSION);
    DBMS_LOB.OPEN(Temp_loc, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.OPEN(Lob_loc2, DBMS_LOB.LOB_READONLY);
    /* Copy the persistent LOB into the temp LOB: */
    DBMS_LOB.COPY(Temp_loc,Lob_loc2,DBMS_LOB.GETLENGTH(Lob_loc2),1,1);
    /* Perform some conversion function on the temp LOB before comparing it*/
    /* ...some_conversion_format_function(Temp_loc); */
    retval := DBMS_LOB.COMPARE(Lob_loc1, Temp_loc, Amount, 1, 1);
    IF retval = 0 THEN
       DBMS_OUTPUT.PUT_LINE('Processing for equal frames');
    ELSE
       DBMS_OUTPUT.PUT_LINE('Processing for non-equal frames');
    END IF;
    DBMS_LOB.CLOSE(Temp_loc);
    DBMS_LOB.CLOSE(Lob_loc1);
    DBMS_LOB.CLOSE(Lob_loc2);
    /* Free the temporary LOB now that we are done using it: */
    DBMS_LOB.FREETEMPORARY(Temp_loc);
    END;

Example: Compare All or Part of Two (Temporary) LOBs Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BLOB-COMPARE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".

       01  BLOB1          SQL-BLOB.
       01  BLOB2          SQL-BLOB.
       01  TEMP-BLOB      SQL-BLOB.
       01  RET            PIC S9(9) COMP.
       01  AMT            PIC S9(9) COMP VALUE 5.
       01 ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.


       PROCEDURE DIVISION.
       BLOB-COMPARE.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL ALLOCATE :BLOB2 END-EXEC.
 
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
                SELECT FRAME INTO :BLOB1
                FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1
           END-EXEC.
 
           EXEC SQL 
                SELECT FRAME INTO :BLOB2
                FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 2
           END-EXEC.

      * Allocate and create a temporary LOB: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.
            
      * Open the BLOBs for READ ONLY, Open temp LOB READ/WRITE:
           EXEC SQL LOB OPEN :BLOB1 READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :BLOB2 READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
         
      * Copy data from BLOB2 to the temporary BLOB: 
           EXEC SQL
                LOB COPY :AMT FROM :BLOB2 TO :TEMP-BLOB
           END-EXEC.

      * Execute PL/SQL to use its COMPARE functionality: 
           MOVE 5 TO AMT.
           EXEC SQL EXECUTE
             BEGIN 
               :RET := DBMS_LOB.COMPARE(:BLOB1,:TEMP-BLOB,:AMT,1,1);
             END;
           END-EXEC.
           
           IF RET = 0
      *        Logic for equal BLOBs goes here
               DISPLAY "BLOBs are equal"
           ELSE
      *        Logic for unequal BLOBs goes here
               DISPLAY "BLOBs are not equal"
           END-IF.

           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.
           EXEC SQL LOB CLOSE :BLOB2 END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.
           EXEC SQL LOB FREE TEMPORARY :TEMP-BLOB END-EXEC.

           EXEC SQL FREE :TEMP-BLOB END-EXEC.

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL FREE :BLOB2 END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Compare All or Part of Two (Temporary) LOBs Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void compareTwoTempOrPersistLOBs_proc()
{
  OCIBlobLocator *Lob_loc1, *Lob_loc2, *Temp_loc;
  int Amount = 128;
  int Retval;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate the LOB locators: */
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  /* Select the LOBs: */
  EXEC SQL SELECT Frame INTO :Lob_loc1
     FROM Multimedia_tab WHERE Clip_ID = 1;
  EXEC SQL SELECT Frame INTO :Lob_loc2
     FROM Multimedia_tab WHERE Clip_ID = 2;
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc1 READ ONLY;
  EXEC SQL LOB OPEN :Lob_loc2 READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Copy the Persistent LOB into the Temporary LOB: */
  EXEC SQL LOB COPY :Amount FROM :Lob_loc2 TO :Temp_loc;
  /* Compare the two Frames using DBMS_LOB.COMPARE() from within PL/SQL: */
  EXEC SQL EXECUTE
     BEGIN
       :Retval := DBMS_LOB.COMPARE(:Lob_loc1, :Temp_loc, :Amount, 1, 1);
     END;
  END-EXEC;
  if (0 == Retval)
    printf("Frames are equal\n");
  else
    printf("Frames are not equal\n");
  /* Closing the LOBs is mandatory if you have opened them: */
  EXEC SQL LOB CLOSE :Lob_loc1;
  EXEC SQL LOB CLOSE :Lob_loc2;
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the locators: */
  EXEC SQL FREE :Lob_loc1;
  EXEC SQL FREE :Lob_loc2;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  compareTwoTempOrPersistLOBs_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

See If a Pattern Exists in a Temporary LOB (instr)

Figure 4-12 Use Case Diagram: See If a Pattern Exists in a Temporary LOB (instr)

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

The following example examines the storyboard text to see if the string "children" is present.

Example: See If a Pattern Exists in a Temporary LOB (instr) Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure instringTempLOB_proc is not part of the 
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE instringTempLOB_proc IS
   Lob_loc        CLOB;
   Temp_clob      CLOB;
   Pattern        VARCHAR2(30) := 'children';    Position       INTEGER := 0;
   Offset         INTEGER := 1;
   Occurrence     INTEGER := 1;
BEGIN
   /* Create the temp LOB and copy a CLOB into it: */
   DBMS_LOB.CREATETEMPORARY(Temp_clob,TRUE, DBMS_LOB.SESSION);
   SELECT Story INTO Lob_loc
      FROM Multimedia_tab
         WHERE Clip_ID = 1;

   DBMS_LOB.OPEN(Temp_clob,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Lob_loc,DBMS_LOB.LOB_READONLY);
   /* Copy the CLOB into the temp CLOB: */
   DBMS_LOB.COPY(Temp_clob,Lob_loc,DBMS_LOB.GETLENGTH(Lob_loc),1,1);
   /* Seek the pattern in the temp CLOB: */
   Position := DBMS_LOB.INSTR(Temp_clob, Pattern, Offset, Occurrence);
   IF Position = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Pattern not found');
   ELSE
      DBMS_OUTPUT.PUT_LINE('The pattern occurs at '|| position);
   END IF;
   DBMS_LOB.CLOSE(Lob_loc);
   DBMS_LOB.CLOSE(Temp_clob);
   /* Free the temporary LOB: */
   DBMS_LOB.FREETEMPORARY(Temp_clob);
END;

Example: See If a Pattern Exists in a Temporary LOB (instr) Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. CLOB-INSTR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".

       01  CLOB1          SQL-CLOB.
       01  TEMP-CLOB      SQL-CLOB.
       01  PATTERN        PIC X(8) VALUE "children".
       01  BUFFER2        PIC X(32767) VARYING.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
       01  OCCURRENCE     PIC S9(9) COMP VALUE 1.
       01  LEN            PIC S9(9) COMP.
       01  POS            PIC S9(9) COMP.
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

           EXEC SQL VAR BUFFER2 IS LONG RAW(32767) END-EXEC.

       PROCEDURE DIVISION.
       CLOB-INSTR.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the CLOB locator:
           EXEC SQL ALLOCATE :CLOB1 END-EXEC.
 
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC.
  
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC.
           EXEC ORACLE OPTION (SELECT_ERROR=NO) END-EXEC.
           EXEC SQL 
                SELECT STORY INTO :CLOB1
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1
           END-EXEC.
           EXEC SQL ALLOCATE :TEMP-CLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-CLOB
           END-EXEC.
 
      * Open the CLOB for READ ONLY: 
           EXEC SQL LOB OPEN :CLOB1 READ ONLY END-EXEC.

      * Use LOB describe to get the length of CLOB1: 
           EXEC SQL
                LOB DESCRIBE :CLOB1 GET LENGTH INTO :LEN
           END-EXEC.
           EXEC SQL
                LOB COPY :LEN FROM :CLOB1 TO :TEMP-CLOB
           END-EXEC.

      * Execute PL/SQL to get INSTR functionality: 
           EXEC SQL EXECUTE
             BEGIN 
               :POS := DBMS_LOB.INSTR(:TEMP-CLOB,:PATTERN,
                                      :OFFSET, :OCCURRENCE);
             END;
           END-EXEC.
           
           IF POS = 0
      *        Logic for pattern not found here
               DISPLAY "Pattern was not found"
           ELSE
      *        Pos contains position where pattern is found
               DISPLAY "Pattern was found"
           END-IF.

      * Close and free the LOBs: 
           EXEC SQL LOB CLOSE :CLOB1 END-EXEC.
           EXEC SQL FREE :TEMP-CLOB END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-CLOB
           END-EXEC.
           EXEC SQL FREE :TEMP-CLOB END-EXEC.

       END-OF-CLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :CLOB1 END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: See If a Pattern Exists in a Temporary LOB (instr) Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void instringTempLOB_proc()
{
  OCIClobLocator *Lob_loc, *Temp_loc;
  char *Pattern = "The End";
  unsigned int Length;
  int Position = 0;
  int Offset = 1;
  int Occurrence = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Initialize the Persistent LOB: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Story INTO :Lob_loc
     FROM Multimedia_tab WHERE Clip_ID = 1;
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Determine the Length of the Persistent LOB: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH into :Length;
  /* Copy the Persistent LOB into the Temporary LOB: */
  EXEC SQL LOB COPY :Length FROM :Lob_loc TO :Temp_loc;
  /* Seek the Pattern using DBMS_LOB.INSTR() in a PL/SQL block: */
  EXEC SQL EXECUTE
     BEGIN
        :Position := 
            DBMS_LOB.INSTR(:Temp_loc, :Pattern, :Offset, :Occurrence);
     END;
  END-EXEC;
  if (0 == Position)
    printf("Pattern not found\n");
  else
    printf("The pattern occurs at %d\n", Position);
  /* Closing the LOBs is mandatory if you have opened them: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  instringTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Get the Length of a Temporary LOB

Figure 4-13 Use Case Diagram: Get the length of a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

The following example gets the length of interview to see if it will run over the 4 gigabyte limit.

Example: Get the Length of a Temporary LOB Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure getLengthTempCLOB_proc is not part of the 
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE getLengthTempCLOB_proc IS
   Length      INTEGER;
   tlob        CLOB;
   bufc        VARCHAR2(8);
   Amount      NUMBER;
   pos         NUMBER;
   Src_loc     BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
BEGIN
    DBMS_LOB.CREATETEMPORARY(tlob,TRUE,DBMS_LOB.SESSION);
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN(tlob,DBMS_LOB.LOB_READWRITE);
    /* Opening the file is mandatory: */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    Amount := 32767;
    DBMS_LOB.LOADFROMFILE(tlob, Src_loc, Amount);
    /* Get the length of the LOB: */
    length := DBMS_LOB.GETLENGTH(tlob);
    IF length = 0 THEN
        DBMS_OUTPUT.PUT_LINE('LOB is empty.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The length is ' || length);
    END IF;
     /* Must close any lobs that were opened: */
    DBMS_LOB.CLOSE(tlob);
    DBMS_LOB.CLOSE(Src_loc);
    /* Free the temporary LOB now that we are done with it: */
    DBMS_LOB.FREETEMPORARY(tlob);
END;

Example: Get the Length of a Temporary LOB Using C (OCI)

/* This function takes a temporary LOB locator as an amount as argument and 
   prints out the length of the corresponding LOB. The function returns 
   0 if it completes successfully, and -1 if it fails.*/ 
sb4 print_length( OCIError      *errhp,
                  OCISvcCtx     *svchp,
                  OCIStmt       *stmthp,
                  OCIEnv        *envhp)

{
  ub4 length=0;
  ub4 amount = 4;
  ub4 pos = 1;
  OCILobLocator *bfile;
  OCILobLocator *tblob;
  sb4 return_code = 0;

  printf("in print_length\n");
  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob,
                        (ub4) OCI_DTYPE_LOB,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }

  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,
                        (ub4) OCI_DTYPE_FILE,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }

  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR", 
                       (ub2)strlen("AUDIO_DIR"), 
                       (text *)"Washington_audio", 
                       (ub2)strlen("Washington_audio")))
  {
     printf("OCILobFileSetName FAILED\n");
     return_code = -1;
  } 

  checkerr(errhp,(OCILobFileOpen(svchp, errhp, 
                                 (OCILobLocator *) bfile, 
                                  OCI_LOB_READONLY))); 

  /* Create a temporary BLOB: */
  if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, SQLCS_IMPLICIT,
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
      (void) printf("FAILED: CreateTemporary() \n");
      return_code = -1 ;
  }

  if(OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
      (void) printf("FAILED: Open Temporary \n");
      return_code = -1;
  }

 if(OCILobLoadFromFile(svchp, errhp, tblob,(OCILobLocator*)bfile,
                       (ub4)amount, (ub4)1,(ub4)1))
  {
      (void) printf("FAILED: Open Temporary \n");
      return_code = -1;
  }

  if (OCILobGetLength(svchp, errhp, tblob,&length))
  {
      printf ("FAILED: OCILobGetLength in print_length\n");
      return_code = -1;
  } 

  /* Close the bfile and the temp LOB */
  checkerr(errhp,OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile));

  checkerr(errhp,OCILobClose(svchp, errhp, (OCILobLocator *) tblob));

  /* Free the temporary LOB now that we are done using it: */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return_code = -1;
  }
  fprintf(stderr,"Length of LOB is %d\n",length);
  return return_code;
}

Example: Get the Length of a Temporary LOB Using COBOL (Pro*COBOL)

      IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-LENGTH.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP VALUE 10.
       01  LEN            PIC S9(9) COMP.
       01  LEN-D          PIC 9(4).
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       TEMP-LOB-LENGTH.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BFILE and BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
           
      * Get the length of the temporary LOB: 
           EXEC SQL 
                LOB DESCRIBE :TEMP-BLOB GET LENGTH INTO :LEN
           END-EXEC.
           MOVE LEN TO LEN-D.
           DISPLAY "Length of TEMPORARY LOB is ", LEN-D.        
      * Close the LOBs: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.

      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.

      * And free the LOB locators: 
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Get the Length of a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void getLengthTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Length, Amount;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Load a specified amount from the BFILE into the Temporary LOB */
  Amount = 4096;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;
  /* Get the length of the Temporary LOB: */
  EXEC SQL LOB DESCRIBE :Temp_loc GET LENGTH INTO :Length;
  /* Note that in this example, Length == Amount == 4096: */
  printf("Length is %d bytes\n", Length);
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  getLengthTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Copy All or Part of One (Temporary) LOB to Another

Figure 4-14 Use Case Diagram: Copy All or Part of One (Temporary) LOB to Another

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

Assume the following table:

CREATE TABLE VoiceoverLib_tab of VOICED_TYP;

Note that this VoiceoverLib_tab is of the same type as the Voiceover_tab which is referenced by the Voiced_ref column of the multimedia table.

INSERT INTO Voiceover_tab 
    (SELECT * FROM VoiceoverLib_tab Vtab1
       WHERE T2.Take = 101);

creates a new LOB locator in the table Voiceover_tab, and copies the LOB data from Vtab1 to the location pointed to by a new LOB locator which is inserted into table Voiceover_tab.

Example: Copy All or Part of One (Temporary) LOB to Another Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure copyTempLOB_proc is not part of the 
   DBMS_LOB package.* /
CREATE OR REPLACE PROCEDURE copyTempLOB_proc IS
   Dest_pos     NUMBER;
   Src_pos      NUMBER;
   Dest_loc     BLOB;
   Dest_loc2    BLOB;
   Src_loc      BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount       INTEGER := 32767;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE,DBMS_LOB.SESSION);
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
   /* Opening the FILE  is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Opening the temporary  LOBs is optional: */
   DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Dest_loc2,DBMS_LOB.LOB_READWRITE);

     DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
       /* Set Dest_pos to the position at which we should start writing in the
          target temp LOB */
       /* Copies the LOB from the source position to the destination
          position:*/
       /* Set amount to the amount you want copied */
          Amount := 328;
          Dest_pos := 1000;
          Src_pos := 1000;
       /* Set Src_pos to the position from which we should start copying data
          from  tclob_src: */
          DBMS_LOB.COPY(Dest_loc2,Dest_loc, Amount, Dest_pos, Src_pos);
     COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
      DBMS_LOB.CLOSE(Dest_loc);
      DBMS_LOB.CLOSE(Dest_loc2);
      DBMS_LOB.CLOSE(Src_loc);
      DBMS_LOB.FREETEMPORARY(Dest_loc);
      DBMS_LOB.FREETEMPORARY(Dest_loc2);
END;

Example: Copy All or Part of One (Temporary) LOB to Another Using C (OCI)

/* This function takes two temporary LOB locators as arguments and copies 4000 
   bytes from one temporary LOB to another. It reads the source LOB starting at 
   offset 1, and writes to the destination at offset 2. The function returns 
   0 if it completes successfully, and -1 otherwise. */
sb4 copy_temp_lobs (OCILobLocator *lob_loc, 
                    OCIError      *errhp,
                    OCISvcCtx     *svchp,
                    OCIStmt       *stmthp,
                    OCIEnv        *envhp)
{
  OCIDefine *defnp1;
  OCILobLocator *tblob;
  OCILobLocator *tblob2;
  OCILobLocator *bfile;
  int rowind =1;
  ub4 amount=4000;
  ub4 src_offset=1;
  ub4 dest_offset=2;
  sb4 return_code = 0;

  printf("in copy_temp_lobs \n");

  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob,
                        (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in copy_temp_lobs\n");
    return -1;
  }

  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&bfile,
                        (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in copy_temp_lobs\n");
    return -1;
  }

  if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, SQLCS_IMPLICIT,
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 

  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob2,
                        (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in copy_temp_lobs\n");
    return_code =  -1;
  }

  if(OCILobCreateTemporary(svchp, errhp, tblob2, (ub2)0,  SQLCS_IMPLICIT,
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return_code = -1;
  } 
 
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),
                       (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED\n");
    return_code = -1;
  }
   
  if(OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))
  {
    printf( "OCILobFileOpen FAILED  for the bfile\n");
    return_code = -1;
  }

  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return_code =  -1;
  }
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob2, OCI_LOB_READWRITE ))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return_code =  -1;
  }
   
  if(OCILobLoadFromFile(svchp, errhp, tblob, (OCILobLocator*)bfile,
                        (ub4)amount, (ub4)1,(ub4)1))
  {
    printf( "OCILobLoadFromFile FAILED\n");
    return_code = -1;
  }

 if (OCILobCopy(svchp, errhp, tblob2, tblob,  amount, dest_offset,
                src_offset))
  {
    printf ("FAILED: OCILobCopy in copy_temp_lobs\n");
    return -1;
  }
  /* Close LOBs here */

  if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile))
  {
    printf( "OCILobFileClose FAILED for bfile \n");
    return_code =  -1;
  }
  if (OCILobClose(svchp, errhp, (OCILobLocator *) tblob))
  {
    printf( "OCILobClose FAILED for temporary LOB \n");
    return_code =  -1;
  }
  if (OCILobClose(svchp, errhp, (OCILobLocator *) tblob2))
  {
    printf( "OCILobClose FAILED for temporary LOB \n");
    return_code =  -1;
  }
  /* free the temporary lobs now that we are done using them */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return_code = -1;
  }
  if(OCILobFreeTemporary(svchp, errhp, tblob2))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return_code = -1;
  }
  return return_code;
}

Example: Copy All or Part of One (Temporary) LOB to Another Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-BLOB-COPY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".

       01  TEMP-DEST      SQL-BLOB.
       01  TEMP-SRC       SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) VARYING.
       01  AMT            PIC S9(9) COMP.

      * Define the source and destination position and location: 
       01  SRC-POS        PIC S9(9) COMP VALUE 1.
       01  DEST-POS       PIC S9(9) COMP VALUE 1.

       01 ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       TEMP-BLOB-COPY.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators:
           EXEC SQL ALLOCATE :TEMP-DEST END-EXEC.
           EXEC SQL ALLOCATE :TEMP-SRC END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-DEST
           END-EXEC. 
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-SRC
           END-EXEC. 

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-SRC READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :TEMP-DEST READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

      * MOVE the desired amount to copy to AMT: 
           MOVE 5 TO AMT.
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-SRC
           END-EXEC.
           
      * Copy data from BFILE to temporary LOB: 
           EXEC SQL 
                LOB COPY :AMT FROM :TEMP-SRC AT :SRC-POS
                TO :TEMP-DEST AT :DEST-POS
           END-EXEC.

           EXEC SQL LOB CLOSE :TEMP-SRC END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-DEST END-EXEC.
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-SRC
           END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-DEST
           END-EXEC.
           EXEC SQL FREE :TEMP-SRC END-EXEC.
           EXEC SQL FREE :TEMP-DEST END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Copy All or Part of One (Temporary) LOB to Another Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void copyTempLOB_proc()
{
  OCIBlobLocator *Temp_loc1, *Temp_loc2;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOBs: */
  EXEC SQL ALLOCATE :Temp_loc1;
  EXEC SQL ALLOCATE :Temp_loc2;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc1;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc2;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc1 READ WRITE;
  EXEC SQL LOB OPEN :Temp_loc2 READ WRITE;
  /* Load a specified amount from the BFILE into one of the
     Temporary LOBs: */
  Amount = 4096;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc1;
  /* Copy a specified amount from one Temporary LOB to another: */
  EXEC SQL LOB COPY :Amount FROM :Temp_loc1 TO :Temp_loc2;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Temp_loc1;
  EXEC SQL LOB CLOSE :Temp_loc2;
  EXEC SQL LOB CLOSE :Lob_loc;
  /* Free the Temporary LOBs: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc1;
  EXEC SQL LOB FREE TEMPORARY :Temp_loc2;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Temp_loc1;
  EXEC SQL FREE :Temp_loc2;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  copyTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Copy a LOB Locator for a Temporary LOB

Figure 4-15 Use Case Diagram: Copy a LOB Locator for a Temporary LOB...208

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This generic operation copies one temporary LOB locator to another.

Example: Copy a LOB Locator (Temporary LOBs) Using PL/SQL

Note:
Assigning one LOB to another using PL/SQL entails using the "=" sign. This is an advanced topic that is discussed in more detail above with regard to "Read-Consistent Locators".

/* Note that the example procedure copyTempLOBLocator_proc is not part of the 
   DBMS_LOB package. */

CREATE OR REPLACE PROCEDURE copyTempLOBLocator_proc(
   Lob_loc1 IN OUT CLOB, Lob_loc2 IN OUT CLOB) IS

   bufp     VARCHAR2(4);
   Amount   NUMBER  := 32767;
   Src_loc  BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
BEGIN
    DBMS_LOB.CREATETEMPORARY(Lob_loc1,TRUE,DBMS_LOB.SESSION);
    DBMS_LOB.CREATETEMPORARY(Lob_loc2,TRUE,DBMS_LOB.SESSION);
    /* Populate the first temporary LOB with some data. */
    /* Opening file is mandatory: */
    DBMS_LOB.OPEN(Src_loc,DBMS_LOB.LOB_READONLY);
    /* Opening LOB is optional: */
    DBMS_LOB.OPEN(Lob_loc1,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(Lob_loc2,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.LOADFROMFILE(Lob_loc1,Src_loc,Amount);

   /* Assign Lob_loc1 to Lob_loc2 thereby creating  a copy of the value of
      the temporary LOB referenced by Lob_loc1 at this point in time: */
   Lob_loc2 := Lob_loc1;

   /* When you write some data to the LOB through Lob_loc1, Lob_loc2
      will not see the newly written data whereas Lob_loc1 will see
      the new data: */
   /*Closing LOBs is mandatory if they were opened: */
    DBMS_LOB.CLOSE (Src_loc);
    DBMS_LOB.CLOSE (Lob_loc1);
    DBMS_LOB.CLOSE (Lob_loc2);
    DBMS_LOB.FREETEMPORARY(Lob_loc1);
    DBMS_LOB.FREETEMPORARY(Lob_loc2);
END;

Example: Copy a LOB Locator for a Temporary LOB Using C (OCI)

/* This function creates two temporary lobs. It populates one and 
   then copies the locator of that one to the other temporary 
   LOB locator: */ 

sb4 copy_locators( OCIError    *errhp,
                   OCISvcCtx   *svchp, 
                   OCIEnv      *envhp)
{
  sb4 return_code = 0;
  OCILobLocator *tblob;
  OCILobLocator *tblob2;
  OCILobLocator *bfile;
  ub4 amount = 4000;

  checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob,                                                                   
                                      (ub4) OCI_DTYPE_LOB, 
                                      (size_t) 0, (dvoid **) 0)); 

  checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob2,                                                             
                                      (ub4) OCI_DTYPE_LOB, 
                                      (size_t) 0, (dvoid **) 0)); 

  checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,                                                 
                                      (ub4) OCI_DTYPE_FILE, 
                                      (size_t) 0, (dvoid **) 0)); 

  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),
                       (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED in load_temp\n");
    return -1;
  }

  if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_FILE_READONLY))
  {
    printf( "OCILobFileOpen FAILED for the bfile load_temp \n");
    return -1;
  }
 

  if(OCILobCreateTemporary(svchp,errhp, tblob,(ub2)0, SQLCS_IMPLICIT, 
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return -1;
  }
 
  if(OCILobCreateTemporary(svchp,errhp, tblob2,(ub2)0, SQLCS_IMPLICIT, 
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return -1;
  }
 
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return -1;
  }

  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob2, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return -1;
  }

  if(OCILobLoadFromFile(svchp, errhp, tblob, (OCILobLocator*)bfile,
                        (ub4)amount, (ub4)1,(ub4)1))
  {
    printf("OCILobLoadFromFile failed \n");
    return_code = -1;
  }

  if(OCILobLocatorAssign(svchp,errhp, (CONST OCILobLocator *)tblob,&tblob2))
  {

    printf("OCILobLocatorAssign failed \n");
    return_code = -1;
  }

  /* Close the lobs */
  if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile))
  {
    printf( "OCILobClose FAILED for bfile \n");
    return -1;
  }

  checkerr(errhp,(OCILobClose(svchp, errhp, (OCILobLocator *) tblob)));
  checkerr(errhp,(OCILobClose(svchp, errhp, (OCILobLocator *) tblob2)));
  

  /* Free the temporary lobs now that we are done using it */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }

  if(OCILobFreeTemporary(svchp, errhp, tblob2))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }
}

Example: Copy a LOB Locator for a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-BLOB-COPY-LOCATOR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
        
       01  TEMP-DEST      SQL-BLOB.
       01  TEMP-SRC       SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) VARYING.
       01  AMT            PIC S9(9) COMP.

       01 ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       TEMP-BLOB-COPY-LOCATOR.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-DEST END-EXEC.
           EXEC SQL ALLOCATE :TEMP-SRC END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-DEST
           END-EXEC. 
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-SRC
           END-EXEC. 
  
      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-SRC READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :TEMP-DEST READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

      * MOVE the desired amount to copy to AMT: 
           MOVE 5 TO AMT.
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-SRC
           END-EXEC.
           
      * Assign source BLOB locator to destination BLOB locator: 
           EXEC SQL 
                LOB ASSIGN :TEMP-SRC TO :TEMP-DEST
           END-EXEC.

           EXEC SQL LOB CLOSE :TEMP-SRC END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-DEST END-EXEC.
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-SRC
           END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-DEST
           END-EXEC.
           EXEC SQL FREE :TEMP-SRC END-EXEC.
           EXEC SQL FREE :TEMP-DEST END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Copy a LOB Locator for a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void copyTempLobLocator_proc()
{
  OCIBlobLocator *Temp_loc1, *Temp_loc2;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount = 4096;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOBs: */
  EXEC SQL ALLOCATE :Temp_loc1;
  EXEC SQL ALLOCATE :Temp_loc2;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc1;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc2;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc1 READ WRITE;
  EXEC SQL LOB OPEN :Temp_loc2 READ WRITE;
  /* Load a specified amount from the BFILE into the Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc1;
  /* Assign Temp_loc1 to Temp_loc2 thereby creating a copy of the value of
     the Temporary LOB referenced by Temp_loc1 at this point in time: */
  EXEC SQL LOB ASSIGN :Temp_loc1 TO :Temp_loc2;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc1;
  EXEC SQL LOB CLOSE :Temp_loc2;
  /* Free the Temporary LOBs: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc1;
  EXEC SQL LOB FREE TEMPORARY :Temp_loc2;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc1;
  EXEC SQL FREE :Temp_loc2;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  copyTempLobLocator_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

See If One LOB Locator for a Temporary LOB Is Equal to Another

Figure 4-16 Use Case Diagram: See If One (Temporary) LOB Locator Is Equal to Another

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

If two locators are equal, this means that they refer to the same version of the LOB data (see "Read-Consistent Locators" )

Example: See If One LOB Locator for a Temporary LOB Is Equal to Another Using C (OCI)

sb4 ck_isequal (OCILobLocator *lob_loc, 
                OCIError      *errhp,
                OCISvcCtx     *svchp,
                OCIStmt       *stmthp,
                OCIEnv        *envhp)
{
  OCILobLocator *loc1;f
  OCILobLocator *loc2;
  boolean is_equal;
  is_equal= FALSE;
  if(OCILobCreateTemporary(svchp, errhp, loc1, (ub2)0, SQLCS_IMPLICIT,
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 
   if(OCILobCreateTemporary(svchp, errhp, loc2, (ub2)0, SQLCS_IMPLICIT,
                            OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                            OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 

  if (OCILobIsEqual(envhp,loc1,loc2, &is_equal))
  {
    printf ("FAILED: OCILobLocatorIsEqual call\n");
    return -1;
  }
  if(is_equal)
  {
      fprintf (stderr,"LOB loators are equal \n");
      return -1;
 
   }else
   {
      fprintf(stderr,"LOB locators are not equal \n");
   }
   if(OCILobFreeTemporary(svchp,errhp,loc1))
   {
        printf("FAILED: OCILobFreeTemporary for temp LOB #1\n");
        return -1;
   }
  if(OCILobFreeTemporary(svchp,errhp,loc2))
   {
        printf("FAILED: OCILobFreeTemporary for temp LOB #2\n");
        return -1;
   }

   return 0;
}

Example: See If One LOB Locator for a Temporary LOB Is Equal to Another Using C++ (Pro*C/C++)

#include <sql2oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("sqlcode = %ld\n", sqlca.sqlcode);
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void seeTempLobLocatorsAreEqual_proc()
{
  OCIBlobLocator *Temp_loc1, *Temp_loc2; 
  OCIBFileLocator *Lob_loc; 
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio"; 
  int Amount = 4096; 
  OCIEnv *oeh; 
  int isEqual = 0; 

  EXEC SQL WHENEVER SQLERROR DO Sample_Error(); 
  /* Allocate and Create the Temporary LOBs: */ 
  EXEC SQL ALLOCATE :Temp_loc1; 
  EXEC SQL ALLOCATE :Temp_loc2; 
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc1; 
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc2; 
  /* Allocate and Initialize the BFILE Locator: */ 
  EXEC SQL ALLOCATE :Lob_loc; 
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name; 
  /* Opening the LOBs is Optional: */ 
  EXEC SQL LOB OPEN :Lob_loc READ ONLY; 
  EXEC SQL LOB OPEN :Temp_loc1 READ WRITE; 
  EXEC SQL LOB OPEN :Temp_loc2 READ WRITE; 
  /* Load a specified amount from the BFILE into one of the Temporary LOBs: */ 
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc1; 
  /* Retrieve the OCI Environment Handle: */ 
  (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh); 
  /* Now assign Temp_loc1 to Temp_loc2 using Embedded SQL: */ 
  EXEC SQL LOB ASSIGN :Temp_loc1 TO :Temp_loc2; 
  /* Determine if the Temporary LOBs are Equal: */ 
  (void) OCILobIsEqual(oeh, Temp_loc1, Temp_loc2, &isEqual); 
  /* This time, isEqual should be 0 (FALSE): */ 
  printf("Locators %s equal\n", isEqual ? "are" : "are not"); 
  /* Assign Temp_loc1 to Temp_loc2 using C pointer assignment: */ 
  Temp_loc2 = Temp_loc1; 
  /* Determine if the Temporary LOBs are Equal again: */ 
  (void) OCILobIsEqual(oeh, Temp_loc1, Temp_loc2, &isEqual); 
  /* The value of isEqual should be 1 (TRUE) in this case: */ 
  printf("Locators %s equal\n", isEqual ? "are" : "are not"); 
  /* Closing the LOBs is Mandatory if they have been Opened: */ 
  EXEC SQL LOB CLOSE :Lob_loc;    
  /* Note that because Temp_loc1 and Temp_loc2 are now equal, closing
     and freeing one will implicitely do the same to the other: */
  EXEC SQL LOB CLOSE :Temp_loc1;  
  EXEC SQL LOB FREE TEMPORARY :Temp_loc1;  
  /* Release resources held by the Locators: */ 
  EXEC SQL FREE :Lob_loc; 
  EXEC SQL FREE :Temp_loc1; 
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  seeTempLobLocatorsAreEqual_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

See If a LOB Locator for a Temporary LOB Is Initialized

Figure 4-17 Use Case Diagram: See If a LOB Locator for a Temporary LOB Is Initialized

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This generic function takes a LOB locator and checks if it is initialized. If it is initialized, then it prints out a message saying "LOB is initialized". Otherwise, it reports "LOB is not initialized".

Example: See If a LOB Locator for a Temporary LOB Is Initialized Using C (OCI)

/* This function takes a LOB locator and checks if it is initialized. If it is 
   initalized, then it prints out a message saying "LOB is initialized". 
   Otherwise, it says "LOB is not initialized". This function returns 
   0 if it completes successfully, and -1 if it doesn't. */

sb4 ck_isinit (OCILobLocator *lob_loc, 
               OCIError      *errhp,
               OCISvcCtx     *svchp,
               OCIStmt       *stmthp,
               OCIEnv        *envhp)

{
  boolean is_init;
 
  is_init= FALSE;
  if (OCILobLocatorIsInit(envhp,errhp, lob_loc, &is_init))
  {
    printf ("FAILED: OCILobLocatorIsInit call\n");
    return -1;
  }
  if(is_init)
  {
      printf ("LOB is initialized\n");
 
  }else
  {
      printf("LOB is not initialized\n");
   }
   return 0;
}

Example: See If a LOB Locator for a Temporary LOB Is Initialized Using C++ (Pro*C/C++)

#include <sql2oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}
void tempLobLocatorIsInit_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIEnv *oeh;
  OCIError *err;
  boolean isInitialized = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Get the OCI Environment Handle using a SQLLIB Routine: */
  (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh);
  /* Allocate the OCI Error Handle: */
  (void) OCIHandleAlloc((dvoid *)oeh, (dvoid **)&err,
                        (ub4)OCI_HTYPE_ERROR, (ub4)0, (dvoid **)0);
  /* Use the OCI to determine if the locator is Initialized */
  (void) OCILobLocatorIsInit(oeh, err, Temp_loc, &isInitialized);
  if (isInitialized)
    printf("Locator is initialized\n");
  else
    printf("Locator is not initialized\n");
  /* Note that in this example, the locator is initialized. */
  /* Deallocate the OCI Error Handle: */
  (void) OCIHandleFree(err, OCI_HTYPE_ERROR);
  /* Free the Temporary LOB */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the locator: */
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  tempLobLocatorIsInit_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Get Character Set ID of a Temporary LOB

Figure 4-18 Use Case Diagram: Get Character Set ID for a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This function takes a LOB locator and prints the character set id of the LOB.

"Example: Get Character Set ID of a Temporary LOB Using C (OCI)"

Example: Get Character Set ID of a Temporary LOB Using C (OCI)

/* This function takes a LOB locator and prints the character set id of the LOB. 
   This function returns 0 if it completes successfully, and -1 
   if it doesn't. */

sb4 get_charsetid (OCILobLocator *lob_loc, 
                   OCIError      *errhp,
                   OCISvcCtx     *svchp,
                   OCIStmt       *stmthp,
                   OCIEnv        *envhp)
{
  ub2 charsetid=199;
  if(OCILobCreateTemporary(svchp, errhp, lob_loc, (ub2)0,  SQLCS_IMPLICIT,
                           OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 
 
  if (OCILobCharSetId(envhp, errhp, lob_loc, &charsetid))
  {
    printf ("FAILED: OCILobCharSetId call\n");
    return -1;
  }
  fprintf (stderr,"LOB charsetid is %d\n",charsetid);
  if(OCILobFreeTemporary(svchp,errhp,lob_loc))
  {
        printf("FAILED: OCILobFreeTemporary \n");
        return -1;
  }

  return 0;
 
}

Get Character Set Form of a Temporary LOB

Figure 4-19 Use Case Diagram: Get Character Set Form of a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This function takes a LOB locator and prints the character set form for the LOB.

"Example: Get Character Set Form of a Temporary LOB Using C (OCI)"

Example: Get Character Set Form of a Temporary LOB Using C (OCI)

/* This function takes a LOB locator and prints out the character set form for 
the LOB. It returns 0 if it completes successfully, and it returns -1 if it 
doesn't. */ 

sb4 get_charsetform (OCILobLocator *lob_loc, 
                     OCIError      *errhp,
                     OCISvcCtx     *svchp,
                     OCIStmt       *stmthp,
                     OCIEnv        *envhp)
{
  ub1 charsetform =0;
  if(OCILobCreateTemporary(svchp,errhp,lob_loc,(ub2)0, 
                           SQLCS_IMPLICIT, OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 
 
  if (OCILobCharSetForm(envhp,errhp, lob_loc, &charsetform))
  {
    printf ("FAILED: OCILobCharSetForm call\n");
    return -1;
  }
  fprintf (stderr,"LOB charsetform is %d\n",charsetform);
 
  if(OCILobFreeTemporary(svchp,errhp,lob_loc))
  {
        printf("FAILED: OCILobFreeTemporary \n");
        return -1;
  } 
  return 0;
}

Append One (Temporary) LOB to Another

Figure 4-20 Use Case Diagram: Append one (Temporary) LOB to another

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This example deals with the task of appending one segment of sound to another. We assume that you use sound-specific editing tools to match the wave-forms.

Example: Append One (Temporary) LOB to Another Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure appendTempLOB_proc is not part of the 
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE appendTempLOB_proc IS
   Dest_loc2 CLOB;
   Dest_loc  CLOB;
   Amount    NUMBER;
   Src_loc   BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE,DBMS_LOB.SESSION);
   DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE,DBMS_LOB.SESSION);
   DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Dest_loc2,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Src_loc,DBMS_LOB.LOB_READWRITE);
   Amount := 32767;
   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
   DBMS_LOB.LOADFROMFILE(Dest_loc2, Src_loc, Amount);
   DBMS_LOB.APPEND(Dest_loc, Dest_loc2);
   /* Close the temporary lobs and then free them: */
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Dest_loc2);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.FREETEMPORARY(Dest_loc);
   DBMS_LOB.FREETEMPORARY(Dest_loc2);
END;

Example: Append One (Temporary) LOB to Another Using C (OCI)

/* This function takes two temporary LOB locators and appends the second LOB to 
   the first one. It returns 0 if it completes successfully, and 
   -1, otherwise.*/

sb4 append_temp_lobs (OCIError      *errhp,
                      OCISvcCtx     *svchp,
                      OCIStmt       *stmthp,
                      OCIEnv        *envhp)
{
  OCILobLocator *tblob;
  OCILobLocator *tblob2;
  OCILobLocator *bfile;
  ub4 amt = 4000;
  sb4 return_code = 0;

  printf("in append \n");
  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob,
                        (ub4) OCI_DTYPE_LOB,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }
  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob2,
                        (ub4) OCI_DTYPE_LOB,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }

  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,
                        (ub4) OCI_DTYPE_FILE,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }

  /* Set the BFILE to point to the Washington_audio file */ 
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),
                       (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED\n");
    return -1;
  }
   
  if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))
  {
    printf( "OCILobFileOpen FAILED  for the bfile\n");
    return_code = -1;
  }
 
  if(OCILobCreateTemporary(svchp,errhp,tblob,(ub2)0, SQLCS_IMPLICIT,
                           OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return_code = -1;
  } 

  if(OCILobCreateTemporary(svchp,errhp,tblob2,(ub2)0, SQLCS_IMPLICIT,
                           OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return_code = -1;
  } 

  /* Open the lobs: */
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB tblob \n");
    return_code =  -1;
  }

  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob2, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB, tblob2 \n");
    return_code =  -1;
  }

  /* Populate the source temporary LOB with some data: */

  If(OCILobLoadFromFile(svchp, errhp, tblob,(OCILobLocator*)bfile,
                       (ub4)amt, (ub4)1,(ub4)1))
  {
    printf( "OCILobLoadFromFile FAILED\n");
    return_code = -1;
  }

  /* Append the source LOB to the dest temp LOB: */
  if (OCILobAppend(svchp, errhp,tblob2,tblob))
  {
    printf ("FAILED: OCILobAppend in append_temp_lobs\n");
    return_code = -1;
  }else 
  {
    printf("Append succeeded\n");
  }

  if(OCILobFreeTemporary(svchp,errhp,tblob))
  {
        printf("FAILED: OCILobFreeTemporary \n");
        return_code = -1;
  } 
 if(OCILobFreeTemporary(svchp,errhp,tblob2))
 {
      printf("FAILED: OCIlobFreeTemporary\n");
      return_code = -1;
 }
 return return_code;
}

Example: Append One (Temporary) LOB to Another Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. APPEND-TEMP-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

      * Define the username and password: 
       01  USERID   PIC X(11) VALUES "USER1/USER1".

      * Define the temporary LOBs and the source BFILE: 
       01  TEMP-BLOB1     SQL-BLOB.
       01  TEMP-BLOB2     SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  AMT            PIC S9(9) COMP.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) VARYING.

      * Define the source position in BFILE: 
       01  SRC-POS        PIC S9(9) COMP.
 
      * Define the line number in case of error: 
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       APPEND-TEMP-BLOB.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB1 END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB2 END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB1
           END-EXEC. 
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB2
           END-EXEC. 

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-BLOB2 READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :TEMP-BLOB1 READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.
           DISPLAY "LOBs opened.".

      * Move the desired amount to copy to AMT: 
           MOVE 5 TO AMT.
           MOVE 1 TO SRC-POS.
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE 
                AT :SRC-POS INTO :TEMP-BLOB1
           END-EXEC.
           
           ADD 1 TO AMT GIVING SRC-POS.
           EXEC SQL 
                LOB LOAD :AMT FROM FILE :SRC-BFILE
                AT :SRC-POS INTO :TEMP-BLOB2 
           END-EXEC.
           DISPLAY "Temporary LOBs loaded".
           
           EXEC SQL
                LOB APPEND :TEMP-BLOB2 TO :TEMP-BLOB1
           END-EXEC.
           DISPLAY "LOB APPEND complete.".

           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB1
           END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB2
           END-EXEC.
           EXEC SQL FREE :TEMP-BLOB1 END-EXEC.
           EXEC SQL FREE :TEMP-BLOB2 END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Append One (Temporary) LOB to Another Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void appendTempLOB_proc()
{
  OCIBlobLocator *Temp_loc1, *Temp_loc2;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount = 2048;
  int Position = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOBs: */
  EXEC SQL ALLOCATE :Temp_loc1;
  EXEC SQL ALLOCATE :Temp_loc2;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc1;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc2;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc1 READ WRITE;
  EXEC SQL LOB OPEN :Temp_loc2 READ WRITE;
  /* Load a specified amount from the BFILE into the first Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc AT :Position INTO :Temp_loc1;
  /* Set the Position for the next load from the same BFILE: */
  Position = Amount + 1;
  /* Load a second amount from the BFILE into the second Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc AT :Position INTO :Temp_loc2;
  /* Append the second Temporary LOB to the end of the first one: */
  EXEC SQL LOB APPEND :Temp_loc2 TO :Temp_loc1;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc1;
  EXEC SQL LOB CLOSE :Temp_loc2;
  /* Free the Temporary LOBs: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc1;
  EXEC SQL LOB FREE TEMPORARY :Temp_loc2;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc1;
  EXEC SQL FREE :Temp_loc2;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  appendTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Write Append to a Temporary LOB

Figure 4-21 Use Case Diagram: Write Append to a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

This example procedure will read in 32767 bytes of data from the Washington_audio file starting at offset 128 and append it to a temporary LOB.

Example: Write Append to a Temporary LOB Using PL/SQL

/* Note that the example procedure writeAppendTempLOB_proc is not part of the 
   DBMS_LOB package. This example procedure will read in 32767 bytes of  data
   from the Washington_audio file starting at offset 128 and append it to a
   temporary LOB. */

CREATE OR REPLACE PROCEDURE writeAppendTempLOB_proc IS
   Lob_loc    BLOB;
   Buffer     RAW(32767);
   Src_loc    BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount     Binary_integer := 32767;
   Position   Binary_integer := 128;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
   /* Opening the temporary LOB is optional: */
   DBMS_LOB.OPEN(Lob_loc,DBMS_LOB.LOB_READWRITE);
   /* Opening the FILE is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Fill the buffer with data: */
   DBMS_LOB.LOADFROMFILE (Lob_loc,Src_loc, Amount);

   /* Append the data from the buffer to the end of the LOB: */
   DBMS_LOB.WRITEAPPEND(Lob_loc, Amount, Buffer);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.CLOSE(Lob_loc);
   DBMS_LOB.FREETEMPORARY(Lob_loc);
END;

Example: Write Append to a Temporary LOB Using C (OCI)

sb4 write_append_temp_lobs (OCIError      *errhp,
                            OCISvcCtx     *svchp,
                            OCIStmt       *stmthp,
                            OCIEnv        *envhp)
{
  OCIClobLocator *tclob;
  unsigned int Total = 40000;
  unsigned int amtp;
  unsigned int  nbytes;
  ub1 bufp[MAXBUFLEN];

  /* Allocate the locators desriptors: */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &tclob ,
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  if(OCILobCreateTemporary(svchp, errhp, tclob, (ub2)0,  SQLCS_IMPLICIT,
           OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 

  /* Open the CLOB */
  printf("calling open \n");
  checkerr (errhp, (OCILobOpen(svchp, errhp, tclob, OCI_LOB_READWRITE)));

  nbytes = MAXBUFLEN;   /* We will use Streaming via Standard Polling */
  
  /* Fill the Buffer with nbytes worth of Data */
  memset(bufp,'a',32767);
  
  amtp = sizeof(bufp);
  /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE */
  
  printf("calling write append \n");
  checkerr (errhp, OCILobWriteAppend (svchp, errhp, tclob, &amtp, 
                                      bufp, nbytes, OCI_ONE_PIECE, (dvoid *)0, 
                                      (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                                       0, SQLCS_IMPLICIT));
  
  printf("calling close \n");
  /* Closing the LOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, tclob));

  /* Free the temporary LOB: */
  printf("calling free\n");
  checkerr(errhp,OCILobFreeTemporary(svchp,errhp,tclob));

  /* Free resources held by the locators: */
  (void) OCIDescriptorFree((dvoid *) tclob, (ub4) OCI_DTYPE_LOB);
}

Example: Write Append to a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. WRITE-APPEND-TEMP.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  BUFFER         PIC X(2048).
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP VALUE 10.
           EXEC SQL VAR BUFFER IS RAW(2048) END-EXEC.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       WRITE-APPEND-TEMP.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BFILE and BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
           
        MOVE "262626" TO BUFFER.
        MOVE 3 TO AMT. 
      * Append the data in BUFFER to TEMP-BLOB: 
           EXEC SQL 
                LOB WRITE APPEND :AMT FROM :BUFFER INTO :TEMP-BLOB      
           END-EXEC.

      * Close the LOBs: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.

      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.

      * And free the LOB locators: 
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Write Append to a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 256

void writeAppendTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount;
  struct {
    unsigned short Length;
    char Data[BufferLength];
  } Buffer;
  EXEC SQL VAR Buffer IS VARRAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Load a specified amount from the BFILE into the Temporary LOB: */
  Amount = 2048;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;
  strcpy((char *)Buffer.Data, "afafafafafaf");
  Buffer.Length = 6;
  /* Write the contents of the Buffer to the end of the Temporary LOB: */
  Amount = Buffer.Length;
  EXEC SQL LOB WRITE APPEND :Amount FROM :Buffer INTO :Temp_loc;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  writeAppendTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Write Data to a Temporary LOB

Figure 4-22 Use Case Diagram: Write data to a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Stream Write

The most efficient way to write large amounts of LOB data is to use OCILobWrite() with the streaming mechanism enabled via polling or a callback. If you know how much data will be written to the LOB specify that amount when calling OCILobWrite(). This will allow for the contiguity of the LOB data on disk. Apart from being spatially efficient, contiguous structure of the LOB data will make for faster reads and writes in subsequent operations.

Scenario

This example procedure allows the STORY data (the storyboard for the clip) to be updated by writing data to the LOB.

Example: Write Data to a Temporary LOB Using the DBMS_LOB Package

/* Note that the example procedure writeDataToTempLOB_proc is not part of the 
   DBMS_LOB package. */
CREATE or REPLACE PROCEDURE writeDataToTempLOB_proc IS
   Lob_loc         CLOB;
   Buffer          VARCHAR2(26);
   Amount          BINARY_INTEGER := 26;
   Position        INTEGER := 1;
   i               INTEGER;
BEGIN
    DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Fill the buffer with data to write to the LOB: */
   Buffer := 'abcdefghijklmnopqrstuvwxyz';

   FOR i IN 1..3 LOOP
      DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);
      /* Fill the buffer with more data to write to the LOB: */
      Position := Position + Amount;
   END LOOP;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
   DBMS_LOB.FREETEMPORARY(Lob_loc);
END;

Example: Write Data to a Temporary LOB Using C (OCI)

/* This example illustrates streaming writes with polling */
sb4 write_temp_lobs (OCIError      *errhp,
                     OCISvcCtx     *svchp,
                     OCIStmt       *stmthp,
                     OCIEnv        *envhp)
{
  OCIClobLocator *tclob;
  unsigned int Total = 40000;
  unsigned int amtp;
  unsigned int offset;
  unsigned int remainder, nbytes;
  boolean last;
  ub1 bufp[MAXBUFLEN];
  sb4     err;

  /* Allocate the locators desriptors: */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &tclob ,
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  if(OCILobCreateTemporary(svchp,
           errhp,
           tclob,
           (ub2)0, 
           SQLCS_IMPLICIT,
           OCI_TEMP_CLOB, 
           OCI_ATTR_NOCACHE, 
           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 

  /* Open the CLOB: */
  checkerr (errhp, (OCILobOpen(svchp, errhp, tclob, OCI_LOB_READWRITE)));

  if (Total > MAXBUFLEN)
    nbytes = MAXBUFLEN;   /* We will use Streaming via Standard Polling */
  else
    nbytes = Total;       /* Only a single WRITE is required */
  
  /* Fill the Buffer with nbytes worth of Data: */
  memset(bufp,'a',32767);
  
  remainder = Total - nbytes;
  amtp = 0;
  offset = 1; 
  /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE: */
  
  if (0 == remainder)
  {
    amtp = nbytes;
    /* Here, (Total <= MAXBUFLEN ) so we can WRITE in ONE piece: */
    checkerr (errhp, OCILobWrite (svchp, errhp, tclob, &amtp, 
                                  offset, bufp, nbytes,
                                  OCI_ONE_PIECE, (dvoid *)0, 
                                  (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                                  0, SQLCS_IMPLICIT));
  }  
  else
  {
      /* Here (Total > MAXBUFLEN ) so we use Streaming via Standard Polling: */
      /* WRITE the FIRST piece.  Specifying FIRST initiates Polling: */
      err = OCILobWrite (svchp, errhp, tclob, &amtp, 
                         offset, bufp, nbytes,
                         OCI_FIRST_PIECE, (dvoid *)0, 
                         (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                         0, SQLCS_IMPLICIT);

      if (err != OCI_NEED_DATA)
        checkerr (errhp, err);

      last = FALSE;
      /* WRITE the NEXT (interim) and LAST pieces: */
      do 
        {
          if (remainder > MAXBUFLEN)
            nbytes = MAXBUFLEN;            /* Still have more pieces to go */
          else
          {
            nbytes = remainder;        /* Here, (remainder <= MAXBUFLEN) */
            last = TRUE;             /* This is going to be the Final piece */
          }

          /* Fill the Buffer with nbytes worth of Data */

          if (last)
          {
            /* Specifying LAST terminates Polling */
            err = OCILobWrite (svchp, errhp, tclob, &amtp, 
                               offset, bufp, nbytes,
                               OCI_LAST_PIECE, (dvoid *)0, 
                               (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                               0, SQLCS_IMPLICIT);

            if (err != 0)
              checkerr (errhp, err);

          } else

          {
            err =  OCILobWrite (svchp, errhp, tclob, &amtp, 
                                offset, bufp, nbytes,
                                OCI_NEXT_PIECE, (dvoid *)0, 
                                (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                                0, SQLCS_IMPLICIT);

            if (err != OCI_NEED_DATA)
              checkerr (errhp, err);
             
          }
          /* Determine how much is left to WRITE: */
          remainder = remainder - nbytes;
        } while (!last);
    }
   /* At this point, (remainder == 0) */
  
 /* Closing the LOB is mandatory if you have opened it: */
    checkerr (errhp, OCILobClose(svchp, errhp, tclob));

  /* Free the temporary LOB: */
  checkerr(errhp,OCILobFreeTemporary(svchp,errhp,tclob));

  /* Free resources held by the locators: */
  (void) OCIDescriptorFree((dvoid *) tclob, (ub4) OCI_DTYPE_LOB);
}

Example: Write Data to a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. WRITE-TEMP.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
       01  TEMP-CLOB      SQL-CLOB.
       01  BUFFER         PIC X(20) VARYING.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP VALUE 10.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       WRITE-TEMP.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BFILE and BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-CLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-CLOB
           END-EXEC.

           EXEC SQL LOB OPEN :TEMP-CLOB READ WRITE END-EXEC.
           
        MOVE "ABCDE12345ABCDE12345" TO BUFFER-ARR.
        MOVE 20 TO BUFFER-LEN.
        MOVE 20 TO AMT. 
      * Append the data in BUFFER to TEMP-CLOB: 
           EXEC SQL 
                LOB WRITE :AMT FROM :BUFFER INTO :TEMP-CLOB      
           END-EXEC.

      * Close the LOBs: 
           EXEC SQL LOB CLOSE :TEMP-CLOB END-EXEC.

      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-CLOB 
           END-EXEC.

      * And free the LOB locators: 
           EXEC SQL FREE :TEMP-CLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Write Data to a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <string.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 1024

void writeDataToTempLOB_proc(multiple) int multiple;
{
  OCIClobLocator *Temp_loc;
  varchar Buffer[BufferLength];
  unsigned int Total;
  unsigned int Amount;
  unsigned int remainder, nbytes;
  boolean last;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Initialize the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Open the Temporary LOB: */
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  Total = Amount = (multiple * BufferLength);
  if (Total > BufferLength)
    nbytes = BufferLength;   /* We will use Streaming via Standard Polling */
  else
    nbytes = Total;          /* Only a single WRITE is required */
  /* Fill the Buffer with nbytes worth of Data: */
  memset((void *)Buffer.arr, 32, nbytes);
  Buffer.len = nbytes;       /* Set the Length */
  remainder = Total - nbytes;
  if (0 == remainder)
    {
      /* Here, (Total <= BufferLength) so we can WRITE in ONE piece: */
      EXEC SQL LOB WRITE ONE :Amount FROM :Buffer INTO :Temp_loc;
      printf("Write ONE Total of %d characters\n", Amount);
    }
  else
    {
      /* Here (Total > BufferLength) so use Streaming via Standard Polling */
      /* WRITE the FIRST piece.  Specifying FIRST initiates Polling: */
      EXEC SQL LOB WRITE FIRST :Amount FROM :Buffer INTO :Temp_loc;
      printf("Write FIRST %d characters\n", Buffer.len);
      last = FALSE;
      /* WRITE the NEXT (interim) and LAST pieces: */
      do 
        {
          if (remainder > BufferLength)
            nbytes = BufferLength;         /* Still have more pieces to go */
          else
            {
              nbytes = remainder;     /* Here, (remainder <= BufferLength) */
              last = TRUE;          /* This is going to be the Final piece */
            }
          /* Fill the Buffer with nbytes worth of Data: */
          memset((void *)Buffer.arr, 32, nbytes);
          Buffer.len = nbytes;       /* Set the Length */
          if (last)
            {
              EXEC SQL WHENEVER SQLERROR DO Sample_Error();
              /* Specifying LAST terminates Polling: */
              EXEC SQL LOB WRITE LAST :Amount FROM :Buffer INTO :Temp_loc;
              printf("Write LAST Total of %d characters\n", Amount);
            }
          else
            {
              EXEC SQL WHENEVER SQLERROR DO break;
              EXEC SQL LOB WRITE NEXT :Amount FROM :Buffer INTO :Temp_loc;
              printf("Write NEXT %d characters\n", Buffer.len);
            }
          /* Determine how much is left to WRITE: */
          remainder = remainder - nbytes;
        } while (!last);
    }
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* At this point, (Amount == Total), the total amount that was written. */
  /* Close the Temporary LOB: */
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Free resources held by the Locator: */
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  writeDataToTempLOB_proc(1);                         /* Write One Piece */
  writeDataToTempLOB_proc(4);     /* Write Multiple Pieces using Polling */
  EXEC SQL ROLLBACK WORK RELEASE;
}

Trim the Temporary LOB Data

Figure 4-23 Use Case Diagram: Trim the Temporary LOB data

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

Our example accesses text (CLOB data) that is referenced in the Script column of the table Voiceover_tab, and trims it.

Example: Trim the Temporary LOB Data Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure trimTempLOB_proc is not part of the 
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE trimTempLOB_proc IS
   Lob_loc        CLOB;
   Amount         number;
   Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   TrimAmount     number := 100;
BEGIN
   /* Create a temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Opening the file is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Populate the temporary LOB with some data: */
   Amount := 32767;
   DBMS_LOB.LOADFROMFILE(Lob_loc, Src_loc, Amount);
   DBMS_LOB.TRIM(Lob_loc,TrimAmount);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.FREETEMPORARY(Lob_loc);
COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

Example: Trim the Temporary LOB Data Using C (OCI)

sb4 trim_temp_lobs (  OCIError      *errhp,
                      OCISvcCtx     *svchp,
                      OCIStmt       *stmthp,
                      OCIEnv        *envhp)
{
  OCILobLocator *tblob;
  OCILobLocator *bfile;
  ub4 amt = 4000;
  ub4 trim_size = 2;
  sb4 return_code = 0;

  printf("in trim\n");
  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob,
                        (ub4) OCI_DTYPE_LOB,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in trim\n");
     return -1;
  }

  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,
                        (ub4) OCI_DTYPE_FILE,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in trim\n");
     return -1;
  }

  /* Set the BFILE to point to the Washington_audio file: */ 
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),
                       (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED\n");
    return -1;
  }
   
  if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))
  {
    printf( "OCILobFileOpen FAILED  for the bfile\n");
    return_code = -1;
  }
 
  if(OCILobCreateTemporary(svchp,errhp,tblob,(ub2)0, SQLCS_IMPLICIT,
                           OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 
   
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return_code =  -1;
  }
   
  /* populate the temp LOB with 4000 bytes of data */
  if(OCILobLoadFromFile(svchp, errhp, tblob, (OCILobLocator*)bfile,
                        (ub4)amt,(ub4)1,(ub4)1))
  {
    printf( "OCILobLoadFromFile FAILED\n");
    return_code = -1;
  }

  if (OCILobTrim(svchp, errhp, (OCILobLocator *) tblob, trim_size))
  {
      printf( "OCILobTrim FAILED for temp LOB \n");
      return_code = -1;
  } else 
  {
      printf( "OCILobTrim succeeded for temp LOB \n");
  }

  if (OCILobClose(svchp, errhp, (OCILobLocator *) bfile))
  {
    printf( "OCILobClose FAILED for bfile \n");
    return_code =  -1;
  }

  if (OCILobClose(svchp, errhp, (OCILobLocator *) tblob))
  {
    printf( "OCILobClose FAILED for temporary LOB \n");
    return_code =  -1;
  }
  /* Free the temporary LOB now that we are done using it: */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return_code = -1;
  }
  return return_code;
}

Example: Trim the Temporary LOB Data Using COBOL (Pro*COBOL)

      IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-TRIM.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP VALUE 10.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       TEMP-LOB-TRIM.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BFILE and BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
           
      * Trim the last half of the data: 
           MOVE 5 TO AMT.
           EXEC SQL 
                LOB TRIM :TEMP-BLOB TO :AMT
           END-EXEC.

      * Close the LOBs: 
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.

      * Free the temporary LOB:
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.

      * And free the LOB locators:
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Trim the Temporary LOB Data Using C++ (Pro*C/C++)

void trimTempLOB_proc()
#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void trimTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount = 4096;
  int trimLength;

  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Load the specified amount from the BFILE into the Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;
  /* Set the new length of the Temporary LOB: */
  trimLength = (int) (Amount / 2);
  /* Trim the Temporary LOB to its new length: */
  EXEC SQL LOB TRIM :Temp_loc TO :trimLength;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  trimTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Erase Part of a Temporary LOB

Figure 4-24 Use Case Diagram: Erase part of a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

Example: Erase Part of a Temporary LOB Using PL/SQL (DBMS_LOB Package)

/* Note that the example procedure eraseTempLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE trimTempLOB_proc IS
   Lob_loc        CLOB;
   amt            number;
   Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount         INTEGER := 32767;
BEGIN
   /* Create a temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Populate the temporary LOB with some data: */
   Amount := 32767;
   DBMS_LOB.LOADFROMFILE(Lob_loc, Src_loc, Amount);
   /* Erase  the LOB data: */
   amt := 3000;
   DBMS_LOB.ERASE(Lob_loc, amt, 2);
    /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.FREETEMPORARY(Lob_loc);
COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

Example: Erase Part of a Temporary LOB Using C (OCI)

/* Erase 2 bytes at offset 100 in a temporary LOB: */ 

sb4 erase_temp_lobs ( OCIError      *errhp,
                      OCISvcCtx     *svchp,
                      OCIStmt       *stmthp,
                      OCIEnv        *envhp)
{

  OCILobLocator *tblob;
  OCILobLocator *bfile;
  ub4 amt = 4000;
  ub4 erase_size = 2;
  ub4 erase_offset = 100;
  sb4 return_code = 0;

  printf("in erase\n");
  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob,
                        (ub4) OCI_DTYPE_LOB,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED \n");
     return -1;
  }

  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,
                        (ub4) OCI_DTYPE_FILE,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED \n");
     return -1;
  }

  /* Set the BFILE to point to the Washington_audio file: */ 
  if(OCILobFileSetName(envhp, errhp, &bfile,
                       (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),
                       (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED\n");
    return -1;
  }
   
  if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))
  {
    printf( "OCILobFileOpen FAILED  for the bfile\n");
    return_code = -1;
  }
 
  if(OCILobCreateTemporary(svchp,errhp,tblob,(ub2)0, SQLCS_IMPLICIT,
      OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 
   
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return_code =  -1;
  }
   
  /* Populate the temp LOB with 4000 bytes of data: */
  if(OCILobLoadFromFile(svchp,
                        errhp,
                        tblob,
                        (OCILobLocator*)bfile,
                        (ub4)amt,
                        (ub4)1,(ub4)1))
  {
    printf( "OCILobLoadFromFile FAILED\n");
    return_code = -1;

  }

  if (OCILobErase(svchp, errhp, (OCILobLocator *) tblob, &erase_size,
                  erase_offset))
  {
      printf( "OCILobErase FAILED for temp LOB \n");
      return_code = -1;
  } else 
  {
      printf( "OCILobErase succeeded for temp LOB \n");
  }

  if (OCILobClose(svchp, errhp, (OCILobLocator *) bfile))
  {
    printf( "OCILobClose FAILED for bfile \n");
    return_code =  -1;
  }

  if (OCILobClose(svchp, errhp, (OCILobLocator *) tblob))
  {
    printf( "OCILobClose FAILED for temporary LOB \n");
    return_code =  -1;
  }
  /* free the temporary LOB now that we are done using it */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return_code = -1;
  }
  return return_code;

}

Example: Erase Part of a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-BLOB-ERASE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".

       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP VALUE 10.
       01  POS            PIC S9(9) COMP VALUE 1.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       TEMP-BLOB-ERASE.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locator: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
           
      * Erase some of the LOB data: 
           EXEC SQL
                LOB ERASE :AMT FROM :TEMP-BLOB AT :POS
           END-EXEC.

      * Close the LOBs
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.

      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.

      * And free the LOB locators: 
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Erase Part of a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void eraseTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount;
  int Position = 1024;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc READ WRITE;
  /* Load a specified amount from the BFILE into the Temporary LOB: */
  Amount = 4096;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;
  /* Erase a specified amount from the Temporary LOB at a given position: */
  Amount = 2048;
  EXEC SQL LOB ERASE :Amount FROM :Temp_loc AT :Position;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc;
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  eraseTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Enable LOB Buffering for a Temporary LOB

Figure 4-25 Use Case Diagram: Enable LOB Buffering for a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

You enable buffering in order to perform a small series of reads or writes. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.

Please note that you would not enable buffering to perform the stream read and write involved in checkin and checkout.

Example: Enable LOB Buffering for a Temporary LOB Using C (OCI)

sb4 lobBuffering (envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *tblob;
  ub4 amt;
  ub4 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;

  /* Allocate the descriptor for the lob locator: */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &tblob, 
                         (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  /* Select the BLOB: */
  printf (" create a temporary Lob\n");
  /* Create a temporary LOB: */
  if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, SQLCS_IMPLICIT, 
                           OCI_TEMP_BLOB, 
                           OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return -1;
  }

  /* Open the BLOB: */
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return -1;
  }

  /* Enable LOB Buffering: */
  printf (" enable LOB buffering\n");
  checkerr (errhp, OCILobEnableBuffering(svchp, errhp, tblob));

  printf (" write data to LOB\n");

  /* Write data into the LOB: */
  amt    = sizeof(bufp);
  buflen = sizeof(bufp);
  offset = 1;
  checkerr (errhp, OCILobWrite (svchp, errhp, tblob, &amt, 
                                offset, bufp, buflen,
                                OCI_ONE_PIECE, (dvoid *)0, 
                                (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                                0, SQLCS_IMPLICIT));


  /* Flush the buffer: */
  printf(" flush the LOB buffers\n");
  checkerr (errhp, OCILobFlushBuffer(svchp, errhp, tblob,
                                     (ub4)OCI_LOB_BUFFER_FREE));

  /* Disable Buffering: */
  printf (" disable LOB buffering\n");
  checkerr (errhp, OCILobDisableBuffering(svchp, errhp, tblob));

  /* Subsequent LOB WRITEs will not use the LOB Buffering Subsystem */

  /* Closing the BLOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, tblob));

  /* Free the temporary LOB now that we are done using it: */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }

  /* Free resources held by the locators: */
  (void) OCIDescriptorFree((dvoid *) tblob, (ub4) OCI_DTYPE_LOB);

  return;

}

Example: Enable LOB Buffering for a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-BUFFERING.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".        
       01  TEMP-BLOB          SQL-BLOB.
       01  BUFFER             PIC X(80).
       01  AMT                PIC S9(9) COMP VALUE 10.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL VAR BUFFER IS RAW(80) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       TEMP-LOB-BUFFERING.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the CLOB locators:
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.
 
      * Enable buffering for the temporary LOB: 
           EXEC SQL 
                LOB ENABLE BUFFERING :TEMP-BLOB 
           END-EXEC.        
      *
      * Write some data to the temporary LOB here: 
      *
           MOVE '252525262626252525' TO BUFFER.
           EXEC SQL
                LOB WRITE ONE :AMT FROM :BUFFER
                INTO :TEMP-BLOB
           END-EXEC

      * Flush the buffered writes: 
           EXEC SQL 
                LOB FLUSH BUFFER :TEMP-BLOB FREE
           END-EXEC.

      * Disable buffering for the temporary LOB: 
           EXEC SQL
                LOB DISABLE BUFFERING :TEMP-BLOB
           END-EXEC.

           EXEC SQL
                LOB FREE TEMPORARY :TEMP-BLOB
           END-EXEC.

           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Enable LOB Buffering for a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 1024

void enableBufferingTempLOB_proc()
{
  OCIClobLocator *Temp_loc;
  varchar Buffer[BufferLength];
  int Amount = BufferLength;
  int multiple, Length = 0, Position = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Enable use of the LOB Buffering Subsystem: */
  EXEC SQL LOB ENABLE BUFFERING :Temp_loc;
  memset((void *)Buffer.arr, 42, BufferLength);
  Buffer.len = BufferLength;
  for (multiple = 0; multiple < 8; multiple++)
    {
      /* Write Data to the Temporary LOB: */
      EXEC SQL LOB WRITE ONE :Amount
                    FROM :Buffer INTO :Temp_loc AT :Position;
      Position += BufferLength;
    }
  /* Flush the contents of the buffers and Free their resources: */
  EXEC SQL LOB FLUSH BUFFER :Temp_loc FREE;
  /* Turn off use of the LOB Buffering Subsystem: */
  EXEC SQL LOB DISABLE BUFFERING :Temp_loc;
  EXEC SQL LOB DESCRIBE :Temp_loc GET LENGTH INTO :Length;
  printf("Wrote %d characters using the Buffering Subsystem\n", Length);
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Temp_loc;
}
void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  enableBufferingTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Flush Buffer for a Temporary LOB

Figure 4-26 Use Case Diagram: Flush Buffer for a Temporary LOB

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

Example: Flush Buffer for a Temporary LOB Using C (OCI)

sb4 lobBuffering (envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *tblob;
  ub4 amt;
  ub4 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;

  /* Allocate the descriptor for the lob locator: */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &tblob, 
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  /* Select the BLOB: */
  printf (" create a temporary Lob\n");
  /* Create a temporary lob :*/
  if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, 
                           SQLCS_IMPLICIT, OCI_TEMP_BLOB, 
                           OCI_ATTR_NOCACHE, OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return -1;
  }

  /* Open the BLOB: */
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp lob \n");
    return -1;
  }

  /* Enable LOB Buffering: */
  printf (" enable LOB buffering\n");
  checkerr (errhp, OCILobEnableBuffering(svchp, errhp, tblob));

  printf (" write data to LOB\n");

  /* Write data into the LOB: */
  amt    = sizeof(bufp);
  buflen = sizeof(bufp);
  offset = 1;
  checkerr (errhp, OCILobWrite (svchp, errhp, tblob, &amt, 
                                offset, bufp, buflen,
                                OCI_ONE_PIECE, (dvoid *)0, 
                                (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                                0, SQLCS_IMPLICIT));

  /* Flush the buffer: */
  printf(" flush the LOB buffers\n");
  checkerr (errhp, OCILobFlushBuffer(svchp, errhp, tblob,
                                     (ub4)OCI_LOB_BUFFER_FREE));

  /* Disable Buffering: */
  printf (" disable LOB buffering\n");
  checkerr (errhp, OCILobDisableBuffering(svchp, errhp, tblob));

  /* Subsequent LOB WRITEs will not use the LOB Buffering Subsystem */

  /* Closing the BLOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, tblob));

  /* Free the temporary lob now that we are done using it: */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }

  /* Free resources held by the locators: */
  (void) OCIDescriptorFree((dvoid *) tblob, (ub4) OCI_DTYPE_LOB);

  return;

}

Example: Flush Buffer for a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. FREE-TEMPORARY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".
        
       01  TEMP-BLOB      SQL-BLOB.
       01  IS-TEMP        PIC S9(9) COMP.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       FREE-TEMPORARY.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.

           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.

      * Do something with the temporary LOB here: 

      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.
           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Flush Buffer for a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 1024

void flushBufferingTempLOB_proc()
{
  OCIClobLocator *Temp_loc;
  varchar Buffer[BufferLength];
  int Amount = BufferLength;
  int multiple, Length = 0, Position = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Enable use of the LOB Buffering Subsystem: */
  EXEC SQL LOB ENABLE BUFFERING :Temp_loc;
  memset((void *)Buffer.arr, 42, BufferLength);
  Buffer.len = BufferLength;
  for (multiple = 0; multiple < 8; multiple++)
    {
      /* Write Data to the Temporary LOB: */
      EXEC SQL LOB WRITE ONE :Amount
         FROM :Buffer INTO :Temp_loc AT :Position;
      Position += BufferLength;
    }
  /* Flush the contents of the buffers and Free their resources: */
  EXEC SQL LOB FLUSH BUFFER :Temp_loc FREE;
  /* Turn off use of the LOB Buffering Subsystem: */
  EXEC SQL LOB DISABLE BUFFERING :Temp_loc;
  EXEC SQL LOB DESCRIBE :Temp_loc GET LENGTH INTO :Length;
  printf("Wrote %d characters using the Buffering Subsystem\n", Length);
  /* Free the Temporary LOB */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  flushBufferingTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Disable LOB Buffering for a Temporary LOB

Figure 4-27 Use Case Diagram: Disable LOB Buffering

To refer to the table of all basic operations having to do with Internal Temporary LOBs see:

"Use Case Model: Internal Temporary LOBs"

Scenario

You enable buffering in order to perform a small series of reads or writes. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.

Please note that you would not enable buffering to perform the stream read and write involved in checkin and checkout.

Example: Disable LOB Buffering Using C (OCI)

sb4 lobBuffering (envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *tblob;
  ub4 amt;
  ub4 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;

 
  /* Allocate the descriptor for the lob locator: */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &tblob, 
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  /* Select the BLOB: */
  printf (" create a temporary Lob\n");
  /* Create a temporary LOB: */
  if(OCILobCreateTemporary(svchp,errhp, tblob, (ub2)0, SQLCS_IMPLICIT, 
                           OCI_TEMP_BLOB, 
                           OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return -1;
  }

  /* Open the BLOB: */
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return -1;
  }

  /* Enable LOB Buffering: */
  printf (" enable LOB buffering\n");
  checkerr (errhp, OCILobEnableBuffering(svchp, errhp, tblob));

  printf (" write data to LOB\n");

  /* Write data into the LOB: */
  amt    = sizeof(bufp);
  buflen = sizeof(bufp);
  offset = 1;
  checkerr (errhp, OCILobWrite (svchp, errhp, tblob, &amt, 
                                   offset, bufp, buflen,
                                   OCI_ONE_PIECE, (dvoid *)0, 
                                   (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                                   0, SQLCS_IMPLICIT));


  /* Flush the buffer: */
  printf(" flush the LOB buffers\n");
  checkerr (errhp, OCILobFlushBuffer(svchp, errhp, tblob,
                                     (ub4)OCI_LOB_BUFFER_FREE));

  /* Disable Buffering: */
  printf (" disable LOB buffering\n");
  checkerr (errhp, OCILobDisableBuffering(svchp, errhp, tblob));

  /* Subsequent LOB WRITEs will not use the LOB Buffering Subsystem */

  /* Closing the BLOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, tblob));

  /* Free the temporary LOB now that we are done using it: */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }

  /* Free resources held by the locators: */
  (void) OCIDescriptorFree((dvoid *) tblob, (ub4) OCI_DTYPE_LOB);

  return;

}

Example: Disable LOB Buffering for a Temporary LOB Using COBOL (Pro*COBOL)

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-BUFFERING.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "USER1/USER1".        
       01  TEMP-BLOB          SQL-BLOB.
       01  BUFFER             PIC X(80).
       01  AMT                PIC S9(9) COMP VALUE 10.
       01  ORASLNRD        PIC 9(4).

           EXEC SQL VAR BUFFER IS RAW(80) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.

       PROCEDURE DIVISION.
       TEMP-LOB-BUFFERING.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the CLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB
           END-EXEC.
 
      * Enable buffering for the temporary LOB: 
           EXEC SQL 
                LOB ENABLE BUFFERING :TEMP-BLOB 
           END-EXEC.        
      
      * Write some data to the temporary LOB here: 
      
           MOVE '252525262626252525' TO BUFFER.
           EXEC SQL
                LOB WRITE ONE :AMT FROM :BUFFER
                INTO :TEMP-BLOB
           END-EXEC

      * Flush the buffered writes: 
           EXEC SQL 
                LOB FLUSH BUFFER :TEMP-BLOB FREE
           END-EXEC.

      * Disable buffering for the temporary LOB: 
           EXEC SQL
                LOB DISABLE BUFFERING :TEMP-BLOB
           END-EXEC.

           EXEC SQL
                LOB FREE TEMPORARY :TEMP-BLOB
           END-EXEC.

           EXEC SQL FREE :TEMP-BLOB END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.

Example: Disable LOB Buffering for a Temporary LOB Using C++ (Pro*C/C++)

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 1024

void disableBufferingTempLOB_proc()
{
  OCIClobLocator *Temp_loc;
  varchar Buffer[BufferLength];
  int Amount = BufferLength;
  int multiple, Length = 0, Position = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;
  /* Enable use of the LOB Buffering Subsystem: */
  EXEC SQL LOB ENABLE BUFFERING :Temp_loc;
  memset((void *)Buffer.arr, 42, BufferLength);
  Buffer.len = BufferLength;
  for (multiple = 0; multiple < 7; multiple++)
    {
      /* Write Data to the Temporary LOB: */
      EXEC SQL LOB WRITE ONE :Amount
                    FROM :Buffer INTO :Temp_loc AT :Position;
      Position += BufferLength;
    }
  /* Flush the contents of the buffers and Free their resources: */
  EXEC SQL LOB FLUSH BUFFER :Temp_loc FREE;
  /* Turn off use of the LOB Buffering Subsystem:  */
  EXEC SQL LOB DISABLE BUFFERING :Temp_loc;
  /* Write APPEND can only be done when Buffering is Disabled: */
  EXEC SQL LOB WRITE APPEND ONE :Amount FROM :Buffer INTO :Temp_loc;
  EXEC SQL LOB DESCRIBE :Temp_loc GET LENGTH INTO :Length;
  printf("Wrote a total of %d characters\n", Length);
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Temp_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  disableBufferingTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

4 Temporary LOBs

Use Case Model: Internal Temporary LOBs

Figure 4-1 Use Case Model Diagram: Internal Temporary LOBs (part 1 of 2)

Figure 4-2 Use Case Model Diagram: Internal temporary LOBs (part 2 of 2)

Programmatic Environments

The Location of Temporary LOBs

The Lifetime and Duration of Temporary LOBs

Memory Handling

Locators and Semantics

Security Issues with Temporary LOBs

Managing Temporary LOBs

Create a Temporary LOB

Figure 4-3 Use Case Diagram: Create a temporary LOB

Scenario

Example: Create a Temporary LOB Using PL/SQL (DBMS_LOB Package)

Example: Create a Temporary LOB Using C (OCI)

Example: Create a Temporary LOB Using COBOL (Pro*COBOL)

Example: Create a Temporary LOB Using C++ (Pro*C/C++)

See If a LOB is Temporary

Figure 4-4 Use Case Diagram: See If a LOB is Temporary

Scenario

Example: See If a LOB is Temporary Using PL/SQL (DBMS_LOB Package)

Example: See If a LOB is Temporary Using C (OCI)

Example: See If a LOB is Temporary Using COBOL (Pro*COBOL)

Example: See If a LOB is Temporary Using C++ (Pro*C/C++)

Free a Temporary LOB

Figure 4-5 Use Case Diagram: Free a Temporary LOB

Scenario

Example: Free a Temporary LOB Using PL/SQL (DBMS_LOB Package)

Example: Free a Temporary LOB Using C (OCI)

Example: Free a Temporary LOB Using COBOL (Pro*COBOL)

Example: Free a Temporary LOB Using C++ (Pro*C/C++)

Load a Temporary LOB with Data from a BFILE

Figure 4-6 Use Case Diagram: Load a LOB with data from a BFILE

Scenario

Example: Load a Temporary LOB with Data from a BFILE Using PL/SQL (DBMS_LOB Package)

Example: Load a Temporary LOB with Data from a BFILE Using C (OCI)

Example: Load a Temporary LOB with Data from a BFILE Using COBOL (Pro*COBOL)

Example: Load a Temporary LOB with Data from a BFILE Using C++ (Pro*C/C++)

See If a Temporary LOB Is Open

Figure 4-7 Use Case Diagram: See If a Temporary LOB Is Open... 206

Scenario

Example: See If a Temporary LOB Is Open Using PL/SQL

Example: See If a Temporary LOB Is Open Using C (OCI)

Example: See If a Temporary LOB Is Open Using COBOL (Pro*COBOL)

Example: See If a Temporary LOB Is Open Using C++ (Pro*C/C++)

Display the Temporary LOB Data

Figure 4-8 Use Case Diagram: Display the Temporary LOB data

Scenario

Example: Display the Temporary LOB Data Using PL/SQL (DBMS_LOB Package)

Example: Display the Temporary LOB Data Using C (OCI)

Example: Display the Temporary LOB Data Using COBOL (Pro*COBOL)

Example: Display the Temporary LOB Data Using C++ (Pro*C/C++)

Read Data from a Temporary LOB

Figure 4-9 Use Case Diagram: Read Data from a Temporary LOB

Stream Read

Scenario

Example: Read Data from a Temporary LOB Using PL/SQL (DBMS_LOB Package)

Example: Read Data from a Temporary LOB Using C (OCI)

Example: Read Data from a Temporary LOB Using COBOL (Pro*COBOL)

Example: Read Data from a Temporary LOB Using C++ (Pro*C/C++)

Read a Portion of the Temporary LOB (substr)

Figure 4-10 Use Case Diagram: Read a portion of the Temporary LOB from the Table (substr)

Scenario

Example: Read a Portion of the Temporary LOB (substr) Using PL/SQL (DBMS_LOB Package)

Example: Read a Portion of the Temporary LOB (substr) Using COBOL (Pro*COBOL)

Example: Read a Portion of the Temporary LOB (substr) Using C++ (Pro*C/C++)

Compare All or Part of Two (Temporary) LOBs

Figure 4-11 Use Case Diagram: Compare All or Part of Two Temporary LOBs

Scenario

Example: Compare All or Part of Two (Temporary) LOBs Using PL/SQL (DBMS_LOB Package)

Example: Compare All or Part of Two (Temporary) LOBs Using COBOL (Pro*COBOL)

Example: Compare All or Part of Two (Temporary) LOBs Using C++ (Pro*C/C++)

See If a Pattern Exists in a Temporary LOB (instr)

Figure 4-12 Use Case Diagram: See If a Pattern Exists in a Temporary LOB (instr)

Scenario

Example: See If a Pattern Exists in a Temporary LOB (instr) Using PL/SQL (DBMS_LOB Package)

Example: See If a Pattern Exists in a Temporary LOB (instr) Using COBOL (Pro*COBOL)

Example: See If a Pattern Exists in a Temporary LOB (instr) Using C++ (Pro*C/C++)

Get the Length of a Temporary LOB

4
Temporary LOBs