Re: CLOB Anyone?

From: Rob Burton <burton.rob_at_gmail.com>
Date: Fri, 27 Aug 2010 04:33:25 -0700 (PDT)
Message-ID: <51c84ad1-7afc-4106-a08c-462a04c3f1ba_at_5g2000yqz.googlegroups.com>

On Aug 26, 10:23 pm, The Magnet <a..._at_unsu.com> wrote:
> Om, I'm bleeping upset as I've been at this for 3 days and been all
> over the net with nothing.  Maybe a genius from here can help.  We are
> on 10g.
>
> Task:  Update a CLOB column.  Actually replace the value, not
> appending.
> Code is this:
>
> PROCEDURE update_message_by_message_id (
>   p_message_id       NUMBER,
>   p_sec_id           NUMBER,
>   p_variables        VARCHAR2,
>   p_name             VARCHAR2,
>   p_body             CLOB,
>   p_changed_by       VARCHAR2,
>   p_status           VARCHAR2) IS
> .
> v_lob_locator        CLOB :=  EMPTY_CLOB();
> .
> .
>   DBMS_LOB.OPEN (v_lob_locator,DBMS_LOB.LOB_READWRITE);
>   DBMS_LOB.WRITE(v_lob_locator, LENGTH(p_body),1,p_body);
>   DBMS_LOB.CLOSE(v_lob_locator);
>
> Tried EVERYTHING.  From a traditional UPDATE statement to this.
> Everything I try gives this error, EVERYTHING:
>
> ORA-22275: invalid LOB locator specified
>
> Even tried:  UPDATE messages SET body = p_body WHERE message_id =
> p_message_id;
>
> ORA-22275: invalid LOB locator specified
>
> Any help?  Please.......

Just updating the clob should work fine. Your problem does sound familiar and looking at some old notes your issue might me you want v_lob_locator CLOB := NULL (and not EMPTY_CLOB() ) - I think empty_clob creates a locator to a different location.

ie
 >create table test_clob (a clob);
Table created.
> insert into test_clob values ('clob_data');
1 row created.
> select * from test_clob;

A

---

clob_data
> update test_clob set a = 'updated clob';
1 row updated.
> update test_clob set a = to_clob('updated_clob');
1 row updated.
1> select * from test_clob;
A

---

updated_clob Received on Fri Aug 27 2010 - 06:33:25 CDT