11
Temporary LOBs

Use Case Model

In this chapter we discuss each operation on a Temporary LOB (such as "Loading a Temporary LOB with Data from a BFILE") in terms of a use case. Table 11-1, "Use Case Model Overview: Internal Temporary LOBs" lists all the use cases.

Individual Use Cases

Each Internal Persistent LOB use case is described as follows:

Use case figure. Depicts the use case. For help in understanding the use case diagrams, see Appendix A, "Unified Modeling Language Diagrams".
Purpose. The purpose of this use case with regards to LOBs.
Usage Notes. Guidelines to assist your implementation of the LOB operation.
Syntax. Pointers to the syntax in different programmatic environments that underlies the LOBs related activity for the use case.
Scenario. Describes the implementation of the use case in terms of the sample schema used in the examples. For further details on sample schemas, refer to Oracle9i Sample Schemas.
Examples. How to apply each use case based on the sample schema used.

Use Case Model: Internal Temporary LOBs

Table 11-1, "Use Case Model Overview: Internal Temporary LOBs" indicates with + where examples are provided for specific use cases and in which programmatic environment (see Chapter 3, "LOB Support in Different Programmatic Environments" for a complete discussion and references to related manuals).

We refer to programmatic environments by means of the following abbreviations:

P -- PL/SQL using the DBMS_LOB Package
O -- C using OCI (Oracle Call Interface)
B -- COBOL using Pro*COBOL precompiler
C -- C/C++ using Pro*C/C++ precompiler
V -- Visual Basic using OO4O (Oracle Objects for OLE)
J -- Java using JDBC (Java Database Connectivity)
S -- SQL

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

Use Case and Page	P	O	B	C	V^*	J
Appending One Temporary LOB to Another	+	+	+	+	-	-
Checking If a LOB is Temporary	+	+	+	+	-	+
Comparing All or Part of Two Temporary LOBs	+	-	+	+	-	-
Copying a LOB Locator for a Temporary LOB	+	+	+	+	-	-
Copying All or Part of One Temporary LOB to Another	+	+	+	+	-	-
Creating a Temporary LOB	+	+	+	+	-	+
Determining if a LOB Locator for a Temporary LOB Is Initialized	-	+	-	+	-	-
Determining If a Pattern Exists in a Temporary LOB (instr)	+	-	+	+	-	-
Loading a Temporary LOB with Data from a BFILE	+	+	+	+	-	-
Loading a Temporary BLOB with Binary Data from a BFILE	+	-	-	-	-	-
Loading a Temporary CLOB/NCLOB with Character Data from a File	+	-	-	-	-	-
Disabling LOB Buffering for a Temporary LOB	-	+	+	+	-	-
Displaying Temporary LOB Data	+	+	+	+	-	-
Enabling LOB Buffering for a Temporary LOB	-	+	+	+	-	-
Erasing Part of a Temporary LOB	+	+	+	+	-	-
Finding Character Set Form of a Temporary LOB	-	+	-	-	-	-
Finding Character Set ID of a Temporary LOB	-	+	-	-	-	-
Finding the Length of a Temporary LOB	+	+	+	+	-	-
Flushing Buffer for a Temporary LOB	-	+	+	+	-	-
Freeing a Temporary LOB	+	+	+	+	-	+
Is One Temporary LOB Locator Equal to Another	-	+	-	+	-	-
Loading a Temporary LOB with Data from a BFILE	+	+	+	+	-	-
Loading a Temporary BLOB with Binary Data from a BFILE	+	-	-	-	-	-
Loading a Temporary CLOB/NCLOB with Character Data from a File	+	-	-	-	-	-
Reading Data from a Temporary LOB	+	+	+	+	-	-
Reading Portion of Temporary LOB (Substr)	+	-	+	+	-	-
Trimming Temporary LOB Data	+	+	+	+	-	-
Write-Appending to a Temporary LOB	+	+	+	+	-	-
Writing Data to a Temporary LOB	+	+	+	+	-	-

^* There are no Visual Basic examples provided for internal temporary LOB use cases.

Programmatic Environments

Oracle9i supports the definition, creation, deletion, access, and update of temporary LOBs in the following programmatic environments or "interfaces":

PL/SQL, using the DBMS_LOB package
C/C++, using PRO*C precompiler
COBOL, using Pro*COBOL precompiler
C, using OCI
Java, using JDBC

Locators

The 'interfaces' listed earlier, 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. You must manipulate them using the DBMS_LOB package, OCI, or the other programmatic interfaces.

Temporary LOB Locators Can be IN Values

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 follows:

As a value in a WHERE clause for INSERT, UPDATE, DELETE, or SELECT. For example:

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

As a variable in a SELECT INTO... statement. For example:

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

Note:

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

Can You Use the Same Functions for Temporary and Internal Persistent LOBs?

You can use the following functions for internal persistent LOBs and 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 (OCILobLocatorAssign, OCILobLocatorIsInit, and so on).

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

Note:

One thing to keep in mind is that temporary LOBs do not support transactions and consistent reads.

DBMS_LOB.createtemporary() Parameter is a Hint

The DBMS_LOB.createtemporary() call takes a duration parameter. This parameter is merely a hint and is not strictly enforced.

Temporary LOB Data is Stored in Temporary Tablespace

Temporary LOBs are not stored permanently in the database like other data. The data is stored in temporary tablespaces and 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 table schema, there is no meaning to the terms "inline" and "out-of-line" for temporary LOBs.

Note:

All temporary LOBs reside on the server. There is no support for client-side temporary LOBs.

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 instance terminates, all temporary LOBs are deleted.

OCI Can Group Temporary LOBs into Logical Buckets

OCI users can group temporary LOBs together into a logical bucket.

"OCIDuration" represents a store for temporary LOBs. There is a default duration for every session into which temporary LOBs are placed if you do not specify a specific duration. The default duration ends when your session ends. Also, you can perform an OCIDurationEnd operation that frees all OCIDuration contents.

Memory Handling

LOB Buffering and CACHE, NOCACHE, CACHE READS

Temporary LOBs are especially useful when you want to perform transformational operations on a LOB -- such as morphing an image, or changing a LOB from one format to another -- and then return it to the database.

These transformational operations can use LOB Buffering. You can specify CACHE,NOCACHE,or CACHE READS for each temporary LOB, and FREE an individual temporary LOB when you have no further need for it.

Temporary Tablespace

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

Explicitly Free Temporary LOB Space to Reuse It

Memory usage increases incrementally as the number of temporary LOBs grows. You can reuse temporary LOB space in your session by freeing temporary LOBs explicitly.

When the Session Finishes: 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.
When the Session Dies: 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.

Selecting a Permanent LOB INTO a Temporary LOB Locator

We previously noted that if you perform the following:

SELECT permanent_lob INTO temporary_lob_locator FROM y_blah WHERE x_blah

the temporary_lob_locator will get overwritten with the locator for the permanent_lob. The temporary_lob_locator now points to the LOB stored in the table.

Note:

Unless you saved the locator for the temporary_lob in another variable, you will lose track of the LOB that temporary_lob_locator originally pointed to before the SELECT INTO operation.

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

Since CR and rollbacks are not 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 LOB data. Temporary LOBs do not support any operations that are not supported for persistent LOB locators, but temporary LOB locators have specific features.

Features Specific to Temporary LOBs

The following features are specific to temporary LOBs:

Temporary LOB Locator is Overwritten by Permanent LOB Locator

For example, 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 by 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.
Assigning Multiple Locators to Same Temporary LOB Impacts Performance

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. This is because semantically each locator will have its own copy of the temporary LOB. Each time you use OCILobLocatorAssign, or the equivalent assignment in PL/SQL, the database makes a copy of the temporary LOB (although it may be done lazily for performance reasons).

Each locator points 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 OCILobLocatorAssign, the database copies the original temporary LOB and cause the second locator to point to the copy, not the original temporary LOB.
- Avoid Using More than One Locator For Each 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 for each 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 round trip to the server:
  - Assigning one temporary LOB to another
```
DECLARE
 Va BLOB;
 Vb BLOB;
BEGIN
 DBMS_LOB.CREATETEMPORARY(Vb,TRUE);
 DBMS_LOB.CREATETEMPORARY(Va,TRUE);
 Va := Vb;
END;
```
    This causes Oracle to create a copy of Vb and point the locator Va to it. This also frees 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 are 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.
    See Also:
    
    Oracle9i Database Concepts
    
    Oracle9i 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 overhead mentioned earlier, then persistent internal LOBs may be more suitable for such applications. More precisely:
    
    * Do not use temporary LOBs inside collections or complex objects when you are doing assignments or copies of those collections or complex objects.
    
    * Do not select LOB values into temporary LOB locators.
Freeing Temporary LOBs in User-Defined Durations. Reassigning Locators Incurs Overhead
- OCI: You will incur overhead if you have a temporary LOB in a duration, you call OCIDurationEnd on that duration, and then reassign the locator for that temporary LOB to another LOB.
  
  Irrespective of whether there was a previous OCIDurationEnd call, Oracle attempts to free the temporary LOB to which the locator pointed. If you try to access the temporary LOB with that locator you will incur an error. Once you issue OCIDurationEnd, all temporary LOBs in that duration are freed regardless of the fact that locators may still exist that used to refer to the now freed LOBs.
  
  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.
- PL/SQL: 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

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 not be able to access temporary LOBs in the new session from the original session.
- 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 is 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.

NOCOPY Restrictions

See PL/SQL User's Guide and Reference, Chapter 7: "SUBPROGRAMS" -- NOCOPY COMPILER HINT, for guidelines, restrictions, and tips on using NOCOPY.

Managing Temporary LOBs

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

Using JDBC and Temporary LOBs

Oracle9i JDBC drivers contain APIs to create and close temporary LOBs. These APIs should replace the previous workaround of using dbms_lob.createTemporary(), dbms_lob.isTemporary() and dbms_lob.freeTemporary() in dbms_lob PL/SQL package.

Using JDBC and Temporary BLOBs

The oracle.sql.BLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Blob interface. Each oracle.sql.BLOB instance represents a BLOB object in the database. Table 11-2 lists new Oracle extension APIs in oracle.sql.BLOB to access temporary BLOBs.

Table 11-2 JDBC: Temporary BLOB APIs

