Home » SQL & PL/SQL » SQL & PL/SQL » Initialize a CLOB Locator?
Initialize a CLOB Locator? [message #270092] Tue, 25 September 2007 13:01 Go to next message
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

Re: Initialize a CLOB Locator? [message #270104 is a reply to message #270092] Tue, 25 September 2007 14:24 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You are freeing your temporary before calling your insert function.
Re: Initialize a CLOB Locator? [message #270105 is a reply to message #270092] Tue, 25 September 2007 14:27 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
OMG .. thank you ..

Sheesh ever have one of those days ????
Re: Initialize a CLOB Locator? [message #270108 is a reply to message #270092] Tue, 25 September 2007 14:30 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
All the time.
Previous Topic: function
Next Topic: Select query
Goto Forum:
  


Current Time: Sun Dec 04 20:25:43 CST 2016

Total time taken to generate the page: 0.09702 seconds