How to Insert CLOB in Oracle

Save

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.

  • 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.

  • 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;

  • 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

References

  • Photo Credit Comstock/Comstock/Getty Images
Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!