Methods	Description
`public static BLOB createTemporary( Connection conn, boolean cache, int duration) throws SQLException`	Creates a temporary BLOB. See
`public static boolean isTemporary( BLOB blob) throws SQLException`	Checks if the specified BLOB locator refers to a temporary BLOB
`public boolean isTemporary() throws SQLException`	Checks if the current BLOB locator refers to a temporary BLOB
`public static void freeTemporary( BLOB temp_blob) throws SQLException`	Frees the specified temporary BLOB
`public void freeTemporary() throws SQLException`	Frees the temporary BLOB

Using JDBC and Temporary CLOBs

The oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 11-3 lists the new Oracle extension APIs in oracle.sql.CLOB for accessing temporary CLOBs.

Table 11-3 JDBC: Temporary CLOB APIs

Methods	Description
`public static CLOB createTemporary( Connection conn, boolean cache, int duration) throws SQLException`	Creates a temporary CLOB
`public static boolean isTemporary( CLOB clob) throws SQLException`	Checks if the specified CLOB locator refers to a temporary CLOB
`public boolean isTemporary() throws SQLException`	Checks if the current CLOB locator refers to a temporary CLOB
`public static void freeTemporary( CLOB temp_clob) throws SQLException`	Frees the specified temporary CLOB
`public void freeTemporary() throws SQLException`	Frees the temporary CLOB

Creating a Temporary LOB

Figure 11-1 Use Case Diagram: Creating a Temporary LOB

Text description of adlob089.gif follows

Text description of the illustration adlob089.gif

Purpose

This procedure describes how to create a temporary LOB.

Usage Notes

A temporary LOB is 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.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- CREATETEMPORARY, FREETEMPORARY.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobCreateTemporary
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB DESCRIBE, LOB COPY.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE, LOB COPY
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

These examples create a temporary LOB and copy the object contents from an ad_composite image from the Print_media table to the temporary LOB. This technique is useful, for example, if you need to convert the image from one graphic format to another. The temporary LOB is read through the CACHE, and is automatically cleaned up at the end of the user's session, if it is not explicitly freed sooner.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Creating a Temporary LOB
C (OCI): Creating a Temporary LOB
COBOL (Pro*COBOL): Creating a Temporary LOB
C/C++ (Pro*C/C++): Creating a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): Creating a Temporary BLOB

PL/SQL (DBMS_LOB Package): Creating a Temporary LOB

Note:

You must set up the following data structures to use most of the examples given in this chapter:

Rem  Set up script for working with Temporary LOBs

DROP TABLE long_raw_tab;
CREATE TABLE long_raw_tab (id number, long_raw_col long raw);
INSERT INTO long_raw_tab VALUES (1, HEXTORAW('7D'));
INSERT INTO Print_media (product_id,ad_composite) SELECT 
   id,TO_LOB(long_raw_col) FROM long_raw_tab;

Note:

The DBMS_LOB.CREATETEMPORARY procedure takes an optional duration parameter. In PL/SQL, this duration parameter is used only as a hint about the duration of the LOB data. PL/SQL calculates the duration of the LOB data internally, taking into account your hint. You do not have to specify the duration of the LOB data!

DECLARE
   Dest_loc       BLOB;
   Src_loc        BLOB;
   Amount         INTEGER := 4000;
BEGIN
   SELECT ad_composite INTO Src_loc FROM Print_media 
       WHERE product_id = 3060 AND ad_id = 11001;
       
    /* Create a temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   
   /* Copy the entire object 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;

C (OCI): Creating a Temporary LOB

/* Creating a temporary LOB using C(OCI). [Example script: 3820.c]
  This function reads in one of the composite ads, ad_composite, 
  from table Print_media.It creates a temporary LOB so that you can use the
  temporary LOB to convert the image from one format to another, say JPG to 
GIFF.
  The Temporary LOB created is read through the CACHE, and is automatically 
cleaned 
  up after the your 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, *defnp2;
  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 product_id =3060: */
  sqlstmt = (text *)
     "SELECT ad_composite FROM Print_media 
         WHERE product_id = 3060 AND ad_id = 11001 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)
     || &defnp2, errhp, (ub4) 2, (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;
  }

COBOL (Pro*COBOL): Creating a Temporary LOB

     * Creating a Temporary LOB  [example script: 3821.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. CREATE-TEMPORARY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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 AD_COMPOSITE INTO :BLOB1
                FROM PRINT_MEDIA 
                WHERE PRODUCT_ID = 3060 AND AD_ID = 11001
           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.

C/C++ (Pro*C/C++): Creating a Temporary LOB

/* Creating a temporary LOB  [example script #: 3822.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 ad_composite INTO :Lob_loc FROM Print_media 
       WHERE product_ID = 3060 AND ad_id = 111001;

  /* 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;
}

Java (JDBC): Creating a Temporary BLOB

To create a temporary BLOB, the JDBC application can use the createTemporary static method defined in oracle.sql.BLOB class as follows:

/** 
 * Create a temporary blob. 
 * 
 * @param cache Specifies if LOB should be read into buffer cache or not. 
 * @param duration The duration of the temporary LOB. The following are 
 *                         valid values: DURATION_SESSION, DURATION_CALL. 
 * @return A temporary blob. 
 * @since 8.2.0 
 */ 
public static BLOB createTemporary (Connection conn, boolean cache, int 
duration)          throws SQLException

Possible values of the duration parameter are:

public static final int DURATION_SESSION

public static final int DURATION_CALL

Each call to createTemporary returns a temporary BLOB. For example,

// Make a JDBC connection 
   Connection conn = ... 

// Create a temporary BLOB 
   BLOB temporaryBlob = BLOB.createTemporary (conn, true, 
        BLOB.DURATION_SESSION);

The new createTemporary API replaces previous workarounds using DBMS_LOB.createtemporary() in the DBMS_LOB PL/SQL package.

Java (JDBC): Creating a Temporary CLOB

To create a temporary CLOB, the JDBC application can use the createTemporary static method defined in oracle.sql.CLOB class as follows:

/** 
* Create a temporary clob. 
* 
* @param cache Specifies if LOB should be read into buffer cache or not. 
* @param duration The duration of the temporary LOB. The following are 
*                            valid values: DURATION_SESSION, DURATION_CALL. 
* @return A temporary clob. 
*/ 
public static CLOB createTemporary (Connection conn, boolean cache, int 
duration) 
throws SQLException

The possible values of the duration parameter are --

public static final int DURATION_SESSION 
public static final int DURATION_CALL

Each call to createTemporary returns a temporary CLOB. For example,

// Make a JDBC connection 
   Connection conn = ... 
// Create a temporary CLOB 
   CLOB temporaryClob = CLOB.createTemporary (conn, true, CLOB.DURATION_
SESSION);

The new createTemporary API should replace the previous workaround of using dbms_lob.createtemporary() in dbms_lob PL/SQL package.

Checking If a LOB is Temporary

Figure 11-2 Use Case Diagram: Checking If a LOB is Temporary

Text description of adlob100.gif follows

Text description of the illustration adlob100.gif

Purpose

This procedure describes how to see if a LOB is temporary.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- ISTEMPORARY, FREETEMPORARY
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobIsTemporary
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB DESCRIBE, ISTEMPORARY.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE...ISTEMPORARY
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): Oracle9i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle9i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

These are generic examples that query whether the locator is associated with a temporary LOB or not.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Checking If a LOB is Temporary
C (OCI): Checking If a LOB is Temporary
COBOL (Pro*COBOL): Checking If a LOB is Temporary
C/C++ (Pro*C/C++): Checking If a LOB is Temporary
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): Checking if a BLOB is Temporary
Java (JDBC): Checking if a CLOB is Temporary .

PL/SQL (DBMS_LOB Package): Checking If a LOB is Temporary

/* Checking if a LOB is temporary. [Example script: 3828.sql]
   This is an example of freeing a temporary LOB. First test to make
   sure that the LOB locator points to a temporary LOB, then free it.
   Otherwise, 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;

C (OCI): Checking If a LOB is Temporary

/* Checking if a LOB is temporary.  [Example script: 3829.c]
   This function frees a temporary LOB. It takes a locator as an argument,   
   checks to see if it is a temporary LOB. If it is, the function frees 
   the temporary LOB. Otherwise, it prints out a message saying the locator 
   was not a temporary LOB locator. This function returns 0 if it 
   completes successfully, -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;
  }

COBOL (Pro*COBOL): Checking If a LOB is Temporary


     * Checking if a LOB is temporary  [Example script: 3830.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-ISTEMP.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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.

C/C++ (Pro*C/C++): Checking If a LOB is Temporary

/* Checking if a LOB is temporary  [Example script: 3831.pc]
#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;
   
  /* Note that in this example, isTemporary should be 1 (TRUE) */
  if (isTemporary)
    printf("Locator is a Temporary LOB locator\n");
  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Temp_loc;
else
    printf("Locator is not a Temporary LOB locator \n");
   }

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

Java (JDBC): Checking if a BLOB is Temporary

To see if a BLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current BLOB object is temporary, or pass the BLOB object to the static isTemporary method to determine whether the specified BLOB object is temporary. These two methods are defined as follows:

/** 
* Checking if a BLOB is temporary. [Example script: 3833.java]
* Returns true if LOB locator points to a temporary BLOB, False if not. 
* @param lob the BLOB to test. 
* @returns true if LOB locator points to a temporary BLOB, False if not. 
*/ 
  public static boolean isTemporary (BLOB lob) throws SQLException 

/** 
 * Returns true if LOB locator points to a temporary BLOB, False if not. 
 * @returns true if LOB locator points to a temporary BLOB, False if not. 
 */ 
   public boolean isTemporary () throws SQLException

  //The usage example is--

  BLOB blob = ... 

  // See if the BLOB is temporary 
  boolean isTemporary = blob.isTemporary (); 

  // See if the specified BLOB is temporary 
  boolean isTemporary2 = BLOB.isTemporary(blob);

This JDBC API replaces previous workarounds that use
DBMS_LOB.isTemporary().

Java (JDBC): Checking if a CLOB is Temporary

To see if a CLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current CLOB object is temporary, or pass the CLOB object to the static isTemporary method to determine whether the specified CLOB object is temporary. These two methods are defined as follows:

/** 
 * Checking if LOB is temporary  [Example script: 3834.java]
 * Return true if the LOB locator points to a temporary CLOB, False if it 
 * does not. 
 * 
 * @param lob the BLOB to test. 
 * @return true if the LOB locator points to a temporary CLOB, False if it 
 *         does not. 
 */ 
  public static boolean isTemporary (CLOB lob) throws SQLException 

  /** 
   * Return true if the LOB locator points to a temporary CLOB, False if it 
   * does not. 
   * 
   * @return true if the LOB locator points to a temporary CLOB, False if it 
   *         does not. 
   */ 
    public boolean isTemporary () throws SQLException 

  //The usage example is--

  CLOB clob = ... 

  // See if the CLOB is temporary 
  boolean isTemporary = clob.isTemporary (); 

  // See if the specified CLOB is temporary 
  boolean isTemporary2 = CLOB.isTemporary(clob);

This API replaces previous workarounds that used DBMS_LOB.istemporary().

Freeing a Temporary LOB

Figure 11-3 Use Case Diagram: Freeing a Temporary LOB

Text description of adlob024.gif follows

Text description of the illustration adlob024.gif

Purpose

This procedure describes how to free a temporary LOB.

Usage Notes

A temporary LOB instance can only be destroyed for example, in 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.

Syntax

See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment.

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- FREETEMPORARY
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobFreeTemporary
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB FREE TEMPORARY.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB FREE TEMPORARY
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 3, "LOB Support in Different Programmatic Environments", Table 3-50, "JDBC: Temporary BLOB APIs". Note: temporaryClob.java has been deprecated as there is no longer a need for this class in the JDBC standard.

Scenario

Not applicable.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Freeing a Temporary LOB
C (OCI): Freeing a Temporary LOB
COBOL (Pro*COBOL): Freeing a Temporary LOB
C/C++ (Pro*C/C++): Freeing a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): Freeing a Temporary BLOB and Java (JDBC): Freeing a Temporary CLOB.
Java (JDBC): Creating and Freeing a Temporary CLOB Using TemporaryClob.java has been deprecated.

PL/SQL (DBMS_LOB Package): Freeing a Temporary LOB

/*  Freeing a temporary LOB  [Example script: 3836.sql]
DECLARE
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('ADPHOTO_DIR', 'monitor_3060_11001');
   Amount         INTEGER := 4000;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   /* 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;

C (OCI): Freeing a Temporary LOB

/* Freeing a temporary LOB.  [Example script: 3837.c]
   This function creates a temporary LOB and then frees it: 
   This function returns 0 if it completes successfully, -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():freeTempLob\n");
      return -1;
  }

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

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

COBOL (Pro*COBOL): Freeing a Temporary LOB

     * Freeing a temporary LOB  [Example script: 3838.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. FREE-TEMPORARY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
        
       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.

C/C++ (Pro*C/C++): Freeing a Temporary LOB

/* Freeing a temporary LOB. [Example script: 3839.pc]
#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;
}

Java (JDBC): Freeing a Temporary BLOB

To free a temporary BLOB, the JDBC application can either use the freeTemporary instance method to free the current BLOB object, or pass the temporary BLOB to be freed to the static freeTemporary method to free the specified temporary BLOB. These two methods are defined as follows:

/** 
* Freeing a temporary BLOB.  [Example script:  3840.java]
* This example frees the contents and locator of a temporary BLOB. 
* @param temp_lob A temporary BLOB to be freed. 
* @exception SQLException if temp_lob is a permanent LOB or temp_lob has already 
* been freed. 
*/ 

public static void freeTemporary (BLOB temp_lob) throws SQLException 

/** 
* Free the contents and the locator of the temporary BLOB. 
* @exception SQLException if self is a permanent LOB or self has already been 
freed. 
*/ 

public void freeTemporary() throws SQLException

/**
* The usage example example is -- 
* BLOB tempBlob1 = ... 
* BLOB tempBlob2 = ... 
* // free the temporary BLOB 
* tempBlob1.freeTemporary (); 
* // free the specified temporary BLOB 
* BLOB.freeTemporary(tempBlob2);
*
* The newer freeTemporary APIs should replace previous workaround of 
* using dbms_lob.freeTemporary() in dbms_lob PL/SQL package. 
*/

The freeTemporary APIs should replace the previous workaround of using dbms_lob.freeTemporary() in dbms_lob PL/SQL package.

Java (JDBC): Freeing a Temporary CLOB

To free a temporary CLOB, the JDBC application can either use the freeTemporary instance method to free the current CLOB object, or pass the temporary CLOB to be freed to the static freeTemporary method to free the specified temporary CLOB. These two methods are defined as follows:

/** 
* Freeing a temporary CLOB.  [Example script: 3841.java]
* Free the contents and the locator of the temporary blob. 
* @param temp_lob A temporary CLOB to be freed. 
* @since 8.2.0 
* @exception SQLException if temp_lob is a permanent LOB or temp_lob has 
*            already been freed. 
*/ 

public static void freeTemporary (CLOB temp_lob) throws SQLException 

/** 
* Free the contents and the locator of the temporary CLOB. 
* 
* @since 8.2.0 
* @exception SQLException if self is a permanent lob or self has 
*            already been freed. 
*/ 
public void freeTemporary() throws SQLException


/**
* Use the free temporary CLOB API as follows:
* CLOB tempClob1 = ... 
* CLOB tempClob2 = ... 
* // free the temporary CLOB 
* tempClob1.freeTemporary (); 
* // free the specified temporary CLOB 
* CLOB.freeTemporary(tempClob2);
*
*The freeTemporary API replaces previous workarounds that use DBMS_
LOB.freetemporary().
*/

The freeTemporary API replaces previous workarounds that use DBMS_LOB.freetemporary().

Java (JDBC): Creating and Freeing a Temporary CLOB Using TemporaryClob.java

Note:

temporaryClob.java and temporaryBlob.java classes have been deprecated. These classes are no longer needed or used in the JDBC standard.

Loading a Temporary LOB with Data from a BFILE

Figure 11-4 Use Case Diagram: Loading a LOB with Data from a BFILE

Text description of adlob083.gif follows

Text description of the illustration adlob083.gif

See Also:

Purpose

This procedure describes how to load a temporary LOB with data from a BFILE.

Usage Notes

Note:

The LOADBLOBFROMFILE and LOADCLOBFROMFILE procedures implement the functionality of this procedure and provide improved features for loading binary data and character data. The improved procedures are available in the PL/SQL environment only. When possible, using one of the improved procedures is recommended. See "Loading a Temporary BLOB with Binary Data from a BFILE" and "Loading a Temporary CLOB/NCLOB with Character Data from a File" for more information.

Binary Data to Character Set Conversion is Needed on BFILE Data

In using 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. When you use the DBMS_LOB.LOADFROMFILE procedure to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. No implicit translation is performed from binary data to a character set. For this reason, you should use the LOADCLOBFROMFILE procedure when loading text (see Loading a Temporary CLOB/NCLOB with Character Data from a File on).

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- CREATETEMPORARY, FREETEMPORARY
C (OCI): Oracle Call Interface Programmer's Guide) Chapter 15, "Relational Functions" -- LOB Functions,OCILobLoadFromFile
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB LOAD.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB LOAD
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Loading a LOB with Data from a BFILE" .

Scenario

The example procedures assume that there is an operating system source directory (ADPHOTO_DIR) that contains the LOB data to be loaded into the target LOB.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Loading a Temporary LOB with Data from a BFILE
C (OCI): Loading a Temporary LOB with Data from a BFILE
COBOL (Pro*COBOL): Loading a Temporary LOB with Data from a BFILE
C/C++ (Pro*C/C++): Loading a Temporary LOB with Data from a BFILE
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Loading a Temporary LOB with Data from a BFILE

/* Loading a temporary LOB with data from a BFILE.  
   Example procedure freeTempLob_proc is not part of DBMS_LOB package: */

CREATE or REPLACE PROCEDURE freeTempLob_proc(Lob_loc IN OUT BLOB) IS
BEGIN
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE);
   /* 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;

C (OCI): Loading a Temporary LOB with Data from a BFILE

/* Loading a temporary LOB with data from a BFILE.
   This section of code shows you how to create a temporary LOB, and load 
   the contents of a BFILE into that 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 *)"ADPHOTO_DIR",
                       (ub2)strlen("ADPHOTO_DIR"), (text *)"monitor_3060_11001",
                       (ub2)strlen("monitor_3060_11001")))
  {
    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;
  }
  }

COBOL (Pro*COBOL): Loading a Temporary LOB with Data from a BFILE

     * Loading a temporary LOB with data from a BFILE. 
       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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo3106_13001" 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.

C/C++ (Pro*C/C++): Loading a Temporary LOB with Data from a BFILE

/* Loading a temporary LOB with data from a BFILE. */
#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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
)

Loading a Temporary BLOB with Binary Data from a BFILE

Figure 11-5 Use Case Diagram: Loading a BLOB with Data from a BFILE

Text description of adlob171.gif follows

Text description of the illustration adlob171.gif

See Also:

Purpose

This procedure describes how to load a temporary BLOB with binary data from a BFILE.

Usage Notes

Use LOADBLOBFROMFILE for loading of binary data and LOADCLOBFROMFILE for text loading. This achieves the same outcome as LOADFROMFILE as well as returning the new offsets to the user. The LOADCLOBFROMFILE API allows you to specify the character set id of the BFILE ensuring that the character set is properly converted from the BFILE data character set to the destination CLOB/NCLOB character set.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- LOADBLOBFROMFILE

Scenario

The example procedures in this section use the Print_media table of the Product Media sample schema and assume that there is an operating system source directory that contains the binary LOB data to be loaded into the target BLOB.

Examples

The example, "PL/SQL (DBMS_LOB Package): Loading a Temporary BLOB with BFILE data", illustrates LOADBLOBFROMFILE usage in the PL/SQL programmatic environment. (Other programmatic environments are not supported.)

PL/SQL (DBMS_LOB Package): Loading a Temporary BLOB with BFILE data

The following example illustrates:

How to load a temporary BLOB with binary data from a BFLIE.
How to load a BLOB with BFILE data without getting the length of the BFILE first.

How to use the source offset and destination offset values returned by LOADBLOBFROMFILE to determine the size of the BLOB.

