DBA Tips Archive for Oracle

  


Load Text Data to BLOB then Write Back Out to Console - (PL/SQL)

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Example
  3. Test



Overview

The following example is part of the Oracle LOB Examples Collection.

This example provides two PL/SQL procedures that demonstrate how to populate a BLOB field with textual data (using UTL_RAW.CAST_TO_RAW to cast the incoming data). A second PL/SQL procedure will read it back piece-wise displaying it to the console (after casting it from RAW to VARCHAR2) using DBMS_OUTPUT.

Write_Text_To_BLOB

This PL/SQL procedure accepts two parameters, the first being a primary key and the second being a RAW parameter to be stored in the BLOB column. When passing in the textual data (VARCHAR2), you will need to cast the incoming character data using UTL_RAW.CAST_TO_RAW. This will convert the character data into RAW for use by the Write_Text_To_BLOB procedure.

Read_Text_From_BLOB

This PL/SQL procedure is used to read piecewise the the contents of the BLOB column and write its contents to the console using DBMS_OUTPUT. The data from the BLOB column is RAW and will need to be casted from RAW to VARCHAR2 before it can be displayed by DBMS_OUTPUT.



Example

Let's first create all Oracle database objects:

DROP TABLE test_blob CASCADE CONSTRAINTS
/

Table dropped.



CREATE TABLE test_blob (
      id           NUMBER(15)
    , lob_text     BLOB
    , timestamp    DATE
)
/

Table created.


Now, let's define our two example procedures:

write_text_to_blob.sql
CREATE OR REPLACE PROCEDURE Write_Text_To_BLOB (
      p_id     IN NUMBER
    , p_buffer IN VARCHAR2)

IS
      lob_loc BLOB;

BEGIN

    INSERT INTO test_blob (id, lob_text, timestamp)
        VALUES (p_id, empty_blob(), sysdate)
        RETURNING lob_text INTO lob_loc;

    -- DBMS_LOB.WRITEAPPEND(lob_loc, UTL_RAW.LENGTH(p_buffer), p_buffer);
    DBMS_LOB.WRITE(lob_loc, LENGTH(p_buffer), 1, UTL_RAW.CAST_TO_RAW(p_buffer));
 
END;
/

SQL> @write_text_to_blob.sql Procedure created.


read_text_from_blob.sql
CREATE OR REPLACE PROCEDURE Read_Text_From_BLOB (
    p_id IN NUMBER) IS

    lob_loc  BLOB;

BEGIN

    DBMS_OUTPUT.ENABLE(100000);

    SELECT lob_text INTO lob_loc
    FROM   test_blob
    WHERE  id = p_id;

    DBMS_OUTPUT.PUT_LINE('THE LENGTH IS    : ' || DBMS_LOB.GETLENGTH(lob_loc));
    DBMS_OUTPUT.PUT_LINE(
        'THE BLOB IS READ : ' ||
        UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(lob_loc, 200, 1))
    );

END;
/

SQL> @read_text_from_blob.sql Procedure created.



Test

SQL> set serveroutput on


SQL> exec Write_Text_To_BLOB(1001, '06182000');

PL/SQL procedure successfully completed.


SQL> exec Write_Text_To_BLOB(1002, 'This is text data being stored in a BLOB.');

PL/SQL procedure successfully completed.


SQL> exec Read_Text_From_BLOB(1001);
THE LENGTH IS    : 8
THE BLOB IS READ : 06182000

PL/SQL procedure successfully completed.


SQL> exec Read_Text_From_BLOB(1002);
THE LENGTH IS    : 41
THE BLOB IS READ : This is text data being stored in a BLOB.

PL/SQL procedure successfully completed.


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 23-Jan-2006 08:55:06 EST
Page Count: 46358