Comparing the LOB Interfaces

A Comparing the LOB Interfaces

The following tables compare the eight LOB programmatic interfaces by listing their functions and methods used to operate on LOBs. The tables are split in two only to accommodate all eight interfaces.

APIs for BLOBs and CLOBs

Table A-1 APIs for BLOBs and CLOBs (PL/SQL, JDBC, OCI, OCCI)

PL/SQL: DBMS_LOB (dbmslob.sql)	JDBC (Java) interfaces java.sql.Clob and java.sql.Blob	OCI (C/ocip.h)	OCCI (C++/occiData.h) classes: Clob and Blob
		`OCILobLocatorIsInit()`	`isInitialized()`
`ISSECUREFILE`	`isSecureFile()`
`OPEN`	`open()`	`OCILobOpen()`	`Open()`
`ISOPEN`	`isOpen()`	`OCILobIsOpen()`	`isOpen()`
`CLOSE`	`close()`	`OCILobClose()`	`Close()`
`CREATETEMPORARY`	`createTemporary`	`OCILobCreateTemporary()`
`FREETEMPORARY`	`freeTemporary`	`OCILobFreeTemporary()`
`ISTEMPORARY`	`isTemporary`	`OCILobIsTemporary()`
`GETLENGTH`	`length()`	`OCIGetLobLength2()`	`length()`
`GET_STORAGE_LIMIT`		`OCILobGetStorageLimit()`
`GETCHUNKSIZE`	`getChunkSize()`	`OCILobGetChunkSize()`	`getChunkSize()`
`READ`	`Blob: getBytes() getBinaryStream()`	`OciLobRead2() OCILobArrayRead()`	`read()`
`READ`	`Clob: getChars() getCharacterStream() getAsciiStream()`	`OciLobRead2() OCILobArrayRead()`	`read()`
`SUBSTR`	`getSubString`
`INSTR`	`position`
		`OCILobCharSetId()`	`getCharSetId() (Clob only)`
		`OCILobCharSetForm()`	`getCharSetForm (Clob only)`
`WRITE`	`Blob: setBytes() setBinaryStream()`	`OCILobWrite2() OCILobArrayWrite()`	`write`
`WRITE`	`Clob: setString() setCharacterStream()`	`OCILobWrite2() OCILobArrayWrite()`	`write`
`WRITEAPPEND`	use `length()` and then `putString()` or `putBytes()`	`OCILobWriteAppend2()`
`ERASE`		`OCILobErase2()`
`TRIM`	`truncate()`	`OCILobTrim2()`	`trim`
	`equal`	`OCILobIsEqual()`	`Use operators == / !=`
`COMPARE`	Use `DBMS_LOB`
`APPEND`	Use `length()` and then `putString()` or `putBytes()`	`OCILobWriteAppend2()`
`COPY`	`Use read and write`	`OCILobCopy2()`	`copy()`
`Use operator :=`	`Use operator =`	`OCILobLocatorAssign()`	`use operator =`
`CONVERTTOBLOB`
`CONVERTTOCLOB`
			`closeStream()`
`GETOPTIONS`		`OCILobGetOptions()`	`getOptions()`
`SETOPTIONS`		`OCILobSetOptions()`	`setOptions()`
`GETCONTENTTYPE`		`OciLobGetContentType()`	`getContentType()`
`SETCONTENTTYPE`		`OciLobSetContentType()`	`setContentType()`
`FRAGMENT_DELETE FRAGMENT_INSERT FRAGMENT_MOVE FRAGMENT_REPLACE`

Table A-2 APIs for BLOB and CLOB (PL/SQL, .NET, Pro*C/C++, Pro COBOL)

