how to insert large data in a table using bind variable

Question

I am trying to insert a large xml file with length 43000 into clob.

asktom remcomanded using bind variables but its links was broken.

so my question how to insert large xml file in a bind variable . this is my procedure

CREATE OR REPLACE PROCEDURE sp_insert_xml
(
p_id IN INT,
p_xml IN clob
)
AS

BEGIN
declare x  clob;
 y  number(10);

begin
SELECT FILE into x from PROCESS_D where PROCESS_ID =1;

select dbms_lob.getlength(x) into y from dual;

DBMS_OUTPUT.PUT_LINE(y);
end;

--INSERT INTO TEST_ID VALUES (p_id, p_xml);
END;

I want to split the length so I can insert them into a table

Where is the XML document coming from? If you only have it as a string (so can't load it directly from a file into the database), which client do you plan to call your procedure from? — Alex Poole, Aug 01 '16 at 13:48
@AlexPoole my document is generated from an app and inserted into a table, I want to make this 'document' as a query in a block statement , and split it so I can insert it into another database . — Moudiz, Aug 01 '16 at 13:51
Not sure I follow. Your code puts `process_d.file` into a CLOB; is that the XML document? Then what is `p_xml`? If you already have a CLOB as a variable then I'm not sure what the issue is. Are you trying to split one of the CLOBs into 32k chunks so you can store it as varchar2? Or are you trying to [construct a CLOB from shorter string literals](http://stackoverflow.com/a/17359646/266304) before calling your procedure? — Alex Poole, Aug 01 '16 at 13:56
@AlexPoole p_xml is added by mistake, yes process_d is my xml document, I want this xml to split it into 4000 chunck . i want to make it as a sql to insert it into the database — Moudiz, Aug 01 '16 at 14:06

Alex Poole · Accepted Answer · 2016-08-01T14:26:18.153

I don't know why you'd want to split a CLOB up into 4k chunks for storage, since your value is already happily stored as a CLOB... but if you really wanted to, you can use a hierarchical query:

create or replace procedure sp_insert_xml (p_id in int) as
  l_xml clob;
  l_len pls_integer;
  l_chunksize pls_integer := 4000;
begin
  select xml into l_xml from process_d where process_id = p_id;

  l_len := dbms_lob.getlength(l_xml);
  dbms_output.put_line(l_len);

  insert into test_id (id, chunk_id, chunk_text)
  select p_id, level, dbms_lob.substr(l_xml, l_chunksize, (l_chunksize * (level - 1)) + 1)
  from dual connect by level <= ceil(l_len / l_chunksize);
end;
/

Or you could use recursive subquery factoring, or a PL/SQL loop:

  for l_chunk_id in 0..floor(l_len/l_chunksize) loop
    insert into test_id (id, chunk_id, chunk_text)
    values (p_id, l_chunk_id,
      dbms_lob.substr(l_xml, l_chunksize, (l_chunksize * l_chunk_id) + 1));
  end loop;

but with the recursive CTE or connect-by you don't really need a procedure, you can do it in plain SQL.

score 1 · Answer 2 · answered Aug 01 '16 at 14:48

1

Try to use sqlldr insted of sqlplus. You don't need to chunk lob into smaller pcises

 1. Save your clobs into files(test.xml,test2.xml)

 2. On destination DB create destination table

    create table clob_table( name varchar2(100), doc clob);

 3. create control file for sqlldr.



    LOAD DATA 
    INFILE *
    append
       INTO TABLE clob_table
       FIELDS TERMINATED BY ','
       (name    char(100),
        doc      LOBFILE(name) TERMINATED BY EOF
        )
    BEGINDATA
    test.xml
    test2.xml


 5. execute sqlldr `sqlldr user/pass@dest_db control=load.ctl`

answered Aug 01 '16 at 14:48

Arkadiusz Łukasiewicz

6,241
1
11
17

The question seems to be about splitting an existing CLOB into smaller chunks, not loading from a file. Which seems an odd thing to do and the question isn't entirely clear; but from comments the value is not coming from a file. – Alex Poole Aug 02 '16 at 08:49
In 2nd comment he said he want to insert document to other dataase. For that he needs splliting clob into chukns. – Arkadiusz Łukasiewicz Aug 02 '16 at 09:09
Because you can't use a LOB over a DB link? OK, I didn't pick up on that. I'd probably use datapump in that case, but it still isn't really clear what's really needed. – Alex Poole Aug 02 '16 at 09:12

how to insert large data in a table using bind variable

2 Answers2