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:
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 are provided in the following programmatic environments:
/* 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;
/* 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; } }
* 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.
/* 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; )
This procedure describes how to load a temporary BLOB with binary data from a BFILE.
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.
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:
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.
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.)
The following example illustrates:
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 ;
This procedure describes how to load a temporary CLOB or NCLOB with character data from a BFILE.
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.
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:
LOADCLOBFROMFILEThe 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.
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.)
The following example illustrates:
getlength on the BFILE.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 ;
The following example illustrates:
NLS_CHARSET_ID function.lang_ctx.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 ;
This procedure describes how to see if a temporary LOB is open.
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 generic examples takes a locator as input, create a temporary LOB, open it and test if the LOB is open.
Examples are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
This procedure describes how to display temporary LOB data.
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:
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 are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
This procedure describes how to read data from a temporary LOB.
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.).
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 read LOB data from the AD_PHOTO LOB column.
Examples are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
This procedure describes how to read portion of a temporary LOB (substr).
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 examples show the operation in terms of reading a portion of the image in the AD_PHOTO column.
Examples are provided in the following programmatic environments:
/* 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;
* 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.
/* 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; }
This procedure describes how to compare all or part of two temporary LOBs.
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:
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 are provided in the following programmatic environments:
/* 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;
* 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.
/* 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; }
This procedure describes how to see if a pattern exists in a temporary LOB (instr).
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:
The following examples examine the advertisement text to see if the string "children" is present.
Examples are provided in the following programmatic environments:
/* 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;
* 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.
/* 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; }
This procedure describes how to get the length of a temporary LOB.
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:
The following examples get the length of the photo LOB to determine whether it will run over the 4 gigabyte limit.
Examples are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
This procedure describes how to copy all or part of one temporary LOB to another.
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:
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 are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
This procedure describes how to copy a LOB locator for a temporary LOB.
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:
This generic operation copies one temporary LOB locator to another.
Examples are provided in the following programmatic environments:
|
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;
* 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; } }
* 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.
/* 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; }
This procedure describes how to see if one LOB locator for a temporary LOB is equal to another.
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").
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:
Not applicable.
Examples are provided in the following programmatic environments:
/* 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; }
/* 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; }
This procedure describes how to see if a LOB locator for a temporary LOB is initialized.
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:
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 are provided in the following programmatic environments:
/* 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; }
/* 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; }
This procedure describes how to get the character set ID of a temporary LOB.
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:
This function takes a LOB locator and prints the character set id of the LOB.
Examples are provided in the following programmatic environments:
/* 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; }
This procedure describes how to get the character set form of a temporary LOB.
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:
This function takes a LOB locator and prints the character set form for the LOB.
Examples are provided in the following programmatic environments:
/* 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; }
This procedure describes how to append one temporary LOB to another.
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 examples deal with the task of appending one segment of sound to another.
Examples are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
This procedure describes how to write append to a temporary LOB.
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 examples read in 32767 bytes of data from an image file and append it to a temporary LOB.
Examples are provided in the following programmatic environments:
/* 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;
/* 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); }
* 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.
/* 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; }
This procedure describes how to write data to a temporary LOB.
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.
When you are passing a hexadecimal string to DBMS_LOB.WRITE() to write data to a BLOB, use the following guidelines:
amount parameter should be <= the buffer length parameterlength 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;
or
an_amount BINARY_INTEGER := 19;
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:
The example procedures allow the ad_sourcetext data (the text for the advertisement) to be updated by writing data to the LOB.
Examples are provided in the following programmatic environments:
/* 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;
/* 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); }
* 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.
/* 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; }
This procedure describes how to trim temporary LOB data.
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:
The following examples access text (CLOB data) referenced in the ad_finaltext column of table adheader_tab, and trim it.
Examples are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
This procedure describes how to erase part of a temporary LOB.
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:
Not applicable.
Examples are provided in the following programmatic environments:
/* 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;
/* 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; }
* 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.
/* 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; }
This procedure describes how to enable LOB buffering for a temporary LOB.
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. |
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:
Not applicable.
Examples are provided in the following programmatic environments:
/* 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; }
* 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.
/* 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; }
This procedure describes how to flush the buffer for a temporary LOB.
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:
Not applicable.
Examples are provided in the following programmatic environments:
/* 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; }
* 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.
/* 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; }
This procedure describes how to disable temporary LOB buffering.
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. |
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:
Not applicable.
Examples are provided in the following programmatic environments:
/* 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; }
* 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.
/* 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; }
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|