DECLARE
  src_loc     BFILE := bfilename('ADPHOTO_DIR','monitor_3060_11001'); 
  dst_loc     BLOB;
  src_offset  NUMBER := 1;
  dst_offset  NUMBER := 1;
  src_osin    NUMBER;
  dst_osin    NUMBER;
  bytes_rd    NUMBER;
  bytes_wt    NUMBER; 
BEGIN
  dbms_lob.createtemporary(dst_loc, TRUE);

  /* Opening the source BFILE is mandatory. */
  dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);

  /* Opening the LOB is optional. */
  dbms_lob.open(dst_loc, dbms_lob.lob_readwrite);
  /* Save the input source/destination offsets. */
  src_osin := src_offset;
  dst_osin := dst_offset;
  
  /* Use LOBMAXSIZE to indicate loading the entire BFILE. */
  dbms_lob.LOADBLOBFROMFILE(dst_loc, src_loc, dbms_lob.lobmaxsize, src_offset, 
      dst_offset) ;

  /* Closing the LOB is mandatory if you have opened it */
  dbms_lob.close(dst_loc);
  dbms_lob.filecloseall();

  /* Use the src_offset returned to calculate the actual 
   * amount read from the BFILE. 
   */
  bytes_rd := src_offset - src_osin;
  dbms_output.put_line(' Number of bytes read from the BFILE ' || bytes_rd ) ;
  
  /* Use the dst_offset returned to calculate the actual 
   * amount written to the BLOB. 
   */
  bytes_wt := dst_offset - dst_osin;
  dbms_output.put_line(' Number of bytes written to the BLOB ' || bytes_wt ) ;
  
  /* If there is no exception the number of bytes read 
   * should equal to the number of bytes written.
   */

  /* Free the temporary LOB. */
  dbms_lob.freetemporary(dst_loc);

END ;

Loading a Temporary CLOB/NCLOB with Character Data from a File

Figure 11-6 Use Case Diagram: Loading a CLOB or NCLOB with Data from a BFILE

Text description of adlob172.gif follows

Text description of the illustration adlob172.gif

See Also:

Purpose

This procedure describes how to load a temporary CLOB or NCLOB with character data from a BFILE.

Usage Notes

Use LOADBLOBFROMFILE for loading of binary data and LOADCLOBFROMFILE for loading of text as the latter method lets you specify the character set id of the BFILE. The LOADCLOBFROMFILE API allows you to specify the character set id of the BFILE ensuring that the character set is properly converted from the BFILE data character set to the destination CLOB/NCLOB character set.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- LOADCLOBFROMFILE

Scenario

The example procedures in this section use the Print_media table of the Product Media sample schema and assume that there is an operating system source directory that contains the character LOB data to be loaded into the target CLOB or NCLOB.

Examples

The example, "PL/SQL (DBMS_LOB Package): Loading a Temporary CLOB/NCLOB with BFILE Data", illustrates LOADCLOBFROMFILE usage in the PL/SQL programmatic environment. (Other programmatic environments are not supported.)

PL/SQL (DBMS_LOB Package): Loading a Temporary CLOB/NCLOB with BFILE Data

Example

The following example illustrates:

How to load a temporary CLOB or NCLOB with character data from BFILE.
How load the entire file without calling getlength on the BFILE.
How to find out the actual amount loaded using return offsets.

This example assumes that ad_source is a BFILE in UTF8 character set format and the database character set is UTF8.

DECLARE
  src_loc     bfile := bfilename('ADVERT_DIR','ad_source_1000');
  dst_loc     clob  ;
  amt         number := dbms_lob.lobmaxsize;
  src_offset  number := 1 ;
  dst_offset  number := 1 ;
  lang_ctx    number := dbms_lob.default_lang_ctx;
  warning     number;
BEGIN
  dbms_lob.createtemporary(dst_loc, TRUE);
  dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);

  /* The default_csid can be used when the BFILE encoding is in the same charset
   * as the destination CLOB/NCLOB charset
   */

  dbms_lob.LOADCLOBFROMFILE(dst_loc, src_loc, amt, dst_offset, src_offset,
      dbms_lob.default_csid, lang_ctx,warning) ;
  commit ;

  dbms_output.put_line(' Amount specified ' || amt ) ;
  dbms_output.put_line(' Number of bytes read from source: ' || 
      (src_offset-1));
  dbms_output.put_line(' Number of characters written to destination: ' || 
      (dst_offset-1) );
  if (warning = dbms_lob.warn_inconvertible_char) 
  then
    dbms_output.put_line('Warning: Inconvertible character');
  end if;

  dbms_lob.filecloseall() ;
  dbms_lob.freetemporary(dst_loc);
END ;

Example

The following example illustrates:

How to get the character set ID from the character set name using the NLS_CHARSET_ID function.
How to load a stream of data from a single BFILE into different LOBs using the returned offset value and the language context lang_ctx.
How to read a warning message.

This example assumes that ad_file_ext_01 is a BFILE in JA16TSTSET format and the database national character set is AL16UTF16.

DECLARE
  src_loc     bfile := bfilename('ADVERT_DIR','ad_file_ext_01') ;
  dst_loc1    nclob;
  dst_loc2    nclob;
  amt         number := 1000;
  src_offset  number := 1;
  dst_offset  number := 1;
  src_osin    number;
  cs_id       number := NLS_CHARSET_ID('JA16TSTSET'); /* 998 */
  lang_ctx    number := dbms_lob.default_lang_ctx;
  warning     number;
BEGIN
  dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
  dbms_output.put_line(' BFILE csid is ' || cs_id) ;

  /* Load the first 1KB of the BFILE into dst_loc1 */
  dbms_output.put_line(' ----------------------------' ) ;
  dbms_output.put_line('   First load  ' ) ;
  dbms_output.put_line(' ----------------------------' ) ;

  dbms_lob.createtemporary(dst_loc1, TRUE);

  dbms_lob.LOADCLOBFROMFILE(dst_loc1, src_loc, amt, dst_offset, src_offset, 
      cs_id, lang_ctx,warning);
  commit;

  /* The number bytes read may or may not be 1k */
  dbms_output.put_line(' Amount specified ' || amt ) ;
  dbms_output.put_line(' Number of bytes read from source: ' || 
      (src_offset-1) );
  dbms_output.put_line(' Number of characters written to destination: ' ||
      (dst_offset-1) );
  IF (warning = dbms_lob.warn_inconvertible_char) 
  then
    dbms_output.put_line('Warning: Inconvertible character');
  END IF;

  /* load the next 1KB of the BFILE into the dst_loc2 */
  dbms_output.put_line(' ----------------------------' ) ;
  dbms_output.put_line('   Second load  ' ) ;
  dbms_output.put_line(' ----------------------------' ) ;

  dbms_lob.createtemporary(dst_loc2, TRUE);
  /* Notice we are using the src_offset and lang_ctx returned from the previous
   * load. We do not use value 1001 as the src_offset here because sometimes the
   * actual amount read may not be the same as the amount specified.
   */
  src_osin := src_offset;
  dst_offset := 1;
  dbms_lob.LOADCLOBFROMFILE(dst_loc2, src_loc, amt, dst_offset, src_offset,
      cs_id, lang_ctx, warning);
  commit;
  dbms_output.put_line(' Number of bytes read from source: ' ||
      (src_offset-src_osin) );
  dbms_output.put_line(' Number of characters written to destination: ' ||
      (dst_offset-1) );
  if (warning = dbms_lob.warn_inconvertible_char)
  then
    dbms_output.put_line('Warning: Inconvertible character');
  END IF;

  dbms_lob.filecloseall() ;
  dbms_lob.freetemporary(dst_loc1);
  dbms_lob.freetemporary(src_loc2);

END ;

Determining If a Temporary LOB Is Open

Figure 11-7 Use Case Diagram: Determining If a Temporary LOB Is Open

Text description of adlob099.gif follows

Text description of the illustration adlob099.gif

Purpose

This procedure describes how to see if a temporary LOB is open.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- ISOPEN
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobIsOpen
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB DESCRIBE...ISOPEN.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE...ISOPEN
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Checking if a LOB is Open" .

Scenario

These generic examples takes a locator as input, create a temporary LOB, open it and test if the LOB is open.

Examples

Examples are provided in the following programmatic environments:

PL/SQL: Determining if a Temporary LOB is Open
C (OCI) Determining if a Temporary LOB is Open
COBOL (Pro*COBOL): Determining if a Temporary LOB is Open
C/C++ (Pro*C/C++): Determining if a Temporary LOB is Open
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL: Determining if a Temporary LOB is Open

/* Determining if a temporary LOB is open. 
   Example procedure seeTempLOBIsOpen_proc is not part of 
   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);
   /* 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;

C (OCI) Determining if a Temporary LOB is Open

/* Determining if a temporary LOB is open. 
   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;

    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,lob_loc))
    {
        printf("OCILobFreeTemporary FAILED \n");
        return -1;
    }
    return 0;
    }

COBOL (Pro*COBOL): Determining if a Temporary LOB is Open

     * Determining if a temporary LOB is open. [Example script: 3850.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-ISOPEN.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) 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  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.
           EXEC SQL
                ROLLBACK WORK RELEASE
           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.

C/C++ (Pro*C/C++): Determining if a Temporary LOB is Open

/* Determining if a temporary LOB is open. [Example script: 3851.pc] */ 
#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;
}

Displaying Temporary LOB Data

Figure 11-8 Use Case Diagram: Displaying Temporary LOB Data

Text description of adlob080.gif follows

Text description of the illustration adlob080.gif

Purpose

This procedure describes how to display temporary LOB data.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- OPEN, LOADFROMFILE, READ. Chapter 29, "DBMS_OUTPUT" -- PUT_LINE
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobRead
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB READ.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB READ
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Displaying LOB Data" .

Scenario

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

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Displaying Temporary LOB Data
C (OCI): Displaying Temporary LOB Data
COBOL (Pro*COBOL): Displaying Temporary LOB Data
C/C++ (Pro*C/C++): Displaying Temporary LOB Data
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Displaying Temporary LOB Data

