Oracle PL/SQL/Data Type/blob

A PL/SQL block to read an operating system file called BLOB.JPG that contains 1 row of binary data.

 

SQL>
SQL>
SQL> create table myTable(
  2     c1 number primary key,
  3     c2 blob
  4  );
Table created.
SQL>
SQL>
SQL> DECLARE
  2    v_raw           RAW(32767);
  3    v_file_id       UTL_FILE.FILE_TYPE;
  4  BEGIN
  5      v_file_id:=
  6      UTL_FILE.FOPEN("c:\","blob.jpg","r",32767);
  7      UTL_FILE.GET_RAW (v_file_id,v_raw);
  8      INSERT INTO myTable VALUES (1,v_raw);
  9      UTL_FILE.FCLOSE( v_file_id);
 10  END;
 11  /

SQL>
SQL>
SQL> drop table myTable;
Table dropped.

Blob type column

 
SQL> create table myTable
  2     (c1 number primary key,
  3     c2 blob);
Table created.
SQL>
SQL> drop table myTable;
Table dropped.

Initialize blob type data

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    blob_column BLOB NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initialize_blob(blob_par IN OUT BLOB,id_par IN INTEGER)IS
  2  BEGIN
  3    SELECT blob_column
  4    INTO blob_par
  5    FROM myTable
  6    WHERE id = id_par;
  7  END initialize_blob;
  8  /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.

Read blob type data, DBMS_LOB.READ

 
SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    blob_column BLOB NOT NULL
  4  );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initialize_blob(blob_par IN OUT BLOB,
  2    id_par IN INTEGER
  3  ) IS
  4  BEGIN
  5    SELECT blob_column
  6    INTO blob_par
  7    FROM myTable
  8    WHERE id = id_par;
  9  END initialize_blob;
 10  /
Procedure created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE read_blob_example(id_par IN INTEGER) IS
  2    blobValue BLOB;
  3    binary_buffer_var RAW(25);
  4    offset INTEGER := 1;
  5    amount_var INTEGER := 25;
  6  BEGIN
  7    initialize_blob(blobValue, id_par);
  8    DBMS_LOB.READ(blobValue, amount_var, offset, binary_buffer_var);
  9    DBMS_OUTPUT.PUT_LINE("binary_buffer_var = " || binary_buffer_var);
 10    DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var);
 11  END read_blob_example;
 12  /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Read data in for sql statement

 
SQL>
SQL>
SQL> select lpad(" ",2*level) || operation || " " || options
  2    || " " || object_name as "Execution Plan"
  3  from plan_table
  4  where statement_id = "&&1"
  5  connect by prior id = parent_id and statement_id = "&1"
  6  start with id = 1;
Enter value for 1:
old   4: where statement_id = "&&1"
new   4: where statement_id = ""
old   5: connect by prior id = parent_id and statement_id = "&1"
new   5: connect by prior id = parent_id and statement_id = ""
no rows selected
SQL>