0

I am bit lost with working with 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;

/

I got error numeric or value error invalid lob locator specified ora-22275

I followed up this SO answer because thats what I needed is to split the blob and move them .but I didnt understand why he used dbms_lob.createtemporary

Community
  • 1
  • 1
Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • Your answer is here: http://stackoverflow.com/questions/33742687/ora-22275-invalid-lob-locator-specified – atokpas Oct 14 '16 at 07:31
  • A LOB that is passed to package DBMS_LOB cannot be a variable initialized by EMPTY_CLOB() as this does not create a valid lob locator. It must either be initialized by selecting the clob from the database or by using DBMS_LOB.CREATETEMPORARY – mohsen.b Oct 15 '16 at 09:18
  • @mohsen.b can you provide me an example so i look into it ? – Moudiz Oct 15 '16 at 09:40
  • please look at below examples on scott schema at answer – mohsen.b Oct 15 '16 at 09:56
  • posted as answer and delete the comment please @mohsen.b – Moudiz Oct 15 '16 at 10:01
  • 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; / – mohsen.b Oct 15 '16 at 10:02

1 Answers1

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;
/
mohsen.b
  • 436
  • 2
  • 8