sql - how to insert large data in a table using bind variable -

i trying insert large xml file length 43000 clob.

asktom remcomanded using bind variables links broken.

so question how insert large xml file in bind variable . procedure

create or replace procedure sp_insert_xml ( p_id in int, p_xml in clob )  begin declare x  clob;  y  number(10);  begin select file x process_d process_id =1;  select dbms_lob.getlength(x) y dual;  dbms_output.put_line(y); end;  --insert test_id values (p_id, p_xml); end; 

i want split length can insert them table

i don't know why you'd want split clob 4k chunks storage, since value happily stored clob... if wanted to, can use hierarchical query:

create or replace procedure sp_insert_xml (p_id in int)   l_xml clob;   l_len pls_integer;   l_chunksize pls_integer := 4000; begin   select xml l_xml process_d process_id = p_id;    l_len := dbms_lob.getlength(l_xml);   dbms_output.put_line(l_len);    insert test_id (id, chunk_id, chunk_text)   select p_id, level, dbms_lob.substr(l_xml, l_chunksize, (l_chunksize * (level - 1)) + 1)   dual connect level <= ceil(l_len / l_chunksize); end; / 

or use recursive subquery factoring, or pl/sql loop:

  l_chunk_id in 0..floor(l_len/l_chunksize) loop     insert 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 recursive cte or connect-by don't need procedure, can in plain sql.