LOBs

1. Create a directory object in Oracle.
   create or replace directory MY_FILES as 'C:\MY_DATA';

2. Insert a row with empty BLOB in your table and return the locator.

3. Point to the Word file to be loaded from the directory created in Step 1.

4. Open the file and use the locator from Step 2 to insert the file.

5. Close the file and commit the transaction.

declare
 f_lob	bfile;
 b_lob	blob;

begin
 insert into sam_emp(empno, ename, resume) values(6571. 'Mike Elms', 'Resume.doc');
 dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
 dbms_lob.loadfromfile(b_lob, f_lob, dbms_log.getlength(f_lob));
 dbms_lob.fileclose(f_lob);
 commit;
end;
/

Example 2:

insert into multimedia_tab values (1, 'abcd', EMPTY_CLOB(), null, EMPTY_BLOB(), null, null, null, null);
commit;

declare
 num_var	integer;
 clob_selected	clob;
 clob_updated	clob;
 clob_copied	clob;
 read_amount	integer;
 read_offset	integer;
 write_amount  	integer;
 write_offset	integer;
 buffer		varchar2(20);
begin
 select story into clob_selected 
 from multimedia_tab where clip_id = 1;

 select story into clob_updated
 from multimedia_tab where clip_id = 1
 for update;

 clob_copied := clob_selected;

 read_amount := 10;
 read_offset := 1;

 dbms_lob.read(clob_selected, read_amount, read_offset, buffer);
 dbms_output.put_line('clob_selected value: '||buffer);

 read_amount := 10;
 dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
 dbms_output.put_line('clob_copied value: '||buffer);

 read_amount := 10;
 dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
 dbms_output.put_line('clob_updated value: '||buffer);

 write_amount := 3;
 write_offset := 5;
 buffer := 'efg';
 dbms_lob.write(clob_updated, write_amount, write_offset, buffer);

 read_amount := 10;
 dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
 dbms_output.put_line('clob_updated value: '||buffer);

 read_amount := 10;
 dbms_lob.read(clob_selected, read_amount, read_offset, buffer);
 dbms_output.put_line('clob_selected value: '||buffer);

 read_amount := 10;
 dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
 dbms_output.put_line('clob_copied value: '||buffer);
end;
/

CREATE OR REPLACE PROCEDURE readBFILE_proc IS
File_loc BFILE := BFILENAME(’ADPHOTO_DIR’,
’keyboard_photo_3060_11001’);
Amount INTEGER := 32767;
Position INTEGER := 1;
Buffer RAW(32767);
BEGIN
/* Select the LOB: */
SELECT ad_graphic INTO File_loc FROM print_media
WHERE Product_ID = 3060 AND ad-Id = 11001;

/* Open the BFILE: */
DBMS_LOB.OPEN(File_loc, DBMS_LOB.LOB_READONLY);

/* Read data: */
DBMS_LOB.READ(File_loc, Amount, Position, Buffer);

/* Close the BFILE: */
DBMS_LOB.CLOSE(File_loc);
END;


// Reading data from a BFILE. [Example script: 4008.java]
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 Ex4_53
{
  public static void main (String args [])
  throws Exception
  {
    // Load the Oracle JDBC driver:
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    // Connect to the database:
    Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp");
    conn.setAutoCommit (false);
    // Create a Statement
    Statement stmt = conn.createStatement ();
    try
    {
      BFILE src_lob = null;
      ResultSet rset = null;
      Boolean result = null;
      InputStream in = null;
      byte buf[] = new byte[1000];
      int length = 0;
      boolean alreadyDisplayed = false;
      rset = stmt.executeQuery (
        "SELECT ad_graphic FROM Print_media
          WHERE product_id = 3106 AND ad_id = 13001");
      if (rset.next())
      {
        src_lob = ((OracleResultSet)rset).getBFILE (1);
      }
      // Open the BFILE:
      src_lob.openFile();
      // Get a handle to stream the data from the BFILE:
      in = src_lob.getBinaryStream();
      // This loop fills the buf iteratively, retrieving data
      // from the InputStream:
      while ((in != null) && ((length = in.read(buf)) != -1))
      {
        // the data has already been read into buf
        // We will only display the first CHUNK in this example:
        if (! alreadyDisplayed)
        {
          System.out.println("Bytes read in: " + Integer.toString(length));
          System.out.println(new String(buf));
          alreadyDisplayed = true;
        }
      }
      // Close the stream, BFILE, statement and connection:
      in.close();
      src_lob.closeFile();
      stmt.close();
      conn.commit();
      conn.close();
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }
}