/* DIsplaying temporary LOB data. [Example script: 3853.sql]
   The following function accesses the monitor_photo 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('ADPHOTO_DIR', 'monitor_photo_3060_11001');
   Amount         INTEGER := 128;
   Bbuf           RAW(128);
   Position       INTEGER :=1;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   /* 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;

C (OCI): Displaying Temporary LOB Data

/* Displaying temporary LOB data.  [Example script: 3854.c]
   This function accesses the monitor_photo file for product 3060 with ad_id
   11001. It creates a temporary LOB, loads some data from the file, 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 "ADPHOTO_DIR". It also assumes that the file is at least
   14000 bytes long, which is the amount 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*)"ADPHOTO_DIR",
                       (ub2)
                       strlen("ADPHOTO_DIR"),(text*)"monitor_photo_3060_11001",
                       (ub2)strlen("monitor_photo_3060_11001")))
  {
      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_ERROR:
      /*  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;
  }

COBOL (Pro*COBOL): Displaying Temporary LOB Data

     * Displaying temporary LOB data. [Example script: 3855.pco]
       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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" 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.

C/C++ (Pro*C/C++): Displaying Temporary LOB Data

/* Displaying temporary LOB data. */
#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 displayTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
}

Reading Data from a Temporary LOB

Figure 11-9 Use Case Diagram: Reading Data from a Temporary LOB

Text description of adlob085.gif follows

Text description of the illustration adlob085.gif

Purpose

This procedure describes how to read data from a temporary LOB.

Usage Notes

Stream Reading

The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled using 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, input to the callback, indicates how many bytes are filled in the buffer. 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.).

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- READ
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobRead
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB READ.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB READ
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs","Java (JDBC): Reading Data from a LOB" .

Scenario

These examples read LOB data from the AD_PHOTO LOB column.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Reading Data from a Temporary LOB
C (OCI): Reading Data from a Temporary LOB
COBOL (Pro*COBOL): Reading Data from a Temporary LOB
C/C++ (Pro*C/C++): Reading Data from a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Reading Data from a Temporary LOB

/* Reading temporary LOB data. [Example script: 3859.sql]
   PL/SQL does not support streaming reads. See the OCI example for an 
   illustration of streaming reads: */ 
DECLARE
  Dest_loc       BLOB;
  Src_loc        BFILE := BFILENAME('ADPHOTO_DIR', 'keyboard_photo_3106_13001');
  Amount         INTEGER := 4000;
  Bbuf           RAW(32767);
  Position       INTEGER :=1;
BEGIN
    DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
    /* 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);
END;

C (OCI): Reading Data from a Temporary LOB

/* Reading temporary LOB data.  [Example script: 3861.c]
   This is the same example as for reading and displaying data from a   
   temporary LOB. This function takes the 'monitor_photo for product 3060, 
   ad_id 11001' 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, -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 *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"); 

  if (OCIDescriptorAlloc((dvoid **)&bfile,
                         (ub4)OCI_DTYPE_LOB, (size_t)0,
                         (dvoid**)0)) 
 
 /* Create a temporary LOB: */
 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(OCILobFileSetName(envhp, errhp, &bfile,(text*)"ADPHOTO_DIR",
                     (ub2)strlen("ADPHOTO_DIR"),
                     (text*)"monitor_photo_3060_11001",
                     (ub2)strlen("monitor_photo_3060_11001")))
  {
      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,lob_loc,(OCILobLocator*)bfile,(ub4)amount,
                       (ub4)1,(ub4)1))
 {
     printf( "OCILobLoadFromFile FAILED\n");
     return -1;
 }
 
 offset = 1;
 memset(bufp, '\0', MAXBUFLEN);
 
 retval = OCILobRead(svchp, errhp, lob_loc, &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_ERROR:
      /*  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, lob_loc, &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;
  }
  if (OCIDescriptorFree ((dvoid*) lob_loc, (ub4) OCI_DTYPE_LOB))
  {
     printf ("failed in OCIDescriptor Free\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;  
}

sb4 check_and_free_temp(OCILobLocator *tblob,
                        OCIError      *errhp,
                        OCISvcCtx     *svchp,
                        OCIStmt       *stnthp,
                        OCIEnv        *envhp)
{
  boolean is_temp;
  is_temp = FALSE;
  if (OCILobIsTemporary (envhp,errhp, tblob, &is_temp))
  {
    printf ("FAILED: OciLobIsTemporary call \n");
  }
  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;
}

COBOL (Pro*COBOL): Reading Data from a Temporary LOB

     * Reading temporary LOB data. [Example script: 3862.pco]
       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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" 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.

C/C++ (Pro*C/C++): Reading Data from a Temporary LOB

/*  Reading temporary LOB data.  [Example script: 3863.pc] */
#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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
}

Reading Portion of Temporary LOB (Substr)

Figure 11-10 Use Case Diagram: Reading Portion of Temporary LOB from the Table (Substr)

Text description of adlob081.gif follows

Text description of the illustration adlob081.gif

Purpose

This procedure describes how to read portion of a temporary LOB (substr).

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- SUBSTR
C (OCI): There is no applicable syntax reference for this use case.
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB READ.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB LOAD. See also PL/SQL DBMS_LOB.SUBSTR.
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Reading a Portion of the LOB (substr)" .

Scenario

These examples show the operation in terms of reading a portion of the image in the AD_PHOTO column.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Reading a Portion of Temporary LOB (substr)
C (OCI): No example is provided with this release.
COBOL (Pro*COBOL): Reading a Portion of Temporary LOB (substr)
C/C++ (Pro*C/C++): Reading a Portion of Temporary LOB (substr)
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Reading a Portion of Temporary LOB (substr)

/* Reading portion of a temporary LOB using substr. [Example script: 3864.sql]
   Example procedure substringTempLOB_proc is not part of DBMS_LOB package.
   This example assumes you have a 'monitor_photo_3060_11001' file in a
   directory which has a ADPHOTO alias */
CREATE or REPLACE PROCEDURE substringTempLOB_proc IS
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('ADPHOTO_DIR', 'monitor_photo_3060_11001');
   Amount         INTEGER := 32767;
   Bbuf           RAW(32767);
   Position       INTEGER :=128;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   /* 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;

COBOL (Pro*COBOL): Reading a Portion of Temporary LOB (substr)

     * Reading portion of a temporary LOB using substr 
     * [Example script: 3865.pco]
       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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" 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.

C/C++ (Pro*C/C++): Reading a Portion of Temporary LOB (substr)

/* Reading a portion of a temporary LOB.  [Example script: 3866.pc]
   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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
}

Comparing All or Part of Two Temporary LOBs

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

Text description of adlob077.gif follows

Text description of the illustration adlob077.gif

Purpose

This procedure describes how to compare all or part of two temporary LOBs.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- COMPARE
C (OCI): There is no applicable syntax reference for this use case.
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB COPY. See also PL/SQL DBMS_LOB.COMPARE.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB COPY. See also PL/SQL DBMS_LOB.COMPARE.
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Comparing All or Part of Two LOBs" .

Scenario

The following examples compare two composite advertisements to see whether they are different. Depending on the result of the comparison, the examples insert the composite into the table.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Comparing All or Part of Two Temporary LOBs
C (OCI): No example is provided with this release.
COBOL (Pro*COBOL): Comparing All or Part of Two Temporary LOBs
C/C++ (Pro*C/C++): Comparing All or Part of Two Temporary LOBs
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Comparing All or Part of Two Temporary LOBs

/* Comparing all or part of two temporary LOBs [Example script: 3868.sql]
   The procedure compareTwoTemporPersistLOBs_proc is not part 
   of DBMS_LOB package. */

CREATE OR REPLACE PROCEDURE compareTwoTmpPerLOBs_proc IS
    Lob_loc1 BLOB;
    Lob_loc2 BLOB;
    Temp_loc BLOB;
    Amount   INTEGER := 32767;
    Retval   INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT ad_composite INTO Lob_loc1 FROM Print_media
        WHERE product_ID = 3060 AND ad_id = 11001;
    SELECT ad_composite INTO Lob_loc2 FROM Print_media
        WHERE product_ID = 2268 AND ad_id = 21001;
        
    /* Copy one of the composite ad files into a temp LOB and convert
       it to a different format before comparing the ads : */
    DBMS_LOB.CREATETEMPORARY(Temp_loc, TRUE);
    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 you are done using it: */
    DBMS_LOB.FREETEMPORARY(Temp_loc);
    END;

