Release 2 (9.2)
Part Number A96591-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 2 (9.2) Part Number A96591-01 |
|
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.
Each Internal Persistent LOB use case is described as follows:
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:
| Use Case and Page | P | O | B | C | V* | J |
|---|---|---|---|---|---|---|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
+ |
|
|
+ |
- |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
+ |
|
|
Determining if a LOB Locator for a Temporary LOB Is Initialized |
- |
+ |
- |
+ |
- |
- |
|
+ |
- |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
+ |
- |
- |
- |
- |
- |
|
|
Loading a Temporary CLOB/NCLOB with Character Data from a File |
+ |
- |
- |
- |
- |
- |
|
- |
+ |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
- |
+ |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
- |
+ |
- |
- |
- |
- |
|
|
- |
+ |
- |
- |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
- |
+ |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
+ |
|
|
- |
+ |
- |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
+ |
- |
- |
- |
- |
- |
|
|
Loading a Temporary CLOB/NCLOB with Character Data from a File |
+ |
- |
- |
- |
- |
- |
|
+ |
+ |
+ |
+ |
- |
- |
|
|
+ |
- |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
|
|
+ |
+ |
+ |
+ |
- |
- |
Oracle9i supports the definition, creation, deletion, access, and update of temporary LOBs in the following programmatic environments or "interfaces":
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.
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:
INSERT, UPDATE, DELETE, or SELECT. For example:
SELECTpatternFROMcomposite_image WHERE temp_lob_pattern_id =somepattern_match_function(lobvalue);
SELECTPermanentLobINTOTemporaryLob_loc FROM Demo_tab WHERE Column1 := 1;
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. |
The DBMS_LOB.createtemporary() call takes a duration parameter. This parameter is merely a hint and is not strictly enforced.
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.
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 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.
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.
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.
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.
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.LOBs is freed along with the deletion of the temporary LOBs. In all cases, when a user's session ends, space is returned to the temporary tablespace for general reuse.We previously noted that if you perform the following:
SELECTpermanent_lobINTOtemporary_lob_locatorFROM y_blahWHERE 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.
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.
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.
The following features are specific to temporary LOBs:
For example, when you perform the following query:
SELECTpermanent_lobINTOtemporary_lob_locatorFROM y_blahWHERE 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.
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.
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:
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.
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.
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.
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.
DBMS_LOB.SESSION or DBMS_LOB.CALLSecurity is provided through the LOB locator.
LOB can access it.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.See PL/SQL User's Guide and Reference, Chapter 7: "SUBPROGRAMS" -- NOCOPY COMPILER HINT, for guidelines, restrictions, and tips on using NOCOPY.
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.
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.
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.
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.
This procedure describes how to create a temporary LOB.
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.
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:
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 are provided in the following programmatic environments:
|
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;
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;
/* 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; }
* 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.
/* 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; }
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.
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.
This procedure describes how to see if a LOB is temporary.
Not applicable.
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:
These are generic examples that query whether the locator is associated with a temporary LOB or not.
Examples are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
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().
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().
This procedure describes how to free a temporary LOB.
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.
See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment.
Not applicable.
Examples are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
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.
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().
|
Note: temporaryClob.java and temporaryBlob.java classes have been deprecated. These classes are no longer needed or used in the JDBC standard. |
This procedure describes how to load a temporary LOB with data from a BFILE.
|
Note: The |
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).
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: