Inserting/Update a long String into a CLOB in Oracle database

Posted by Pourya Shahroudi on May 26, 2010 in Software Development | 0 comments

Inserting a long String into a CLOB field in Oracle database using SQL queries is quite simple, but at the same time can be confusing. Mainly because personally couldn’t find good tutorials on the web when I was trying to do this. Here is a simple way to do it:
To insert a ClOB field you need to have a good query browser. The reason is that I will be using variables to do this, I was not able to do this using DbVisualizer but it works perfectly fine in TOAD.
Here is how to do it:

-- First: you need to declare a variable to hold the long CLOB value.
declare
clobVariable varchar2(32767) :='YOUR LONG CLOB VALUE GOES HERE';

begin
-- ALL the queries you need to run as to be inside the begin tag.
-- As you can see I'm using the variable 'clobVariable' in the insert statement to insert into the table.
-- You can use this variable in any combination of SQL statements.

insert into sometbale(id,clob_column) values(1,clobVariable);
end;
/