Oracle® Database Application Developer's Guide - Large Objects 10g Release 1 (10.1) Part Number B10796-01 |
|
|
View PDF |
This chapter describes APIs that perform basic operations on BLOB, CLOB, and NCLOB datatypes. The operations given in this chapter can be used with either persistent or temporary LOB instances. Note that operations in this chapter do not apply to BFILEs. APIs covered in this chapter are listed in Table 14-1.
See Also:
|
The following information is given for each operation described in this chapter:
Table 14-1, "Environments Supported for Basic LOB APIs" indicates which programmatic environments are supported for the APIs discussed in this chapter. The first column describes the operation that the API performs. The remaining columns indicate with "Yes" or "No" whether the API is supported in PL/SQL, OCI, OCCI, COBOL, Pro*C, Visual Basic (VB), and JDBC.
Operation | PL/SQL | OCI | OCCI | COBOL | Pro*C | VB | JDBC |
---|---|---|---|---|---|---|---|
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
No |
Yes |
No |
No |
No |
No |
No | |
No |
Yes |
No |
No |
No |
No |
No | |
Determining Chunk Size, See: Writing Data to a LOB |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
No |
No |
Yes |
Yes |
Yes |
Yes | |
Yes |
No |
No |
No |
No |
No |
No | |
Yes |
No |
No |
No |
No |
No |
No | |
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
No |
Yes |
No |
Yes |
Yes |
Yes |
No | |
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
No |
No |
No |
Yes |
Yes |
Yes |
No | |
No |
Yes |
No |
No |
Yes |
No |
Yes | |
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
No |
Yes |
No |
Yes |
Yes |
No |
No | |
No |
Yes |
No |
No |
Yes |
No |
No | |
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
Yes |
No |
No |
No |
No |
No |
No | |
Yes |
No |
No |
No |
No |
No |
No | |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes | |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes | |
Yes |
No |
No |
Yes |
Yes |
No |
Yes | |
Yes |
No |
No |
Yes |
Yes |
Yes |
Yes | |
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
Storage Limit, Determining: Maximum Storage Limit for Terabyte-Size LOBs |
Yes |
No |
No |
No |
No |
No |
No |
Yes |
Yes |
No |
Yes |
Yes |
Yes |
Yes | |
WriteNoAppend, see Appending to a LOB . |
No |
No |
No |
No |
No |
No |
No |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
This operation appends one LOB instance to another.
Before you can append one LOB to another, the following conditions must be met:
BLOB
or both CLOB
types.Persistent LOBs: You must lock the row you are selecting the LOB from prior to updating a LOB value if you are using the PL/SQL DBMS_LOB package or OCI. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking the row can be done explicitly using the SQL SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using an OCI
pin
or lock
function in OCI programs. For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".
See the following syntax references for each programmatic environment:
To run the following examples, you must create two LOB instances and pass them when you call the given append operation. Creating a LOB instance is described in Chapter 12, "Operations Specific to Persistent and Temporary LOBs".
Examples for this use case are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lappend.sql */ /* Procedure appendLOB_proc is not part of the DBMS_LOB package: */ /* appending one lob to another */ CREATE OR REPLACE PROCEDURE appendLOB_proc (Dest_loc IN OUT BLOB, Src_loc IN OUT BLOB) IS /* Note: Dest_loc and Src_loc can be persistent or temporary LOBs */ BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB APPEND EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Dest_loc, DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN (Src_loc, DBMS_LOB.LOB_READONLY); DBMS_LOB.APPEND(Dest_loc, Src_loc); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Dest_loc); DBMS_LOB.CLOSE (Src_loc); DBMS_OUTPUT.PUT_LINE('Append succeeded'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Append failed'); DBMS_LOB.CLOSE (Dest_loc); DBMS_LOB.CLOSE (Src_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lappend.c */ /* Appending one LOB to another. */ /* This function appends the Source LOB to the end of the Destination LOB */ #include <oratypes.h> #include <lobdemo.h> void appendLOB_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { printf ("----------- OCILobAppend Demo --------------\n"); /* Opening the LOBs is Optional */ checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc2, OCI_LOB_READWRITE)); checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc1, OCI_LOB_READONLY)); /* Append Source LOB to the end of the Destination LOB. */ printf(" append the source Lob to the destination Lob\n"); checkerr(errhp, OCILobAppend(svchp, errhp, Lob_loc2, Lob_loc1)); /* Closing the LOBs is Mandatory if they have been Opened */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc2)); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc1)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lappend.pco * APPENDING ONE LOB TO ANOTHER IDENTIFICATION DIVISION. PROGRAM-ID. LOB-APPEND. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 DEST SQL-BLOB. 01 SRC SQL-BLOB. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. APPEND-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 :DEST END-EXEC. EXEC SQL ALLOCATE :SRC END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_PHOTO INTO :DEST FROM PRINT_MEDIA WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 FOR UPDATE END-EXEC. EXEC SQL SELECT AD_PHOTO INTO :SRC FROM PRINT_MEDIA WHERE PRODUCT_ID = 3060 AND AD_ID = 11001 END-EXEC. * Open the DESTination LOB read/write and SRC LOB read only: EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC. EXEC SQL LOB OPEN :SRC READ ONLY END-EXEC. * Append the source LOB to the destination LOB: EXEC SQL LOB APPEND :SRC TO :DEST END-EXEC. EXEC SQL LOB CLOSE :DEST END-EXEC. EXEC SQL LOB CLOSE :SRC END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :DEST END-EXEC. EXEC SQL FREE :SRC END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lappend.pc */ /* Appending one LOB to another */ #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 appendLOB_proc() { OCIBlobLocator *Dest_loc, *Src_loc; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate the locators: */ EXEC SQL ALLOCATE :Dest_loc; EXEC SQL ALLOCATE :Src_loc; /* Select the destination locator: */ EXEC SQL SELECT Sound INTO :Dest_loc FROM Print_media WHERE product_id = 2268 AND ad_id = 21001 FOR UPDATE; /* Select the source locator: */ EXEC SQL SELECT Sound INTO :Src_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001; /* Opening the LOBs is Optional: */ EXEC SQL LOB OPEN :Dest_loc READ WRITE; EXEC SQL LOB OPEN :Src_loc READ ONLY; /* Append the source LOB to the end of the destination LOB: */ EXEC SQL LOB APPEND :Src_loc TO :Dest_loc; /* Closing the LOBs is mandatory if they have been opened: */ EXEC SQL LOB CLOSE :Dest_loc; EXEC SQL LOB CLOSE :Src_loc; /* Release resources held by the locators: */ EXEC SQL FREE :Dest_loc; EXEC SQL FREE :Src_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; appendLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lappend.bas 'Appending one LOB to another Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value Set OraAdPhotoClone = OraAdPhoto1 OraDyn.MoveNext OraDyn.Edit OraAdPhoto1.Append OraAdPhotoClone OraDyn.Update
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lappend.java */ // Appending one LOB to another import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_121 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { ResultSet rset = null; BLOB dest_loc = null; BLOB src_loc = null; InputStream in = null; byte[] buf = new byte[MAXBUFSIZE]; int length = 0; long pos = 0; rset = stmt.executeQuery ( "SELECT ad_photo FROM Print_media WHERE product_id = 2268 AND ad_id = 21001"); if (rset.next()) { src_loc = ((OracleResultSet)rset).getBLOB (1); } in = src_loc.getBinaryStream(); rset = stmt.executeQuery ( "SELECT ad_photo FROM Print_media WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE"); if (rset.next()) { dest_loc = ((OracleResultSet)rset).getBLOB (1); } // Start writing at the end of the LOB. ie. append: pos = dest_loc.length(); while ((length = in.read(buf)) != -1) { // Write the contents of the buffer into position pos of the output LOB: dest_loc.putBytes(pos, buf); pos += length; } // Close all streams and handles: in.close(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to get the character set form of a LOB instance.
Use the following syntax references for each programmatic environment:
The example demonstrates how to determine the character set form of the foreign language text (ad_fltextn
).
This functionality is currently available only in OCI:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lgetchfm.c */ /* Getting character set form of the foreign language ad text, ad_fltextn */ #include <oratypes.h> #include <lobdemo.h> /* This function takes a valid LOB locator and prints the character set form of the LOB. */ void getCsformLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { ub1 charset_form = 0 ; printf ("----------- OCILobCharSetForm Demo --------------\n"); printf (" get the character set form of ad_fltextn\n"); /* Get the charactersid form of the LOB*/ checkerr (errhp, OCILobCharSetForm(envhp, errhp, Lob_loc, &charset_form)); printf(" character Set Form of ad_fltextn is : %d\n", charset_form); return; }
This section describes how to determine the character set ID.
Use the following syntax references for each programmatic environment:
This functionality is currently available only in OCI:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lgetchar.c */ /* Getting character set id */ /* This function takes a valid LOB locator and prints the character set id of the LOB. */ #include <oratypes.h> #include <lobdemo.h> void getCsidLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { ub2 charsetid =0 ; printf ("----------- OCILobCharSetID Demo --------------\n"); printf (" get the character set id of adfltextn_locator\n"); /* Get the charactersid ID of the LOB*/ checkerr (errhp, OCILobCharSetId(envhp, errhp, Lob_loc, &charsetid)); printf(" character Set ID of ad_fltextn is : %d\n", charsetid); return; }
This operation loads a LOB with data from a BFILE. This procedure can be used to load data into any persistent or temporary LOB instance of any LOB datatype.
See Also:
|
Before you can load a LOB with data from a BFILE, the following conditions must be met:
Note the following issues regarding this operation.
When you use the DBMS_LOB.LOADFROMFILE
procedure to load a CLOB or NCLOB instance, you are loading the LOB with binary data from the BFILE and no implicit character set conversion is performed. For this reason, using the DBMS_LOB.LOADCLOBFROMFILE
procedure is recommended when loading character data, see Loading a CLOB or NCLOB with Data from a BFILE on for more information.
The value you pass for the amount parameter to functions listed in Table 14-2 must be one of the following:
See the following syntax references for details on using this operation in each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lloaddat.sql */ /* Procedure loadLOBFromBFILE_proc is not part of the DBMS_LOB package: */ /* loading a lob with bfile data */ CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc (Dest_loc IN OUT BLOB) IS /* Note: Dest_loc can be a persistent or temporary LOB */ Src_loc BFILE := BFILENAME('MEDIA_DIR', 'keyboard_logo.jpg'); Amount INTEGER := 4000; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB LOADFORMFILE EXAMPLE ------------'); /* 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(Dest_loc); DBMS_LOB.CLOSE(Src_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lloaddat.c */ #include <oratypes.h> #include <lobdemo.h> void loadLOBDataFromBFile_proc(OCILobLocator *Lob_loc, OCILobLocator* BFile_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 amount= 2000; printf ("----------- OCILobLoadFromFile Demo --------------\n"); printf (" open the bfile\n"); /* Opening the BFILE locator is Mandatory */ checkerr (errhp, (OCILobOpen(svchp, errhp, BFile_loc, OCI_LOB_READONLY))); printf(" open the lob\n"); /* Opening the CLOB locator is optional */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Load the data from the graphic file (bfile) into the blob */ printf (" load the LOB from File\n"); checkerr (errhp, OCILobLoadFromFile2(svchp, errhp, Lob_loc, BFile_loc, amount, (oraub8)1, (oraub8)1)); /* Closing the LOBs is Mandatory if they have been Opened */ checkerr (errhp, OCILobClose(svchp, errhp, BFile_loc)); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lloaddat.pco IDENTIFICATION DIVISION. PROGRAM-ID. LOB-LOAD. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 DEST SQL-BLOB. 01 BFILE1 SQL-BFILE. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(20) VARYING. * Declare the amount to load. The value here * was chosen arbitrarily 01 LOB-AMT PIC S9(9) COMP VALUE 10. 01 USERID PIC X(11) VALUES "PM/PM". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. LOB-LOAD. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BFILE locator EXEC SQL ALLOCATE :BFILE1 END-EXEC. * Set up the directory and file information MOVE "ADGRAPHIC_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "keyboard_3106_13001" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. EXEC SQL LOB FILE SET :BFILE1 DIRECTORY = :DIR-ALIAS,FILENAME = :FNAME END-EXEC. * Allocate and initialize the destination BLOB EXEC SQL ALLOCATE :DEST END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_GRAPHIC INTO :DEST FROM PRINT_MEDIA WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 FOR UPDATE END-EXEC. * Open the source BFILE for READ EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. * Open the destination BLOB for READ/WRITE EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC. * Load the destination BLOB from the source BFILE EXEC SQL LOB LOAD :LOB-AMT FROM FILE :BFILE1 INTO :DEST END-EXEC. * Close the source and destination LOBs EXEC SQL LOB CLOSE :BFILE1 END-EXEC. EXEC SQL LOB CLOSE :DEST END-EXEC. END-OF-BLOB. EXEC SQL FREE :DEST END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lloaddat.bas Dim OraDyn as OraDynaset, OraPhoto1 as OraBLOB, OraMyBfile as OraBFile OraConnection.BeginTrans Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT) Set OraPhoto1 = OraDyn.Fields("ad_photo").Value OraDb.Parameters.Add "id", 3060,ORAPARAM_INPUT OraDb.Parameters.Add "mybfile", Null,ORAPARAM_OUTPUT OraDb.Parameters("mybfile").serverType = ORATYPE_BFILE OraDb.ExecuteSQL ("begin GetBFile(:id, :mybfile); end;") Set OraMyBFile = OraDb.Parameters("mybfile").Value 'Go to Next row OraDyn.MoveNext OraDyn.Edit 'Lets update OraPhoto1 data with that from the BFILE OraPhoto1.CopyFromBFile OraMyBFile OraDyn.Update OraConnection.CommitTrans
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lloaddat.java */ // Java IO classes: import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_45 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm"); conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BFILE src_lob = null; BLOB dest_lob = null; InputStream in = null; OutputStream out = null; byte buf[] = new byte[1000]; ResultSet rset = null; rset = stmt.executeQuery ( "SELECT BFILENAME('ADPHOTO_DIR', 'keyboard_3106_13001') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); src_lob.openFile(); in = src_lob.getBinaryStream(); } rset = stmt.executeQuery ( "SELECT ad_photo FROM Print_media WHERE product_id = 3106 AND AD_ID = 13001 FOR UPDATE"); if (rset.next()) { dest_lob = ((OracleResultSet)rset).getBLOB (1); // Fetch the output stream for dest_lob: out = dest_lob.getBinaryOutputStream(); } int length = 0; int pos = 0; while ((in != null) && (out != null) && ((length = in.read(buf)) != -1)) { System.out.println( "Pos = " + Integer.toString(pos) + ". Length = " + Integer.toString(length)); pos += length; out.write(buf, pos, length); } // Close all streams and file handles: in.close(); out.flush(); out.close(); src_lob.closeFile(); // Commit the transaction: conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This procedure loads a BLOB with data from a BFILE. This procedure can be used to load data into any persistent or temporary BLOB instance.
See Also:
|
The following conditions must be met before calling this procedure:
Note the following with respect to this operation:
Using DBMS_LOB.LOADBLOBFROMFILE
to load binary data into a BLOB achieves the same result as using DBMS_LOB.LOADFROMFILE
, but also returns the new offsets of BLOB.
The value you pass for the amount parameter to the
DBMS_LOB.LOADBLOBFROMFILE
function must be one of the following:
DBMS_LOB.LOBMAXSIZE
Passing this value causes the function to load the entire BFILE. This is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.
See PL/SQL Packages and Types Reference, "DBMS_LOB" -- LOADBLOBFROMFILE procedure for syntax details on this procedure.
This example is available in PL/SQL only. This API is not provided in other programmatic environments. This example illustrates:
LOADBLOBFROMFILE
to load the entire BFILE without getting its length first./* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lldblobf.sql */ CREATE OR REPLACE PROCEDURE loadBLOB_proc (dst_loc IN OUT BLOB) IS src_loc BFILE := bfilename('MEDIA_DIR','keyboard_logo.jpg') ; src_offset NUMBER := 1; dst_offset NUMBER := 1; src_osin NUMBER; dst_osin NUMBER; bytes_rd NUMBER; bytes_wt NUMBER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB LOADBLOBFORMFILE EXAMPLE ------------'); /* 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 */ END; /
This procedure loads a CLOB or NCLOB with character data from a BFILE. This procedure can be used to load data into a persistent or temporary CLOB or NCLOB instance.
See Also:
|
The following conditions must be met before calling this procedure:
You can specify the character set id of the BFILE when calling this procedure. Doing so, ensures that the character set is properly converted from the BFILE data character set to the destination CLOB or NCLOB character set.
The value you pass for the amount parameter to the
DBMS_LOB.LOADCLOBFROMFILE
function must be one of the following:
DBMS_LOB.LOBMAXSIZE
Passing this value causes the function to load the entire BFILE. This is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.See PL/SQL Packages and Types Reference, "DBMS_LOB" -- LOADCLOBFROMFILE procedure for syntax details on this procedure.
The following examples illustrate different techniques for using this API:
The following example illustrates:
0
).getlength
for the BFILE.This example assumes that ad_source
is a BFILE
in UTF8 character set format and the database character set is UTF8.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lldclobf.sql */ CREATE OR REPLACE PROCEDURE loadCLOB1_proc (dst_loc IN OUT CLOB) IS src_loc bfile := bfilename('MEDIA_DIR','monitor_3060.txt') ; 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_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE ------------'); 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) ; 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() ; 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lldclobs.sql */ CREATE OR REPLACE PROCEDURE loadCLOB2_proc (dst_loc1 IN OUT NCLOB, dst_loc2 IN OUT NCLOB) IS src_loc bfile := bfilename('MEDIA_DIR','monitor_3060.txt'); amt number := 100; 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_OUTPUT.PUT_LINE('------------ LOB LOADCLOBFORMFILE EXAMPLE ------------'); 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.LOADCLOBFROMFILE(dst_loc1, src_loc, amt, dst_offset, src_offset, cs_id, lang_ctx, warning); /* 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(' ----------------------------' ) ; /* 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); 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() ; END; /
This operation determines whether a LOB is open.
The LOB instance must exist before executing this procedure.
When a LOB is open, it must be closed at some point later in the session.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lisopen.sql */ /* Procedure lobIsOpen_proc is not part of the DBMS_LOB package: */ /* seeing if lob is open */ CREATE OR REPLACE PROCEDURE lobIsOpen_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be a persistent or a temporary LOB */ Retval INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB ISOPEN EXAMPLE ------------'); /* See if the LOB is open: */ Retval := DBMS_LOB.ISOPEN(Lob_loc); /* The value of Retval will be 1 meaning that the LOB is open. */ if Retval = 1 THEN DBMS_OUTPUT.PUT_LINE('Input locator is open'); else DBMS_OUTPUT.PUT_LINE('Input locator is not open'); end if; END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lisopen.c */ /* Checking if LOB is Open. */ #include <oratypes.h> #include <lobdemo.h> void seeIfLOBIsOpen_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { boolean isOpen; printf ("----------- OCILobIsOpen Demo --------------\n"); /* See if the LOB is Open */ checkerr (errhp, OCILobIsOpen(svchp, errhp, Lob_loc, &isOpen)); if (isOpen) { printf(" Lob is Open\n"); /* ... Processing given that the LOB has already been Opened */ } else { printf(" Lob is not Open\n"); /* ... Processing given that the LOB has not been Opened */ } return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lisopen.pco * Checking if LOB is Open IDENTIFICATION DIVISION. PROGRAM-ID. LOB-OPEN. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BLOB1 SQL-BLOB. 01 LOB-ATTR-GRP. 05 ISOPN PIC S9(9) COMP. 01 SRC 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 USERID PIC X(11) VALUES "PM/PM". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. LOB-OPEN. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the target BLOB EXEC SQL ALLOCATE :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_COMPOSITE INTO :BLOB1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3060 AND AD_ID = 11001 END-EXEC. * See if the LOB is OPEN EXEC SQL LOB DESCRIBE :BLOB1 GET ISOPEN INTO :ISOPN END-EXEC. IF ISOPN = 1 * <Processing for the LOB OPEN case> DISPLAY "The LOB is open" ELSE * <Processing for the LOB NOT OPEN case> DISPLAY "The LOB is not open" END-IF. * Free the resources used by the BLOB END-OF-BLOB. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lisopen.pc */ /* Checking if LOB is open */ #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 seeIfLOBIsOpen() { OCIBlobLocator *Lob_loc; int isOpen = 1; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_composite INTO :Lob_loc FROM Print_media WHERE product_id = 3106 and ad_id = 13001; /* See if the LOB is Open: */ EXEC SQL LOB DESCRIBE :Lob_loc GET ISOPEN INTO :isOpen; if (isOpen) printf("LOB is open\n"); else printf("LOB is not open\n"); /* Note that in this example, the LOB is not open */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "pm/pm"; EXEC SQL CONNECT :pm; seeIfLOBIsOpen(); EXEC SQL ROLLBACK WORK RELEASE; }
To see if a CLOB is open, your JDBC application can use the isOpen
method defined in oracle.sql.CLOB
. The return Boolean value indicates whether the CLOB has been previously opened or not. The isOpen
method is defined as follows:
/** * Check whether the CLOB is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
CLOB clob = ... // See if the CLOB is opened boolean isOpen = clob.isOpen ();
To see if a BLOB is open, your JDBC application can use the isOpen
method defined in oracle.sql.BLOB
. The return Boolean value indicates whether the BLOB has been previously opened or not. The isOpen
method is defined as follows:
/** * Check whether the BLOB is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
BLOB blob = ... // See if the BLOB is opened boolean isOpen = blob.isOpen ();
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lisopen.java */ // Checking if LOB is open // Core JDBC classes: import java.io.OutputStream; import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.Types; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_48 { public Ex2_48 () { } public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB blob = null; ResultSet rset = stmt.executeQuery ( "SELECT ad_composite FROM Print_media product_id = 3060 AND ad_id = 11001"); if (rset.next()) { blob = ((OracleResultSet)rset).getBLOB (1); } OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall ( "BEGIN ? := DBMS_LOB.ISOPEN(?); END;"); cstmt.registerOutParameter (1, Types.NUMERIC); cstmt.setBLOB(2, blob); cstmt.execute(); int result = cstmt.getInt(1); System.out.println("The result is: " + Integer.toString(result)); OracleCallableStatement cstmt2 = (OracleCallableStatement) conn.prepareCall ( "BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READONLY); END;"); cstmt2.setBLOB(1, blob); cstmt2.execute(); System.out.println("The LOB has been opened with a call to DBMS_LOB.OPEN()"); // Use the existing cstmt handle to re-query the status of the locator: cstmt.setBLOB(2, blob); cstmt.execute(); result = cstmt.getInt(1); System.out.println("This result is: " + Integer.toString(result)); stmt.close(); cstmt.close(); cstmt2.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes APIs that allow you to read LOB data. You can use this operation to read LOB data into a buffer. This is useful if your application requires displaying large amounts of LOB data or streaming data operations.
Note the following when using these APIs.
The most efficient way to read large amounts of LOB data is to use OCILobRead2
() with the streaming mechanism enabled.
The value you pass for the amount parameter is restricted for the APIs described in Table 14-3.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/ldisplay.sql */ /* Procedure displayLOB_proc is not part of the DBMS_LOB package: */ /* displaying lob data */ CREATE OR REPLACE PROCEDURE displayLOB_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be a persistent or temporary LOB */ Buffer RAW(1024); Amount BINARY_INTEGER := 1024; Position INTEGER := 1; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB DATA DISPLAY EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); LOOP DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); /* Display the buffer contents: */ DBMS_OUTPUT.PUT_LINE(rawtohex(Buffer)); Position := Position + Amount; END LOOP; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); EXCEPTION WHEN OTHERS THEN DBMS_LOB.CLOSE (Lob_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/ldisplay.c */ /* Displaying LOB data. This example reads the entire contents of a CLOB piecewise into a buffer using the standard polling method, processing each buffer piece after every READ operation until the entire CLOB has been read. */ #include <oratypes.h> #include <lobdemo.h> void displayLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 amt; ub4 offset; sword retval; boolean done; ub1 bufp[MAXBUFLEN]; ub4 buflen; ub1 piece; printf ("----------- LOB Data Display Demo --------------\n"); /* Open the CLOB */ printf(" open the lob\n"); checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY))); /* Setting amt = 0 will read till the end of LOB*/ amt = 0; buflen = sizeof(bufp); /* Process the data in pieces */ printf(" Process the data in pieces\n"); offset = 1; memset((void *)bufp, '\0', MAXBUFLEN); done = FALSE; piece = OCI_FIRST_PIECE; while (!done) { retval = OCILobRead2(svchp, errhp, Lob_loc, &amt, NULL, offset, (void *) bufp, buflen, piece, (void *)0, (OCICallbackLobRead2) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); switch (retval) { case OCI_SUCCESS: /* Only one piece or last piece*/ /* Process the data in bufp. amt will give the amount of data just read in bufp in bytes. */ done = TRUE; break; case OCI_ERROR: checkerr (errhp, retval); done = TRUE; break; case OCI_NEED_DATA: /* There are 2 or more pieces */ /* Process the data in bufp. amt will give the amount of data just read in bufp in bytes. */ piece = OCI_NEXT_PIECE; break; default: checkerr (errhp, retval); done = TRUE; break; } } /* while */ /* Closing the CLOB is mandatory if you have opened it */ printf(" close the lob \n"); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/ldisplay.pco * DISPLAYING LOB DATA IDENTIFICATION DIVISION. PROGRAM-ID. DISPLAY-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BLOB1 SQL-BLOB. 01 BUFFER2 PIC X(5) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 D-AMT PIC 9. EXEC SQL VAR BUFFER2 IS RAW(5) END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. DISPLAY-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 :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT PM.AD_PHOTO INTO :BLOB1 FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 2268 AND AD_ID = 21001 END-EXEC. DISPLAY "Found column AD_PHOTO". * Initiate polling read: MOVE 0 TO AMT. EXEC SQL LOB READ :AMT FROM :BLOB1 AT :OFFSET INTO :BUFFER2 END-EXEC. DISPLAY " ". MOVE AMT TO D-AMT. DISPLAY "first read (", D-AMT, "): " BUFFER2. READ-BLOB-LOOP. MOVE " " TO BUFFER2. EXEC SQL LOB READ :AMT FROM :BLOB1 INTO :BUFFER2 END-EXEC. MOVE AMT TO D-AMT. DISPLAY "next read (", D-AMT, "): " BUFFER2. GO TO READ-BLOB-LOOP. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. MOVE AMT TO D-AMT. DISPLAY "last read (", D-AMT, "): " BUFFER2(1:AMT). EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/ldisplay.pc */ /* Displaying LOB data. This example reads the entire contents of a BLOB piecewise into a buffer using a standard polling method, processing each buffer piece after every READ operation until the entire BLOB has been read: */ #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 32767 void displayLOB_proc() { OCIBlobLocator *Lob_loc; int 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(); EXEC SQL ALLOCATE :Lob_loc; /* Select the BLOB: */ EXEC SQL SELECT m.ad_header.header_text INTO Lob_loc FROM Print_media m WHERE m.product_id = 3060 AND ad_id = 11001; /* Open the BLOB: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* 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 :Lob_loc INTO :Buffer; /* Process (Buffer.Length == BufferLength) amount of Buffer.Data */ } /* Process (Buffer.Length == Amount) amount of Buffer.Data */ /* Closing the BLOB is mandatory if you have opened it: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; displayLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/ldisplay.bas 'Displaying LOB data 'Using the OraClob.Read mechanism Dim MySession As OraSession Dim OraDb As OraDatabase Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Dim OraDyn as OraDynaset, OraAdSourceText as OraClob, amount_read%, chunksize%, chunk chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT) Set OraAdSourceText = OraDyn.Fields("ad_sourcetext").Value OraAdSourceText.PollingAmount = OraAdSourceText.Size 'Read entire CLOB contents Do 'chunk returned is a variant of type byte array: amount_read = OraAdSourceText.Read(chunk, chunksize) 'Msgbox chnunk Loop Until OraAdSourceText.Status <> ORALOB_NEED_DATA
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/ldisplay.java */ // Core JDBC classes: import java.io.OutputStream; import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_72 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB lob_loc = null; InputStream in = null; byte buf[] = new byte[MAXBUFSIZE]; int pos = 0; int length = 0; ResultSet rset = stmt.executeQuery ( "SELECT pm.ad_header.logo FROM Print_media pm WHERE pm.product_id = 2056 AND ad_id = 12001"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBLOB (1); } // read this LOB through an InputStream: in = lob_loc.getBinaryStream(); while ((length = in.read(buf)) != -1) { pos += length; System.out.println(Integer.toString(pos)); // Process the contents of the buffer here. } in.close(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to read data from LOBs.
Note the following when using this operation.
The most efficient way to read of large amounts of LOB data is to use OCILobRead2()
with the streaming mechanism enabled using polling or callback. To do so, specify the starting point of the read using the offset parameter and use the symbol OCI_LOBMAXSIZE
for the amount, as follows:
ub8 char_amt = 20; ub8 byte_amt = 20; ub4 offset = 1000; OCILobRead2(svchp, errhp, locp, &byte_amt, &char_amt, offset, bufp, bufl, OCI_ONE_PIECE, 0, 0, 0, 0)
When using polling mode, be sure to look at the value of the 'amount
' parameter after each OCILobRead2
() call to see how many bytes were read into the buffer because the buffer may not be entirely full.
When using callbacks, the 'len
' parameter, which is input to the callback, will indicate how many bytes are filled in the buffer. Be sure to check the 'len
' parameter during your callback processing because the entire buffer may not be filled with data (see the Oracle Call Interface Programmer's Guide.)
A chunk is one or more Oracle blocks. You can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the chunk size used by Oracle when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The getchunksize
function returns the amount of space used in the LOB chunk to store the LOB value.
You will improve performance if you run read
requests using a multiple of this chunk size. The reason for this is that you are using the same unit that the Oracle database uses when reading data from disk. If it is appropriate for your application, then you should batch reads until you have enough for an entire chunk instead of issuing several LOB read calls that operate on the same LOB chunk.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lread.sql */ /* Procedure readLOB_proc is not part of the DBMS_LOB package: */ /* reading data from lob */ CREATE OR REPLACE PROCEDURE readLOB_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be a persistent or a temporary LOB */ Buffer RAW(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 2; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB READ EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); /* Read data from the LOB: */ DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); DBMS_OUTPUT.PUT_LINE(RAWTOHEX(substr(Buffer, 1, 200))); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lread.c */ /* Reading LOB data. This example reads the entire contents of a CLOB piecewise into a buffer using a standard polling method, processing each buffer piece after every READ operation until the entire CLOB has been read. */ #include <oratypes.h> #include <lobdemo.h> static void pollingRead(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 amt; oraub8 offset; sword retval; ub1 bufp[MAXBUFLEN]; oraub8 buflen; boolean done; ub1 piece; buflen = (oraub8)MAXBUFLEN; /* Open the CLOB */ printf(" call OCILobOpen\n"); checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY)); printf (" ---------- OCILobRead one-piece mode -------------\n"); offset=1; amt = 5; checkerr(errhp, OCILobRead2(svchp, errhp, Lob_loc, NULL, &amt, offset, (void *)bufp, buflen, OCI_ONE_PIECE, (void *)0, (OCICallbackLobRead2) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT)); printf (" amt read= %d\n", (ub4)amt); printf (" ---------- OCILobRead polling mode -------------\n"); /* Setting the amt to the buffer length. Note here that amt is in chars since we are using a CLOB */ amt = 0; /* Process the data in pieces */ printf(" process the data in pieces\n"); offset = 1; memset((void *)bufp, '\0', MAXBUFLEN); done = FALSE; piece = OCI_FIRST_PIECE; while (!done) { retval = OCILobRead2(svchp, errhp, Lob_loc, NULL, &amt, offset, (void *) bufp, buflen, piece, (void *)0, (OCICallbackLobRead2) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); switch (retval) { case OCI_SUCCESS: /* Only one piece since amtp == bufp */ /* Process the data in bufp. amt will give the amount of data just read in bufp in bytes. */ printf(" amt read=%d in the last call\n", (ub4)amt); done = TRUE; break; case OCI_ERROR: /* report_error(); this function is not shown here */ done = TRUE; break; case OCI_NEED_DATA: printf(" amt read=%d\n", (ub4)amt); piece = OCI_NEXT_PIECE; break; default: (void) printf("Unexpected ERROR: OCILobRead2() LOB.\n"); done = TRUE; break; } } /* Closing the CLOB is mandatory if you have opened it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); } struct somestruct { ub4 count; }; sb4 lobCallback(dvoid *ctxp, CONST dvoid *bufp, oraub8 len, ub1 piece, dvoid **changed_bufpp, oraub8 *changed_lenp) { struct somestruct *ctx = (struct somestruct *)ctxp; printf(" In callback, count = %d, len passed in=%d, piece=%d\n", ctx->count++,(ub4)len, piece); return OCI_CONTINUE; } static void callbackRead(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 amt; oraub8 offset; sword retval; ub1 bufp[MAXBUFLEN]; oraub8 buflen; boolean done; struct somestruct ctx; printf (" ---------- OCILobRead Callback mode -------------\n"); /* Open the CLOB */ printf(" call OCILobOpen\n"); checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY)); /* Setting the amt to the buffer length. Note here that amt is in chars since we are using a CLOB */ amt = 0; buflen =(oraub8) sizeof(bufp); /* Process the data in pieces */ printf(" process the data in pieces\n"); offset = 1; memset((void *)bufp, '\0', MAXBUFLEN); done = FALSE; ctx.count = 0; checkerr (errhp, OCILobRead2(svchp, errhp, Lob_loc, NULL, &amt, offset, (void *) bufp, buflen, OCI_FIRST_PIECE, (void *)&ctx, lobCallback, (ub2) 0, (ub1) SQLCS_IMPLICIT)); printf(" total amt read=%d in OCILobRead2 callback mode\n", (ub4)amt); /* Closing the CLOB is mandatory if you have opened it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); } void readLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { printf ("----------- OCILobRead Demo --------------\n"); /* One-piece and Polling mode */ pollingRead(Lob_loc, envhp, errhp, svchp, stmthp); /* Callback mode */ callbackRead(Lob_loc, envhp, errhp, svchp, stmthp); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lread.pco * READING LOB DATA IDENTIFICATION DIVISION. PROGRAM-ID. ONE-READ-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BLOB1 SQL-BLOB. 01 BUFFER2 PIC X(32767) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 USERID PIC X(11) VALUES "PM/PM". EXEC SQL INCLUDE SQLCA 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 CLOB locator: EXEC SQL ALLOCATE :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_COMPOSITE INTO :BLOB1 FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 2268 AND AD_ID = 21001 END-EXEC. EXEC SQL LOB OPEN :BLOB1 END-EXEC. * Perform a single read: MOVE 32767 TO AMT. EXEC SQL LOB READ :AMT FROM :BLOB1 INTO :BUFFER2 END-EXEC. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. END-OF-BLOB. DISPLAY "BUFFER2: ", BUFFER2(1:AMT). EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lread.pc */ /* Reading 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 32767 void readLOB_proc() { OCIBlobLocator *Lob_loc; int Amount = BufferLength; /* Here (Amount == BufferLength) so only one READ is needed: */ char Buffer[BufferLength]; /* Datatype equivalencing is mandatory for this datatype: */ EXEC SQL VAR Buffer IS RAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_composite INTO :Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001; /* Open the BLOB: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; EXEC SQL WHENEVER NOT FOUND CONTINUE; /* Read the BLOB data into the Buffer: */ EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer; printf("Read %d bytes\n", Amount); /* Close the BLOB: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "pm/pm"; EXEC SQL CONNECT :pm; readLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lread.bas 'Reading LOB data using the OraClob.Read mechanism Dim MySession As OraSession Dim OraDb As OraDatabase Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Dim OraDyn as OraDynaset, OraAdSourceText as OraClob, amount_read%, chunksize%, chunk chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT) Set OraAdSourceText = OraDyn.Fields("ad_sourcetext").Value OraAdSourceText.pollingAmount = OraAdSourceText.Size 'Read entire CLOB contents Do amount_read = OraAdSourceText.Read(chunk,chunksize) 'chunk returned is a variant of type byte array Loop Until OraAdSourceText.Status <> ORALOB_NEED_DATA
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lread.java */ // Reading LOB data // Java IO classes: import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_79 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB lob_loc = null; byte buf[] = new byte[MAXBUFSIZE]; ResultSet rset = stmt.executeQuery ( "SELECT ad_composite FROM Print_media WHERE product_id = 2056 AND ad_ id = 12001"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBLOB (1); } // MAXBUFSIZE is the number of bytes to read and 1000 is the offset from // which to start reading buf = lob_loc.getBytes(1000, MAXBUFSIZE); // Display the contents of the buffer here: System.out.println(new String(buf)); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to read a portion of a LOB using SUBSTR.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lsubstr.sql */ /* Procedure substringLOB_proc is not part of the DBMS_LOB package: */ /* reading portion of lob (substr) */ CREATE OR REPLACE PROCEDURE substringLOB_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loca can be a persistent or a temporary LOB */ Amount BINARY_INTEGER := 32767; Position INTEGER := 3; Buffer RAW(32767); BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB SUBSTR EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); Buffer := DBMS_LOB.SUBSTR(Lob_loc, Amount, Position); /* Process the data */ /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); DBMS_OUTPUT.PUT_LINE(rawtohex(substr(buffer,1,200))); END; / SHOW ERRORS; /* For persistent LOBs, DBMS_LOB.SUBSTR can be used in a SQL statement too. In the following SQL statement, 200 is the amount to read and 1 is the starting offset from which to read: */ SELECT DBMS_LOB.SUBSTR(ad_sourcetext, 200, 1) FROM print_media WHERE product_id = 2268;
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lsubstr.pco * READING PORTION OF THE LOB DATA USING SUBSTR IDENTIFICATION DIVISION. PROGRAM-ID. BLOB-SUBSTR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BLOB1 SQL-BLOB. 01 BUFFER2 PIC X(32767) VARYING. 01 AMT PIC S9(9) COMP. 01 POS PIC S9(9) COMP VALUE 1. 01 USERID PIC X(11) VALUES "SAMP/SAMP". EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL VAR BUFFER2 IS VARRAW(32767) END-EXEC. PROCEDURE DIVISION. BLOB-SUBSTR. 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 :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_COMPOSITE INTO :BLOB1 FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 2268 AND AD_ID = 21001 END-EXEC. DISPLAY "Selected the BLOB". * Open the BLOB for READ ONLY: EXEC SQL LOB OPEN :BLOB1 READ ONLY END-EXEC. * Execute PL/SQL to get SUBSTR functionality: MOVE 5 TO AMT. EXEC SQL EXECUTE BEGIN :BUFFER2 := DBMS_LOB.SUBSTR(:BLOB1,:AMT,:POS); END; END-EXEC. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. DISPLAY "Substr: ", BUFFER2-ARR(POS:AMT). END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lsubstr.pc */ /* Reading portion of the LOB using (substr). 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 32767 void substringLOB_proc() { OCIBlobLocator *Lob_loc; int Position = 1; 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(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_photo INTO Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001; /* Open the BLOB: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Invoke SUBSTR() from within an anonymous PL/SQL block: */ EXEC SQL EXECUTE BEGIN :Buffer := DBMS_LOB.SUBSTR(:Lob_loc, :Amount, :Position); END; END-EXEC; /* Close the BLOB: */ EXEC SQL LOB CLOSE :Lob_loc; /* Process the Data */ /* Release resources used by the locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; substringLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; exit(0); }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lsubstr.bas 'Reading portion of a LOB (or BFILE). In OO4O this is accomplished by 'setting the OraBlob.Offset and OraBlob.chunksize properties. 'Using the OraClob.Read mechanism Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn as OraDynaset, OraAdSourceText as OraClob, amount_read%, chunksize%, chunk Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT) Set OraAdSourceText = OraDyn.Fields("ad_sourcetext").Value 'Let's read 100 bytes from the 500th byte onwards: OraAdSourceText.Offset = 500 OraAdSourceText.PollingAmount = OraAdSourceText.Size 'Read entire CLOB contents amount_read = OraAdSourceText.Read(chunk, 100) 'chunk returned is a variant of type byte array
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lsubstr.java */ // Reading portion of a LOB using substr import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_79 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB lob_loc = null; byte buf[] = new byte[MAXBUFSIZE]; ResultSet rset = stmt.executeQuery ( "SELECT ad_composite FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBLOB (1); } OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall ("BEGIN DBMS_LOB.OPEN(?, " +"DBMS_LOB.LOB_READONLY); END;"); cstmt.setBLOB(1, lob_loc); cstmt.execute(); // MAXBUFSIZE is the number of bytes to read and 1000 is the offset from // which to start reading: buf = lob_loc.getBytes(1000, MAXBUFSIZE); // Display the contents of the buffer here. cstmt = (OracleCallableStatement) conn.prepareCall ("BEGIN DBMS_LOB.CLOSE(?); END;"); cstmt.setBLOB(1, lob_loc); cstmt.execute(); stmt.close(); cstmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to compare all or part of two LOBs.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lcompare.sql */ /* Procedure compareTwoLOBs_proc is not part of the DBMS_LOB package: */ /* comparing all or part of lob */ CREATE OR REPLACE PROCEDURE compareTwoLOBs_proc (Lob_loc1 IN OUT BLOB, Lob_loc2 IN OUT BLOB) IS /* Note: Lob_loc1 and Lob_loc2 can be persistent or temporary LOBs */ Amount INTEGER := 32767; Retval INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB COMPARE EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc1, DBMS_LOB.LOB_READONLY); DBMS_LOB.OPEN (Lob_loc2, DBMS_LOB.LOB_READONLY); /* Compare the two LOBs: */ retval := DBMS_LOB.COMPARE(Lob_loc1, Lob_loc2, Amount, 1, 1); IF retval = 0 THEN DBMS_OUTPUT.PUT_LINE('LOBs are equal'); ELSE DBMS_OUTPUT.PUT_LINE('LOBs are not equal'); END IF; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc1); DBMS_LOB.CLOSE (Lob_loc2); END; / SHOW ERRORS;
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lcompare.pco * COMPARING ALL OR PART OF TWO LOBS IDENTIFICATION DIVISION. PROGRAM-ID. 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 BUFFER2 PIC X(32767) VARYING. 01 RET PIC S9(9) COMP. 01 AMT PIC S9(9) COMP. 01 POS PIC S9(9) COMP VALUE 1024. 01 OFFSET PIC S9(9) COMP VALUE 1. EXEC SQL VAR BUFFER2 IS VARRAW(32767) END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. COMPARE-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 :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 PM WHERE PM.PRODUCT_ID = 2268 AND AD_ID = 21001 END-EXEC. EXEC SQL SELECT AD_COMPOSITE INTO :BLOB2 FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 3060 AND AD_ID = 11001 END-EXEC. * Open the BLOBs for READ ONLY: EXEC SQL LOB OPEN :BLOB1 READ ONLY END-EXEC. EXEC SQL LOB OPEN :BLOB2 READ ONLY END-EXEC. * Execute PL/SQL to get COMPARE functionality: MOVE 4 TO AMT. EXEC SQL EXECUTE BEGIN :RET := DBMS_LOB.COMPARE(:BLOB1,:BLOB2,: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. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL FREE :BLOB2 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lcompare.pc */ /* Comparing all or part of two LOBs #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 compareTwoLobs_proc() { OCIBlobLocator *Lob_loc1, *Lob_loc2; int Amount = 32767; 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 = 2056 AND ad_id = 12001; /* Opening the LOBs is Optional: */ EXEC SQL LOB OPEN :Lob_loc1 READ ONLY; EXEC SQL LOB OPEN :Lob_loc2 READ ONLY; /* Compare the two Frames using DBMS_LOB.COMPARE() from within PL/SQL: */ EXEC SQL EXECUTE BEGIN :Retval := DBMS_LOB.COMPARE(:Lob_loc1, :Lob_loc2, :Amount, 1, 1); END; END-EXEC; if (0 == Retval) printf("The frames are equal\n"); else printf("The 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; /* Release resources held by the locators: */ EXEC SQL FREE :Lob_loc1; EXEC SQL FREE :Lob_loc2; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; compareTwoLobs_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lcompare.bas 'Comparing all or part of two LOBs Dim MySession As OraSession Dim OraDb As OraDatabase Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Dim OraDyn as OraDynaset, OraAdPhoto1 as OraBLOB, OraAdPhotoClone as OraBLOB Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value 'Clone it for future reference Set OraAdPhotoClone = OraAdPhoto1.Clone 'Lets go to the next row and compare LOBs OraDyn.MoveNext MsgBox CBool(OraAdPhotot1.Compare(OraAdPhototClone, OraAdPhotoClone.size, 1, 1))
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lcompare.java */ // Comparing all or part of two LOBs import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_87 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB lob_loc1 = null; BLOB lob_loc2 = null; ResultSet rset = stmt.executeQuery ( "SELECT ad_composite FROM Print_media WHERE product_id = 2056 AND ad_id = 12001"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBLOB (1); } rset = stmt.executeQuery ( "SELECT ad_composite FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { lob_loc2 = ((OracleResultSet)rset).getBLOB (1); } if (lob_loc1.length() > lob_loc2.length()) System.out.println ("Looking for LOB2 inside LOB1. result = " + Long.toString(lob_loc1.position(lob_loc2, 1))); else System.out.println("Looking for LOB1 inside LOB2. result = " + Long.toString(lob_loc2.position(lob_loc1, 1))); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to see if a pattern exists in a LOB using INSTR.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/linstr.sql */ /* Procedure instringLOB_proc is not part of the DBMS_LOB package: */ /* seeing if pattern exists in lob (instr) */ CREATE OR REPLACE PROCEDURE instringLOB_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loccan be a persistent or temporary LOB */ Pattern RAW(30) := hextoraw('aabb'); Position INTEGER := 0; Offset INTEGER := 1; Occurrence INTEGER := 1; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB INSTR EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); /* Seek for the pattern: */ Position := DBMS_LOB.INSTR(Lob_loc, 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; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); END; / SHOW ERRORS;
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/linstr.pco * SEEING IF A PATTERN EXISTS IN THE LOB USING INSTR IDENTIFICATION DIVISION. PROGRAM-ID. CLOB-INSTR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 CLOB1 SQL-CLOB. 01 PATTERN PIC X(8) VALUE "children". 01 POS PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 OCCURRENCE PIC S9(9) COMP VALUE 1. 01 USERID PIC X(11) VALUES "SAMP/SAMP". EXEC SQL INCLUDE SQLCA 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 SELECT AD_SOURCETEXT INTO :CLOB1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 END-EXEC. * Open the CLOB for READ ONLY: EXEC SQL LOB OPEN :CLOB1 READ ONLY END-EXEC. * Execute PL/SQL to get INSTR functionality: EXEC SQL EXECUTE BEGIN :POS := DBMS_LOB.INSTR(:CLOB1, :PATTERN,:OFFSET,:OCCURRENCE); END; END-EXEC. IF POS = 0 * Logic for pattern not found here DISPLAY "Pattern not found." ELSE * Pos contains position where pattern is found DISPLAY "Pattern found." END-IF. EXEC SQL LOB CLOSE :CLOB1 END-EXEC. END-OF-CLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :CLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/linstr.pc */ /* Seeing if a pattern exists in the LOB using instr #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 instringLOB_proc() { OCIClobLocator *Lob_loc; char *Pattern = "The End"; int Position = 0; int Offset = 1; int Occurrence = 1; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_sourcetext INTO :Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001; /* Opening the LOB is Optional: */ EXEC SQL LOB OPEN :Lob_loc; /* Seek the Pattern using DBMS_LOB.INSTR() in a PL/SQL block: */ EXEC SQL EXECUTE BEGIN :Position := DBMS_LOB.INSTR(:Lob_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 LOB is mandatory if you have opened it: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "pm/pm"; EXEC SQL CONNECT :pm; instringLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/linstr.java */ // Seeing if a pattern exists in the LMOB using instr import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_91 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "pm", "pm"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { final int offset = 1; // Start looking at the first byte final int occurrence = 1; // Start at the 1st occurrence of the pattern within the CLOB CLOB lob_loc = null; String pattern = new String("Junk"); // Pattern to look for within the CLOB. ResultSet rset = stmt.executeQuery ( "SELECT ad_sourcetext FROM Print_media WHERE product_id = 2268 AND ad_id = 21001"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getCLOB (1); } // Search for location of pattern string in the CLOB, starting at offset 1: long result = lob_loc.position(pattern, offset); System.out.println("Results of Pattern Comparison : " + Long.toString(result)); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to determine the length of a LOB.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/llength.sql */ /* Procedure getLengthLOB_proc is not part of the DBMS_LOB package: */ /* getting the length of a lob */ CREATE OR REPLACE PROCEDURE getLengthLOB_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be a persistent or temporary LOB */ Length INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB GETLENGTH EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); /* Get the length of the LOB: */ length := DBMS_LOB.GETLENGTH(Lob_loc); IF length IS NULL THEN DBMS_OUTPUT.PUT_LINE('LOB is null.'); ELSE DBMS_OUTPUT.PUT_LINE('The length is '|| length); END IF; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/llength.c */ /* Getting the length of a LOB */ #include <oratypes.h> #include <lobdemo.h> /* This function gets the length of the LOB */ void getLengthLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 length; printf("----------- OCILobGetLength Demo --------------\n"); /* Opening the LOB is Optional */ printf(" Open the locator (optional)\n"); checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY))); printf(" get the length of ad_fltextn.\n"); checkerr (errhp, OCILobGetLength2(svchp, errhp, Lob_loc, &length)); /* Length is undefined if the LOB is NULL or undefined */ printf(" Length of LOB is %d\n",(ub4)length); /* Closing the LOBs is Mandatory if they have been Opened */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/llength.pco * GETTING THE LENGTH OF A LOB IDENTIFICATION DIVISION. PROGRAM-ID. LOB-LENGTH. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 CLOB1 SQL-CLOB. 01 LOB-ATTR-GRP. 05 LEN PIC S9(9) COMP. 01 D-LEN PIC 9(4). 01 USERID PIC X(11) VALUES "SAMP/SAMP". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. LOB-LENGTH. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the target CLOB: EXEC SQL ALLOCATE :CLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC. EXEC SQL SELECT AD_SOURCETEXT INTO :CLOB1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3060 AND AD_ID = 11001 END-EXEC. * Obtain the length of the CLOB: EXEC SQL LOB DESCRIBE :CLOB1 GET LENGTH INTO :LEN END-EXEC. MOVE LEN TO D-LEN. DISPLAY "The length is ", D-LEN. * Free the resources used by the CLOB: END-OF-CLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :CLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/llength.pc */ /* Getting the length of a LOB */ #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 getLengthLOB_proc() { OCIClobLocator *Lob_loc; unsigned int Length; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_sourcetext INTO :Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001; /* Opening the LOB is Optional: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Get the Length: */ EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length; /* If the LOB is NULL or unitialized, then Length is Undefined: */ printf("Length is %d characters\n", Length); /* Closing the LOB is mandatory if you have Opened it: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; getLengthLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/llength.bas 'Getting the length of a LOB Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value 'Display out size of the lob: MsgBox "Length of the lob is " & OraAdPhoto1.Size
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/llength.java */ //Getting the length of a LOB import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_95 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { CLOB lob_loc = null; ResultSet rset = stmt.executeQuery ("SELECT ad_sourcetext FROM Print_media WHERE product_id = 3106"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getCLOB (1); } System.out.println( "Length of this column is : " + Long.toString(lob_loc.length())); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to copy all or part of a LOB to another LOB. These APIs copy an amount of data you specify from a source LOB to a destination LOB.
Note the following issues when using this API.
The value you pass for the amount
parameter to the DBMS_LOB.COPY
function must be one of the following:
DBMS_LOB.LOBMAXSIZE
Passing this value causes the function to read the entire LOB. This is a useful technique for reading the entire LOB without introspecting the size of the LOB.Note that for character data, the amount is specified in characters, while for binary data, the amount is specified in bytes.
If you plan to update a LOB value, then you must lock the row containing the LOB prior to updating. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using an OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".
See the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lcopy.sql */ /* Procedure copyLOB_proc is not part of the DBMS_LOB package: */ /* copying all or part of a lob to another lob */ CREATE OR REPLACE PROCEDURE copyLOB_proc (Dest_loc IN OUT BLOB, Src_loc IN OUT BLOB) IS /* Note: Dest_loc and Src_loc can be persistent or temporary LOBs */ Amount NUMBER := 1; Dest_pos NUMBER := 3; Src_pos NUMBER := 2; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB COPY EXAMPLE ------------'); /* Opening the LOBs is optional: */ DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY); /* Copies the LOB from the source position to the destination position: */ DBMS_LOB.COPY(Dest_loc, Src_loc, Amount, Dest_pos, Src_pos); /* Closing LOBs is mandatory if you have opened them: */ DBMS_LOB.CLOSE(Dest_loc); DBMS_LOB.CLOSE(Src_loc); DBMS_OUTPUT.PUT_LINE('Copy succeeded'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Copy failed'); DBMS_LOB.CLOSE(Dest_loc); DBMS_LOB.CLOSE(Src_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lcopy.c */ /* This function copies part of the Source LOB into a specified position in the destination LOB */ #include <oratypes.h> #include <lobdemo.h> void copyAllPartLOB_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 Amount = 100; /* <Amount to Copy> */ oraub8 Dest_pos = 100; /*<Position to start copying into> */ oraub8 Src_pos = 1; /* <Position to start copying from> */ printf ("----------- OCILobCopy Demo --------------\n"); /* Opening the LOBs is Optional */ printf (" open the destination locator (optional)\n"); checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc2, OCI_LOB_READWRITE)); printf (" open the source locator (optional)\n"); checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc1, OCI_LOB_READONLY)); printf (" copy the lob (amount) from the source to destination\n"); checkerr (errhp, OCILobCopy2(svchp, errhp, Lob_loc2, Lob_loc1, Amount, Dest_pos, Src_pos)); /* Closing the LOBs is Mandatory if they have been Opened */ printf(" close the locators\n"); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc2)); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc1)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lcopy.pco * COPYING ALL OR PART OF A LOB TO ANOTHER LOB IDENTIFICATION DIVISION. PROGRAM-ID. BLOB-COPY. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 DEST SQL-BLOB. 01 SRC SQL-BLOB. * Define the amount to copy. * This value has been chosen arbitrarily: 01 AMT PIC S9(9) COMP VALUE 10. * Define the source and destination position. * These values have been chosen arbitrarily: 01 SRC-POS PIC S9(9) COMP VALUE 1. 01 DEST-POS PIC S9(9) COMP VALUE 1. * The return value from PL/SQL function: 01 RET PIC S9(9) COMP. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. COPY-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 :DEST END-EXEC. EXEC SQL ALLOCATE :SRC END-EXEC. DISPLAY "Source and destination LOBs are open.". EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_PHOTO INTO :SRC FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 3106 AND AD_ID = 13001 END-EXEC. DISPLAY "Source LOB populated.". EXEC SQL SELECT AD_PHOTO INTO :DEST FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 3060 AND AD_ID = 11001 FOR UPDATE END-EXEC. DISPLAY "Destination LOB populated.". * Open the DESTination LOB read/write and SRC LOB read only EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC. EXEC SQL LOB OPEN :SRC READ ONLY END-EXEC. DISPLAY "Source and destination LOBs are open.". * Copy the desired amount EXEC SQL LOB COPY :AMT FROM :SRC AT :SRC-POS TO :DEST AT :DEST-POS END-EXEC. DISPLAY "Src LOB copied to destination LOB.". * Execute PL/SQL to get COMPARE functionality * to make sure that the BLOBs are identical EXEC SQL EXECUTE BEGIN :RET := DBMS_LOB.COMPARE(:SRC,:DEST,: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 :DEST END-EXEC. EXEC SQL LOB CLOSE :SRC END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :DEST END-EXEC. EXEC SQL FREE :SRC END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lcopy.pc */ /* Copying all or part of a LOB to another LOB */ #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 copyLOB_proc() { OCIBlobLocator *Dest_loc, *Src_loc; int Amount = 5; int Dest_pos = 10; int Src_pos = 1; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate the LOB locators: */ EXEC SQL ALLOCATE :Dest_loc; EXEC SQL ALLOCATE :Src_loc; /* Select the LOBs: */ EXEC SQL SELECT ad_photo INTO :Dest_loc FROM Print_media WHERE product_id = 2268 AND AD_ID = 21001 FOR UPDATE; EXEC SQL SELECT ad_photo INTO :Src_loc FROM Print_media WHERE product_id = 2056 AND ad_id = 12001; /* Opening the LOBs is Optional: */ EXEC SQL LOB OPEN :Dest_loc READ WRITE; EXEC SQL LOB OPEN :Src_loc READ ONLY; /* Copies the specified Amount from the source position in the source LOB to the destination position in the destination LOB: */ EXEC SQL LOB COPY :Amount FROM :Src_loc AT :Src_pos TO :Dest_loc AT :Dest_pos; /* Closing the LOBs is mandatory if they have been opened: */ EXEC SQL LOB CLOSE :Dest_loc; EXEC SQL LOB CLOSE :Src_loc; /* Release resources held by the locators: */ EXEC SQL FREE :Dest_loc; EXEC SQL FREE :Src_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; copyLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lcopy.bas 'Copying all or part of a LOB to another LOB Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value Set OraAdPhotoClone = OraAdPhoto1.Clone 'Go to next row and copy LOB OraDyn.MoveNext OraDyn.Edit OraAdPhoto1.Copy OraAdPhotoClone, OraAdPhotoClone.Size, 1, 1 OraDyn.Update
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lcopy.java */ // Copying all or part of a LOB to another LOB import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_100 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { final int AMOUNT_TO_COPY = 2000; ResultSet rset = null; BLOB dest_loc = null; BLOB src_loc = null; InputStream in = null; OutputStream out = null; byte[] buf = new byte[AMOUNT_TO_COPY]; rset = stmt.executeQuery ( "SELECT ad_photo FROM Print_media WHERE product_id = 3060 AND ad_ad = 11001"); if (rset.next()) { src_loc = ((OracleResultSet)rset).getBLOB (1); } in = src_loc.getBinaryStream(); rset = stmt.executeQuery ( "SELECT ad_photo FROM Print_media WHERE product_id = 3106 AND ad_id = 13001 FOR UPDATE"); if (rset.next()) { dest_loc = ((OracleResultSet)rset).getBLOB (1); } out = dest_loc.getBinaryOutputStream(); // read AMOUNT_TO_COPY bytes into buf from stream, starting from offset 0: in.read(buf, 0, AMOUNT_TO_COPY); // write AMOUNT_TO_COPY bytes from buf into output stream, starting at offset 0: out.write(buf, 0, AMOUNT_TO_COPY); // Close all streams and handles in.close(); out.flush(); out.close(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to copy a LOB locator. Note that different locators may point to the same or different data, or to current or outdated data.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
Note: Assigning one LOB to another using PL/SQL entails using the ":=" sign. This is an advanced topic that is discussed in more detail in "Read Consistent Locators". |
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lcopyloc.sql */ /* Procedure lobAssign_proc is not part of the DBMS_LOB package. */ /* copying a lob locator */ CREATE OR REPLACE PROCEDURE lobAssign_proc (Lob_loc1 IN OUT BLOB) IS /* Note that Lob_loc1 can be a persistent or temporary LOB */ Lob_loc2 BLOB; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB ASSIGN EXAMPLE ------------'); /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the * lob 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. */ END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lcopyloc.c */ /* Copying a LOB locator */ #include <oratypes.h> #include <lobdemo.h> void assignLOB_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { boolean isEqual; printf ("----------- OCILobAssign Demo --------------\n"); /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the LOB at this point in time. */ printf(" assign the src locator to dest locator\n"); checkerr (errhp, OCILobLocatorAssign(svchp, errhp, Lob_loc1, &Lob_loc2)); /* 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. */ /* Call OCI to see if the two locators are Equal */ printf (" check if Lobs are Equal.\n"); checkerr (errhp, OCILobIsEqual(envhp, Lob_loc1, Lob_loc2, &isEqual)); if (isEqual) { /* ... The LOB locators are Equal */ printf(" Lob Locators are equal.\n"); } else { /* ... The LOB locators are not Equal */ printf(" Lob Locators are NOT Equal.\n"); } /* Note that in this example, the LOB locators will be Equal */ return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lcopyloc.pco * COPYING A LOB LOCATOR IDENTIFICATION DIVISION. PROGRAM-ID. COPY-LOCATOR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 DEST SQL-BLOB. 01 SRC SQL-BLOB. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. COPY-BLOB-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 :DEST END-EXEC. EXEC SQL ALLOCATE :SRC END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_COMPOSITE INTO :SRC FROM PRINT_MEDIA WHERE PRODUCT_ID = 2268 AND AD_ID = 21001 FOR UPDATE END-EXEC. EXEC SQL LOB ASSIGN :SRC TO :DEST END-EXEC. * When you write data to the LOB through SRC, DEST will * not see the newly written data END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :DEST END-EXEC. EXEC SQL FREE :SRC END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lcopyloc.pc */ /* Copying a LOB locator */ #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 lobAssign_proc() { OCIBlobLocator *Lob_loc1, *Lob_loc2; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc1; EXEC SQL ALLOCATE :Lob_loc2; EXEC SQL SELECT ad_composite INTO :Lob_loc1 FROM Print_media WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE; /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the LOB at this point in time: */ EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2; /* 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: */ } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; lobAssign_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lcopyloc.bas 'Copying a LOB locator Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id, ad_id ", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value Set OraAdPhotoClone = OraAdPhoto1.Clone OraDyn.MoveNext 'Copy 1000 bytes of LOB values OraAdPhotoClone to OraAdPhotol at OraAdPhotol 'offset 100: OraDyn.Edit OraAdPhoto1.Copy OraAdPhotoClone, 1000, 100 OraDyn.Update
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lcopyloc.java */ // Copying a LOB locator import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_104 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB lob_loc1 = null; BLOB lob_loc2 = null; ResultSet rset = stmt.executeQuery ( "SELECT ad_composite FROM Print_media WHERE product_id = 2056 AND ad_id = 12001"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBLOB (1); } // When you write data to LOB through lob_loc1,lob_loc2 will not see changes lob_loc2 = lob_loc1; stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to determine whether one LOB locator is equal to another. If two locators are equal, then this means that they refer to the same version of the LOB data.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lequal.c */ /* Seeing if One LOB locator is Equal to Another */ #include <oratypes.h> #include <lobdemo.h> void locatorIsEqual_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { boolean isEqual; OCILobLocator *Lob_loc3; printf ("----------- OCILobIsEqual Demo --------------\n"); /* Call OCI to see if the two locators are Equal: */ printf (" check if Lob1 and Lob2 are Equal.\n"); checkerr (errhp, OCILobIsEqual(envhp, Lob_loc1, Lob_loc2, &isEqual)); if (isEqual) { /* ... The LOB locators are Equal: */ printf(" Lob Locators are equal.\n"); } else { /* ... The LOB locators are not Equal: */ printf(" Lob Locators are NOT Equal.\n"); } (void)OCIDescriptorAlloc((void *) envhp, (void **) &Lob_loc3, (ub4)OCI_DTYPE_LOB, (size_t) 0, (void **) 0); /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the LOB at this point in time: */ printf(" assign the src locator to dest locator\n"); checkerr (errhp, OCILobLocatorAssign(svchp, errhp, Lob_loc1, &Lob_loc3)); /* 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: */ /* Call OCI to see if the two locators are Equal: */ printf (" check if Lob1 and Lob3 are Equal.\n"); checkerr (errhp, OCILobIsEqual(envhp, Lob_loc1, Lob_loc3, &isEqual)); if (isEqual) { /* ... The LOB locators are Equal: */ printf(" Lob Locators are equal.\n"); } else { /* ... The LOB locators are not Equal: */ printf(" Lob Locators are NOT Equal.\n"); } OCIDescriptorFree((void *)Lob_loc3, (ub4)OCI_DTYPE_LOB); return; }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lequal.pc */ /* Seeing if One LOB locator is equal to another */ /* Pro*C/C++ does not provide a mechanism to test the equality of two locators. But you can use OCI directly. Two locators can be compared to determine whether or not they are equal as this example demonstrates: */ #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 LobLocatorIsEqual_proc() { OCIBlobLocator *Lob_loc1, *Lob_loc2; OCIEnv *oeh; boolean isEqual; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc1; EXEC SQL ALLOCATE :Lob_loc2; EXEC SQL SELECT ad_composite INTO Lob_loc1 FROM Print_media where product_id = 3060 AND ad_id = 11001 FOR UPDATE; /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the LOB at this point in time: */ EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2; /* 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. */ /* Get the OCI Environment Handle using a SQLLIB Routine: */ (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh); /* Call OCI to see if the two locators are Equal: */ (void) OCILobIsEqual(oeh, Lob_loc1, Lob_loc2, &isEqual); if (isEqual) printf("The locators are equal\n"); else printf("The locators are not equal\n"); /* Note that in this example, the LOB locators will be Equal */ EXEC SQL FREE :Lob_loc1; EXEC SQL FREE :Lob_loc2; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; LobLocatorIsEqual_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lequal.java */ // Seeing if one LOB locator is equal to another import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_108 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB lob_loc1 = null; BLOB lob_loc2 = null; ResultSet rset = stmt.executeQuery ( "SELECT ad_photo FROM Print_media WHERE product_id = 3106 AND ad_id = 13001"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBLOB (1); } // When you write data to LOB through lob_loc1,lob_loc2 will not see the changes: lob_loc2 = lob_loc1; // Note that in this example, the Locators will be equal. if (lob_loc1.equals(lob_loc2)) { // The Locators are equal: System.out.println("Locators are equal"); } else { // The Locators are different: System.out.println("Locators are NOT equal"); } stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to determine whether a LOB locator is initialized.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/linit.c */ /* Seeing if a LOB locator is initialized */ #include <oratypes.h> #include <lobdemo.h> void isInitializedLOB_proc(OCILobLocator *Lob_loc1, OCILobLocator *Lob_loc2, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { boolean isInitialized; printf ("----------- OCILobLocatorIsInit Demo --------------\n"); /* Determine if the locator 1 is Initialized -: */ checkerr(errhp, OCILobLocatorIsInit(envhp, errhp, Lob_loc1, &isInitialized)); /* IsInitialized should return TRUE here */ printf(" for Locator 1, isInitialized = %d\n", isInitialized); /* Determine if the locator 2 is Initialized -: */ checkerr(errhp, OCILobLocatorIsInit(envhp, errhp, Lob_loc2, &isInitialized)); /* IsInitialized should return FALSE here */ printf(" for Locator 2, isInitialized = %d\n", isInitialized); return; }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/linit.pc */ /* Seeing if a LOB locator is initialized */ /* Pro*C/C++ has no form of embedded SQL statement to determine if a LOB locator is initialized. Locators in Pro*C/C++ are initialized when they are allocated through the EXEC SQL ALLOCATE statement. An example can be written that uses embedded SQL and the OCI as is shown here: */ #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 LobLocatorIsInit_proc() { OCIBlobLocator *Lob_loc; OCIEnv *oeh; OCIError *err; boolean isInitialized; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_composite INTO Lob_loc FROM Print_media where product_id = 2056 AND ad_id = 12001; /* 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, Lob_loc, &isInitialized); if (isInitialized) printf("The locator is initialized\n"); else printf("The 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); /* Release resources held by the locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; LobLocatorIsInit_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
This section describes how to write-append the contents of a buffer to a LOB.
Note the following issues regarding usage of this API.
The writeappend
operation writes a buffer to the end of a LOB.
For OCI, the buffer can be written to the LOB in a single piece with this call; alternatively, it can be rendered piecewise using callbacks or a standard polling method.
If the value of the piece parameter is OCI_FIRST_PIECE
, then data must be provided through callbacks or polling.
OCILobWriteAppend2
() returns the OCI_NEED_DATA
error code. The application must call OCILobWriteAppend2
() again to write more pieces of the LOB. In this mode, the buffer pointer and the length can be different in each call if the pieces are of different sizes and from different locations. A piece value of OCI_LAST_PIECE
terminates the piecewise write.Prior to updating a LOB value using the PL/SQL DBMS_LOB
package or the OCI, you must lock the row containing the LOB. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of an SQL SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using an OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lwriteap.sql */ /* Procedure lobWriteAppend_proc is not part of the DBMS_LOB package: */ /* writing to the end of lob (write append) */ CREATE OR REPLACE PROCEDURE lobWriteAppend_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be a persistent or temporary LOB */ Buffer RAW(32767); Amount Binary_integer := 4; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB WRITEAPPEND EXAMPLE ------------'); /* Fill the buffer with data... */ Buffer := hextoraw('ab2daa44'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); /* Append the data from the buffer to the end of the LOB: */ DBMS_LOB.WRITEAPPEND(Lob_loc, Amount, Buffer); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE(Lob_loc); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lwriteap.c */ /* Write-appending to a LOB */ #include <oratypes.h> #include <lobdemo.h> void writeAppendLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 amt; oraub8 offset; sword retval; ub1 bufp[MAXBUFLEN]; oraub8 buflen; printf ("----------- OCILobWriteAppend Demo --------------\n"); /* Open the CLOB: */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Setting the amt to the buffer length. Note here that amt is in chars since we are using a CLOB: */ amt = sizeof(bufp); buflen = sizeof(bufp); /* Fill bufp with data: */ /* Write the data from the buffer at the end of the LOB: */ printf(" write-append data to the frame Lob\n"); checkerr (errhp, OCILobWriteAppend2(svchp, errhp, Lob_loc, NULL, &amt, (void *)bufp, buflen, OCI_ONE_PIECE, (void *)0, (OCICallbackLobWrite2) 0, 0, SQLCS_IMPLICIT)); /* Closing the CLOB is mandatory if you have opened it: */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lwriteap.pco * WRITE-APPENDING TO A LOB IDENTIFICATION DIVISION. PROGRAM-ID. WRITE-APPEND-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BLOB1 SQL-BLOB. 01 AMT PIC S9(9) COMP. 01 BUFFER PIC X(32767) VARYING. EXEC SQL VAR BUFFER IS LONG RAW (32767) END-EXEC. 01 USERID PIC X(11) VALUES "SAMP/SAMP". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. WRITE-APPEND-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 :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_COMPOSITE INTO :BLOB1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 FOR UPDATE END-EXEC. * Open the target LOB: EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC. * Populate AMT here: MOVE 5 TO AMT. MOVE "2424242424" to BUFFER. * Append the source LOB to the destination LOB: EXEC SQL LOB WRITE APPEND :AMT FROM :BUFFER INTO :BLOB1 END-EXEC. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lwriteap.pc */ /* Write-appending to a LOB */ #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 128 void LobWriteAppend_proc() { OCIBlobLocator *Lob_loc; int Amount = BufferLength; /* Amount == BufferLength so only a single WRITE is needed: */ char Buffer[BufferLength]; /* Datatype equivalencing is mandatory for this datatype: */ EXEC SQL VAR Buffer IS RAW(BufferLength); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_composite INTO :Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE; /* Opening the LOB is Optional: */ EXEC SQL LOB OPEN :Lob_loc; memset((void *)Buffer, 1, BufferLength); /* Write the data from the buffer at the end of the LOB: */ EXEC SQL LOB WRITE APPEND :Amount FROM :Buffer INTO :Lob_loc; /* Closing the LOB is mandatory if it has been opened: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; LobWriteAppend_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lwriteap.java */ // Write-appending to a LOB import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_126 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB dest_loc = null; byte[] buf = new byte[MAXBUFSIZE]; long pos = 0; ResultSet rset = stmt.executeQuery ( "SELECT ad_composite FROM Print_media WHERE product_id = 2056 AND ad_id = 12001 FOR UPDATE"); if (rset.next()) { dest_loc = ((OracleResultSet)rset).getBLOB (1); } // Start writing at the end of the LOB. ie. append: pos = dest_loc.length(); // fill buf with contents to be written: buf = (new String("Hello World")).getBytes(); // Write the contents of the buffer into position pos of the output LOB: dest_loc.putBytes(pos, buf); // Close all streams and handles: stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to write the contents of a buffer to a LOB.
Note the following issues regarding usage of this API.
The most efficient way to write large amounts of LOB data is to use OCILobWrite2
() with the streaming mechanism enabled using polling or a callback. If you know how much data will be written to the LOB, then specify that amount when calling OCILobWrite2
(). This ensures that LOB data on the disk is contiguous. Apart from being spatially efficient, the contiguous structure of the LOB data will make for faster reads and writes in subsequent operations.
A chunk is one or more Oracle blocks. As noted previously, you can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the chunk size used by Oracle when accessing/modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The getchunksize
function returns the amount of space used in the LOB chunk to store the LOB value.
You will improve performance if you run write
requests using a multiple of this chunk size. The reason for this is that the LOB chunk is versioned for every write
operation. If all writes
are done on a chunk basis, then no extra or excess versioning is incurred or duplicated. If it is appropriate for your application, then you should batch writes until you have enough for an entire chunk instead of issuing several LOB write calls that operate on the same LOB chunk.
Prior to updating a LOB value using the PL/SQL DBMS_LOB
package or OCI, you must lock the row containing the LOB. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using an OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".
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:
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;
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lwrite.sql */ /* Procedure writeDataToLOB_proc is not part of the DBMS_LOB package: */ /* writing data to a lob */ CREATE or REPLACE PROCEDURE writeDataToLOB_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be a persistent or temporary LOB */ Buffer RAW(32767); Amount BINARY_INTEGER := 10; OptimalAmount BINARY_INTEGER; Position INTEGER := 1; i INTEGER; Chunk_size INTEGER; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB WRITE EXAMPLE ------------'); /* For persistent LOBs, for each DBMS_LOB call, * we write data in multiples of chunksize, * and write on chunk boundaries. This ensures best performance */ Chunk_size := DBMS_LOB.GETCHUNKSIZE(Lob_loc); OptimalAmount := (Amount/Chunk_size) * Chunk_size; if OptimalAmount = 0 then OptimalAmount := Amount; end if; /* Fill a buffer */ Buffer := hextoraw(lpad('4', Amount*4, '4')); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); FOR i IN 1..3 LOOP Amount := OptimalAmount; 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); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lwrite.c */ /* Writing data to a LOB. Using OCI you can write arbitrary amounts of data to an Internal LOB in either a single piece or in multiple pieces using streaming with standard polling. A dynamically allocated Buffer holds the data being written to the LOB. */ #include <oratypes.h> #include <lobdemo.h> void writeLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, oraub8 amt_to_write) { /* <total amount of data to write to the CLOB in bytes> */ oraub8 amt; oraub8 offset; unsigned int remainder, nbytes; boolean last; ub1 bufp[MAXBUFLEN]; sword err; oraub8 lob_len; printf ("----------- OCILobWrite Demo --------------\n"); /* Open the CLOB */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); if (amt_to_write > MAXBUFLEN) nbytes = MAXBUFLEN; /* We will use streaming through standard polling */ else nbytes = (unsigned int)amt_to_write; /* Only a single write is required */ /* Fill in all a's */ memset((void *)bufp, 'a', MAXBUFLEN); /* Fill the buffer with nbytes worth of data */ remainder = (unsigned int)(amt_to_write - nbytes); /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE */ amt = 0; offset = 1; /* check lob length before update */ checkerr (errhp, OCILobGetLength2(svchp, errhp, Lob_loc, &lob_len)); printf(" Lob length before update = %d\n", (ub4)lob_len); if (0 == remainder) { printf(" writing the Lob data in one-piece mode\n"); amt = (oraub8)nbytes; /* Here, (amt_to_write <= MAXBUFLEN ) so we can write in one piece */ checkerr (errhp, OCILobWrite2(svchp, errhp, Lob_loc, &amt, NULL, offset, (void *)bufp, nbytes, OCI_ONE_PIECE, (void *)0, (OCICallbackLobWrite2)0, 0, SQLCS_IMPLICIT)); } else { printf(" writing the Lob data in streaming polling mode\n"); /* Here (amt_to_write > MAXBUFLEN ) so we use streaming through standard polling */ /* write the first piece. Specifying first initiates polling. */ err = OCILobWrite2(svchp, errhp, Lob_loc, &amt, NULL, offset, (void *)bufp, nbytes, OCI_FIRST_PIECE, (void *)0, (OCICallbackLobWrite2) 0, 0, SQLCS_IMPLICIT); printf(" 1st call. amt returned = %d\n", (ub4)amt); 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 = OCILobWrite2(svchp, errhp, Lob_loc, &amt, NULL, offset, (void *)bufp, nbytes, OCI_LAST_PIECE, (void *)0, (OCICallbackLobWrite2) 0, 0, SQLCS_IMPLICIT); printf(" last call. amt returned = %d\n", (ub4)amt); if (err != OCI_SUCCESS) checkerr(errhp, err); } else { err = OCILobWrite2(svchp, errhp, Lob_loc, &amt, NULL, offset, (void *)bufp, nbytes, OCI_NEXT_PIECE, (void *)0, (OCICallbackLobWrite2) 0, 0, SQLCS_IMPLICIT); printf(" subsequent call. amt returned = %d\n", (ub4)amt); if (err != OCI_NEED_DATA) checkerr (errhp, err); } /* Determine how much is left to write */ remainder = remainder - nbytes; } while (!last); } /* check lob length after update */ checkerr (errhp, OCILobGetLength2(svchp, errhp, Lob_loc, &lob_len)); printf(" Lob length after update = %d\n", (ub4)lob_len); /* At this point, (remainder == 0) */ /* Closing the LOB is mandatory if you have opened it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lwrite.pco * WRITING DATA TO A LOB IDENTIFICATION DIVISION. PROGRAM-ID. WRITE-CLOB. ENVIRONMENT DIVISION. INPUT-OUTPUT SECTION. FILE-CONTROL. SELECT INFILE ASSIGN TO "datfile.dat" ORGANIZATION IS SEQUENTIAL. DATA DIVISION. FILE SECTION. FD INFILE RECORD CONTAINS 5 CHARACTERS. 01 INREC PIC X(5). WORKING-STORAGE SECTION. 01 CLOB1 SQL-CLOB. 01 BUFFER PIC X(5) VARYING. 01 AMT PIC S9(9) COMP VALUES 321. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 END-OF-FILE PIC X(1) VALUES "N". 01 D-BUFFER-LEN PIC 9. 01 D-AMT PIC 9. 01 USERID PIC X(11) VALUES "SAMP/SAMP". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. WRITE-CLOB. EXEC SQL WHENEVER SQLERROR GOTO SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Open the input file: OPEN INPUT INFILE. * Allocate and initialize the CLOB locator: EXEC SQL ALLOCATE :CLOB1 END-EXEC. EXEC SQL SELECT AD_SOURCETEXT INTO :CLOB1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3106 AND AD_ID = 13001 FOR UPDATE END-EXEC. * Either write entire record or write first piece * Read a data file here and populate BUFFER-ARR and BUFFER-LEN * END-OF-FILE will be set to "Y" when the entire file has been * read. PERFORM READ-NEXT-RECORD. MOVE INREC TO BUFFER-ARR. MOVE 5 TO BUFFER-LEN. IF (END-OF-FILE = "Y") EXEC SQL LOB WRITE ONE :AMT FROM :BUFFER INTO :CLOB1 AT :OFFSET END-EXEC ELSE DISPLAY "LOB WRITE FIRST: ", BUFFER-ARR EXEC SQL LOB WRITE FIRST :AMT FROM :BUFFER INTO :CLOB1 AT :OFFSET END-EXEC. * Continue reading from the input data file * and writing to the CLOB: PERFORM READ-NEXT-RECORD. PERFORM WRITE-TO-CLOB UNTIL END-OF-FILE = "Y". MOVE INREC TO BUFFER-ARR. MOVE 1 TO BUFFER-LEN. DISPLAY "LOB WRITE LAST: ", BUFFER-ARR(1:BUFFER-LEN). EXEC SQL LOB WRITE LAST :AMT FROM :BUFFER INTO :CLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. WRITE-TO-CLOB. IF ( END-OF-FILE = "N") MOVE INREC TO BUFFER-ARR. MOVE 5 TO BUFFER-LEN. DISPLAY "LOB WRITE NEXT: ", BUFFER-ARR(1:BUFFER-LEN). EXEC SQL LOB WRITE NEXT :AMT FROM :BUFFER INTO :CLOB1 END-EXEC. PERFORM READ-NEXT-RECORD. READ-NEXT-RECORD. MOVE SPACES TO INREC. READ INFILE NEXT RECORD AT END MOVE "Y" TO END-OF-FILE. DISPLAY "END-OF-FILE IS " END-OF-FILE. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lwrite.pc */ /* Writing data to a LOB */ /* This example shows how you can use Pro*C/C++ to write arbitrary amounts of data to an Internal LOB in either a single piece of in multiple pieces using a Streaming Mechanism that utilizes standard polling. A dynamically allocated Buffer holds the data being written to the LOB: */ #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 writeDataToLOB_proc(multiple) int multiple; { OCIClobLocator *Lob_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 Locator: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_sourcetext INTO Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE; /* Open the CLOB: */ EXEC SQL LOB OPEN :Lob_loc READ WRITE; Total = Amount = (multiple * BufferLength); if (Total > BufferLength) nbytes = BufferLength; /* Using streaming through 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 :Lob_loc; printf("Write ONE Total of %d characters\n", Amount); } else { /* Here (Total > BufferLength) so we use streaming through /* standard polling to write the first piece. /* Specifying FIRST initiates polling: */ EXEC SQL LOB WRITE FIRST :Amount FROM :Buffer INTO :Lob_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 :Lob_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 :Lob_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 CLOB: */ EXEC SQL LOB CLOSE :Lob_loc; /* Free resources held by the Locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; writeDataToLOB_proc(1); EXEC SQL ROLLBACK WORK; writeDataToLOB_proc(4); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lwrite.bas 'Writing data to a LOB 'There are two ways of writing a lob, with orablob.write or orablob.copyfromfile 'Using the OraBlob.Write mechanism Dim OraDyn As OraDynaset, OraAdPhoto As OraBlob, amount_written%, chunksize%, curchunk() As Byte chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media", ORADYN_DEFAULT) Set OraAdPhoto = OraDyn.Fields("ad_photo").Value fnum = FreeFile Open "c:\tmp\keyboard_3106_13001" For Binary As #fnum OraAdPhoto.offset = 1 OraAdPhoto.pollingAmount = LOF(fnum) remainder = LOF(fnum) Dim piece As Byte Get #fnum, , curchunk OraDyn.Edit piece = ORALOB_FIRST_PIECE OraAdPhoto.Write curchunk, chunksize, ORALOB_FIRST_PIECE While OraAdPhoto.Status = ORALOB_NEED_DATA remainder = remainder - chunksize If remainder <= chunksize Then chunksize = remainder piece = ORALOB_LAST_PIECE Else piece = ORALOB_NEXT_PIECE End If Get #fnum, , curchunk OraAdPhoto.Write curchunk, chunksize, piece Wend OraDyn.Update 'Using the OraBlob.CopyFromFile mechanism Set OraDyn = OraDb.CreateDynaset("select * from Print_media", ORADYN_DEFAULT) Set OraAdPhoto = OraDyn.Fields("ad_photo").Value Oradyn.Edit OraAdPhoto.CopyFromFile "c:\keyboardphoto3106.jpg" Oradyn.Update
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lwrite.java */ //Writing data to a LOB import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_126 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB dest_loc = null; byte[] buf = new byte[MAXBUFSIZE]; long pos = 0; ResultSet rset = stmt.executeQuery ( "SELECT ad_composite FROM Print_media WHERE product_id = 2056 AND ad_id = 12001 FOR UPDATE"); if (rset.next()) { dest_loc = ((OracleResultSet)rset).getBLOB (1); } // Start writing at the end of the LOB. ie. append: pos = dest_loc.length(); // fill buf with contents to be written: buf = (new String("Hello World")).getBytes(); // Write the contents of the buffer into position pos of the output LOB: dest_loc.putBytes(pos, buf); // Close all streams and handles: stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to trim a LOB to the size you specify.
Note the following issues regarding usage of this API.
Prior to updating a LOB value using the PL/SQL DBMS_LOB
package, or OCI, you must lock the row containing the LOB. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of:
SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs.OCI
pin
or lock
function in OCI programs.For more details on the state of the locator after an update, refer to "Updating LOBs Through Updated Locators".
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/ltrim.sql */ /* Procedure trimLOB_proc is not part of the DBMS_LOB package: */ /* trimming lob data */ CREATE OR REPLACE PROCEDURE trimLOB_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be a persistent or temporary LOB */ BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB TRIM EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); /* Trim the LOB data: */ DBMS_LOB.TRIM(Lob_loc,3); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); DBMS_OUTPUT.PUT_LINE('Trim succeeded'); /* Exception handling: */ EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Trim failed'); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/ltrim.c */ /* Trimming LOB data */ #include <oratypes.h> #include <lobdemo.h> void trimLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 trimLength; printf ("----------- OCILobTrim Demo --------------\n"); /* Open the CLOB */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Trim the LOB to its new length */ trimLength = 200; /* <New truncated length of the LOB>*/ printf (" trim the lob to %d bytes\n", (ub4)trimLength); checkerr (errhp, OCILobTrim2(svchp, errhp, Lob_loc, trimLength )); /* Closing the CLOB is mandatory if you have opened it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/ltrim.pco * Trimming LOB data IDENTIFICATION DIVISION. PROGRAM-ID. TRIM-CLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 CLOB1 SQL-CLOB. 01 NEW-LEN PIC S9(9) COMP. * Define the source and destination position and location: 01 SRC-POS PIC S9(9) COMP. 01 DEST-POS PIC S9(9) COMP. 01 SRC-LOC PIC S9(9) COMP. 01 DEST-LOC PIC S9(9) COMP. 01 USERID PIC X(11) VALUES "SAMP/SAMP". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. TRIM-CLOB. 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 :CLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC. EXEC SQL SELECT PM.AD_SOURCETEXT INTO :CLOB1 FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 3060 AND AD_ID = 11001 FOR UPDATE END-EXEC. * Open the CLOB: EXEC SQL LOB OPEN :CLOB1 READ WRITE END-EXEC. * Move some value to NEW-LEN: MOVE 3 TO NEW-LEN. EXEC SQL LOB TRIM :CLOB1 TO :NEW-LEN END-EXEC. EXEC SQL LOB CLOSE :CLOB1 END-EXEC. END-OF-CLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :CLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/ltrim.pc */ /* Trimming LOB data */ #include "pers_trim.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 trimLOB_proc() { voiced_typ_ref *vt_ref; voiced_typ *vt_typ; OCIClobLocator *Lob_loc; unsigned int Length, trimLength; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL ALLOCATE :vt_ref; EXEC SQL ALLOCATE :vt_typ; /* Retrieve the REF using Associative SQL */ EXEC SQL SELECT PMtab.ad_sourctext INTO :vt_ref FROM Print_media PMtab WHERE PMtab.product_id = 3060 AND ad_id = 11001 FOR UPDATE; /* Dereference the Object using the Navigational Interface */ EXEC SQL OBJECT DEREF :vt_ref INTO :vt_typ FOR UPDATE; Lob_loc = vt_typ->script; /* Opening the LOB is Optional */ EXEC SQL LOB OPEN :Lob_loc READ WRITE; EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length; printf("Old length was %d\n", Length); trimLength = (unsigned int)(Length / 2); /* Trim the LOB to its new length */ EXEC SQL LOB TRIM :Lob_loc TO :trimLength; /* Closing the LOB is mandatory if it has been opened */ EXEC SQL LOB CLOSE :Lob_loc; /* Mark the Object as Modified (Dirty) */ EXEC SQL OBJECT UPDATE :vt_typ; /* Flush the changes to the LOB in the Object Cache */ EXEC SQL OBJECT FLUSH :vt_typ; /* Display the new (modified) length */ EXEC SQL SELECT Mtab.Voiced_ref.Script INTO :Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 2; EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length; printf("New length is now %d\n", Length); /* Free the Objects and the LOB Locator */ EXEC SQL FREE :vt_ref; EXEC SQL FREE :vt_typ; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; trimLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/ltrim.bas 'Trimming LOB data Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value OraDyn.Edit OraAdPhoto1.Trim 10 OraDyn.Update
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/ltrim.java */ // Trimming BLOBs and CLOBs. // You need to import the java.sql package to use JDBC import java.sql.*; // You need to import the oracle.sql package to use oracle.sql.BLOB import oracle.sql.*; class TrimLob { public static void main (String args []) throws SQLException { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url = "jdbc:oracle:oci8:@"; try { String url1 = System.getProperty("JDBC_URL"); if (url1 != null) url = url1; } catch (Exception e) { // If there is any security exception, ignore it // and use the default } // Connect to the database Connection conn = DriverManager.getConnection (url, "pm", "pm"); // It's faster when auto commit is off conn.setAutoCommit (false); // Create a Statement Statement stmt = conn.createStatement (); try { stmt.execute ("drop table basic_lob_table"); } catch (SQLException e) { // An exception could be raised here if the table did not exist already. } // Create a table containing a BLOB and a CLOB stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob, c clob)"); // Populate the table stmt.execute ("insert into basic_lob_table values ('one', '010101010101010101010101010101', 'onetwothreefour')"); // Select the lobs ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); while (rset.next ()) { // Get the lobs BLOB blob = (BLOB) rset.getObject (2); CLOB clob = (CLOB) rset.getObject (3); // Show the original lob length System.out.println ("Open the lobs"); System.out.println ("blob.length()="+blob.length()); System.out.println ("clob.length()="+clob.length()); // Trim the lobs System.out.println ("Trim the lob to length = 6"); blob.trim (6); clob.trim (6); // Show the lob length after trim() System.out.println ("Open the lobs"); System.out.println ("blob.length()="+blob.length()); System.out.println ("clob.length()="+clob.length()); } // Close the ResultSet rset.close (); // Close the Statement stmt.close (); // Close the connection conn.close (); } }
Here is the old way of trimming LOB data, using DBMS_LOB.TRIM
:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/oldltrim.java */ // Trimming LOB data // Java IO classes: import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_141 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { CLOB lob_loc = null; ResultSet rset = stmt.executeQuery ( "SELECT pm.ad_finaltext FROM Print_media pm WHERE pm.product_id = 2056 AND ad_id = 12001 FOR UPDATE"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getCLOB (1); } // Open the LOB for READWRITE: OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall ("BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READWRITE); END;"); cstmt.setCLOB(1, lob_loc); cstmt.execute(); // Trim the LOB to length of 400: cstmt = (OracleCallableStatement) conn.prepareCall ("BEGIN DBMS_LOB.TRIM(?, 400); END;"); cstmt.setCLOB(1, lob_loc); cstmt.execute(); // Close the LOB: cstmt = (OracleCallableStatement) conn.prepareCall ( "BEGIN DBMS_LOB.CLOSE(?); END;"); cstmt.setCLOB(1, lob_loc); cstmt.execute(); stmt.close(); cstmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to erase part of a LOB.
Note the following issues regarding usage of this API.
Prior to updating a LOB value using the PL/SQL DBMS_LOB
package or OCI, you must lock the row containing the LOB. While INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of a SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using the OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to"Updating LOBs Through Updated Locators".
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/lerase.sql */ /* Procedure eraseLOB_proc is not part of the DBMS_LOB package: */ /* erasing part of a lob */ CREATE OR REPLACE PROCEDURE eraseLOB_proc (Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be a persistent or temporary LOB */ Amount INTEGER := 3000; BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB ERASE EXAMPLE ------------'); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); /* Erase the data: */ DBMS_LOB.ERASE(Lob_loc, Amount, 4); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); DBMS_OUTPUT.PUT_LINE('Erase succeeded'); /* Exception handling: */ EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Erase failed'); END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/lerase.c */ /* Erasing part of a LOB (persistent LOBs) */ #include <oratypes.h> #include <lobdemo.h> void eraseLOB_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { oraub8 amount = 300; oraub8 offset = 10; printf ("----------- OCILobErase Demo --------------\n"); /* Open the CLOB: */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Erase the data starting at the specified Offset: */ printf(" erase %d bytes at offset %d from the Lob\n", (ub4)amount, (ub4)offset); checkerr (errhp, OCILobErase2(svchp, errhp, Lob_loc, &amount, offset )); /* Closing the CLOB is mandatory if you have opened it: */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lerase.pco * ERASING PART OF A LOB IDENTIFICATION DIVISION. PROGRAM-ID. ERASE-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BLOB1 SQL-BLOB. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. ERASE-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 :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_PHOTO INTO :BLOB1 FROM PRINT_MEDIA PM WHERE PM.PRODUCT_ID = 2268 AND AD_ID = 21001 FOR UPDATE END-EXEC. * Open the BLOB: EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC. * Move some value to AMT and OFFSET: MOVE 2 TO AMT. MOVE 1 TO OFFSET. EXEC SQL LOB ERASE :AMT FROM :BLOB1 AT :OFFSET END-EXEC. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lerase.pc */ /* Erasing part of a LOB */ #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 eraseLob_proc() { OCIBlobLocator *Lob_loc; int Amount = 5; int Offset = 5; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_composite INTO :Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE; /* Opening the LOB is Optional: */ EXEC SQL LOB OPEN :Lob_loc READ WRITE; /* Erase the data starting at the specified Offset: */ EXEC SQL LOB ERASE :Amount FROM :Lob_loc AT :Offset; printf("Erased %d bytes\n", Amount); /* Closing the LOB is mandatory if it has been opened: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; eraseLob_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lerase.bas 'Erasing part of a LOB Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Print_media ORDER BY product_ id", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value 'Erase 10 bytes begining from the 100th byte: OraDyn.Edit OraAdPhoto1.Erase 10, 100 OraDyn.Update
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/lerase.java */ // Erasing part of a LOB import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_145 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB lob_loc = null; int eraseAmount = 30; ResultSet rset = stmt.executeQuery ( "SELECT ad_photo FROM Print_media WHERE product_id = 2056 AND ad_id = 12001 FOR UPDATE"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBLOB (1); } // Open the LOB for READWRITE: OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall ("BEGIN DBMS_LOB.OPEN(?, " +"DBMS_LOB.LOB_READWRITE); END;"); cstmt.setBLOB(1, lob_loc); cstmt.execute(); // Erase eraseAmount bytes starting at offset 2000: cstmt = (OracleCallableStatement) conn.prepareCall ("BEGIN DBMS_LOB.ERASE(?, ?, 1); END;"); cstmt.registerOutParameter (1, OracleTypes.BLOB); cstmt.registerOutParameter (2, Types.INTEGER); cstmt.setBLOB(1, lob_loc); cstmt.setInt(2, eraseAmount); cstmt.execute(); lob_loc = cstmt.getBLOB(1); eraseAmount = cstmt.getInt(2); // Close the LOB: cstmt = (OracleCallableStatement) conn.prepareCall ( "BEGIN DBMS_LOB.CLOSE(?); END;"); cstmt.setBLOB(1, lob_loc); cstmt.execute(); conn.commit(); stmt.close(); cstmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
This section describes how to enable LOB buffering.
Enable LOB buffering when you are performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.
Note:
|
For more information, refer to "LOB Buffering Subsystem".
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lenbuf.pco * ENABLING LOB BUFFERING IDENTIFICATION DIVISION. PROGRAM-ID. LOB-BUFFERING. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BLOB1 SQL-BLOB. 01 BUFFER PIC X(10). 01 AMT PIC S9(9) COMP. EXEC SQL VAR BUFFER IS RAW(10) END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. LOB-BUFFERING. 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 :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT ad_photo INTO :BLOB1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 3060 AND AD_ID = 11001 FOR UPDATE END-EXEC. * Open the BLOB and enable buffering: EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC. EXEC SQL LOB ENABLE BUFFERING :BLOB1 END-EXEC. * Write some data to the BLOB: MOVE "242424" TO BUFFER. MOVE 3 TO AMT. EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC. MOVE "212121" TO BUFFER. MOVE 3 TO AMT. EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC. * Now flush the buffered writes: EXEC SQL LOB FLUSH BUFFER :BLOB1 END-EXEC. EXEC SQL LOB DISABLE BUFFERING :BLOB1 END-EXEC. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lenbuf.pc */ /* Enabling LOB buffering #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 256 void enableBufferingLOB_proc() { OCIBlobLocator *Lob_loc; int Amount = BufferLength; int multiple, Position = 1; /* Datatype equivalencing is mandatory for this datatype: */ char Buffer[BufferLength]; EXEC SQL VAR Buffer is RAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate and Initialize the LOB: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_composite INTO :Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE; /* Enable use of the LOB Buffering Subsystem: */ EXEC SQL LOB ENABLE BUFFERING :Lob_loc; memset((void *)Buffer, 0, BufferLength); for (multiple = 0; multiple < 8; multiple++) { /* Write data to the LOB: */ EXEC SQL LOB WRITE ONE :Amount FROM :Buffer INTO :Lob_loc AT :Position; Position += BufferLength; } /* Flush the contents of the buffers and Free their resources: */ EXEC SQL LOB FLUSH BUFFER :Lob_loc FREE; /* Turn off use of the LOB Buffering Subsystem: */ EXEC SQL LOB DISABLE BUFFERING :Lob_loc; /* Release resources held by the Locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; enableBufferingLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/lenbuf.bas 'Enabling LOB buffering (persistent LOBs) Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value 'Enable buffering: OraAdPhoto1.EnableBuffering
This section describes how to flush the LOB buffer.
Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.
Notes:
|
For more information, refer to "LOB Buffering Subsystem".
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/lflbuf.pco * Flushing the LOB buffer (persistent LOBs) IDENTIFICATION DIVISION. PROGRAM-ID. LOB-BUFFERING. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BLOB1 SQL-BLOB. 01 BUFFER PIC X(10). 01 AMT PIC S9(9) COMP. EXEC SQL VAR BUFFER IS RAW(10) END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. LOB-BUFFERING. 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 :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT AD_PHOTO INTO :BLOB1 FROM PRINT_MEDIA WHERE PRODUCT_ID = 2056 AND AD_ID = 12001 FOR UPDATE END-EXEC. * Open the BLOB and enable buffering: EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC. EXEC SQL LOB ENABLE BUFFERING :BLOB1 END-EXEC. * Write some data to the BLOB: MOVE "242424" TO BUFFER. MOVE 3 TO AMT. EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC. MOVE "212121" TO BUFFER. MOVE 3 TO AMT. EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC. * Now flush the buffered writes: EXEC SQL LOB FLUSH BUFFER :BLOB1 END-EXEC. EXEC SQL LOB DISABLE BUFFERING :BLOB1 END-EXEC. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/lflbuf.pc */ /* Flushing the LOB Buffer (persistent LOBs) #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 256 void flushBufferingLOB_proc() { OCIBlobLocator *Lob_loc; int Amount = BufferLength; int multiple, Position = 1; /* Datatype equivalencing is mandatory for this datatype: */ char Buffer[BufferLength]; EXEC SQL VAR Buffer is RAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate and Initialize the LOB: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT Sound INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Enable use of the LOB Buffering Subsystem: */ EXEC SQL LOB ENABLE BUFFERING :Lob_loc; memset((void *)Buffer, 0, BufferLength); for (multiple = 0; multiple < 8; multiple++) { /* Write data to the LOB: */ EXEC SQL LOB WRITE ONE :Amount FROM :Buffer INTO :Lob_loc AT :Position; Position += BufferLength; } /* Flush the contents of the buffers and Free their resources: */ EXEC SQL LOB FLUSH BUFFER :Lob_loc FREE; /* Turn off use of the LOB Buffering Subsystem: */ EXEC SQL LOB DISABLE BUFFERING :Lob_loc; /* Release resources held by the Locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; flushBufferingLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
This section describes how to disable LOB buffering.
Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.
Note:
|
For more information, refer to"LOB Buffering Subsystem"
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/ldisbuf.c */ /* Disabling LOB buffering (persistent LOBs) */ #include <oratypes.h> #include <lobdemo.h> void LOBBuffering_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { ub4 amt; ub4 offset; sword retval; ub1 bufp[MAXBUFLEN]; ub4 buflen; printf ("----------- OCI LOB Buffering Demo --------------\n"); /* Open the CLOB: */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Enable LOB Buffering: */ printf (" enable LOB buffering\n"); checkerr (errhp, OCILobEnableBuffering(svchp, errhp, Lob_loc)); printf (" write data to LOB\n"); /* Write data into the LOB: */ amt = sizeof(bufp); buflen = sizeof(bufp); offset = 1; checkerr (errhp, OCILobWrite (svchp, errhp, Lob_loc, &amt, offset, (void *)bufp, buflen, OCI_ONE_PIECE, (void *)0, (sb4 (*)(void*,void*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT)); /* Flush the buffer: */ printf(" flush the LOB buffers\n"); checkerr (errhp, OCILobFlushBuffer(svchp, errhp, Lob_loc, (ub4)OCI_LOB_BUFFER_FREE)); /* Disable Buffering: */ printf (" disable LOB buffering\n"); checkerr (errhp, OCILobDisableBuffering(svchp, errhp, Lob_loc)); /* Subsequent LOB WRITEs will not use the LOB Buffering Subsystem: */ /* Closing the CLOB is mandatory if you have opened it: */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); return; }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/ldisbuf.pco * DISABLING LOB BUFFERING (PERSISTENT LOBS) IDENTIFICATION DIVISION. PROGRAM-ID. LOB-BUFFERING. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BLOB1 SQL-BLOB. 01 BUFFER PIC X(10). 01 AMT PIC S9(9) COMP. EXEC SQL VAR BUFFER IS RAW(10) END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. LOB-BUFFERING. 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 :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT SOUND INTO :BLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 FOR UPDATE END-EXEC. * Open the BLOB and enable buffering: EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC. EXEC SQL LOB ENABLE BUFFERING :BLOB1 END-EXEC. * Write some data to the BLOB: MOVE "242424" TO BUFFER. MOVE 3 TO AMT. EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC. MOVE "212121" TO BUFFER. MOVE 3 TO AMT. EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC. * Now flush the buffered writes: EXEC SQL LOB FLUSH BUFFER :BLOB1 END-EXEC. EXEC SQL LOB DISABLE BUFFERING :BLOB1 END-EXEC. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/ldisbuf.pc */ /* Disabling LOB buffering (persistent LOBs) */ #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 256 void disableBufferingLOB_proc() { OCIBlobLocator *Lob_loc; int Amount = BufferLength; int multiple, Position = 1; /* Datatype equivalencing is mandatory for this datatype: */ char Buffer[BufferLength]; EXEC SQL VAR Buffer is RAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate and Initialize the LOB: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT ad_photo INTO :Lob_loc FROM Print_media WHERE product_id = 3060 AND ad_id = 11001 FOR UPDATE; /* Enable use of the LOB Buffering Subsystem: */ EXEC SQL LOB ENABLE BUFFERING :Lob_loc; memset((void *)Buffer, 0, BufferLength); for (multiple = 0; multiple < 7; multiple++) { /* Write data to the LOB: */ EXEC SQL LOB WRITE ONE :Amount FROM :Buffer INTO :Lob_loc AT :Position; Position += BufferLength; } /* Flush the contents of the buffers and Free their resources: */ EXEC SQL LOB FLUSH BUFFER :Lob_loc FREE; /* Turn off use of the LOB Buffering Subsystem: */ EXEC SQL LOB DISABLE BUFFERING :Lob_loc; /* Write APPEND can only be done when Buffering is Disabled: */ EXEC SQL LOB WRITE APPEND ONE :Amount FROM :Buffer INTO :Lob_loc; /* Release resources held by the Locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; disableBufferingLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
' This file is installed in the following path when you install ' the database: $ORACLE_HOME/rdbms/demo/lobs/vb/ldisbuf.bas 'Disabling LOB buffering (persistent LOBs) Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraAdPhoto1 As OraBlob, OraAdPhotoClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Print_media ORDER BY product_id, ad_id", ORADYN_DEFAULT) Set OraAdPhoto1 = OraDyn.Fields("ad_photo").Value 'Disable buffering: OraAdPhoto1.DisableBuffering
This section describes how to determine whether a LOB instance is temporary.
Use the following syntax references for each programmatic environment:
Examples are provided in the following programmatic environments:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/listemp.sql */ /* Procedure isTempLob_proc is not part of the DBMS_LOB package: */ /* seeing if lob is temporary. */ CREATE or REPLACE PROCEDURE isTempLob_proc(Lob_loc IN OUT BLOB) IS /* Note: Lob_loc can be persistent or temporary LOB */ BEGIN DBMS_OUTPUT.PUT_LINE('------------ LOB ISTEMPORARY EXAMPLE ------------'); /* Check to make sure that the locator is pointing to a temporary LOB */ IF DBMS_LOB.ISTEMPORARY(Lob_loc) = 1 THEN DBMS_OUTPUT.PUT_LINE('Input locator is a temporary LOB locator'); ELSE /* Print an error: */ DBMS_OUTPUT.PUT_LINE('Input locator is not a temporary LOB locator'); END IF; END; / SHOW ERRORS;
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/oci/listemp.c */ /* Checking if a LOB is temporary. 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: */ #include <oratypes.h> #include <lobdemo.h> void isTempLOBAndFree_proc(OCILobLocator *Lob_loc, OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp) { boolean is_temp; is_temp = FALSE; printf ("----------- OCILobIsTemporary and OCILobFreeTemporary Demo \ --------------\n"); checkerr (errhp, OCILobIsTemporary(envhp, errhp, Lob_loc, &is_temp)); if(is_temp) { checkerr(errhp, (OCILobFreeTemporary(svchp, errhp, Lob_loc))); printf("Temporary LOB freed\n"); } else { printf("locator is not a temporary LOB locator\n"); } }
* This file is installed in the following path when you install * the database: $ORACLE_HOME/rdbms/demo/lobs/procob/listemp.pco * Checking if a LOB is temporary. 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.
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/proc/listemp.pc */ /* Checking if a LOB is temporary. #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:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/listempb.java */ /** * Checking if a BLOB is temporary. * 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 determine whether 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. These two methods are defined as follows:
/* This file is installed in the following path when you install */ /* the database: $ORACLE_HOME/rdbms/demo/lobs/java/listempc.java */ /** * Checking if LOB is temporary. * 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);
You can convert a BLOB instance to a CLOB using the PL/SQL procedure DBMS_LOB.CONVERTTOCLOB
. This technique is convenient if you have character data stored in binary format that you want to store in a CLOB. You specify the character set of the binary data when calling this procedure. See PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.
You can convert a CLOB instance to a BLOB instance using the PL/SQL procedure DBMS_LOB.CONVERTTOBLOB
. This technique is a convenient way to convert character data to binary data using LOB APIs. See PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.