How to Insert CLOB in Oracle

Written by andy carr
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Insert CLOB in Oracle
Binary data can be stored in large Oracle objects. (Comstock/Comstock/Getty Images)

The CLOB data type stands for "Character Large Object" and can contain character data of between 8 terabytes to 128 terabytes in size. Simply attempting to insert CLOB data using the standard "INSERT" statement is not always possible. CLOB data can be used to hold all types of character based information in large quantities. Inserting CLOB data into a database can be achieved in a number of ways. Depending on the type of data and the source of the information, it may be necessary to write a stored procedure or anonymous Oracle block to insert the data successfully.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

    Create a temporary table with a CLOB data type as follows at the "SQL>" prompt in SQLPlus:

    create table clobtable (id number, clob_data CLOB);

    The table can now be used to enter and reference CLOB data via the id field and the clob_data field storing the actual information.

  2. 2

    Create code to populate the CLOB field in the database. This is achieved by writing up an anonymous block as follows:

    DECLARE

    vlob_loc CLOB;

    charcount binary_integer;

    position integer := 1;

    v_charclob varchar2(32000);

    begin

    for I in 1..12000 loop

    v_charclob := v_charclob || 'x';

    end loop;

    insert into clobtable values (12, empty_clob());

    charcount := LENGTH(v_charclob);

    select clob_data into vlob_loc from clobtable where id=12;

    DBMS_LOB.WRITE(vlob_loc, charcount, position, v_charclob);

    dbms_output.put_line('CLOB Row Inserted');

    END;

  3. 3

    Test the CLOB insert. This can be achieved by selecting the contents of the table thus:

    Select * from clobtable;

    The output should be:

    ID CLOB_DATA

    12 xxxxxxxxxxxxxxxx ... etc

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.