COBOL (Pro*COBOL): Comparing All or Part of Two Temporary LOBs

     * Comparing all or part of two temporary LOBs [Example script: 3869.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. BLOB-COMPARE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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 AD_COMPOSITE INTO :BLOB1
                FROM PRINT_MEDIA M WHERE M.PRODUCT_ID = 3106 AND AD_ID = 13001
           END-EXEC.
 
           EXEC SQL 
                SELECT AD_COMPOSITE INTO :BLOB2
                FROM PRINT_MEDIA M WHERE M.PRODUCT_ID = 2268 AND AD_ID = 21001
           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.

C/C++ (Pro*C/C++): Comparing All or Part of Two Temporary LOBs

/* Comparing all or part of two temporary LOBs. [Example script: 3870.pc]
#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 ad_composite INTO :Lob_loc1
     FROM Print_media WHERE Product_ID = 3060 AND ad_id = 11001;
  EXEC SQL SELECT ad_composite INTO :Lob_loc2
     FROM Print_media WHERE Product_ID = 2268 AND ad_id = 21001;
  /* 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;
}

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

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

Text description of adlob078.gif follows

Text description of the illustration adlob078.gif

Purpose

This procedure describes how to see if a pattern exists in a temporary LOB (instr).

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- INSTR
C (OCI): There is no applicable syntax reference for this use case.
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB COPY. See also PL/SQL DBMS_LOB.INSTR.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB COPY. See also DBMS_LOB.INSTR.
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Checking for Patterns in the LOB (instr)" .

Scenario

The following examples examine the advertisement text to see if the string "children" is present.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Determining If a Pattern Exists in a Temporary LOB
C (OCI): No example is provided with this release.
COBOL (Pro*COBOL): Determining If a Pattern Exists in a Temporary LOB (instr)
Table , "C/C++ (Pro*C/C++): Determining If a Pattern Exists in a Temporary LOB (instr)"
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Determining If a Pattern Exists in a Temporary LOB

/* Determining if a pattern exists in a temporary LOB. [Example script: 
3871.sql]
   Procedure instringTempLOB_proc is not part of 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);
   SELECT ad_sourcetext INTO Lob_loc FROM Print_media 
      WHERE Product_ID = 3060 AND ad_id = 11001;

   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;

COBOL (Pro*COBOL): Determining If a Pattern Exists in a Temporary LOB (instr)

* Determining if a pattern exists in a temporary LOB. [Example script: 3872.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. CLOB-INSTR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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 AD_SOURCETEXT INTO :CLOB1
                FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 11001
           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.

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

/* Determining if a pattern exists in a temporary LOB using instr
   [Example script: 3873.pc] */
#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 ad_sourcetext INTO :Lob_loc
     FROM print_media WHERE product_ID = 3060 AND ad_id = 11001;
  /* 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;
}

Finding the Length of a Temporary LOB

Figure 11-13 Use Case Diagram: Finding the Length of a Temporary LOB

Text description of adlob079.gif follows

Text description of the illustration adlob079.gif

Purpose

This procedure describes how to get the length of a temporary LOB.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- GETLENGTH
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobGetLength
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB DESCRIBE... GET LENGTH.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE...GET LENGTH
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Determining the Length of a LOB" .

Scenario

The following examples get the length of the photo LOB to determine whether it will run over the 4 gigabyte limit.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Finding the Length of a Temporary LOB
C (OCI): Finding the Length of a Temporary LOB
COBOL (Pro*COBOL): Finding the Length of a Temporary LOB
C/C++ (Pro*C/C++): Finding the Length of a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Finding the Length of a Temporary LOB

/* Finding the length of a temporary LOB.  [Example script: 3874.sql]
   Procedure getLengthTempCLOB_proc is not part of 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('ADPHOTO_DIR', 'monitor_photo_3060_11001');
BEGIN
    DBMS_LOB.CREATETEMPORARY(tlob,TRUE);
    /* 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;

C (OCI): Finding the Length of a Temporary LOB

/* Finding the length of a temporary LOB. [Example script: 3875.c]
   This function takes a temporary LOB locator 'amount' as argument, and 
   prints out the length of the corresponding LOB. The function returns 
   0 if it completes successfully, -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 *)"ADPHOTO_DIR", 
                       (ub2)strlen("ADPHOTO_DIR"), 
                       (text *)"monitor_photo_3060_11001", 
                       (ub2)strlen("monitor_photo_3060_11001")))
  {
     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;
  }

COBOL (Pro*COBOL): Finding the Length of a Temporary LOB

     * Finding the length of a temporary LOB. [Example script: 3876.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-LENGTH.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) 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  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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" 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.

C/C++ (Pro*C/C++): Finding the Length of a Temporary LOB

/* Finding the length of a temporary LOB.  [Example script: 3877.pc]  */
#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 = "ADPHOTO_DIR", *Name = "modem_photo_2268_21001";
  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;
}

Copying All or Part of One Temporary LOB to Another

Figure 11-14 Use Case Diagram: Copying All or Part of One Temporary LOB to Another

Text description of adlob086.gif follows

Text description of the illustration adlob086.gif

Purpose

This procedure describes how to copy all or part of one temporary LOB to another.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- COPY
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobCopy.
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB COPY.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB COPY
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Copying All or Part of One LOB to Another LOB" .

Scenario

Assume the following table:

CREATE TABLE ad_library_tab of adheader_typ;

INSERT INTO adheader_tab 
    (SELECT * FROM adheaderlib_tab Vtab1
       WHERE T2.creation_date = '08/16/2001');

These examples create a new LOB locator in table adheader_tab, and copy LOB data from Vtab1 to the location pointed to by a new LOB locator inserted into table adheader_tab.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Copying All or Part of One Temporary LOB
C (OCI): Copying All or Part of One Temporary LOB to Another
COBOL (Pro*COBOL): Copying All or Part of One Temporary LOB to Another
C/C++ (Pro*C/C++): Copying All or Part of One Temporary LOB to Another
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Copying All or Part of One Temporary LOB

/* Copying all or part of one temporary LOB to another. [Example script: 
3880.sql]
   Procedure copyTempLOB_proc is not part of 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('ADPHOTO_DIR', 'monitor_photo_3060_11001');
   Amount       INTEGER := 32767;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE);
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   /* 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;

C (OCI): Copying All or Part of One Temporary LOB to Another

/* Copying all or part of one temporary LOB to another. [Example script: 3881.c]
   This function 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, -1 otherwise. */
   sb4 copy_temp_lobs (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 *)"ADPHOTO_DIR",
                       (ub2)strlen("ADPHOTO_DIR"),
                       (text *)"monitor_photo_3060_11001",
                       (ub2)strlen("monitor_photo_3060_11001")))
  {
    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;
}

COBOL (Pro*COBOL): Copying All or Part of One Temporary LOB to Another

     * Copying all or part of one temporary LOB to another
     * [Example script: 3882.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-BLOB-COPY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" 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.

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

/* Copying all or part of one temporary LOB to another. [Example script: 
3883.pc] */
#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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
}

Copying a LOB Locator for a Temporary LOB

Figure 11-15 Use Case Diagram: Copying a LOB Locator for a Temporary LOB

Text description of the illustration adlob081.gif

Purpose

This procedure describes how to copy a LOB locator for a temporary LOB.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference "DBMS_LOB" --CREATETEMPORARY, LOADFROMFILE, FREETEMPORARY
PL/SQL (DBMS_LOB):There is no applicable syntax reference for this use case.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobLocatorIsInit
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB ASSIGN.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB ASSIGN
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Copying a LOB Locator".

Scenario

This generic operation copies one temporary LOB locator to another.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Copying a LOB Locator for a Temporary LOB
C (OCI): Copying a LOB Locator for a Temporary LOB
COBOL (Pro*COBOL): Copying a LOB Locator for a Temporary LOB
C/C++ (Pro*C/C++): Copying a LOB Locator for a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Copying a LOB Locator for a Temporary LOB

Note:

Assigning one LOB to another using PL/SQL entails using the "=" sign. This is discussed in more detail with regard to "Read Consistent Locators" in Chapter 5, "Large Objects: Advanced Topics".

/* Copying a LOB locator for a temporary LOB.  
   Procedure copyTempLOBLocator_proc is not part of 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('ADPHOTO_DIR', 'monitor_photo_3060_11001');
BEGIN
    DBMS_LOB.CREATETEMPORARY(Lob_loc1,TRUE);
    DBMS_LOB.CREATETEMPORARY(Lob_loc2,TRUE);
    /* 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;

C (OCI): Copying a LOB Locator for a Temporary LOB

* Copying a LOB locator for a temporary LOB.  [Example script: 3886.c]
  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 *)"PHOTO_DIR",
                       (ub2)strlen("ADPHOTO_DIR"),
                       (text *)"monitor_photo_3060_11001",
                       (ub2)strlen("monitor_photo_3060_11001")))
  {
    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;
  }
}

COBOL (Pro*COBOL): Copying a LOB Locator for a Temporary LOB

     * Copying a LOB locator for a temporary LOB
     * [Example script: 3887.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-BLOB-COPY-LOCATOR.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
        
       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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" 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.

C/C++ (Pro*C/C++): Copying a LOB Locator for a Temporary LOB

/* copying a lOB locator for a temporary LOB.  [Example script: 3888.pc]
#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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
}

Is One Temporary LOB Locator Equal to Another

Figure 11-16 Use Case Diagram: Is One Temporary LOB Locator Equal to Another

Text description of adlob097.gif follows

Text description of the illustration adlob097.gif

Purpose

This procedure describes how to see if one LOB locator for a temporary LOB is equal to another.

Usage Notes

If two locators are equal they refer to the same version of the LOB data (see "Read Consistent Locators" in Chapter 5, "Large Objects: Advanced Topics").

Syntax

PL/SQL (DBMS_LOB): There is no applicable syntax reference for this use case.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobIsEqual
COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB ASSIGN. See also C(OCI) function, OCILobIsEqual
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Checking If One LOB Locator Is Equal to Another" .

Scenario

Not applicable.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package). No example is provided with this release.
C (OCI): Is One LOB Locator for a Temporary LOB Equal to Another
COBOL (Pro*Cobol): No example is provided with this release.
C/C++ (Pro*C/C++): Is One LOB Locator for a Temporary LOB Equal to Another
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

C (OCI): Is One LOB Locator for a Temporary LOB Equal to Another

/* Equality - Is one temporary LOB locator equal to another?  [Example script: 
3889.c] */

sb4 ck_isequal (OCIError      *errhp,
                OCISvcCtx     *svchp,
                OCIStmt       *stmthp,
                OCIEnv        *envhp)
{
  OCILobLocator *loc1;
  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;
   }
OCILobDescriptor free????
   return 0;
  }

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

/* Equality - Is one LOB locator for a temporary LOB equal to another? */
/* [Example script: 3890.pc] */

#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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001"; 
  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;
}

Determining if a LOB Locator for a Temporary LOB Is Initialized

Figure 11-17 Use Case Diagram: Determining If a LOB Locator for a Temporary LOB Is Initialized

Text description of adlob091.gif follows

Text description of the illustration adlob091.gif

Purpose

This procedure describes how to see if a LOB locator for a temporary LOB is initialized.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): There is no applicable syntax reference for this use case.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobLocatorIsInit
COBOL: A syntax reference is not applicable in this release.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB CREATE TEMPORARY. See also C(OCI) function, OCILobLocatorIsInit
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "C (OCI): Checking If a LOB Locator Is Initialized"

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".

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package). No example is provided with this release.
C (OCI): Determining If a LOB Locator for a Temporary LOB Is Initialized
COBOL (Pro*Cobol): No example is provided with this release.
C/C++ (Pro*C/C++): Determining If a LOB Locator Is Initialized
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

C (OCI): Determining If a LOB Locator for a Temporary LOB Is Initialized

/* Is a LOB locator for a temporary LOB is initialized? [Example script:3892.c]
   This function takes a LOB locator and checks if it is initialized. If it is
   initialized, it prints out a message, "LOB is initialized". 
   Otherwise, it says "LOB is not initialized". This function returns 
   0 if it completes successfully, -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;
   }

C/C++ (Pro*C/C++): Determining If a LOB Locator Is Initialized

/* Is a LOB locator for a temporary LOB initialized? [Example script: 3893.pc] 
*/

#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;
}

Finding Character Set ID of a Temporary LOB

Figure 11-18 Use Case Diagram: Finding Character Set ID for a Temporary LOB

Text description of adlob093.gif follows

Text description of the illustration adlob093.gif

Purpose

