Working with dbms_lob.copy and error ORA-06502:

Question

我有点失去了与dbms_lob.copy的工作

CREATE OR REPLACE PROCEDURE Ex_PRC IS
 dest_lob CLOB;
 src_lob  CLOB;
BEGIN
  SELECT F_CLOB INTO dest_lob
  FROM EX_EMPLOYEE
  WHERE id = 1;
dbms_lob.copy (dest_lob, src_lob, 30, 1, 1);
  COMMIT;
END;

/

我得到错误numeric or value error invalid lob locator specified ora-22275

我跟进了这个SO的答案，因为我需要的是分裂blob并移动它们，但我不明白为什么他使用dbms_lob.createtemporary

Answer 1

set serveroutput on
create or replace procedure test_clob (p_clob_res out clob) is
  cursor c_tabs is
  select ename from emp;
  v_clob clob;
  amt integer := 0;
begin
  dbms_lob.createtemporary(v_clob,true,dbms_lob.session);
  for r_tabs in c_tabs
  loop
    dbms_lob.writeappend(v_clob,length(r_tabs.ename)+1,r_tabs.ename||' ');
    amt := amt + length(r_tabs.ename);
  end loop;
  p_clob_res := v_clob;
end test_clob;
/
create or replace procedure call_clob is
  p_clob clob;
  my_buff varchar2 (2000);
  amt binary_integer := 2000;
begin
  test_clob(p_clob);
  my_buff := dbms_lob.substr(p_clob,amt,1);
  dbms_output.put_line(my_buff);
end call_clob;
/
begin
  call_clob();
end;
/