PL/SQL: DBMS_LOB (dbmslob.sql)	ODP.NET Classes: OracleClob and OracleBlob	ProC/C++ and ProCOBOL
`OPEN`	`BeginChunkWrite`	`OPEN`
`ISOPEN`	`IsInChunkWriteMode`	`DESCRIBE [ISOPEN]`
`CLOSE`	`EndChunkWrite`	`CLOSE`
`CREATETEMPORARY`	`Add()`	`CREATE TEMPORARY`
`FREETEMPORARY`	`Dispose() and Close()`	`FREE TEMPORARY`
`ISTEMPORARY`	`IsTemporary()`	`DESCRIBE [ISTEMPORARY]`
`GETLENGTH`	`Length()`	`DESCRIBE [LENGTH]`
`GETCHUNKSIZE`	`OptimumChunkSize()`	`DESCRIBE [CHUNKSIZE]`
`READ`	`Value Read`	`READ`
`INSTR`	`Search`
`WRITE`	`Write`	`WRITE`
`WRITEAPPEND`	`Append`	`WRITE APPEND`
`ERASE`	`Erase`	`ERASE`
`TRIM`	`SetLength`	`TRIM`
	`IsEqual`
`COMPARE`	`Compare`
`APPEND`	`Append`	`APPEND`
`COPY`	`CopyTo`	`COPY`
`Use operator :=`	`Clone`	`ASSIGN`

APIs for BFILEs

Table A-3 APIs for BFILEs (PL/SQL, JDBC, OCI, OCCI)

PL/SQL: DBMS_LOB (dbmslob.sql)	JDBC (Java) interface oracle.jdbc.OracleBfile	OCI (C/ociap.h)	OCCI (C++/occiData.h) class: Bfile
`FILEEXISTS`	`fileExists`	`OciLobFileExist()`	`fileExists()`
`FILEGETNAME`	`getDirAlias, getName`	`OCILobFileGetName()`	`getDirAlias()getFileName()`
`SQL BFILENAME operator`	`SQL BFILENAME operator`	`OCILobFileSetName()`	`setName()`
`OPEN`	`openFile`	`OCILobOpen()`	`open()`
`ISOPEN`	`isFileOpen()`	`OCILobIsOpen()`	`isOpen()`
`CLOSE`	`closeFile`	`OCILobClose()`	`close()`
`FILECLOSEALL`	`Use DBMS_LOB`	`OCILobFileCloseAll()`
`GETLENGTH`	`length`	`OCILobGetLength2()`	`length()`
`READ`	`getBytes()getBinaryStream()`	`OCILobRead()OCILobArrayRead()`	`read`
`SUBSTR`	`getBytes`
`INSTR`	`position`
`Use operator :=`	`Use operator =`	`OCILobLocatorAssign()`	`Use operator =`
`LOADCLOBFROMFILE LOADBLOBFROMFILE`		`OCILobLoadFromFile2()`	`Blob.copy()` or `Clob.copy()`
`COMPARE`		N/A
`N/A`	`equal`	`OCILobIsEqual()`	Use operators ==/!=

Table A-4 APIs for BFILEs (PL/SQL, ODP.NET, Pro*C/C++ and Pro*COBOL)

PL/SQL: DBMS_LOB (dbmslob.sql)	ODP.NET Class: OracleBfile	ProC/C++ and ProCOBOL
`FILEEXISTS`	`FileExists`	`DESCRIBE [FILEEXISTS]`
`FILEGETNAME`	`DirectoryName, Filename`	`DESCRIBE [DIRECTORY, FILENAME]`
`SQL BFILENAME operator`	`DirectoryName, Filename`	`FILE SET`
`OPEN`	`OpenFile`	`OPEN`
`ISOPEN`	`IsOpen()`	`DESCRIBE [ISOPEN]`
`CLOSE`	`CloseFile`	`CLOSE`
`FILECLOSEALL`	N/A	`FILE CLOSE ALL`
`GETLENGTH`	`Length`	`DESCRIBE [LENGTH]`
`READ`	`Value,Read`	`READ`
`SUBSTR`
`INSTR`	`Search`
`Use operator :=`
`LOADCLOBFROMFILE LOADBLOBFROMFILE`
`COMPARE`	`Compare`
`N/A`	`IsEqual`