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.

   <source lang="sql">

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.

 </source>
   
  

Blob type column

   <source lang="sql">

SQL> create table myTable

 2     (c1 number primary key,
 3     c2 blob);

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

 </source>
   
  

Initialize blob type data

   <source lang="sql">

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.

 </source>
   
  

Read blob type data, DBMS_LOB.READ

   <source lang="sql">

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>

 </source>
   
  

Read data in for sql statement

   <source lang="sql">

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>

 </source>