This procedure describes how to get the character set ID of a temporary LOB.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): A syntax reference is not applicable with this release.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobCharSetId
COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.
C/C++ (Pro*C/C++): There is no applicable syntax reference for this use case.
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "C (OCI): Determining Character Set ID".

Scenario

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

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package). No example is provided with this release.
C (OCI): Finding Character Set ID of a Temporary LOB
COBOL (Pro*Cobol): No example is provided with this release.
C/C++ (Pro*C/C++): No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

C (OCI): Finding Character Set ID of a Temporary LOB

/* Finding the character set id of a temporary LOB. [Example script: 3894.c]
   This function takes a LOB locator and prints the character set id of the LOB. 
   The function returns 0 if it completes successfully, -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;
 
}

Finding Character Set Form of a Temporary LOB

Figure 11-19 Use Case Diagram: Finding Character Set Form of a Temporary LOB

Text description of adlob092.gif follows

Text description of the illustration adlob092.gif

Purpose

This procedure describes how to get the character set form of a temporary LOB.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): A syntax reference is not applicable in this release.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobCharSetForm
COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.
C/C++ (Pro*C/C++): There is no applicable syntax reference for this use case.
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "C (OCI): Determining Character Set Form".

Scenario

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

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package). No example is provided with this release.
C (OCI): Finding Character Set Form of a Temporary LOB
COBOL (Pro*Cobol): No example is provided with this release.
C/C++ (Pro*C/C++): No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

C (OCI): Finding Character Set Form of a Temporary LOB

/* Finding the character set form of a temporary LOB [Example script: 3895.c]
   This function takes a LOB locator and prints out the character set form for 
   the LOB. It returns 0 if it completes successfully, -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;
  }

Appending One Temporary LOB to Another

Figure 11-20 Use Case Diagram: Appending One Temporary LOB to Another

Text description of adlob082.gif follows

Text description of the illustration adlob082.gif

Purpose

This procedure describes how to append one temporary LOB to another.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- APPEND
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobAppend.
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives -- LOB APPEND.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB APPEND
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Appending One LOB to Another" .

Scenario

These examples deal with the task of appending one segment of sound to another.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Appending One Temporary LOB to Another
C (OCI): Appending One Temporary LOB to Another
COBOL (Pro*COBOL): Appending One Temporary LOB to Another
C/C++ (Pro*C/C++): Appending One Temporary LOB to Another
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Appending One Temporary LOB to Another

/* Appending one temporary LOB to another [Example script: 3896.sql]
   Procedure appendTempLOB_proc is not part of DBMS_LOB package. */
   
CREATE OR REPLACE PROCEDURE appendTempLOB_proc IS
   Dest_loc2 CLOB;
   Dest_loc  CLOB;
   Amount    NUMBER;
   Src_loc   BFILE := BFILENAME('ADPHOTO_DIR', 'monitor_photo_3060_11001');
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE);
   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;

C (OCI): Appending One Temporary LOB to Another

/* Appending one temporary LOB to another  [Example script: 3897.c]
   This function takes two temporary LOB locators and appends the second 
   LOB to the first one. It returns 0 if it completes successfully, -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 monitor_photo file */ 
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"PHOTO_DIR",
                       (ub2)strlen("ADPHOTO_DIR"),
                       (text *)"monitor_photo_3060_11001",
                       (ub2)strlen("monitor_photo_3060_11001")))
  {
    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;
 }

COBOL (Pro*COBOL): Appending One Temporary LOB to Another

     * Appending one temporary LOB to another  [Example script: 3898.pco]
       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 "SAMP/SAMP".

      * 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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_11001" 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.

C/C++ (Pro*C/C++): Appending One Temporary LOB to Another

/* Appending one temporary LOB to another.  [Example script: 3899.pc] */

#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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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-Appending to a Temporary LOB

Figure 11-21 Use Case Diagram: Write-Appending to a Temporary LOB

Text description of adlob098.gif follows

Text description of the illustration adlob098.gif

Purpose

This procedure describes how to write append to a temporary LOB.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- WRITEAPPEND
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobWriteAppend
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB WRITE.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB WRITE
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Writing to the End of (Append-Write to) a LOB" .

Scenario

These examples read in 32767 bytes of data from an image file and append it to a temporary LOB.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Writing-Appending to a Temporary LOB
C (OCI): Writing-Appending to a Temporary LOB
COBOL (Pro*COBOL): Write-Appending to a Temporary LOB
C/C++ (Pro*C/C++): Write-Appending to a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Writing-Appending to a Temporary LOB

/* Write-appending to a temporary LOB.  [Example script: 3900.sql]
   Procedure writeAppendTempLOB_proc is not part of DBMS_LOB package. 
   This procedure reads in 32767 bytes of data from the monitor_photo_3060_11001
   file and appends it to a temporary LOB. */

CREATE OR REPLACE PROCEDURE writeAppendTempLOB_proc IS
   Lob_loc    BLOB;
   Buffer     RAW(32767);
   Src_loc    BFILE := BFILENAME('ADPHOTO_DIR', 'monitor_photo_3060_11001');
   Amount     Binary_integer := 32767;
   Position   Binary_integer := 128;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE);
   /* 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;

C (OCI): Writing-Appending to a Temporary LOB

/* Write-appending to a temporary LOB  [Example script: 3901.c] */

#define MAXBUFLEN 32767 
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);
 }

COBOL (Pro*COBOL): Write-Appending to a Temporary LOB

     * Write-appending to a temporary LOB. [Example script: 3902.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. WRITE-APPEND-TEMP.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_11001" 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.

C/C++ (Pro*C/C++): Write-Appending to a Temporary LOB

/* Write-appending to a temporary LOB.  [Example script: 3903.pc]  */

#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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
}

Writing Data to a Temporary LOB

Figure 11-22 Use Case Diagram: Writing Data to a Temporary LOB

Text description of adlob084.gif follows

Text description of the illustration adlob084.gif

Purpose

This procedure describes how to write data to a temporary LOB.

Usage Notes

Stream Writing

The most efficient way to write large amounts of LOB data is to use OCILobWrite() with the streaming mechanism enabled using 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.

Using DBMS_LOB.WRITE() to Write Data to a Temporary BLOB

When you are passing a hexadecimal string to DBMS_LOB.WRITE() to write data to a BLOB, use the following guidelines:

The amount parameter should be <= the buffer length parameter
The length of the buffer should be ((amount*2) - 1). This guideline exists because the two characters of the string are seen as one hexadecimal character (and an implicit hexadecimal-to-raw conversion takes place), that is, every two bytes of the string are converted to one raw byte.

The following example is correct:

/* Writing data to a temporary LOB.  [Example script: 3905.sql]

declare
   blob_loc  BLOB;
   rawbuf RAW(10);
   an_offset INTEGER := 1;
   an_amount BINARY_INTEGER := 10;
begin
   select blob_col into blob_loc from a_table
where id = 1;
   rawbuf := '1234567890123456789';
   dbms_lob.write(blob_loc, an_amount, an_offset,
rawbuf);
   commit;
end;

Replacing the value for 'an_amount' in the previous example with the following values, yields error message, ora_21560:

    an_amount BINARY_INTEGER := 11;

    an_amount BINARY_INTEGER := 19;

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- WRITE
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobWrite
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB WRITE.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB WRITE
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Writing Data to a LOB" .

Scenario

The example procedures allow the ad_sourcetext data (the text for the advertisement) to be updated by writing data to the LOB.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Writing Data to a Temporary LOB
C (OCI): Writing Data to a Temporary LOB
COBOL (Pro*COBOL): Writing Data to a Temporary LOB
C/C++ (Pro*C/C++): Writing Data to a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Writing Data to a Temporary LOB

/* Writing data to a temporary LOB  [Example script: 3909.sql]
   Procedure writeDataToTempLOB_proc is not part of 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);
   /* 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;

C (OCI): Writing Data to a Temporary LOB

/* Writing data to a temporary LOB.  [Example script: 3910] */
/* This example illustrates streaming writes with polling */

#define MAXBUFLEN 32767
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 descriptors: */
  (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);
}

COBOL (Pro*COBOL): Writing Data to a Temporary LOB

     * Writing data to a temporary LOB   [Example script: 3911.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. WRITE-TEMP.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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.

C/C++ (Pro*C/C++): Writing Data to a Temporary LOB

/* Writing data to a temporary LOB. [Example script: 3912] */

#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;
}

Trimming Temporary LOB Data

Figure 11-23 Use Case Diagram: Trimming Temporary LOB Data

Text description of adlob088.gif follows

Text description of the illustration adlob088.gif

Purpose

This procedure describes how to trim temporary LOB data.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- TRIM
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobTrim
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB TRIM.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB TRIM
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Trimming LOB Data" .

Scenario

The following examples access text (CLOB data) referenced in the ad_finaltext column of table adheader_tab, and trim it.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Trimming Temporary LOB Data
C (OCI): Trimming Temporary LOB Data
COBOL (Pro*COBOL): Trimming Temporary LOB Data
C/C++ (Pro*C/C++): Trimming Temporary LOB Data
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Trimming Temporary LOB Data

/* Trimming temporary LOB data.  [Example script: 3914.sql]
   Procedure trimTempLOB_proc is not part of DBMS_LOB package. */
   
CREATE OR REPLACE PROCEDURE trimTempLOB_proc IS
   Lob_loc        CLOB;
   Amount         number;
   Src_loc        BFILE := BFILENAME('ADPHOTO_DIR', 'monitor_photo_3060_11001');
   TrimAmount     number := 100;
BEGIN
   /* Create a temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE);
   /* 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;

C (OCI): Trimming Temporary LOB Data

/* Trimming temporary LOB data.  [Example script: 3915.c]

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 monitor_photo file: */ 
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"ADPHOTO_DIR",
                       (ub2)strlen("ADPHOTO_DIR"),
                       (text *)"monitor_photo_3060_11001",
                       (ub2)strlen("monitor_photo_3060_11001")))
  {
    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;
  }

COBOL (Pro*COBOL): Trimming Temporary LOB Data

     * Trimming temporary LOB data.  [Example script: 3916.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-TRIM.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) 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  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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" 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.

C/C++ (Pro*C/C++): Trimming Temporary LOB Data

/* Trimming temporary LOB data.  [Example script:  3917.pc]  */

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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
}

Erasing Part of a Temporary LOB

