OraFAQ Forum: SQL & PL/SQL » Initialize a CLOB Locator?

Home » SQL & PL/SQL » SQL & PL/SQL » Initialize a CLOB Locator?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Initialize a CLOB Locator? [message #270092]

Tue, 25 September 2007 13:01

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

It's been years since I've dealt with CLOBS and am having an issue with doing an insert into the clob column of a table.

Oracle Version: 9.2.0.7.0

I've already spent more time on this than I should have and am looking for some guidance. I have browsed the web, but could not find an example of initializing a CLOB locator.

Sorry for such an simple question.

 
SQL> create table marc_test_clob (col1 clob);
Table created

SQL> create or replace procedure marc_insert_Clob(vi_clob clob) as
  2  begin
  3  insert into marc_test_clob(col1) values (vi_clob);
  4  end;
  5  /
Procedure Created

SQL> create or replace procedure marc_call_clob_ins as
  2  v_clob clob;
  3  begin
  4  dbms_lob.createtemporary(v_clob,false);
  5  dbms_lob.writeappend(v_clob,length('TEST'),'TEST');
  6   dbms_lob.freetemporary(v_clob);
  7  marc_insert_clob(v_clob);
  8  end;
  9  /

Procedure created

begin marc_call_clob_ins; end;

ORA-22275: invalid LOB locator specified
ORA-06512: at "SFMFG.MARC_INSERT_CLOB", line 6
ORA-06512: at "SFMFG.MARC_CALL_CLOB_INS", line 7
ORA-06512: at line 1

When I try this:

 
SQL> create or replace procedure marc_insert_Clob(vi_clob clob) as
  2  begin
  3  insert into marc_test_clob(col1) values (vi_clob);
  4  end;
  5  /

begin marc_call_clob_ins; end;

ORA-22275: invalid LOB locator specified
ORA-06512: at "SFMFG.MARC_INSERT_CLOB", line 3
ORA-06512: at "SFMFG.MARC_CALL_CLOB_INS", line 7
ORA-06512: at line 1

I've tried playing with the Empty_clob() function and creating a new clob via createtemporary in the proc marc_insert_Clob and setting that equal to the parameter.

That fails with the same error.

Thanks

[Updated on: Tue, 25 September 2007 14:23]

Report message to a moderator