Inserting data into a table with one value as clob and blob

Question

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.

Answer 1

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 ?

Answer 2

For inserting CLOB data using SQL Developer (in my case), you can do something like this:

INSERT INTO mytable VALUES('REQUEST2',:a,NULL);

Writing :a will result in a window popping up for you to enter value for parameter :a.

Just write all your '{"customer"asxcbasjbab....:}' in the window that popped up.

I think that should do the work for you.

Answer 3

SQL*Plus has a limit of 3,000 characters. If you only have a small number of records to load you can split the text up into chunks of say, 2900 characters. There are a couple of ways to do that.

Straight SQL with TO_CLOB():

-- disable variable substitution in case your text has ampersands
SET DEFINE OFF
CREATE TABLE clob_table
(
 id_num NUMBER,
 clob_text CLOB
);
INSERT INTO clob_table
(id_num,
 clob_text)
VALUES
(1,
 TO_CLOB('Record 1, first 2900 characters...')
 || 'Next 2900 characters...'
 || 'Next 2900 characters...');
COMMIT;

PL/SQL script with a CLOB variable:

-- disable variable substitution in case your text has ampersands
SET DEFINE OFF
CREATE TABLE clob_table
(
 id_num NUMBER,
 clob_text CLOB
);
DECLARE
 
 v_clob CLOB;
 
 PROCEDURE INSERT_CLOB_RECORD
 (P_ID IN NUMBER,
 P_CLOB_TXT IN CLOB) AS
 BEGIN
 INSERT INTO clob_table
 (id_num,
 clob_text)
 VALUES
 (P_ID,
 P_CLOB_TXT);
 
 END INSERT_CLOB_RECORD;
BEGIN
 
 v_clob := 'Record 1, first 2900 characters...';
 DBMS_LOB.APPEND(v_clob, 'Next 2900 characters...');
 DBMS_LOB.APPEND(v_clob, 'Next 2900 characters...');
 DBMS_LOB.APPEND(v_clob, 'Next 2900 characters..');
 INSERT_CLOB_RECORD(1, v_clob);
 
 v_clob := 'Record 2, first 2900 characters...';
 DBMS_LOB.APPEND(v_clob, 'Next 2900 characters...');
 DBMS_LOB.APPEND(v_clob, 'Next 2900 characters...');
 DBMS_LOB.APPEND(v_clob, 'Next 2900 characters..');
 INSERT_CLOB_RECORD(2, v_clob);
END;
/
COMMIT;
SELECT * FROM clob_table;

It's also worth noting that using an SQL Window in PL/SQL Developer I was able to include strings of up to about 31,000 characters