Figure 11-24 Use Case Diagram: Erasing Part of a Temporary LOB

Text description of adlob087.gif follows

Text description of the illustration adlob087.gif

Purpose

This procedure describes how to erase part of a temporary LOB.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages and Types Reference Chapter 18, "DBMS_LOB" -- ERASE
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobErase
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB ERASE.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB ERASE
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See "Java (JDBC): Erasing Part of a LOB" in Chapter 10, "Internal Persistent LOBs".

Scenario

Not applicable.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Erasing Part of a Temporary LOB
(OCI): Erasing Part of a Temporary LOB
COBOL (Pro*COBOL): Erasing Part of a Temporary LOB
C/C++ (Pro*C/C++): Erasing Part of a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Erasing Part of a Temporary LOB

/* Erasing part of a temporary LOB.  [Example script: 3918.sql]
   Procedure eraseTempLOB_proc is not part of DBMS_LOB package: */
   
CREATE OR REPLACE PROCEDURE trimTempLOB_proc IS
   Lob_loc        CLOB;
   amt            number;
   Src_loc        BFILE := BFILENAME('ADPHOTO_DIR', 'monitor_photo_3060_11001');
   Amount         INTEGER := 32767;
BEGIN

   /* Create a temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE);

   /* 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;

(OCI): Erasing Part of a Temporary LOB

/* Erasing part of a temporary LOB.  [Example script: 3919.c]
   This example erases 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 monitor_photo_3060_11001 file: */ 
  if(OCILobFileSetName(envhp, errhp, &bfile,
                       (text *)"ADPHOTO_DIR",
                       (ub2)strlen("ADPHOTO_DIR"),
                       (text *)"monitor_photo_3060_11001",
                       (ub2)strlen("monitor_photo_3060_11001")))
  {
    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;
  }

COBOL (Pro*COBOL): Erasing Part of a Temporary LOB

     * Erasing part of a temporary LOB.  [Example script: 3920.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-BLOB-ERASE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) 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  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 "ADPHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "keyboard_photo_3106_13001" 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.

C/C++ (Pro*C/C++): Erasing Part of a Temporary LOB

/* Erasing part of a temporary LOB.  [Example script: 3921.pc] */

#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 = "ADPHOTO_DIR", *Name = "monitor_photo_3060_11001";
  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;
}

Enabling LOB Buffering for a Temporary LOB

Figure 11-25 Use Case Diagram: Enabling LOB Buffering for a Temporary LOB

Text description of adlob096.gif follows

Text description of the illustration adlob096.gif

Purpose

This procedure describes how to enable LOB buffering for a temporary LOB.

Usage Notes

Enable buffering when performing 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.

Note:

Do not enable buffering to perform the stream read and write involved in checkin and checkout.

Syntax

PL/SQL (DBMS_LOB): There is no applicable syntax reference for this use case.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCIEnableLobBuffering, OCIDisbleLobBuffering, OCILobFlushBuffer
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB ENABLE BUFFERING.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB ENABLE BUFFERING
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Enabling LOB Buffering" .

Scenario

Not applicable.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): No example is provided with this release.
C (OCI): Enabling LOB Buffering for a Temporary LOB
COBOL (Pro*COBOL): Enabling LOB Buffering for a Temporary LOB
C/C++ (Pro*C/C++): Enabling LOB Buffering for a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

C (OCI): Enabling LOB Buffering for a Temporary LOB

/* Enabling LOB buffering for a temporary LOB.  [Example script: 3922.c] */

#define MAXBUFLEN 32767
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;

}

COBOL (Pro*COBOL): Enabling LOB Buffering for a Temporary LOB

     * Enabling LOB buffering for a temporary LOB.  [Example script: 3923.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-BUFFERING.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       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.

C/C++ (Pro*C/C++): Enabling LOB Buffering for a Temporary LOB

/* Enabling LOB buffering for a temporary LOB.  [Example script: 3924.pc] */

#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;
}

Flushing Buffer for a Temporary LOB

Figure 11-26 Use Case Diagram: Flushing Buffer for a Temporary LOB

Text description of adlob095.gif follows

Text description of the illustration adlob095.gif

Purpose

This procedure describes how to flush the buffer for a temporary LOB.

Usage Notes

Not applicable.

Syntax

PL/SQL (DBMS_LOB): There is no applicable syntax reference for this use case.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobFlushBuffer
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB FLUSH BUFFER.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB FLUSH BUFFER
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Flushing the Buffer" .

Scenario

Not applicable.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): No example is provided with this release.
C (OCI): Flushing Buffer for a Temporary LOB
COBOL (Pro*COBOL): Determining If a Pattern Exists in a Temporary LOB (instr)
C/C++ (Pro*C/C++): Determining If a Pattern Exists in a Temporary LOB (instr)
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

C (OCI): Flushing Buffer for a Temporary LOB

/* Flushing the temporary LOB buffer. [Exmaple script: 3926]  */

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;

}

COBOL (Pro*COBOL): Flushing Buffer for a Temporary LOB

     * Flushing a temporary LOB buffer.  [Example script: 3927.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. FREE-TEMPORARY.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
        
       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.

C/C++ (Pro*C/C++): Flushing Buffer for a Temporary LOB

/* Flushing a temporary LOB buffer.  [Example script: 3928.pc]  */

#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;
}

Disabling LOB Buffering for a Temporary LOB

Figure 11-27 Use Case Diagram: Disabling LOB Buffering

Text description of adlob094.gif follows

Text description of the illustration adlob094.gif

Purpose

This procedure describes how to disable temporary LOB buffering.

Usage Notes

You enable buffering when performing 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.

Note:

Do not enable buffering to perform the stream read and write involved in checkin and checkout.

Syntax

PL/SQL (DBMS_LOB): A syntax reference is not applicable in this release.
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobDisableBuffering
COBOL (Pro*COBOL) Pro*COBOL Precompiler Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives -- LOB DISABLE BUFFERING.
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DISABLE BUFFERING
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See the section, Chapter 10, "Internal Persistent LOBs", "C (OCI): Disabling LOB Buffering".

Scenario

Not applicable.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): No example provided with this release.
C (OCI): Disabling LOB Buffering
COBOL (Pro*COBOL): Disabling LOB Buffering for a Temporary LOB
C/C++ (Pro*C/C++): Disabling LOB Buffering for a Temporary LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.

C (OCI): Disabling LOB Buffering

/* Disabling LOB buffering for temporary LOBs. [Example script: 3929.c] */

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;

}

COBOL (Pro*COBOL): Disabling LOB Buffering for a Temporary LOB

     * Disabling LOB buffering for a temporary LOB.  [Example script: 3930.pco]
       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-LOB-BUFFERING.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".        
       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.

C/C++ (Pro*C/C++): Disabling LOB Buffering for a Temporary LOB

/* Disabling LOB buffering for a temporary LOB.  [Example script: 3931.pc] */

#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;
}

11 Temporary LOBs

Use Case Model

Individual Use Cases

Use Case Model: Internal Temporary LOBs

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

Programmatic Environments

Locators

Temporary LOB Locators Can be IN Values

Can You Use the Same Functions for Temporary and Internal Persistent LOBs?

DBMS_LOB.createtemporary() Parameter is a Hint

Temporary LOB Data is Stored in Temporary Tablespace

Lifetime and Duration of Temporary LOBs

OCI Can Group Temporary LOBs into Logical Buckets

Memory Handling

LOB Buffering and CACHE, NOCACHE, CACHE READS

Temporary Tablespace

Explicitly Free Temporary LOB Space to Reuse It

Selecting a Permanent LOB INTO a Temporary LOB Locator

Locators and Semantics

Features Specific to Temporary LOBs

Security Issues with Temporary LOBs

NOCOPY Restrictions

Managing Temporary LOBs

Using JDBC and Temporary LOBs

Using JDBC and Temporary BLOBs

Table 11-2 JDBC: Temporary BLOB APIs

Using JDBC and Temporary CLOBs

Table 11-3 JDBC: Temporary CLOB APIs

Creating a Temporary LOB

Figure 11-1 Use Case Diagram: Creating a Temporary LOB

Purpose

Usage Notes

Syntax

Scenario

Examples

PL/SQL (DBMS_LOB Package): Creating a Temporary LOB

C (OCI): Creating a Temporary LOB

COBOL (Pro*COBOL): Creating a Temporary LOB

C/C++ (Pro*C/C++): Creating a Temporary LOB

Java (JDBC): Creating a Temporary BLOB

Java (JDBC): Creating a Temporary CLOB

Checking If a LOB is Temporary

Figure 11-2 Use Case Diagram: Checking If a LOB is Temporary

Purpose

Usage Notes

Syntax

Scenario

Examples

PL/SQL (DBMS_LOB Package): Checking If a LOB is Temporary

C (OCI): Checking If a LOB is Temporary

COBOL (Pro*COBOL): Checking If a LOB is Temporary

C/C++ (Pro*C/C++): Checking If a LOB is Temporary

Java (JDBC): Checking if a BLOB is Temporary

Java (JDBC): Checking if a CLOB is Temporary

Freeing a Temporary LOB

Figure 11-3 Use Case Diagram: Freeing a Temporary LOB

Purpose

Usage Notes

Syntax

Scenario

Examples

PL/SQL (DBMS_LOB Package): Freeing a Temporary LOB

C (OCI): Freeing a Temporary LOB

COBOL (Pro*COBOL): Freeing a Temporary LOB

C/C++ (Pro*C/C++): Freeing a Temporary LOB

Java (JDBC): Freeing a Temporary BLOB

Java (JDBC): Freeing a Temporary CLOB

Java (JDBC): Creating and Freeing a Temporary CLOB Using TemporaryClob.java

Loading a Temporary LOB with Data from a BFILE

Figure 11-4 Use Case Diagram: Loading a LOB with Data from a BFILE

Purpose

Usage Notes

Binary Data to Character Set Conversion is Needed on BFILE Data

Syntax

Scenario

Examples

PL/SQL (DBMS_LOB Package): Loading a Temporary LOB with Data from a BFILE

C (OCI): Loading a Temporary LOB with Data from a BFILE

COBOL (Pro*COBOL): Loading a Temporary LOB with Data from a BFILE

C/C++ (Pro*C/C++): Loading a Temporary LOB with Data from a BFILE

11
Temporary LOBs