Features Specific to Temporary LOBs

The following features are specific to temporary LOBs:

• Temporary LOB Locator is Overwritten by Permanent LOB Locator

  For instance, when you perform the following query:

  SELECT permanent_lob INTO temporary_lob_locator FROM y_blah 
     WHERE x_blah = a_number;
  

  temporary_lob_locator is overwritten 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 Per Temporary LOB

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

    Here are two examples of situations where a user will incur a copy, or at least an extra 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 for more information about collections.

      If your application involves several such assignments and copy operations of collections or complex objects, and you seek to avoid the above overheads, then persistent internal LOBs may be more suitable for such applications. More precisely:

      * 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 per 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

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

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