Release 8.1.5
A68004-01
 Library |
 Product |
 Contents |
 Index |
| Oracle 8i Application Developer's Guide - Large Objects (LOBs) Release 8.1.5 A68004-01 |
|
In this chapter we describe how to work with internal persistent LOBs in terms of use cases. That is, we discuss each operation on a LOB (such as "See If a LOB is Open") in terms of a use case by that name. The table listing all the use cases is provided at the head of the chapter (see "Use Case Model: Internal Persistent LOBs"). A summary figure, "Use Case Model Diagram: Internal Persistent LOBs", locates all the use cases in single drawing. If you are using the HTML version of this document, you can use this figure to navigate to the use case in which you are interested by clicking on the relevant use case title.
The individual use cases are themselves laid out as follows:
| Use Case and Page |
|---|
|
CREATE a Table Containing an Object Type with a LOB Attribute |
It is possible to incorporate LOBs into tables in three ways.
In all cases SQL DDL is used -- to define LOB columns in a table and LOB attributes in an object type.
You can set an internal LOB -- that is, a LOB column in a table, or a LOB attribute in an object type defined by you-- to be NULL or empty. A LOB set to NULL has no locator. By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column / attribute, you get back a locator which you can use to populate the LOB with data via the OCI or DBMS_LOB routines. This is discussed in more detail below.
Alternatively, LOB columns, but not LOB attributes, may be initialized to a value. Which is to say -- internal LOB attributes differ from internal LOB columns in that LOB attributes may not be initialized to a value other than NULL or empty. As discussed below, an external LOB (i.e. BFILE) can be initialized to NULL or to a filename.
You can initialize the LOBs in Multimedia_tab by using the following SQL INSERT statement:
INSERT INTO Multimedia_tab VALUES (1001, EMPTY_CLOB(), EMPTY_CLOB(), NULL, EMPTY_BLOB(), EMPTY_BLOB(), NULL, NULL, NULL, NULL);
This sets the value of story, flsub, frame and sound to an empty value, and sets photo, and music to NULL.
You may want to set the internal LOB value to NULL upon inserting the row in cases where you do not have the LOB data at the time of the INSERT and/or if you want to issue a SELECT statement at some later time such as:
SELECT COUNT (*) FROM Voiced_tab WHERE Recording IS NOT NULL;
because you want to see all the voice-over segments that have been recorded, or
SELECT COUNT (*) FROM Voiced_tab WHERE Recording IS NULL;
if you wish to establish which segments still have to be recorded.
However, the drawback to this approach is that you must then issue a SQL UPDATE statement to reset the null LOB column -- to EMPTY_BLOB() /EMPTY_CLOB() or to a value (e.g. 'Denzel Washington') for internal LOBs, or to a filename for external LOBs. The point is that you cannot call the OCI or the PL/SQL DBMS_LOB functions on a LOB that is NULL. These functions only work with a locator, and if the LOB column is NULL, there is no locator in the row.
If you do not want to set an internal LOB column to NULL, another option is for you to set the LOB value to empty by using the function EMPTY_BLOB () /EMPTY_CLOB() in the INSERT statement:
INSERT INTO a_table VALUES (EMPTY_BLOB());
Even better is to use the returning clause (thereby eliminating a round trip that is necessary for the subsequent SELECT), and then immediately call OCI or the PL/SQL DBMS_LOB functions to populate the LOB with data.
DECLARE Lob_loc BLOB; BEGIN INSERT INTO a_table VALUES (EMPTY_BLOB()) RETURNING blob_col INTO Lob_loc; /* Now use the locator Lob_loc to populate the BLOB with data */ END;
When defining LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each internal LOB. There are no extra tablespace or storage characteristics for external LOBs since they are not stored in the database. If you later wish to modify the LOB storage parameters, use the MODIFY LOB clause of the ALTER TABLE command. For example:
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB) lob (c) STORE AS (CHUNK 4096 PCTVERSION 5 NOCACHE LOGGING STORAGE (MAXEXTENTS 5) );
Specifying a name for the LOB data segment makes for a much more intuitive working environment. When querying the LOB data dictionary views USER_LOBS, ALL_LOBS, DBA_LOBS (see Oracle8i Reference), you see the LOB data segment that you chose instead of system-generated names.
The LOB storage characteristics that can be specified for a LOB column or a LOB attribute include PCTVERSION, CACHE, NOCACHE, LOGGING, NOLOGGING, CHUNK and ENABLE/DISABLE STORAGE IN ROW. For most users, defaults for these storage characteristics will be sufficient. If you want to fine-tune LOB storage, you should consider the following guidelines.
Best performance for LOBs can be achieved by specifying storage for LOBs in a tablespace that is different from the one used for the table that contains the LOB. If many different LOBs will be accessed frequently, it may also be useful to specify a separate tablespace for each LOB column/attribute in order to reduce device contention.
The LOB index is an internal structure that is strongly associated with the LOB storage. This implies that a user may not drop the LOB index and rebuild it. Note that the LOB index cannot be altered. The system determines which tablespace to use for the LOB data and LOB index depending on the user specification in the LOB storage clause:
LOB data, the table's tablespace is used for the LOB data and index.
LOB data, both the LOB data and index use the tablespace that was specified.
If in creating tables in 8.1 you specify a tablespace for the LOB index for a non-partitioned table, your specification of the tablespace will be ignored and the LOB index will be co-located with the LOB data. Partitioned LOBs do not include the LOB index syntax.
Specifying a separate tablespace for the LOB storage segments will allow for a decrease in contention on the table's tablespace.
When a LOB is modified, a new version of the LOB page is made in order to support consistent read of prior versions of the LOB value.
PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle will try to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning of old LOB data.
Default: 10 (%) Minimum: 0 (%) Maximum: 100 (%)
In order to decide what value PCTVERSION should be set to, you should consider how often LOBs are updated, and how often you read the updated LOBs.
Several LOB updates concurrent with heavy reads of LOBs.
set PCTVERSION = 20%
Setting PCTVERSION to twice the default allows more free pages to be used for old versions of data pages. Since large queries may require consistent reads of LOBs, it may be useful to retain old versions of LOB pages. In this case LOB storage may grow because Oracle will not reuse free pages aggressively.
LOBs are created and written just once and are primarily read-only afterwards. Updates are infrequent.
set PCTVERSION = 5% or lower
The more infrequent and smaller the LOB updates are, the less space needs to be reserved for old copies of LOB data. If existing LOBs are known to be read-only, you could safely set PCTVERSION to 0% since there would never be any pages needed for old versions of data.
Use the CACHE option on LOBs if the same LOB data will be accessed frequently. Use the NOCACHE option (the default) if LOB data will be read only once, or infrequently.
[NO] LOGGING has a similar application with regard to using LOBs as it does for other table operations. In the normal case, if the [NO]LOGGING clause is omitted, this means that neither NO LOGGING nor LOGGING is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.
For LOBs, there is a further alternative depending on how CACHE is stipulated.
NO]LOGGING clause is omitted and CACHE is specified, LOGGING is automatically implemented (because you cannot have CACHE NOLOGGING).
NO]LOGGING clause is omitted and CACHE is not specified, the process defaults in the same way as it does for tables and partitioned tables. That is, the [NO]LOGGING value is obtained from the tablespace in which the LOB value resides.
The following issues should also be kept in mind.
LOBs will always generate undo for LOB index pages. Regardless of whether LOGGING or NOLOGGING is set LOBs will never generate rollback information (undo) for LOB data pages because old LOB data is stored in versions. Rollback information that is created for LOBs tends to be small because it is only for the LOB index page changes.
LOGGING is set Oracle will generate full redo for LOB data pages. NOLOGGING is intended to be used when a customer does not care about media recovery. Thus, if the disk/tape/storage media fails, you will not be able to recover your changes from the log since the changes were never logged.
An example of when NOLOGGING is useful is bulk loads or inserts. For instance, when loading data into the LOB, if you don't care about redo and can just start the load over if it fails, set the LOB's data segment storage characteristics to NOCACHE NOLOGGING. This will give good performance for the initial load of data. Once you have completed loading the data, you can use ALTER TABLE to modify the LOB storage characteristics for the LOB data segment to be what you really want for normal LOB operations -- i.e. CACHE or NOCACHE LOGGING.
Set CHUNK to the number of blocks of LOB data that will be accessed at one time i.e. the number of blocks that will be read/written via OCILobRead(), OCILobWrite(), DBMS_LOB.READ(), or DBMS_LOB.WRITE() during one access of the LOB value. Note that the default value for CHUNK is one Oracle block and does not vary across platforms. For example, if only one block of LOB data is accessed at a time, set CHUNK to the size of one block. For example, if the database block size is 2K, then set CHUNK to 2K.
If you explicitly specify the storage characteristics for the LOB, make sure that INITIAL and NEXT for the LOB data segment storage are set to a size that is larger than the CHUNK size. For example, if the database block size is 2K and you specify a CHUNK of 8K, make sure that the INITIAL and NEXT are bigger than 8K and preferably considerably bigger (for example, at least 16K).
Put another way: If you specify a value for INITIAL, NEXT or the LOB CHUNK size, make sure that:
and
You use the ENABLE | DISABLE STORAGE IN ROW clause to indicate whether the LOB should be stored inline (i.e. in the row) or out of line. You may not alter this specification once you have made it: if you ENABLE STORAGE IN ROW, you cannot alter it to DISABLE STORAGE IN ROW and vice versa. The default is ENABLE STORAGE IN ROW.
The maximum amount of LOB data that will be stored in the row is the maximum VARCHAR size (4000). Note that this includes the control information as well as the LOB value. If the user indicates that the LOB should be stored in the row, once the LOB value and control information is larger than 4000, the LOB value is automatically moved out of the row.
This suggests the following guideline. If the LOB is small (i.e. < 4000 bytes), then storing the LOB data out of line will decrease performance. However, storing the LOB in the row increases the size of the row. This will impact performance if the user is doing a lot of base table processing, such as full table scans, multi-row accesses (range scans) or many UPDATE/SELECT to columns other than the LOB columns. If the user doesn't expect the LOB data to be < 4000, i.e. if all LOBs are big, then the default is the best choice since
(a) the LOB data is automatically moved out of line once it gets bigger than 4000 (which will be the case here since the LOB data is big to begin with), and
(b) performance will be slightly better since we still store some control information in the row even after we move the LOB data out of the row.
For LOBs in index organized tables, inline LOB storage is allowed only if the table is created with an overflow segment (see "LOBs in Index Organized Tables" in Chapter 2, "Advanced Topics").
The heart of our hypothetical application is the table Multimedia_tab. The varied types which make up the columns of this table make it possible to collect together the many different kinds multimedia elements used in the composition of clips.
Since one can use SQL DDL directly to create a table containing one or more LOB columns, it is not necessary to use the DBMS_LOB package.
CREATE TABLE Multimedia_tab ( Clip_ID NUMBER NOT NULL, Story CLOB default EMPTY_CLOB(), FLSub NCLOB default EMPTY_CLOB(), Photo BFILE default NULL, Frame BLOB default EMPTY_BLOB(), Sound BLOB default EMPTY_BLOB(), Voiced_ref REF Voiced_typ, InSeg_ntab InSeg_tab, Music BFILE default NULL, Map_obj Map_typ ) NESTED TABLE InSeg_ntab STORE AS InSeg_nestedtab;
EMPTY_BLOB () and EMPTY_CLOB() means that the LOB is initialized, but not populated with data. LOBs that are empty are not null, and vice versa. This topic is discussed in more detail in "INSERT a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()".
For more information see:
LOBs in the DDL commands CREATE TABLE and ALTER TABLE with:
- BLOB, CLOB, NCLOB and BFILE columns
- EMPTY_BLOB and EMPTY_CLOB functions
- LOB storage clause for internal LOB columns, and LOB attributes of embedded objects
As shown in the diagram, you must create the object type that contains the LOB attributes before you can proceed to create a table that makes use of that object type.
Our example application contains examples of two different ways in which object types can contain LOBs:
Multimedia_tab contains a column Voiced_ref that references row objects in the table VoiceOver_tab which is based on the type Voiced_typ. This type contains two kinds of LOBs -- a CLOB to store the script that's read by the actor, and a BFILE to hold the audio recording.
Multimedia_tab contains a column Map_obj that contains column objects of the type Map_typ. This type utilizes the BLOB datatype for storing maps in the form of drawings.
/* Create type Voiced_typ as a basis for tables that can contain recordings of voice-over readings using SQL DDL: */ CREATE TYPE Voiced_typ AS OBJECT ( Originator VARCHAR2(30), Script CLOB, Actor VARCHAR2(30), Take NUMBER, Recording BFILE ); /* Create table Voiceover_tab Using SQL DDL: */ CREATE TABLE Voiceover_tab of Voiced_typ ( Script DEFAULT EMPTY_CLOB(), CONSTRAINT Take CHECK (Take IS NOT NULL), Recording DEFAULT NULL );
/* Create Type Map_typ using SQL DDL as a basis for the table that will contain the column object: */ CREATE TYPE Map_typ AS OBJECT ( Region VARCHAR2(30), NW NUMBER, NE NUMBER, SW NUMBER, SE NUMBER, Drawing BLOB, Aerial BFILE ); /* Create support table MapLib_tab as an archive of maps using SQL DDL: */ CREATE TABLE MapLib_tab of Map_typ;
|
For more information see:
|
As shown in the diagram, you must create the object type that contains the LOB attributes before you can proceed to create a nested table based on that object type.
In our example, Multimedia_tab contains a nested table Inseg_ntab that is based on the type InSeg_typ. This type makes use of two LOB datatypes -- a BFILE for audio recordings of the interviews, and a CLOB should the user wish to make transcripts of the recordings.
We have already described how to create a table with LOB columns (see "CREATE a Table Containing One or More LOB Columns"), so here we only describe the SQL DDL syntax the creating the underlying type:
/* Create a type InSeg_typ as the base type for the nested table containing a LOB: */ CREATE TYPE InSeg_typ AS OBJECT ( Segment NUMBER, Interview_Date DATE, Interviewer VARCHAR2(30), Interviewee VARCHAR2(30), Recording BFILE, Transcript CLOB ); /* Type created, but need a nested table of that type to embed in multi_media_tab; so: */ CREATE TYPE InSeg_tab AS TABLE of InSeg_typ;
The actual embedding of the nested table is accomplished when the structure of the containing table is defined. In our example, this is effected by means of the following statement at the time that Multimedia_tab is created.
NESTED TABLE InSeg_ntab STORE AS InSeg_nestedtab;
There are three different ways of inserting LOB values into a row:
Before you can start writing data to an internal LOB, the LOB column must be made non-null; that is, it must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value by using the function EMPTY_BLOB() as a default predicate. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB(). You can perform this initialization during CREATE TABLE (see "CREATE a Table Containing One or More LOB Columns") or, as in this case, by means of an INSERT.
These functions are available as special functions in Oracle8 SQL DML, and are not part of the DBMS_LOB package.
/* In the new row of table Multimedia_tab, the columns STORY and FLSUB are initialized using EMPTY_CLOB(), the columns FRAME and SOUND are initialized using EMPTY_BLOB(), the column TRANSSCRIPT in the nested table is initialized using EMPTY_CLOB(), the column DRAWING in the column object is initialized using EMPTY_BLOB(): */ INSERT INTO Multimedia_tab VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), NULL, EMPTY_BLOB(), EMPTY_BLOB(), NULL, InSeg_tab(InSeg_typ(1, NULL, 'Ted Koppell', 'Jimmy Carter', NULL, EMPTY_CLOB())), NULL, Map_typ('Moon Mountain', 23, 34, 45, 56, EMPTY_BLOB(), NULL)); /* In the new row of table Voiceover_tab, the column SCRIPT is initialized using EMPTY_CLOB(): */ INSERT INTO Voiceover_tab VALUES ('Abraham Lincoln', EMPTY_CLOB(), 'James Earl Jones', 1, NULL);
With regard to LOBs, one of the advantages of utilizing an object-relational approach is that you can define a type as a common template for related tables. For instance, it makes sense that both the tables that store archival material and the working tables that use those libraries share a common structure. The following code fragment is based on the fact that a library table VoiceoverLib_tab is of the same type (Voiced_typ) as Voiceover_tab referenced by the Voiced_ref column of the Multimedia_tab table. It inserts values into the library table, and then inserts this same data into Multimedia_tab by means of a SELECT operation.
Note that the internal LOB types -- BLOB, CLOB, and NCLOB -- use copy semantics, as opposed to the reference semantics that apply to BFILEs. When a BLOB, CLOB, or NCLOB is copied from one row to another row in the same table or in a different table, the actual LOB value is copied, not just the LOB locator. For example, assuming Voiceover_tab and VoiceoverLib_tab have identical schemas, the statement creates a new LOB locator in the table Voiceover_tab, and copies the LOB data from VoiceoverLib_tab to the location pointed to by a new LOB locator which is inserted in table Voiceover_tab.
/* Store records in the archive table VoiceoverLib_tab: */ INSERT INTO VoiceoverLib_tab VALUES ('George Washington', EMPTY_CLOB(), 'Robert Redford', 1, NULL); /* Insert values into Voiceover_tab by selecting from VoiceoverLib_tab: */ INSERT INTO Voiceover_tab (SELECT * from VoiceoverLib_tab WHERE Take = 1);
In this example we use a LOB locator bind variable to take Sound data that is in one row of Multimedia_tab and insert it into another row.
/* Note that the example procedure insertUseBindVariable_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE insertUseBindVariable_proc (Rownum IN NUMBER, Blob_loc IN BLOB) IS BEGIN INSERT INTO Multimedia_tab (Clip_ID, Sound) VALUES (Rownum, Blob_loc); END; DECLARE Blob_loc BLOB; BEGIN /* Select the LOB from the row where Clip_ID = 1, Initialize the LOB locator bind variable: */ SELECT Sound INTO Blob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* Insert into the row where Clip_ID = 2: */ insertUseBindVariable_proc (2, Blob_loc); COMMIT; END;
/* Select the locator into a locator variable */ sb4 select_MultimediaLocator (Lob_loc, errhp, stmthp, svchp) OCILobLocator *Lob_loc; OCIError *errhp; OCIStmt *stmthp; OCISvcCtx *svchp; { OCIDefine *defnp1; text *sqlstmt = (text *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=1"; /* Prepare the SQL statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Define the column being selected */ checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2)SQLT_BLOB,(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT)); /* Execute and fetch one row */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return (0); } /* Insert the selected Locator into table using Bind Variables. This function selects a locator from the Multimedia_tab and inserts it into the same table in another row. */ void insertUseBindVariable (envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { int clipid; OCILobLocator *Lob_loc; OCIBind *bndhp2; OCIBind *bndhp1; text *insstmt = (text *) "INSERT INTO Multimedia_tab (Clip_ID, Sound) VALUES (:1, :2)"; /* Allocate locator resources */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select a LOB locator from the Multimedia Table */ select_MultimediaLocator(Lob_loc, errhp, stmthp, svchp); /* Insert the locator into the Multimedia_tab with Clip_ID=2 */ clipid = 2; /* Prepare the SQL statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) strlen((char *) insstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Binds the bind positions */ checkerr (errhp, OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1, (dvoid *) &clipid, (sb4) sizeof(clipid), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIBindByPos(stmthp, &bndhp2, errhp, (ub4) 2, (dvoid *) &Lob_loc, (sb4) 0, SQLT_BLOB, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); /* Execute the SQL statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); /* Free LOB resources*/ OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); }
IDENTIFICATION DIVISION. PROGRAM-ID. INSERT-LOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BLOB1 SQL-BLOB. 01 USERID PIC X(11) VALUES "USER1/USER1". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. INSERT-LOB. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Initialize the BLOB locator EXEC SQL ALLOCATE :BLOB1 END-EXEC. * Populate the LOB EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT SOUND INTO :BLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC. * Insert the value with CLIP_ID of 2. EXEC SQL INSERT INTO MULTIMEDIA_TAB (CLIP_ID, SOUND) VALUES (2, :BLOB1) END-EXEC. * Free resources held by locator END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL COMMIT WORK 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.
#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 insertUseBindVariable_proc(Rownum, Lob_loc) int Rownum; OCIBlobLocator *Lob_loc; { EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL INSERT INTO Multimedia_tab (Clip_ID, Sound) VALUES (:Rownum, :Lob_loc); } void insertBLOB_proc() { OCIBlobLocator *Lob_loc; /* Initialize the BLOB Locator: */ EXEC SQL ALLOCATE :Lob_loc; /* Select the LOB from the row where Clip_ID = 1: */ EXEC SQL SELECT Sound INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* Insert into the row where Clip_ID = 2: */ insertUseBindVariable_proc(2, Lob_loc); /* Release resources held by the locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; insertBLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBLOB Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value 'Clone it for future reference Set OraSoundClone = OraSound1 'Go to Next row OraDyn.MoveNext 'Lets update the current row and set the LOB to OraSoundClone OraDyn.Edit Set OraSound1 = OraSoundClone OraDyn.Update
// 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_31 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver Class.forName ("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 = stmt.executeQuery ( "SELECT sound FROM multimedia_tab WHERE clip_id = 1"); if (rset.next()) { // retrieve the LOB locator from the ResultSet BLOB sound_blob = ((OracleResultSet)rset).getBLOB (1); OraclePreparedStatement ops = (OraclePreparedStatement) conn.prepareStatement( "INSERT INTO multimedia_tab (clip_id, sound) VALUES (2, ?)"); ops.setBlob(1, sound_blob); ops.execute(); conn.commit(); conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
Since LOBs can be quite large in size, it makes sense that SQL*Loader can load LOB data from either the main datafile (that is, in-line with the rest of the data) or from one or more secondary datafiles.
To load LOB data from the main datafile, the usual SQL*Loader formats can be used. The LOB data instances can be in predetermined size fields, delimited fields, or length-value pair fields.
LOBs; unfortunately, the LOBs to be loaded will not usually be of the same size (note: a possible work-around to this problem is to pad the LOB data with white space to make all of the LOBs the same length within the particular datafield; for information on the trimming of trailing white spaces see "Trimming of Blanks and Tabs" in the Oracle8i Utilities). To load LOBs using this format, use either CHAR or RAW as the loading datatype. For example:
LOAD DATA INFILE 'sample.dat' "fix 21" INTO TABLE Multimedia_tab (Clip_ID POSITION(1:3) INTEGER EXTERNAL, Story POSITION(5:20) CHAR DEFAULTIF Story=BLANKS)
007 Once upon a time
Clip_ID,(O07) from the beginning of the story. The story is 20 characters long.
LOB instead of a null LOB is produced. A null LOB is produced if the NULLIF directive was used instead of the DEFAULTIF directive. Also note that you can use loader datatypes other than CHAR to load LOBS. When loading BLOBs you would probably want to use the RAW datatype.
In this format, having different size LOBs within the same column (that is, datafile field) is not a problem. The trade-off for this added flexibility is performance. Loading in this format is somewhat slower because the loader has to scan through the data, looking for the delimiter string. For example:
LOAD DATA INFILE 'sample1.dat' "str X'7c0a'" INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' ( Clip_ID CHAR(3), Story CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>' )
007, <startlob> Once upon a time,The end. <endlob>| 008, <startlob> Once upon another time ....The end. <endlob>|
<startlob> and <endlob> are the delimiting strings. Note that the maximum length for a LOB that can be read using the CHAR (507) is 507 bytes.
<endlob> and followed with the newline character, the newline would have been interpreted as part of the next record. One way around this problem would be to make the newline part of the record separator (for example, "|\n" or in hexadecimal notation: X"7c0a").
You could use VARCHAR (see Oracle8i Utilities), VARCHARC, or VARRAW datatypes to load LOB data organized in this way. Note that this method of loading produces better performance over the previous method, however, it removes some of the flexibility (that is, it requires you to know the LOB length for each LOB before loading).
LOAD DATA INFILE 'sample2.dat' "str X'3c656e647265633e0a'" INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL (3), Story VARCHARC (3, 500) )
007,041 Once upon a time... .... The end. <endrec> 008,000<endrec>
Story is a field corresponding to a CLOB column. In the control file, it is described as a VARCHARC whose length field is 3 characters long and maximum size is 500 bytes.
VARCHARC is 0 (that is, the value subfield is empty); consequently, the LOB instance is initialized to empty.
As mentioned earlier, LOB data can be so large that it is very reasonable to want to load it from secondary datafile(s). While you can use secondary data files as the source of LOB data, it is better to use LOBFILEs instead.
In the LOBFILE, LOB data instances are still thought to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILES); thus, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.
Each LOBFILE clause is the source of just one LOB. To load LOB data organized in this manner into the control file, follow the column/field name with the LOBFILE specification and the datatype specification. The following example illustrates loading LOBS, with one LOB per file.
LOAD DATA INFILE 'sample3.dat' INTO TABLE Multimedia_tab REPLACE FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), ext_FileName FILLER CHAR(40), Story LOBFILE(ext_FileName) TERMINATED BY EOF )
007,FirstStory.txt, 008,/tmp/SecondStory.txt,
Once upon a time ... The end.
Once upon another time .... The end.
FILLER field is mapped to the 40-byte long datafield which is read using the SQL*Loader CHAR datatype.
LOBFILE file name from the ext-FileName FILLER field. The data from a specified LOBFILE file (that is, from the first byte to the EOF character) is loaded to make a LOB instance. Note that if you specify a LOBFILE file that doesn't exist, the Story field is initialized to empty. Also note that since no SQL*Loader datatype is specified, the CHAR datatype is used.
In the control file, the size of the LOBs to be loaded into a particular column is specified. During the load, any LOB data loaded into that particular column is assumed to be of the specified size. The predetermined size of the fields allows the dataparser to perform very well. Unfortunately, it is often hard to guarantee that all of the LOBs are of the same size.
LOAD DATA INFILE 'sample4.dat' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT 'FirstStory1.txt') CHAR(32) )
007, 008,
Once upon the time ... The end, Upon another time ... The end,
FirstStory. txt LOBFILE, using CHAR datatype, starting with the byte following the byte loaded last during the current loading session.
The LOB data instances in the LOBFILE files are delimited. In this format, loading different size LOBs into the same column is not a problem. The trade-off for this added flexibility is performance. Loading in this format is somewhat slower because the loader has to scan through the data, looking for the delimiter string. For example:
LOAD DATA INFILE 'sample5.dat' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' (Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT 'FirstStory2.txt') CHAR(2000) TERMINATED BY "<endlob>")
007, 008,
Once upon a time... The end.<endlob> Once upon another time... The end.<endlob>
Specifying maximum length (that is, 2000) gives a hint to the loader as to the maximum length of the field. This often results in optimized memory usage. (Note that if you use this hint, you should not estimate the value too low). The TERMINATED BY clause specifies the string that terminates the LOBs. You can also use the ENCLOSED BY clause. Note that the ENCLOSED BY clause allows a bit more flexibility as to the relative positioning of the LOBs in the LOBFILE (that is, the LOBs in the LOBFILE wouldn't have to follow one after another).
Each LOB in the LOBFILE is preceded by its length. You can use VARCHAR (see Oracle8 Utilities), VARCHARC, or VARRAW datatypes to load LOB data organized in this way. The controllable syntax for loading length-value pair specified LOBs is quite simple.
Note that this method of loading enjoys better performance over the previous one, but at the same time it takes some of the flexibility away (that is, it requires that you know the length of each LOB before loading).
LOAD DATA INFILE 'sample6.dat' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT 'FirstStory3.txt') VARCHARC(4,2000) )
007, 008,
0031 Once upon a time ... The end. 0000
The VARCHARC(4, 2000) tells the loader that the LOBs in the LOBFILE are in length-value pair format and that the first four bytes should be interpreted as the length. The max_length part (that is, 2000) gives the hint to the loader as to the maximum size of the field.
LOB (not a NULL LOB).
Note the following LOB loading details:
LOB doesn't result in the rejection of the record containing that LOB; instead, the record ends up containing an empty LOB.
LOB-type column. Nevertheless, if the maximum length is specified, it is taken as a hint to help optimize memory usage. It is very important that the maximum length specification doesn't underestimate the true maximum length.
In using the OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another. However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile operation to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing loadfromfile.
The example procedure assumes that there is an operating system source file (Washington_audio) that contains the LOB data to be loaded into the target LOB (Music). The example procedure also assumes that the directory object AUDIO_DIR already exists and is mapped to the location of the source file.
/* Note that the example procedure loadLOBFromBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc IS Dest_loc BLOB; Src_loc BFILE := BFILENAME('FRAME_DIR', 'Washington_frame'); Amount INTEGER := 4000; BEGIN SELECT Frame INTO Dest_loc FROM Multimedia_tab WHERE Clip_ID = 3 FOR UPDATE; /* Opening the LOB 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); COMMIT; END;
/* This example illustrates how to select a BLOB from a Multimedia table and load it with data from a BFILE */ sb4 select_lock_frame_locator_3(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=3 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void LoadLobDataFromBFile(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *bfile; OCILobLocator *blob; ub4 amount= 4000; /* Allocate the Source (bfile) & destination (blob) locators desriptors*/ OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&bfile, (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid **)0); OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&blob, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0); /* Select a frame locator for update */ printf (" select the frame locator...\n"); select_lock_frame_locator_2(blob, errhp, svchp, stmthp); /* Set the Directory Alias and File Name of the frame file */ printf (" set the file name in bfile\n"); checkerr (errhp, OCILobFileSetName(envhp, errhp, &bfile, (text*)"FRAME_DIR", (ub2)strlen("FRAME_DIR"), (text*)"Washington_frame", (ub2)strlen("Washington_frame"))); printf (" open the bfile\n"); /* Opening the BFILE locator is Mandatory */ checkerr (errhp, (OCILobOpen(svchp, errhp, bfile, OCI_LOB_READONLY))); printf(" open the lob\n"); /* Opening the BLOB locator is optional */ checkerr (errhp, (OCILobOpen(svchp, errhp, blob, OCI_LOB_READWRITE))); /* Load the data from the audio file (bfile) into the blob */ printf (" load the LOB from File\n"); checkerr (errhp, OCILobLoadFromFile(svchp, errhp, blob, bfile, (ub4)amount, (ub4)1, (ub4)1)); /* Closing the LOBs is Mandatory if they have been Opened */ checkerr (errhp, OCILobClose(svchp, errhp, bfile)); checkerr (errhp, OCILobClose(svchp, errhp, blob)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) bfile, (ub4) OCI_DTYPE_FILE); (void) OCIDescriptorFree((dvoid *) blob, (ub4) OCI_DTYPE_LOB); return; }
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 "USER1/USER1".
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 "AUDIO_DIR" TO DIR-ALIAS-ARR.
MOVE 9 TO DIR-ALIAS-LEN.
MOVE "washington_audio" 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 SOUND INTO :DEST
FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 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
COMMIT 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.
#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 loadLOBFromBFILE_proc() { OCIBlobLocator *Dest_loc; OCIBFileLocator *Src_loc; char *Dir = "FRAME_DIR", *Name = "Washington_frame"; int Amount = 4000; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Initialize the BFILE Locator */ EXEC SQL ALLOCATE :Src_loc; EXEC SQL LOB FILE SET :Src_loc DIRECTORY = :Dir, FILENAME = :Name; /* Initialize the BLOB Locator */ EXEC SQL ALLOCATE :Dest_loc; EXEC SQL SELECT frame INTO :Dest_loc FROM Multimedia_tab WHERE Clip_ID = 3 FOR UPDATE; /* Opening the BFILE is Mandatory */ EXEC SQL LOB OPEN :Src_loc READ ONLY; /* Opening the BLOB is Optional */ EXEC SQL LOB OPEN :Dest_loc READ WRITE; EXEC SQL LOB LOAD :Amount FROM FILE :Src_loc INTO :Dest_loc; /* Closing LOBs and BFILEs 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; loadLOBFromBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraMyBfile as OraBFile OraConnection.BeginTrans Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value OraParameters.Add "id", 1,ORAPARAM_INPUT OraParameters.Add "mybfile", Empty,ORAPARAM_OUTPUT OraParameters("mybfile").ServerType = ORATYPE_BFILE OraDatabase.ExecuteSQL ("begin GetBFile(:id, :mybfile ") end") Set OraMyBFile = OraParameters("mybfile").Value 'Go to Next row OraDyn.MoveNext OraDyn.Edit 'Lets update OraSound1 data with that from the BFILE OraSound1.CopyFromBFile OraMyBFile OraDyn.Update OraConnection.CommitTrans
// 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: Class.forName ("oracle.jdbc.driver.OracleDriver"); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); 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('AUDIO_DIR', 'Washington_audio') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); src_lob.openFile(); in = src_lob.getBinaryStream(); } rset = stmt.executeQuery ( "SELECT sound FROM multimedia_tab WHERE clip_id = 99 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(); } } }
The following example opens a Video frame (Frame), and then evaluates to see if the LOB is open.
/* Note that the example procedure lobIsOpen_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE lobIsOpen_proc IS Lob_loc BLOB; Retval INTEGER; BEGIN SELECT Frame INTO Lob_loc FROM Multimedia_tab where Clip_ID = 1; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc , DBMS_LOB.LOB_READONLY); /* 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. */ END;
/* Select the locator into a locator variable */ sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return (0); } void seeIfLOBIsOpen(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; int isOpen; /* Allocate locator resources */ (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0); /* Select the locator */ (void)select_frame_locator(Lob_loc, errhp, svchp, stmthp); /* 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 */ } /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
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 "USER1/USER1". 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 FRAME INTO :BLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 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 COMMIT 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.
#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 Frame INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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 = "samp/samp"; EXEC SQL CONNECT :samp; seeIfLOBIsOpen(); EXEC SQL ROLLBACK WORK RELEASE; }
// 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.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: Class.forName ("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 blob = null; ResultSet rset = stmt.executeQuery ( "SELECT frame FROM multimedia_tab WHERE clip_id = 1"); 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(); } } }
Assume that the following archival source table SoundsLib_tab was defined and contains data:
CREATE TABLE SoundsLib_tab ( Id NUMBER, Description VARCHAR2(30), SoundEffects LONG RAW );
The example assumes that you want to copy the data from the LONG RAW column (SoundEffects) into the BLOB column (Sound) of the multimedia table, and uses the SQL function TO_LOB to accomplish this.
INSERT INTO Multimedia_tab (clip_id,sound) SELECT id, TO_LOB(SoundEffects) FROM SoundsLib_tab WHERE id =1;
This functionality is based on using an operator on LONGs called TO_LOB that converts the LONG to a LOB. The TO_LOB operator copies the data in all the rows of the LONG column to the corresponding LOB column, and then lets you apply the LOB functionality to what was previously LONG data. Note that the type of data that is stored in the LONG column must match the type of data stored in the LOB. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data.
Once you have completed this one-time only operation and are satisfied that the data has been copied correctly, you could then drop the LONG column. However, this will not reclaim all the storage originally required to store LONGs in the table. In order to avoid unnecessary, excessive storage, you are better advised to copy the LONG data to a LOB in a new or different table. Once you have made sure that the data has been accurately copied, you should then drop the original table.
One simple way to effect this transposing of LONGs to LOBs is to use the CREATE TABLE... SELECT statement, using the TO_LOB operator on the LONG column as part of the SELECT statement. You can also use INSERT... SELECT.
In the examples in the following procedure, the LONG column named LONG_COL in table LONG_TAB is copied to a LOB column named LOB_COL in table LOB_TAB. These tables include an ID column that contains identification numbers for each row in the table.
Complete the following steps to copy data from a LONG column to a LOB column:
LONG column, but use a LOB datatype in place of the LONG datatype.
For example, if you have a table with the following definition:
CREATE TABLE Long_tab ( id NUMBER, long_col LONG);
Create a new table using the following SQL statement:
CREATE TABLE Lob_tab ( id NUMBER, blob_col BLOB);
INSERT command using the TO_LOB operator to insert the data from the table with the LONG datatype into the table with the LOB datatype.
For example, issue the following SQL statement:
INSERT INTO Lob_tabSELECT id, TO_LOB(long_col) FROM long_tab;
LONG column.
For example, issue the following SQL command to drop the LONG_TAB table:
DROP TABLE Long_tab;
LONG data. The synonym ensures that your database and applications continue to function properly.
For example, issue the following SQL statement:
CREATE SYNONYM Long_tab FOR Lob_tab;
Once the copy is complete, any applications that use the table must be modified to use the LOB data.
You can use the TO_LOB operator to copy the data from the LONG to the LOB in statements that employ CREATE TABLE...AS SELECT or INSERT...SELECT. In the latter case, you must have already ALTERed the table and ADDed the LOB column prior to the UPDATE. If the UPDATE returns an error (because of lack of undo space), you can incrementally migrate LONG data to the LOB using the WHERE clause. The WHERE clause cannot contain functions on the LOB but can test the LOB's nullness.
Note that use of TO_LOB is subject to the following limitations:
TO_LOB to copy data to a LOB column, but not to a LOB attribute.
TO_LOB with any remote table. Consequently, all the following statements will fail:
INSERT INTO tb1@dblink (lob_col) SELECT TO_LOB(long_col) FROM tb2; INSERT INTO tb1 (lob_col) SELECT TO_LOB(long_col) FROM tb2@dblink; CREATE table tb1 AS SELECT TO_LOB(long_col) FROM tb2@dblink;
BEFORE INSERT or INSTEAD OF INSERT -- the :NEW.lob_col variable can't be referenced in the trigger body.
TO_LOB inside any PL/SQL block.
The most efficient way to write large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled via polling or a callback. You should use the OCI or PRO*C interface with streaming for the underlying read operation; using DBMS_LOB.READ will result in non-optimal performance.
In the typical use of the checkout-checkin operation, the user wants to checkout a version of the LOB from the database to the client, modify the data on the client without accessing the database, and then in one fell swoop, checkin all the modifications that were made to the document on the client side.
Here we portray the checkout portion of the scenario: the code lets the user read the CLOB Transcript from the nested table InSeg_ntab which contains interview segments for the purpose of processing it in some text editor on the client. The checkin portion of the scenario is described in "Checkin a LOB".
/* Note that the example procedure checkOutLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE checkOutLOB_proc IS Lob_loc CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 2147483647; BEGIN /* Select the LOB: */ SELECT Intab.Transcript INTO Lob_loc FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 1) Intab WHERE Intab.Segment = 1; * Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); LOOP DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); /* Process the buffer: */ Position := Position + Amount; END LOOP; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data'); END;
/* This example will READ 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: */ #define MAXBUFLEN 32767 /* Select the locator into a locator variable: */ sb4 select_transcript_locator(Lob_loc, errhp, stmthp, svchp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *) "SELECT Intab.Transcript \ FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab \ WHERE Mtab.Clip_ID = 1) Intab \ WHERE Intab.Segment = 1"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_CLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row: */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void checkoutLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; ub4 amt; ub4 offset; sword retval; boolean done; ub1 bufp[MAXBUFLEN]; ub4 buflen; /* Allocate locators desriptors: */ (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB,(size_t) 0, (dvoid **) 0); /* Select the BLOB: */ printf(" select the transcript locator...\n"); select_transcript_locator(Lob_loc, errhp, stmthp, svchp); /* Open the CLOB: */ printf (" open lob in checkOutLOB_proc\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 (" read lob in pieces\n"); offset = 1; memset(bufp, '\0', MAXBUFLEN); done = FALSE; while (!done) { retval = OCILobRead(svchp, errhp, Lob_loc, &amt, offset, (dvoid *)bufp, buflen, (dvoid *)0,(sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 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. This is in bytes for BLOBs and in characters for fixed width CLOBS and in bytes for variable width CLOBs */ done = TRUE; break; case OCI_ERROR: checkerr (errhp, OCI_ERROR); 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. This is in bytes for BLOBs and in characters for fixed width CLOBS and in bytes for variable width CLOBs. */ break; default: checkerr (errhp, retval); done = TRUE; break; } /* while */ } /* Closing the CLOB is mandatory if you have opened it: */ printf (" close lob in checkOutLOB_proc\n"); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators: */ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
IDENTIFICATION DIVISION. PROGRAM-ID. CHECKOUT. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 01 CLOB1 SQL-CLOB. 01 BUFFER PIC X(5) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 D-BUFFER-LEN PIC 9. 01 D-AMT PIC 9. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. READ-CLOB. 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 STORY INTO :CLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 2 END-EXEC. * Initiate polling read: MOVE 0 TO AMT. * Read first piece of the CLOB into the buffer: EXEC SQL LOB READ :AMT FROM :CLOB1 AT :OFFSET INTO :BUFFER END-EXEC. DISPLAY "Reading a CLOB ...". DISPLAY " ". MOVE BUFFER-LEN TO D-BUFFER-LEN. DISPLAY "first read (", D-BUFFER-LEN, "): " BUFFER-ARR(1:BUFFER-LEN). * Read subsequent pieces of the CLOB: READ-LOOP. MOVE " " TO BUFFER-ARR. EXEC SQL LOB READ :AMT FROM :CLOB1 INTO :BUFFER END-EXEC. MOVE BUFFER-LEN TO D-BUFFER-LEN. DISPLAY "next read (", D-BUFFER-LEN, "): " BUFFER-ARR(1:BUFFER-LEN). GO TO READ-LOOP. * Read the last piece of the CLOB: END-OF-CLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :CLOB1 END-EXEC. MOVE BUFFER-LEN TO D-BUFFER-LEN. DISPLAY "last read (", D-BUFFER-LEN, "): " BUFFER-ARR(1:BUFFER-LEN). EXEC SQL COMMIT WORK RELEASE END-EXEC. 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 example will READ 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 <oci.h> #include <stdio.h> #include <sqlca.h> void Sample_Error() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); } #define BufferLength 256 void checkOutLOB_proc() { OCIClobLocator *Lob_loc; int Amount; int Clip_ID, Segment; VARCHAR Buffer[BufferLength]; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; /* Use Dynamic SQL to retrieve the LOB: */ EXEC SQL PREPARE S FROM 'SELECT Intab.Transcript \ FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab \ WHERE Mtab.Clip_ID = :cid) Intab \ WHERE Intab.Segment = :seg'; EXEC SQL DECLARE C CURSOR FOR S; Clip_ID = Segment = 1; EXEC SQL OPEN C USING :Clip_ID, :Segment; EXEC SQL FETCH C INTO :Lob_loc; EXEC SQL CLOSE C; /* Open the LOB: */ 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.len = BufferLength; EXEC SQL WHENEVER NOT FOUND DO break; while (TRUE) { /* Read a piece of the LOB into the Buffer: */ EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer; printf("Checkout %d characters\n", Buffer.len); } printf("Checkout %d characters\n", Amount); /* 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; checkOutLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
'Note that this code fragment assumes an orablob object as the result of a 'dynaset operation. This object could have been an OUT parameter of a PL/SQL 'procedure. For more information please refer to chapter 1. There are two ways 'of reading a lob using orablob.read or orablob.copytofile 'Using OraBlob.Read mechanism Dim OraDyn as OraDynaset, OraSound as OraBlob, amount_read%, chunksize%, chunk chunksize = 32767 set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) set OraSound = OraDyn.Fields("Sound") OraSound.PollingAmount = OraSound.Size'Read entire BLOB contentsDo amount_read = OraSound.Read(chunk,chunksize)'chunk returned is a variant of typebyte array If amount_read = 0 Then Exit Do End If OraMusic.offset = OraSound.offset + amount_read + 1 Loop Until amount_read = 0 'Using OraBlob.CopyToFile mechanism Dim OraDyn as OraDynaset, OraSound as OraBlob, amount_read%, chunksize%, chunk Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraSound = OraDyn.Fields("Sound").ValueOraSound.pollingAmount = OraSound.Size'Read entire BLOB contents OraSound.CopyToFile "c:\mysound.aud"
// 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_59 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 src_lob = null; InputStream in = null; byte buf[] = new byte[MAXBUFSIZE]; ResultSet rset = stmt.executeQuery ( "SELECT intab.transcript FROM TABLE( SELECT mtab.inseg_ntab FROM multimedia_tab mtab WHERE mtab.clip_id = 1) intab WHERE intab.segment = 1"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getCLOB (1); in = src_lob.getAsciiStream(); } int length = 0; int pos = 0; while ((in != null) && ((length = in.read(buf)) != -1)) { pos += length; System.out.println(Integer.toString(pos)); // Process the buffer: } in.close(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
The most efficient way to write large amounts of LOB data is to use OCILobWrite() with the streaming mechanism enabled via polling or a callback
The checkin operation demonstrated here follows from "Checkout a LOB". In this case, the procedure writes the data back into the CLOB Transcript column within the nested table InSeg_ntab that contains interview segments. As noted above, you should the OCI or PRO*C interface with streaming for the underlying write operation; using DBMS_LOB.WRITE will result in non-optimal performance.
/* Note that the example procedure checkInLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE checkInLOB_proc IS Lob_loc CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 2147483647; i INTEGER; BEGIN /* Select the LOB: */ SELECT Intab.Transcript INTO Lob_loc FROM TABLE(SELECT m.InSeg_ntab FROM Multimedia_tab Mtab WHERE Clip_ID = 2) Intab WHERE Intab.Segment = 1 FOR UPDATE; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE) FOR i IN 1..3 LOOP /* Fill the Buffer with data to be written. */ /* Write data: */ DBMS_LOB.WRITE (Lob_loc, Amount, Position, 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_OUTPUT.PUT_LINE('Operation failed'); END;
/* This example demonstrates how OCI provides for the ability 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 statically allocated Buffer is used to hold the data being written to the LOB. */ #define MAXBUFLEN 32767 /* Select the locator into a locator variable */ sb4 select_lock_transcript_locator(Lob_loc, errhp, stmthp,svchp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *) "SELECT Intab.Transcript \ FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab \ WHERE Mtab.Clip_ID = 2) Intab \ WHERE Intab.Segment = 1 FOR UPDATE"; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_CLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return OCI_SUCCESS; } void checkinLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIClobLocator *Lob_loc; ub4 Total = 2.5*MAXBUFLEN; ub4 amtp; ub4 offset; ub4 remainder; ub4 nbytes; boolean last; ub1 bufp[MAXBUFLEN]; sb4 err; /* Allocate locators desriptors*/ (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB,(size_t) 0,(dvoid **) 0); /* Select the CLOB */ printf(" select the transcript locator...\n"); select_lock_transcript_locator(Lob_loc, errhp, stmthp, svchp); /* Open the CLOB */ printf (" open the locator.\n"); checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); printf (" write the lob in pieces\n"); if (Total > MAXBUFLEN) nbytes = MAXBUFLEN; /* We will use streaming via standard polling */ else nbytes = Total; /* Only a single write is required */ /* Fill the buffer with nbytes worth of data */ remainder = Total - nbytes; /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE */ amtp = 0; /* offset = <Starting position where to begin writing the data>; */ offset = 1; if (0 == remainder) { amtp = nbytes; /* Here, (Total <= MAXBUFLEN ) so we can write in one piece */ checkerr (errhp, OCILobWrite (svchp, errhp, Lob_loc, amtp, offset, bufp, nbytes, OCI_ONE_PIECE, (dvoid *) 0, (sb4 (*)(dvoid *,dvoid *,ub4 *,ub1 *)) 0, 0, SQLCS_IMPLICIT)); } else { /* Here (Total > MAXBUFLEN ) so we use streaming via standard polling */ /* write the first piece. Specifying first initiates polling. */ err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, offset, bufp, nbytes, OCI_FIRST_PIECE, (dvoid *) 0, (sb4 (*)(dvoid *,dvoid *,ub4 *,ub1 *)) 0, 0, SQLCS_IMPLICIT); if (err != OCI_NEED_DATA) checkerr (errhp, err); last = FALSE; /* write the next (interim) and last pieces */ do { if (remainder > MAXBUFLEN) nbytes = MAXBUFLEN; /* Still have more pieces to go */ else { nbytes = remainder; /* Here, (remainder <= MAXBUFLEN) */ last = TRUE; /* This is going to be the Final piece */ } /* Fill the buffer with nbytes worth of data */ if (last) { /* Specifying LAST terminates polling */ err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, offset, bufp, nbytes, OCI_LAST_PIECE, (dvoid *) 0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT); if (err != OCI_SUCCESS) checkerr(errhp, err); } else { err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, offset, bufp, nbytes, OCI_NEXT_PIECE, (dvoid *) 0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT); if (err != OCI_NEED_DATA) checkerr (errhp, err); } /* Determine how much is left to write */ remainder = remainder - nbytes; } while (!last); } /* At this point, (remainder == 0) */ /* Closing the BLOB is mandatory if you have opend it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); }
IDENTIFICATION DIVISION. PROGRAM-ID. CHECKIN. ENVIRONMENT DIVISION. INPUT-OUTPUT SECTION. FILE-CONTROL. SELECT INFILE ASSIGN TO "datfile.dat" ORGANIZATION IS SEQUENTIAL. DATA DIVISION. FILE SECTION. FD INFILE RECORD CONTAINS 80 CHARACTERS. 01 INREC PIC X(80). WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 01 CLOB1 SQL-CLOB. 01 BUFFER PIC X(80) VARYING. 01 AMT PIC S9(9) COMP VALUE 0. 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. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. WRITE-CLOB. 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 SELECT STORY INTO :CLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 FOR UPDATE END-EXEC. * Open the input file for reading: OPEN INPUT INFILE. * 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 80 TO BUFFER-LEN. IF (END-OF-FILE = "Y") MOVE 80 TO AMT EXEC SQL LOB WRITE ONE :AMT FROM :BUFFER INTO :CLOB1 AT :OFFSET END-EXEC ELSE DISPLAY "LOB WRITE FIRST" DISPLAY BUFFER-ARR MOVE 321 TO AMT EXEC SQL LOB WRITE FIRST :AMT FROM :BUFFER INTO :CLOB1 END-EXEC END-IF. * Continue reading from the input data file * and writing to the CLOB: PERFORM READ-WRITE UNTIL END-OF-FILE = "Y". PERFORM SIGN-OFF. STOP RUN. READ-WRITE. PERFORM READ-NEXT-RECORD. MOVE INREC TO BUFFER-ARR. DISPLAY "READ-WRITE". DISPLAY INREC. MOVE 80 TO BUFFER-LEN. IF END-OF-FILE = "Y" DISPLAY "LOB WRITE LAST: ", BUFFER-ARR MOVE 1 TO BUFFER-LEN EXEC SQL LOB WRITE LAST :AMT FROM :BUFFER INTO :CLOB1 END-EXEC ELSE DISPLAY "LOB WRITE NEXT: ", BUFFER-ARR MOVE 0 TO AMT EXEC SQL LOB WRITE NEXT :AMT FROM :BUFFER INTO :CLOB1 END-EXEC END-IF. READ-NEXT-RECORD. MOVE SPACES TO INREC. READ INFILE NEXT RECORD AT END MOVE "Y" TO END-OF-FILE. SIGN-OFF. CLOSE INFILE. EXEC SQL FREE :CLOB1 END-EXEC. EXEC SQL COMMIT 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 example demonstrates how Pro*C/C++ provides for the ability to WRITE arbitrary amounts of data to an Internal LOB in either a single piece or in multiple pieces using a Streaming Mechanism that utilizes standard polling. A static Buffer is used to hold the data being written: */ #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 512 void checkInLOB_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 Story INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Open the LOB: */ EXEC SQL LOB OPEN :Lob_loc READ WRITE; Total = Amount = (multiple * BufferLength); if (Total > BufferLength) nbytes = BufferLength; /* We will use streaming via standard polling */ else nbytes = Total; /* Only a single WRITE is required */ /* Fill the Buffer with nbytes worth of data: */ memset((void *)Buffer.arr, 32, nbytes); Buffer.len = nbytes; /* Set the Length */ remainder = Total - nbytes; if (0 == remainder) { /* Here, (Total <= BufferLength) so we can WRITE in ONE piece: */ EXEC SQL LOB WRITE ONE :Amount FROM :Buffer INTO :Lob_loc; printf("Write ONE Total of %d characters\n", Amount); } else { /* Here (Total > BufferLength) so use streaming via standard polling: WRITE the FIRST piece. Specifying FIRST initiates polling: */ EXEC SQL LOB WRITE FIRST :Amount FROM :Buffer INTO :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; 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 LOB: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; checkInLOB_proc(1); EXEC SQL ROLLBACK WORK; checkInLOB_proc(4); EXEC SQL ROLLBACK WORK RELEASE; }
'Note that this code fragment assumes an orablob object as the result of a 'dynaset operation. This object could have been an OUT parameter of a PL/SQL 'procedure. For more information please refer to chapter 1. there are two ways 'of writing a lob using orablob.write or orablob.copyfromfile 'Using OraBlob.Write mechanism Dim MySession As OraSession Dim OraDb As OraDatabase Dim fnum As Integer Dim OraDyn As OraDynaset, OraSound As OraBlob, amount_written%, chunksize%, curchunk() As Byte Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) chunksize = 500 ReDim curchunk(chunksize) Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab", ORADYN_DEFAULT) Set OraSound = OraDyn.Fields("Sound").Value fnum = FreeFile Open "c:\tmp\washington_audio" For Binary As #fnum OraSound.offset = 1 OraSound.pollingAmount = LOF(fnum) remainder = LOF(fnum) Dim piece As Byte Get #fnum, , curchunk OraDyn.Edit piece = ORALOB_FIRST_PIECE amount_written = OraSound.Write(curchunk, chunksize, ORALOB_FIRST_PIECE) While OraSound.Status = ORALOB_NEED_DATA remainder = remainder - chunksize If amount_written <= chunksize Then chunksize = remainder piece = ORALOB_LAST_PIECE Else piece = ORALOB_NEXT_PIECE End If Get #fnum, , curchunk amount_written = OraSound.Write(curchunk, chunksize, piece) Wend OraDyn.Update 'Using OraBlob.CopyFromFile mechanism Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab order by clip_ id", ORADYN_DEFAULT) Set OraSound = OraDyn.Fields("Sound").Value OraDyn.Edit OraSound.CopyFromFile "c:\tmp\washington_audio" OraDyn.Update
// 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_66 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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; String buf = new String ("Some Text To Write"); ResultSet rset = stmt.executeQuery ( "SELECT story FROM multimedia_tab WHERE clip_id = 2 FOR UPDATE"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getCLOB (1); } long pos = 0; // Offset within the CLOB where the data is to be written long length = 0; // This is the size of the buffer to be written // This loop writes the buffer three times consecutively: for (int i = 0; i < 3; i++) { pos = lob_loc.length(); // an alternative is: lob_loc.putString(pos, buf); lob_loc.putChars(pos, buf.toCharArray()); // Some debug information: System.out.println(" putChars(" + Long.toString(pos) + ", buf.toCharArray());"); } stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled.
As an example of displaying a LOB, our scenario stream-reads the image Drawing from the column object Map_obj onto the client-side in order to view the data.
/* Note that the example procedure displayLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE displayLOB_proc IS Lob_loc BLOB; Buffer RAW(1024); Amount BINARY_INTEGER := 1024; Position INTEGER := 1; BEGIN /* Select the LOB: */ SELECT m.Map_obj.Drawing INTO Lob_loc FROM Multimedia_tab m WHERE m.Clip_ID = 1; /* 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(utl_raw.cast_to_varchar2(Buffer)); Position := Position + Amount; END LOOP; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data'); END;
/* This example will READ 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. */ #define MAXBUFLEN 32767 /* Select the locator into a locator variable */ sb4 select_mapobjectdrawing_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *) "SELECT m.Map_obj.Drawing \ FROM Multimedia_tab m WHERE m.Clip_ID = 1"; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void displayLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIBlobLocator *Lob_loc; ub4 amt; ub4 offset; sword retval; boolean done; ub1 bufp[MAXBUFLEN]; ub4 buflen; OCILobLocator *Lob_Loc; /* Allocate the Source (bfile) & destination (blob) locators desriptors*/ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the BLOB */ printf(" select the mapobjectdrawing locator...\n"); select_mapobjectdrawing_locator(Lob_loc, errhp, svchp, stmthp); /* Open the BLOB */ 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(bufp, '\0', MAXBUFLEN); done = FALSE; while (!done) { retval = OCILobRead(svchp, errhp, Lob_loc, &amt, offset, (dvoid *) bufp, buflen, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 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. This is in bytes for BLOBs and in characters for fixed width CLOBS and in bytes for variable width CLOBs */ 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. This is in bytes for BLOBs and in characters for fixed width CLOBS and in bytes for variable width CLOBs */ break; default: checkerr (errhp, retval); done = TRUE; break; } } /* while */ /* Closing the BLOB is mandatory if you have opend it */ printf(" close the lob \n"); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); }
IDENTIFICATION DIVISION. PROGRAM-ID. DISPLAY-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 M.SOUND INTO :BLOB1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 END-EXEC. DISPLAY "Found column SOUND". * 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 COMMIT 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 example will READ 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.Map_obj.Drawing INTO Lob_loc FROM Multimedia_tab m WHERE m.Clip_ID = 1; /* 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; }
'Using 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, OraStory as OraClob, amount_read%, chunksize%, chunk chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab", ORADYN_DEFAULT) Set OraStory = OraDyn.Fields("Story").Value OraStory.PollingAmount = OraStory.Size'Read entire CLOB contentsDo'chunk returned is a variant of type byte array:amount_read = OraStory.Read(chunk, chunksize) If amount_read = 0 Then Exit Do End If 'Display the data here OraStory.offset = OraStory.offset + amount_read + 1 Loop Until amount_read = 0
// 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_72 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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; InputStream in = null; byte buf[] = new byte[MAXBUFSIZE]; int pos = 0; int length = 0; ResultSet rset = stmt.executeQuery ( "SELECT m.map_obj.drawing FROM multimedia_tab m WHERE m.clip_id = 1"); 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(); } } }
The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled via polling or a callback.
When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4 gigabytes regardless of the starting offset and the amount of data in the LOB. You do need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read.
For example, assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here's the OCI read call, excluding the initialization of all parameters:
#define MAX_LOB_SIZE 4294967295 ub4 amount = MAX_LOB_SIZE; ub4 offset = 1000; OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0)
When using polling mode, be sure to look at the value of the 'amount' parameter after each OCILobRead() call to see how many bytes were read into the buffer since the buffer may not be entirely full.
When using callbacks, the 'len' parameter, 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 since the entire buffer may not be filled with data (see theOracle 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 the you execute read requests using a multiple of this chunk size. The reason for this is that you're using the same unit that the Oracle database uses when reading data from disk. If it is appropriate for your application, 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.
Our example reads the data from a single video Frame.
/* Note that the example procedure readLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE readLOB_proc IS Lob_loc BLOB; Buffer RAW(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 1000; Chunksize INTEGER; BEGIN /* Select the LOB: */ SELECT Frame INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* Find out the chunksize for this LOB column: */ Chunksize := DBMS_LOB.GETCHUNKSIZE(Lob_loc); IF (Chunksize < 32767) THEN Amount := (32767 / Chunksize) * Chunksize; END IF; /* 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); END;
/* This example will READ 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. */ #define MAXBUFLEN 32767 /* Select the locator into a locator variable */ sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *) "SELECT Frame \ FROM Multimedia_tab m WHERE m.Clip_ID = 1"; printf(" prepare statement in select_frame_locator\n"); checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); printf(" OCIDefineByPos in select_frame_locator\n"); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ printf(" OCIStmtExecute in select_frame_locator\n"); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void readLOB_proc(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { ub4 amt; ub4 offset; sword retval; ub1 bufp[MAXBUFLEN]; ub4 buflen; boolean done; OCILobLocator *Lob_loc; OCILobLocator *blob; /* Allocate the Source (bfile) & destination (blob) locators desriptors*/ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the BLOB */ printf(" call select_frame4read_locator\n"); select_frame_locator(Lob_loc); /* Open the BLOB */ printf(" call OCILobOpen\n"); checkerr (errhp, (OCILobOpen(svchp, errhp, blob, OCI_LOB_READONLY))); /* Setting the amt to the buffer length. Note here that amt is in bytes since we are using a BLOB */ amt = sizeof(bufp); buflen = sizeof(bufp); /* Process the data in pieces */ printf(" process the data in piece\n"); offset = 1; memset(bufp, '\0', MAXBUFLEN); retval = OCILobRead(svchp, errhp, Lob_loc, &amt, offset, (dvoid *) bufp, buflen, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 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. This is in bytes for BLOBs and in characters for fixed width CLOBS and in bytes for variable width CLOBs */ break; case OCI_ERROR: /* report_error(); this function is not shown here */ break; default: (void) printf("Unexpected ERROR: OCILobRead() LOB.\n"); done = TRUE; break; } /* Closing the BLOB is mandatory if you have opend it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); }
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 "USER1/USER1". 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 FRAME INTO :BLOB1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 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 COMMIT 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.
#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 Frame INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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 = "samp/samp"; EXEC SQL CONNECT :samp; readLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
'Using 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, OraStory as OraClob, amount_read%, chunksize%, chunk chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab", ORADYN_DEFAULT) Set OraStory = OraDyn.Fields("Story").Value OraStory.ChunkSize = chunksize OraStory.pollingAmount = OraStory.Size 'Read entire CLOB contents Do amount_read = OraStory.Read(chunk) 'chunk returned is a variant of type byte array If amount_read = 0 Then Exit Do End If 'Display the data here OraStory.offset = OraStory.offset + amount_read + 1 Loop Until amount_read = 0
// 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: Class.forName ("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; byte buf[] = new byte[MAXBUFSIZE]; ResultSet rset = stmt.executeQuery ( "SELECT frame FROM multimedia_tab WHERE clip_id = 1"); 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 example demonstrates reading a portion from sound-effect Sound.
/* Note that the example procedure substringLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE substringLOB_proc IS Lob_loc BLOB; Amount BINARY_INTEGER := 32767; Position INTEGER := 1024; Buffer RAW(32767); BEGIN /* Select the LOB: */ SELECT Sound INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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); END; /* In the following SQL statement, 255 is the amount to read and 1 is the starting offset from which to read: */ SELECT DBMS_LOB.SUBSTR(Sound, 255, 1) FROM Multimedia_tab WHERE Clip_ID = 1;
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 "USER1/USER1". 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 FRAME INTO :BLOB1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 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 COMMIT 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.
/* 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 Sound INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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); }
'Note that reading a portion of a LOB (or BFILE) in OO4O is accomplished by
'setting the OraBlob.Offset and OraBlob.chunksize properties.
'Using OraClob.Read mechanism
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn as OraDynaset, OraStory 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 Multimedia_tab", ORADYN_DEFAULT)
Set OraStory = OraDyn.Fields("Story").Value
'Let's read 100 bytes from the 500th byte onwards:
OraStory.Offset = 500
OraStory.PollingAmount = OraStory.Size 'Read entire CLOB contents
amount_read = OraStory.Read(chunk, 100)
'chunk returned is a variant of type byte array
// 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: Class.forName ("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; byte buf[] = new byte[MAXBUFSIZE]; ResultSet rset = stmt.executeQuery ( "SELECT frame FROM multimedia_tab WHERE clip_id = 1"); 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(); } } }
The following example compares two frames from the archival table VideoframesLib_tab to see whether they are different and, depending on the result of the comparison, inserts the Frame into the Multimedia_tab.
/* Note that the example procedure compareTwoLOBs_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE compareTwoLOBs_proc IS Lob_loc1 BLOB; Lob_loc2 BLOB; Amount INTEGER := 32767; Retval INTEGER; BEGIN /* Select the LOB: */ SELECT Frame INTO Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 1; SELECT Frame INTO Lob_loc2 FROM Multimedia_tab WHERE Clip_ID = 2; /* 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 frames: */ retval := DBMS_LOB.COMPARE(Lob_loc1, Lob_loc2, Amount, 1, 1); IF retval = 0 THEN DBMS_OUTPUT.PUT_LINE('Processing for equal frames'); ELSE DBMS_OUTPUT.PUT_LINE('Processing for non-equal frames'); END IF; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc1); DBMS_LOB.CLOSE (Lob_loc2); END;
IDENTIFICATION DIVISION. PROGRAM-ID. COMPARE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 FRAME INTO :BLOB1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 END-EXEC. EXEC SQL SELECT FRAME INTO :BLOB2 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 2 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 COMMIT 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.
#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 Frame INTO :Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 1; EXEC SQL SELECT Frame INTO :Lob_loc2 FROM Multimedia_tab WHERE Clip_ID = 2; /* 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; }
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, OraSound1 as OraBLOB, OraSoundClone as OraBLOB Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value 'Clone it for future reference Set OraSoundClone = OraSound1 'Lets go to the next row and compare LOBs OraDyn.MoveNext MsgBox CBool(OraSound1.Compare(OraSoundClone, OraSoundClone.size, 1, 1))
// 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_87 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 frame FROM multimedia_tab WHERE clip_id = 1"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBLOB (1); } rset = stmt.executeQuery ( "SELECT frame FROM multimedia_tab WHERE clip_id = 99"); 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, 0))); else System.out.println("Looking for LOB1 inside LOB2. result = " + Long.toString(lob_loc2.position(lob_loc1, 0))); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
The example examines the storyboard text to see if the string "children" is present.
/* Note that the example procedure instringLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE instringLOB_proc IS Lob_loc CLOB; Pattern VARCHAR2(30) := 'children'; Position INTEGER := 0; Offset INTEGER := 1; Occurrence INTEGER := 1; BEGIN /* Select the LOB: */ SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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;
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 "USER1/USER1". 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 STORY INTO :CLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 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 COMMIT 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.
#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 Story INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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 = "samp/samp"; EXEC SQL CONNECT :samp; instringLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
// 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_91 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 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 story FROM multimedia_tab WHERE clip_id = 2"); 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 example demonstrates how to determine the length of a LOB in terms of the foreign language subtitle (FLSub).
/* Note that the example procedure getLengthLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE getLengthLOB_proc IS Lob_loc NCLOB; Length INTEGER; BEGIN /* Select the LOB: */ SELECT FLSub INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 2; /* 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;
/* Select the locator into a locator variable */ sb4 select_FLSub_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *)"SELECT FLSub FROM Multimedia_tab WHERE Clip_ID = 2"; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Define the column being selected */ checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2)SQLT_CLOB,(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT)); /* Execute and fetch one row */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } /* This function gets the length of the selected LOB */ void getLengthLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { ub4 length; OCILobLocator *Lob_loc; /* Allocate Locator resources */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select a LOB locator from FLSub */ printf(" select a FLSub locator\n"); select_FLSub_locator(Lob_loc, errhp, svchp, stmthp); /* 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 FLSub.\n"); checkerr (errhp, OCILobGetLength(svchp, errhp, Lob_loc, &length)); /* Length is undefined if the LOB is NULL or undefined */ fprintf(stderr," Length of LOB is %d\n",length); /* Closing the LOBs is Mandatory if they have been Opened */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
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 "USER1/USER1". 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 STORY INTO :CLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 2 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 COMMIT 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.
#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 Story INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value 'Display out size of the lob: MsgBox "Length of the lob is " & OraSound1.Size
// 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_95 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 story FROM multimedia_tab WHERE clip_id = 2"); 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(); } } }
Prior to updating a LOB value via 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 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 "Updated locators" in Chapter 2, "Advanced Topics".
The code in this example shows you to copy a portion of Sound from one clip to another.
/* Note that the example procedure copyLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE copyLOB_proc IS Dest_loc BLOB; Src_loc BLOB; Amount NUMBER; Dest_pos NUMBER; Src_pos NUMBER; BEGIN SELECT Sound INTO Dest_loc FROM Multimedia_tab WHERE Clip_ID = 2 FOR UPDATE; /* Select the LOB: */ SELECT Sound INTO Src_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); END;
/* Select the locator */ sb4 select_lock_sound_locator_2(Lob_loc, dest_type, errhp, svchp, stmthp) OCILobLocator *Lob_loc; ub1 dest_type; /* whether destination locator */ OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { char sqlstmt[150]; OCIDefine *defnp1; if (dest_type == TRUE) { strcpy (sqlstmt, (char *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=2 FOR UPDATE"); printf (" select destination sound locator\n"); } else { strcpy(sqlstmt, (char *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=1"); printf (" select source sound locator\n"); } checkerr (errhp, OCIStmtPrepare(stmthp, errhp, (text *)sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } /* This function copies part of the Source LOB into a specified position in the destination LOB */ void copyAllPartLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIBlobLocator *Dest_loc, *Src_loc; int Amount = 1000; /* <Amount to Copy> */ int Dest_pos = 100; /*<Position to start copying into> */ int Src_pos = 1; /* <Position to start copying from> */ /* Allocate the LOB locators */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Dest_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Src_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the LOBs */ printf(" select the destination and source locators\n"); select_lock_sound_locator_2(Dest_loc, TRUE, errhp, svchp, stmthp); /* destination locator */ select_lock_sound_locator_2(Src_loc, FALSE, errhp, svchp, stmthp); /* source locator */ /* Opening the LOBs is Optional */ printf (" open the destination locator (optional)\n"); checkerr (errhp, OCILobOpen(svchp, errhp, Dest_loc, OCI_LOB_READWRITE)); printf (" open the source locator (optional)\n"); checkerr (errhp, OCILobOpen(svchp, errhp, Src_loc, OCI_LOB_READONLY)); printf (" copy the lob (amount) from the source to destination\n"); checkerr (errhp, OCILobCopy(svchp, errhp, Dest_loc, Src_loc, 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, Dest_loc)); checkerr (errhp, OCILobClose(svchp, errhp, Src_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Dest_loc, (ub4) OCI_DTYPE_LOB); (void) OCIDescriptorFree((dvoid *) Src_loc, (ub4) OCI_DTYPE_LOB); return; }
IDENTIFICATION DIVISION. PROGRAM-ID. BLOB-COPY. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 SOUND INTO :SRC FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 2 END-EXEC. DISPLAY "Source LOB populated.". EXEC SQL SELECT SOUND INTO :DEST FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 3 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 COMMIT 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.
#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 Sound INTO :Dest_loc FROM Multimedia_tab WHERE Clip_ID = 2 FOR UPDATE; EXEC SQL SELECT Sound INTO :Src_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value Set OraSoundClone = OraSound1 'Go to next row and copy LOB OraDyn.MoveNext OraDyn.Edit OraSound1.Copy OraSoundClone, OraSoundClone.Size, 1, 1 OraDyn.Update
// 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_100 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 sound FROM multimedia_tab WHERE clip_id = 1"); if (rset.next()) { src_loc = ((OracleResultSet)rset).getBLOB (1); } in = src_loc.getBinaryStream(); rset = stmt.executeQuery ( "SELECT sound FROM multimedia_tab WHERE clip_id = 2 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 example shows the copying of one locator to another involving the video frame (Frame). Note how different locators may point to the same/different, current/outdated data.
|
Note: Assigning one LOB to another using PL/SQL entails using the ":=" sign. This is an advanced topic that is discussed in more detail under the heading "Read-Consistent Locators". |
/* Note that the example procedure lobAssign_proc is not part of the DBMS_LOB package. */ CREATE OR REPLACE PROCEDURE lobAssign_proc IS Lob_loc1 blob; Lob_loc2 blob; BEGIN SELECT Frame INTO Lob_loc1 FROM Multimedia_tab where Clip_ID = 1 FOR UPDATE; /* 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;
/* Select the locator */ sb4 select_lock_frame_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return (0); } void assignLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *dest_loc, *src_loc; boolean isEqual; /* Allocate the LOB locators */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &dest_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &src_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the LOBs */ printf (" select and lock a frame locator\n"); select_lock_frame_locator(src_loc, errhp, svchp, stmthp);/* source locator */ /* Assign src_loc to dest_loc 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, OCILobAssign(envhp, errhp, src_loc, &dest_loc)); /* 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, src_loc, dest_loc, &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 */ /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) dest_loc, (ub4) OCI_DTYPE_LOB); (void) OCIDescriptorFree((dvoid *) src_loc, (ub4) OCI_DTYPE_LOB); return; }
IDENTIFICATION DIVISION. PROGRAM-ID. COPY-LOCATOR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 FRAME INTO :SRC FROM MULTIMEDIA_TAB WHERE CLIP_ID = 2 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 COMMIT 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.
#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 Frame INTO :Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 1 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; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id ", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value Set OraSoundClone = OraSound1 OraDyn.MoveNext 'Copy 1000 bytes of LOB values OraSoundClone to OraSoundl at OraSoundl 'offset 100: OraDyn.Edit OraSound1.Copy OraSoundClone, 1000, 100 OraDyn.Update
// 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_104 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 frame FROM multimedia_tab WHERE clip_id = 1"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBLOB (1); } // When you write some data to the LOB through lob_loc1, lob_loc2 will not see the changes lob_loc2 = lob_loc1; stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
If two locators are equal, this means that they refer to the same version of the LOB data (see "Read-Consistent Locators"). In this example, the locators are equal. However, it may be as important to determine that locators do not refer to same version of the LOB data.
This functionality is available in only a limited number of environments.
/* Select the locator: */ sb4 select_lock_frame_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row: */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return (0); } void assignLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *dest_loc, *src_loc; boolean isEqual; /* Allocate the LOB locators: */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &dest_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &src_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the LOBs: */ printf (" select and lock a frame locator\n"); select_lock_frame_locator(src_loc, errhp, svchp, stmthp);/* source locator */ /* Assign src_loc to dest_loc 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, OCILobAssign(envhp, errhp, src_loc, &dest_loc)); /* 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, src_loc, dest_loc, &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 */ /* Free resources held by the locators: */ (void) OCIDescriptorFree((dvoid *) dest_loc, (ub4) OCI_DTYPE_LOB); (void) OCIDescriptorFree((dvoid *) src_loc, (ub4) OCI_DTYPE_LOB); return; }
/* Pro*C/C++ does not provide a mechanism to test the equality of two locators. However, by using the 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 Frame INTO Lob_loc1 FROM Multimedia_tab where Clip_ID = 1 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; }
// 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_108 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 sound FROM multimedia_tab WHERE clip_id = 2"); if (rset.next()) { lob_loc1 = ((OracleResultSet)rset).getBLOB (1); } // When you write some data to the 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(); } } }
The operation allows you to determine if the locator has been initialized or not. In the example shown both locators are found to be initialized.
This functionality is currently available in only two environments.
/* Select the locator: */ sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row: */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return (0); } void isInitializedLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc1, *Lob_loc2; boolean isInitialized; /* Allocate the LOB locators: */ printf(" allocate locator 1 and 2\n"); (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc1, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc2, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the LOBs: */ printf (" select a frame locator into locator 1\n"); select_frame_locator(Lob_loc1, errhp, svchp, stmthp); /* locator 1 */ /* 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 TRUE here */ printf(" for Locator 2, isInitialized = %d\n", isInitialized); /* Free resources held by the locators: */ (void) OCIDescriptorFree((dvoid *) Lob_loc1, (ub4) OCI_DTYPE_LOB); (void) OCIDescriptorFree((dvoid *) Lob_loc2, (ub4) OCI_DTYPE_LOB); return; }
/* 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 via the EXEC SQL ALLOCATE statement. However, 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 Frame INTO Lob_loc FROM Multimedia_tab where Clip_ID = 1; /* 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; }
The use case demonstrates how to determine the characterset ID of the foreign language subtitle (FLSub). This functionality is available only in OCI.
/* This function takes a validLOBlocator and prints the character set id of theLOB. */ /* Select the locator */ sb4 select_FLSub_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *)"SELECT FLSub FROM Multimedia_tab WHERE Clip_ID = 2"; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Define the column being selected */ checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2)SQLT_CLOB,(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT)); /* Execute and fetch one row */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } sb4 getcsidLob (envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; ub2 charsetid =0 ; (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); printf (" select a FLSub locator\n"); select_FLSub_locator(Lob_loc, errhp, svchp, stmthp); printf (" get the character set id of FLSub_locator\n"); /* Get the charactersid ID of the LOB*/ checkerr (errhp, OCILobCharSetId(envhp, errhp, Lob_loc, &charsetid)); printf(" character Set ID of FLSub is : %d\n", charsetid); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
The use case demonstrates how to determine the character set form of the foreign language subtitle (FLSub). This functionality is available only in OCI.
/* Select the locator */ sb4 select_FLSub_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *)"SELECT FLSub FROM Multimedia_tab WHERE Clip_ID = 2"; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Define the column being selected */ checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2)SQLT_CLOB,(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT)); /* Execute and fetch one row */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } /* This function takes a valid LOB locator and prints the character set form of the LOB. */ sb4 getcsformLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; ub1 charset_form = 0 ; (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); printf (" select a FLSub locator\n"); select_FLSub_locator(Lob_loc, errhp, svchp, stmthp); printf (" get the character set form of FLSub\n"); /* Get the charactersid ID of the LOB*/ checkerr (errhp, OCILobCharSetForm(envhp, errhp, Lob_loc, &charset_form)); printf(" character Set Form of FLSub is : %d\n", charset_form); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
Prior to updating a LOB value via 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 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 "Updated locators" in Chapter 2, "Advanced Topics".
This example deals with the task of appending one segment of Sound to another. We assume that you use sound-specific editing tools to match the wave-forms.
/* Note that the example procedure appendLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE appendLOB_proc IS Dest_loc BLOB; Src_loc BLOB; BEGIN /* Select the LOB, get the destination LOB locator: */ SELECT Sound INTO Dest_loc FROM Multimedia_tab WHERE Clip_ID = 2 FOR UPDATE; /* Select the LOB, get the destination LOB locator: */ SELECT Sound INTO Src_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); END;
/* This function appends the Source LOB to the end of the Destination LOB*/ /* Select the locator */ sb4 select_lock_sound_locator_2(Lob_loc, dest_type, errhp, svchp, stmthp) OCILobLocator *Lob_loc; ub1 dest_type; /* whether destination locator */ OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { char sqlstmt[150]; OCIDefine *defnp1; if (dest_type == TRUE) { strcpy (sqlstmt, (char *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=2 FOR UPDATE"); printf (" select destination sound locator\n"); } else { strcpy(sqlstmt, (char *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=1"); printf (" select source sound locator\n"); } checkerr (errhp, OCIStmtPrepare(stmthp, errhp, (text *)sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void appendLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Dest_loc, *Src_loc; /* Allocate the LOB locators */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Dest_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Src_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the LOBs */ printf(" select source and destination Lobs\n"); select_lock_sound_locator_2(Dest_loc, TRUE, errhp, svchp, stmthp); /* destination locator */ select_lock_sound_locator_2(Src_loc, FALSE, errhp, svchp, stmthp); /* source locator */ /* Opening the LOBs is Optional */ checkerr (errhp, OCILobOpen(svchp, errhp, Dest_loc, OCI_LOB_READWRITE)); checkerr (errhp, OCILobOpen(svchp, errhp, Src_loc, 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, Dest_loc, Src_loc)); /* Closing the LOBs is Mandatory if they have been Opened */ checkerr (errhp, OCILobClose(svchp, errhp, Dest_loc)); checkerr (errhp, OCILobClose(svchp, errhp, Src_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Dest_loc, (ub4) OCI_DTYPE_LOB); (void) OCIDescriptorFree((dvoid *) Src_loc, (ub4) OCI_DTYPE_LOB); return; }
IDENTIFICATION DIVISION. PROGRAM-ID. LOB-APPEND. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 SOUND INTO :DEST FROM MULTIMEDIA_TAB WHERE CLIP_ID = 2 FOR UPDATE END-EXEC. EXEC SQL SELECT SOUND INTO :SRC FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 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 COMMIT 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.
#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 Multimedia_tab WHERE Clip_ID = 2 FOR UPDATE; /* Select the source locator: */ EXEC SQL SELECT Sound INTO :Src_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* 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; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value Set OraSoundClone = OraSound1 OraDyn.MoveNext OraDyn.Edit OraSound1.Append OraSoundClone OraDyn.Update
// 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_121 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 sound FROM multimedia_tab WHERE clip_id = 2"); if (rset.next()) { src_loc = ((OracleResultSet)rset).getBLOB (1); } in = src_loc.getBinaryStream(); rset = stmt.executeQuery ( "SELECT sound FROM multimedia_tab WHERE clip_id = 1 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(); // populate the buffer: buf = (new String("Hello World")).getBytes(); while ((length = in.read(buf)) != -1) { // Write the contents of the buffer into position pos of the output LOB: dest_loc.putBytes(pos, buf); } // Close all streams and handles: in.close(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
The writeappend operation writes a buffer to the end of a LOB. For the 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, data must be provided through callbacks or polling. If a callback function is defined in the cbfp parameter, then this callback function will be invoked to get the next piece after a piece is written to the pipe. Each piece will be written from bufp. If no callback function is defined, then OCILobWriteAppend() returns the OCI_NEED_DATA error code. The application must call OCILobWriteAppend() 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 via 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 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 "Updated locators" in Chapter 2, "Advanced Topics".
This example demonstrates writing to the end of a video frame (Frame).
/* Note that the example procedure lobWriteAppend_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE lobWriteAppend_proc IS Lob_loc BLOB; Buffer RAW(32767); Amount Binary_integer := 32767; BEGIN SELECT Frame INTO Lob_loc FROM Multimedia_tab where Clip_ID = 1 FOR UPDATE; /* Fill the buffer with data... */ /* 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;
/* Select the locator into a locator variable: */ sb4 select_lock_frame_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row: */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return (0); } #define MAXBUFLEN 32767 void writeAppendLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIBlobLocator *Lob_loc; ub4 amt; ub4 offset; sword retval; ub1 bufp[MAXBUFLEN]; ub4 buflen; OCILobLocator *Lob_Loc; /* Allocate the Source (bfile) & destination (blob) locators desriptors: */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the BLOB: */ printf(" select and lock a frame locator\n"); select_lock_frame_locator(Lob_loc, errhp, svchp, stmthp); /* Open the BLOB: */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Setting the amt to the buffer length. Note here that amt is in bytes since we are using a BLOB: */ 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, OCILobWriteAppend (svchp, errhp, Lob_loc, &amt, bufp, buflen, OCI_ONE_PIECE, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *))0, 0, SQLCS_IMPLICIT)); /* Closing the BLOB is mandatory if you have opened it: */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators: */ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
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 "USER1/USER1". 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 FRAME INTO :BLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 FOR UPDATE END-EXEC. * Open the target LOB: EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC. 1 * 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 COMMIT 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.
#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 Frame INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 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; }
// 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_126 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 frame FROM multimedia_tab WHERE clip_id = 1 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(); } } }
The most efficient way to write large amounts of LOB data is to use OCILobWrite() with the streaming mechanism enabled via polling or a callback. If you know how much data will be written to the LOB, specify that amount when calling OCILobWrite(). This will allow for the contiguity of the LOB data on disk. Apart from being spatially efficient, 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 the you execute 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, no extra or excess versioning is incurred or duplicated. If it is appropriate for your application, 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 via 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 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 "Updated locators" in Chapter 2, "Advanced Topics".
The following example procedure allows the STORY data (the storyboard for the clip) to be updated by writing data to the LOB.
/* Note that the example procedure writeDataToLOB_proc is not part of the CREATE or REPLACE PROCEDURE writeDataToLOB_proc IS Lob_loc CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 1; i INTEGER; BEGIN /* Select a LOB: */ SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); /* Fill the buffer with data to write to the LOB: */ FOR i IN 1..3 LOOP DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer); /* Fill the buffer with more data to write to the LOB: */ Position := Position + Amount; END LOOP; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); END; /* We add a second example to show a case in which the buffer size and amount differs from the first example: */ CREATE or REPLACE PROCEDURE writeDataToLOB_proc IS Lob_loc CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER; i INTEGER; Chunk_size INTEGER; BEGIN SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); Chunk_size := DBMS_LOB.GETCHUNKSIZE(Lob_loc); /* Fill the buffer with 'Chunk_size' worth of data to write to the LOB. Use the chunk size (or a multiple of chunk size) when writing data to the LOB. Make sure that you write within a chunk boundary and don't overlap different chunks within a single call to DBMS_LOB.WRITE. */ Amount := Chunk_size; /* Write data starting at the beginning of the second chunk: */ Position := Chunk_size + 1; FOR i IN 1..3 LOOP DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer); /* Fill the buffer with more data (of size Chunk_size) 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;
/* This example demonstrates how OCI provides for the ability to write arbitrary amounts of data to an Internal LOB in either a single piece or in multiple pieces using a streaming mechanism that utilizes standard polling. A dynamically allocated Buffer is used to hold the data being written to the LOB. */ /* Select the locator into a locator variable */ sb4 select_lock_story_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *) "SELECT Story FROM Multimedia_tab m \ WHERE m.Clip_ID = 1 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_CLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return (0); } void writeDataToLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIClobLocator *Lob_loc; ub4 Total = 2.5*MAXBUFLEN; /* <total amount of data to write to the CLOB in bytes> */ unsigned int amt; unsigned int offset; unsigned int remainder, nbytes; boolean last; ub1 bufp[MAXBUFLEN]; sb4 err; /* Allocate the locators desriptors*/ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the CLOB */ printf (" select a story Lob\n"); select_lock_story_locator(Lob_loc, errhp, svchp, stmthp); /* Open the CLOB */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); if (Total > MAXBUFLEN) nbytes = MAXBUFLEN; /* We will use streaming via standard polling */ else nbytes = Total; /* Only a single write is required */ /* Fill the buffer with nbytes worth of data */ remainder = Total - nbytes; /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE */ amt = 0; offset = 1; printf(" write the Lob data in pieces\n"); if (0 == remainder) { amt = nbytes; /* Here, (Total <= MAXBUFLEN ) so we can write in one piece */ checkerr (errhp, OCILobWrite (svchp, errhp, Lob_loc, &amt, offset, bufp, nbytes, OCI_ONE_PIECE, (dvoid *)0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT)); } else { /* Here (Total > MAXBUFLEN ) so we use streaming via standard polling */ /* write the first piece. Specifying first initiates polling. */ err = OCILobWrite (svchp, errhp, Lob_loc, &amt, offset, bufp, nbytes, OCI_FIRST_PIECE, (dvoid *)0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT); if (err != OCI_NEED_DATA) checkerr (errhp, err); last = FALSE; /* Write the next (interim) and last pieces */ do { if (remainder > MAXBUFLEN) nbytes = MAXBUFLEN; /* Still have more pieces to go */ else { nbytes = remainder; /* Here, (remainder <= MAXBUFLEN) */ last = TRUE; /* This is going to be the final piece */ } /* Fill the Buffer with nbytes worth of data */ if (last) { /* Specifying LAST terminates polling */ err = OCILobWrite (svchp, errhp, Lob_loc, &amt, offset, bufp, nbytes, OCI_LAST_PIECE, (dvoid *)0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT); if (err != OCI_SUCCESS) checkerr(errhp, err); } else { err = OCILobWrite (svchp, errhp, Lob_loc, &amt, offset, bufp, nbytes, OCI_NEXT_PIECE, (dvoid *)0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT); if (err != OCI_NEED_DATA) checkerr (errhp, err); } /* Determine how much is left to write */ remainder = remainder - nbytes; } while (!last); } /* At this point, (remainder == 0) */ /* Closing the LOB is mandatory if you have opened it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
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 "USER1/USER1". 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 STORY INTO :CLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 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 COMMIT WORK RELEASE END-EXEC. STOP RUN. WRITE-TO-CLOB. 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. 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 example demonstrates how Pro*C/C++ provides for the ability 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 is used to hold 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 Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Open the CLOB: */ EXEC SQL LOB OPEN :Lob_loc READ WRITE; Total = Amount = (multiple * BufferLength); if (Total > BufferLength) nbytes = BufferLength; /* We will use streaming via standard polling */ else nbytes = Total; /* Only a single write is required */ /* Fill the buffer with nbytes worth of data: */ memset((void *)Buffer.arr, 32, nbytes); Buffer.len = nbytes; /* Set the Length */ remainder = Total - nbytes; if (0 == remainder) { /* Here, (Total <= BufferLength) so we can write in one piece: */ EXEC SQL LOB WRITE ONE :Amount FROM :Buffer INTO :Lob_loc; printf("Write ONE Total of %d characters\n", Amount); } else { /* Here (Total > BufferLength) so we streaming via standard polling */ /* 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; }
'Note that this code fragment assumes an orablob object as the result of a 'dynaset operation. This object could have been an OUT parameter of a PL/SQL 'procedure. For more information please refer to chapter 1. There are two ways 'of writing a lob using orablob.write or orablob.copyfromfile 'Using OraBlob.Write mechanism Dim OraDyn as OraDynaset, OraSound as OraBlob,amount_written%, chunksize%, curchunk chunksize = 32768 Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab", ORADYN_DEFAULT) Set OraSound = OraDyn.Fields("Sound")OraSound.offset = 1OraSound.pollingAmount = LOF(fnum)Dim piece As ByteGet #fnum, , curchunkpiece = ORALOB_FIRST_PIECEamount_written = OraSound.Write(curchunk, chunksize, ORALOB_FIRST_PIECE)WhileOraSound.Status = ORALOB_NEED_DATAIf amount_written <= chunksize Thenpiece = ORALOB_LAST_PIECEElsepiece = ORALOB_NEXT_PIECEEnd IfGet #fnum, , curchunkamount_written =OraSound.Write(curchunk, chunksize, piece)Wend'Using OraBlob.CopyFromFile mechanism Dim OraDyn as OraDynaset, OraSound as OraBlob, amount_read%, chunksize%, chunk Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraSound = OraDyn.Fields("Sound").Value OraSound.CopyFromFile "c:\mysound.aud"
// 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_66 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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; String buf = new String ("Some Text To Write"); ResultSet rset = stmt.executeQuery ( "SELECT intab.transcript FROM TABLE( SELECT mtab.inseg_ntab FROM multimedia_tab mtab WHERE mtab.clip_id = 1) intab WHERE intab.segment=1 FOR UPDATE"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getCLOB (1); } OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall ("BEGIN DBMS_LOB.OPEN( ?, DBMS_LOB.LOB_READWRITE); END;"); cstmt.setCLOB(1, lob_loc); cstmt.execute(); long pos = 0; // This is the offset within the CLOB where the data is to be written long length = 0; // This is the size of the buffer to be written. // This loop writes the buffer three times consecutively: for (int i = 0; i < 3; i++) { // Fill the buffer with some data to be written: length = buf.length(); pos += length; // This is an Oracle-specific method: lob_loc.plsql_write(pos, buf.toCharArray()); } // All OPENed LOBS must be CLOSEd: 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(); } } }
Prior to updating a LOB value via 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 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 "Updated locators" in Chapter 2, "Advanced Topics".
Our example accesses text (CLOB data) that is referenced in the Script column of the table Voiceover_tab, and trims it.
/* Note that the example procedure trimLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE trimLOB_proc IS Lob_loc CLOB; BEGIN /* Select the LOB, get the LOB locator: */ SELECT Mtab.Voiced_ref.Script INTO Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 2 FOR UPDATE; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); /* Trim the LOB data: */ DBMS_LOB.TRIM(Lob_loc,100); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); COMMIT; /* Exception handling: */ EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); END;
/* Select the locator into a locator variable */ sb4 select_lock_voice_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *) "SELECT Mtab.Voiced_ref.Script \ FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 2 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_CLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void trimLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; unsigned int trimLength; (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the CLOB */ printf( " select a voice LOB\n"); select_lock_voice_locator(Lob_loc, errhp, svchp, stmthp); /* Open the CLOB */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Trim the LOB to its new length */ trimLength = 100; /* <New truncated length of the LOB>*/ printf (" trim the lob to %d bytes\n", trimLength); checkerr (errhp, OCILobTrim (svchp, errhp, Lob_loc, trimLength )); /* Closing the CLOB is mandatory if you have opend it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); }
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 "USER1/USER1". 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 MTAB.STORY INTO :CLOB1 FROM MULTIMEDIA_TAB MTAB WHERE MTAB.CLIP_ID = 2 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 COMMIT 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.
|
Note: In addition to the data structures set up above in the section "Example: Create a Table Containing One or More LOB Columns using SQL DDL", you should use DML like this: INSERT INTO multimedia_tab VALUES ( 2, 'The quick brown fox jumped over the lazy dog', empty_clob(), NULL, empty_blob(), empty_blob(), NULL, NULL, NULL, NULL); INSERT INTO voiceover_tab VALUES ( voiced_typ('hello', (SELECT story FROM multimedia_tab WHERE clip_id = 2), 'world', 1, NULL)) UPDATE multimedia_tab SET voiced_ref = (SELECT REF(r) FROM voiceover_tab r WHERE r.take = 1) WHERE clip_id = 2 Then create this text file, pers_trim.typ, containing: case=lower type voiced_typ Then run this Object Type Translator command: ott intyp=pers_trim.typ outtyp=pers_trim_o.typ hfile=pers_trim.h code=c user=samp/samp |
#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 Mtab.Voiced_ref INTO :vt_ref FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 2 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; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value OraDyn.Edit OraSound1.Trim 10 OraDyn.Update
// 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: Class.forName ("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 mtab.voiced_ref.script FROM multimedia_tab mtab WHERE mtab.clip_id = 2 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(); } } }
Prior to updating a LOB value via 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 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 "Updated locators" in Chapter 2, "Advanced Topics".
The example demonstrates erasing a portion of sound (Sound).
/* Note that the example procedure eraseLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE eraseLOB_proc IS Lob_loc BLOB; Amount INTEGER := 3000; BEGIN /* Select the LOB, get the LOB locator: */ SELECT Sound INTO lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); /* Erase the data: */ DBMS_LOB.ERASE(Lob_loc, Amount, 2000); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); COMMIT; /* Exception handling: */ EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); END;
/* Select the locator into a locator variable: */ sb4 select_lock_sound_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=1 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row: */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void eraseLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; ub4 amount = 3000; ub4 offset = 2000; OCILobLocator *Lob_Loc; (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the CLOB: */ printf( " select and lock a sound LOB\n"); select_lock_sound_locator(Lob_loc, errhp, svchp, stmthp); /* Open the BLOB: */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); /* Erase the data starting at the specified Offset: */ printf(" erase %d bytes from the sound Lob\n", amount); checkerr (errhp, OCILobErase (svchp, errhp, Lob_loc, &amount, offset )); /* Closing the BLOB is mandatory if you have opened it: */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators: */ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
IDENTIFICATION DIVISION. PROGRAM-ID. ERASE-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 SOUND INTO :BLOB1 FROM MULTIMEDIA_TAB MTAB WHERE MTAB.CLIP_ID = 2 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 COMMIT 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.
#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 Sound INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 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; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab ORDER BY clip_ id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value 'Erase 10 bytes begining from the 100th byte: OraDyn.Edit OraSound1.Erase 10, 100 OraDyn.Update
// 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_145 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 sound FROM multimedia_tab WHERE clip_id = 2 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 scenario is part of the management of a buffering example related to Sound that is developed in this and related methods.
You enable buffering in order to perform a small read or write of the data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations. Note that you must flush the buffer in order to make your modifications persistent. For more information, refer to "LOB Buffering Subsystem" in Chapter 2, "Advanced Topics".
Please note that you would not enable buffering to perform the stream read and write involved in checkin and checkout.
IDENTIFICATION DIVISION. PROGRAM-ID. LOB-BUFFERING. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 COMMIT 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.
#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 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; enableBufferingLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value 'Enable buffering: OraSound1.EnableBuffering
This scenario is part of the management of a buffering example related to Sound that is developed in this and related methods.
You enable buffering in order to perform a small read or write of the data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations. Note that you must flush the buffer in order to make your modifications persistent. For more information, refer to "LOB Buffering Subsystem" in Chapter 2, "Advanced Topics".
Please note that you would not enable buffering to perform the stream read and write involved in checkin and checkout.
IDENTIFICATION DIVISION. PROGRAM-ID. LOB-BUFFERING. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 COMMIT 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.
#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 scenario is part of the management of a buffering example related to Sound that is developed in this and related methods.
You enable buffering in order to perform a small read or write of the data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations. Note that you must flush the buffer in order to make your modifications persistent.
Please note that you would not enable buffering to perform the stream read and write involved in checkin and checkout.
/* Select the locator into a locator variable: */ sb4 select_lock_sound_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=1 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row: */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void lobBuffering (envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; ub4 amt; ub4 offset; sword retval; ub1 bufp[MAXBUFLEN]; ub4 buflen; /* Allocate the locator desriptor: */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the BLOB: */ printf (" select a sound Lob\n"); select_lock_sound_locator(Lob_loc, errhp, svchp, stmthp); /* Open the BLOB: */ 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, bufp, buflen, OCI_ONE_PIECE, (dvoid *)0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT)); /* Flush the buffer: */ printf(" flush the LOB buffers\n"); checkerr (errhp, OCILobFlushBuffer(svchp, errhp, 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 BLOB is mandatory if you have opened it: */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators: */ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
IDENTIFICATION DIVISION. PROGRAM-ID. LOB-BUFFERING. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 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 COMMIT 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.
#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 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 < 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; }
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value 'Disable buffering: OraSound1.DisableBuffering
This example shows a series of updates via the EMPTY_CLOB operation to different data types of the first clip.
UPDATE Multimedia_tab SET Story = EMPTY_CLOB() WHERE Clip_ID = 1; UPDATE Multimedia_tab SET FLSub = EMPTY_CLOB() WHERE Clip_ID = 1; UPDATE multimedia_tab SET Sound = EMPTY_BLOB() WHERE Clip_ID = 1;
This example updates voice-over data from archival storage (VoiceoverLib_tab) by means of a reference.
UPDATE Voiceover_tab SET (Originator, Script, Actor, Take, Recording) = (SELECT * FROM VoiceoverLib_tab T2 WHERE T2.Take = 101); UPDATE Multimedia_tab Mtab SET Voiced_ref = (SELECT REF(Vref) FROM Voiceover_tab Vref WHERE Vref.Actor = 'James Earl Jones' AND Vref.Take = 1) WHERE Mtab.Clip_ID = 1;
This example updates Sound data by means of a locator bind variable.
/* Note that the example procedure updateUseBindVariable_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE updateUseBindVariable_proc (Lob_loc BLOB) IS BEGIN UPDATE Multimedia_tab SET Sound = lob_loc WHERE Clip_ID = 2; END; DECLARE Lob_loc BLOB; BEGIN /* Select the LOB: */ SELECT Sound INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; updateUseBindVariable_proc (Lob_loc); COMMIT; END;
/* Select the locator into a locator variable: */ sb4 select_sound_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=2"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row: */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } /* Update the LOB in the selected row in the table: */ void updateLobUsingBind (envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *updstmt = (text *) "UPDATE Multimedia_tab SET Sound = :1 WHERE Clip_ID = 1"; OCILobLocator *Lob_loc; OCIBind *bndhp1; /* Allocate locator resources: */ (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0); /* Select the locator: */ printf(" select a sound locator\n"); (void)select_sound_locator(Lob_loc, errhp, svchp, stmthp); /* Prepare the SQL statement: */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, updstmt, (ub4) strlen((char *) updstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Binds the bind positions: */ printf(" bind locator to bind position\n"); checkerr (errhp, OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1, (dvoid *) &Lob_loc, (sb4)0, SQLT_BLOB, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); /* Execute the SQL statement: */ printf ("update LOB column in another row using this locator\n"); checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return; }
IDENTIFICATION DIVISION. PROGRAM-ID. UPDATE-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BLOB1 SQL-BLOB. 01 NEW-LEN PIC S9(9) COMP. 01 AMT PIC S9(9) COMP. 01 OFFSET 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 "USER1/USER1". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. UPDATE-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 SOUND INTO :BLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC. EXEC SQL UPDATE MULTIMEDIA_TAB SET SOUND = :BLOB1 WHERE CLIP_ID = 2 END-EXEC. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL COMMIT 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.
#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 updateUseBindVariable_proc(Lob_loc) OCIBlobLocator *Lob_loc; { EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL UPDATE Multimedia_tab SET Sound = :Lob_loc WHERE Clip_ID = 2; } void updateLOB_proc() { OCIBlobLocator *Lob_loc; EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT Sound INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; updateUseBindVariable_proc(Lob_loc); EXEC SQL FREE :Lob_loc; EXEC SQL COMMIT WORK; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; updateLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
Dim OraDatabase as OraDatabase,OraDyn as OraDynaset, OraSound as OraBLOB, 'Select a column with clip_id = 1: Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab WHERE clip_id = 1", ORADYN_DEFAULT) 'Get the OraBlob object from the field: Set OraSound = OraDyn.Fields("Sound").Value 'Create a parameter for OraBlob object: OraDatabase.Parameters.Add "SOUND",NULL,ORAPARM_INPUT,ORATYPE_BLOB 'Set the value of SOUND parameter to OraSound: OraDatabase.Parameters("SOUND").Value = OraSound 'Update the Multimedia_tab with OraSound for clip_id = 2: OraDatabase.ExecuteSQL("Update Multimedia_tab SET Sound = :SOUND WHERE Clip_id = 2")
// 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_163 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: Class.forName ("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 = stmt.executeQuery ( "SELECT sound FROM multimedia_tab WHERE clip_id = 1"); if (rset.next()) { // retrieve the LOB locator from the ResultSet: BLOB sound_blob = ((OracleResultSet)rset).getBLOB (1); OraclePreparedStatement ops = (OraclePreparedStatement) conn.prepareStatement( "UPDATE multimedia_tab SET SOUND = ? WHERE clip_id = 2"); ops.setBlob(1, sound_blob); ops.execute(); conn.commit(); conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
You delete a row that contains an internal LOB column / attribute by using
DELETE, or
DROP TABLE, TRUNCATE TABLE, or DROP TABLESPACE.
In either case you delete the LOB locator and the LOB value as well.
But note that due to the consistent read mechanism, the old LOB value remains accessible with the value that it had at the time of execution of the statement (such as SELECT) that returned the LOB locator.
|
Note: This is an advanced topic that is discussed in more detail with regard to "Read-Consistent Locators". |
Of course, two distinct rows of a table with a LOB column have their own distinct LOB locators and distinct copies of the LOB values irrespective of whether the LOB values are the same or different. This means that deleting one row has no effect on the data or LOB locator in another row even if one LOB was originally copied from another row.
In this case we delete all the data associated with Clip 10.
DELETE FROM Multimedia_tab WHERE Clip_ID = 10; DROP TABLE Multimedia_tab; TRUNCATE TABLE Multimedia_tab;
