Re: CLOB Anyone?

From: mhoys <matthias.hoys_at_gmail.com>
Date: Fri, 27 Aug 2010 04:41:17 -0700 (PDT)
Message-ID: <b22fd05c-210c-4232-9669-920173c882d4_at_g17g2000yqe.googlegroups.com>

On Aug 26, 11: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.......

Strange... does this code work for you?

CREATE TABLE clobs(cid int, clob_data clob)

INSERT INTO clobs(cid,clob_data)
VALUES(1,'test')

COMMIT; CREATE OR REPLACE PROCEDURE update_message_by_message_id (

  p_message_id       NUMBER,
  p_body             CLOB

)
IS
BEGIN
UPDATE clobs SET clob_data = p_body WHERE cid = p_message_id; END; BEGIN
update_message_by_message_id(1,'updated'); COMMIT;
END; SELECT * FROM clobs;

Matthias Received on Fri Aug 27 2010 - 06:41:17 CDT