ODAC : Working with BLOB and CLOB data types ODAC supports Oracle 8 BLOB and CLOB datatypes.
You can retrieve values of LOB fields by TOra Query component as easy as LONG or LONG ROW fields.
This is the difference to the way that data of LONG or LONG RAW types is stored in database - tables hold their immediate values. The next sample routines show how to upload a file from hard disk to server and download it back. Read Bytes(stream Length), 0, stream Length) 'Perform INSERT Dim my Command As Oracle Command = New Oracle Command( "INSERT INTO Pictures (ID, Pic Name, Picture) VALUES(1,'pict1',: Pictures)", my Connection) Dim my Param As Oracle Parameter = my Command.
With some MSSQL and My SQL skills, I did some reading up on how Oracle can manipulate CLOB/LOB Columns using DBMS_LOB.The result is a simple SQL Statement that results in an SQL UPDATE statement, which updates a column, with XML located in /directory/to/ Create Directory Object To start with, you’ll need to place the XML file in a directory and create the File Handler in Oracle for use later.The restrictions in place made it impossible to edit the fields using utilities such as DBVIS or Toad for Oracle.I wanted to be able to write SQL Statements that allowed files to be read into the stream and update the backend database column (CLOB).--Keep in mind that LOB pointers are transaction dependent.
This means that if you have a COMMIT command in your code, the LOB pointer could become invalid (not pointing to anything) and you may not be able to perform some operations by using that locator.In the case of LONG or LONG RAW the entire value is stored in the table column. If Param Type is pt Input ODAC writes data to server, if Param Type is pt Output it reads data.When you access LOB column, it is the locator which is returned. You can also use dt Blob and dt Memo datatypes with LOB parameters to write ordinary DML statements.Once you create an Oracle Lob instance corresponding temporary LOB appears on the server, and any data you insert into the object is sent immediately to server. An example of using temporary LOBs you will see later in the article. Write() methods to transfer data to and from server. This kind of object is not referenced by any table yet. You have to use temporary LOBs when inserting new data to a table. Value is treated as array of bytes (byte), whereas Oracle Db Type. Open() 'Create temporary BLOB Dim my Lob As Oracle Lob = New Oracle Lob(my Connection, Oracle Db Type.