Oracle 11g R2 – Inserting Data with CLOB and BLOB Values

oracleoracle-11g-r2

My table has one clob column and one blob column. I need to insert data into it through insert statements.

The length of data to be inserted is more than 4000 chars.

When I do export of insert statements is sql navigator I get empty values.

INSERT INTO mytable VALUES('REQUEST',EMPTY_CLOB(),EMPTY_BLOB());

I also tried to do insert it data like this.I have added only example data to be inserted.The actual data to be inserted is large.

INSERT INTO mytable VALUES('REQUEST2','{"customer"asxcbasjbab....:}',NULL);

Can some one tell me best way to insert data into table in these scenario.

Or anyway to do export and import this data will also help.

Best Answer

In SQL, the limit is 4,000 characters. Using straight SQL like that, without a bind variable, you'll be limited to 4,000 characters. Steps to insert lob values :

1) Create a table and name it TBL_CLOB with 2 fields:
```
   id_int = integer;
    clob_txt =clob;
```
2) Create a stored procedure and name it P_CLOB with the following code:

    (P_ID_INT in int,P_CLOB_TXT in varchar2)as

    begin
    insert into TBL_CLOB values(P_ID_INT,P_CLOB_TXT);
    end;

3) Test inserting up to 32000. Use SQL Plus and enter some starts in the CLOB field:

    SQL>  exec p_clob(1,rpad('*',32000,'*'));

    SQL> commit;

    SQL>  exec p_clob(2,rpad('*',19872,'*'));

    SQL> commit;

4) Retrieve the 2 records you just inserted and count the number of characters in the CLOB fields:

    SQL> select id_int,dbms_lob.getlength(clob_txt) from tbl_clob;

5) You should get something like this:

        ID_INT DBMS_LOB.GETLENGTH(CLOB_TXT)
    ---------- ----------------------------
             1                        32000
             2                        19872

Makes sense ?

Related Solutions

Can Oracle SQL developer export data as a set of Update commands

Not using a built in command, but...

If an UPDATE really can satisfy your requirements as your title implies, then no inserts should be necessary and the primary key must be in sync between the databases.

Your requirements don't allow remote updates, but if you can remotely query the database, then you can join the local table to the remote table to create the statements that will need to be run the remote system. Here is a demonstration:

drop table t1;
drop table t2;
create table t1 as (select level c1, level c2 from dual connect by level <=7);
create table t2 as (select * from t1);
update t2 set c2 = null where c1 in (3,6);
select * from t1;
select * from t2;

select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1 
join t2 on t1.c1 = t2.c1
where t1.c2 <> t2.c2 OR t2.c2 is null;

If you cannot query the remote database, you can update every row like this:

drop table t1;
create table t1 as (select level c1, level c2 from dual connect by level <=7);

select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1;

Oracle Database Design – Table Design for Multi-Type Data

Not sure why one of the answers made such bold statement . Regardless: everything can be modeled in relational model

create table entry 
( 
  id number(12) primary key, 
  name varchar2(256) not null unique,  
  customer_id number not null references customer(id)
);

create table datatypes
(
 id number primary key,
 name varchar2(256) not null unique
);
create table entry_data ( 
  id number(12) primary key, 
  datatype_id  number not null references datatypes(id),
  value     varchar2(256),  --or CLOB ,no index
  value_geometry geom  ,    -- no index
  --value_blob   blob -- if you neeed, no index
  entry_id number not null references entry(id),
);

your task is related to the multi-valued attributes task. Datatypes table will abstract your datatypes, however 2 value** columns are created in entry_data due to the fact that your data can be different in nature(belong to different Classes), thus would need different storage. But as far as varchar2 vs. number vs.date -- all can be placed into varchar2 , just would need later on some casting. And with very simplistic logic of NVL in code you can determine which column was populated out of value or value_geometry.

Best Answer

Related Solutions

Can Oracle SQL developer export data as a set of Update commands

Oracle Database Design – Table Design for Multi-Type Data

Related Question