Moudiz Moudiz - 4 months ago 11
SQL Question

how to insert large data in a table using bind variable

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

Answer

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.