4

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.

3 Answers 3

3

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 ?

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

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.