Re: CLOB Anyone?

From: The Magnet <art_at_unsu.com>
Date: Fri, 27 Aug 2010 08:30:41 -0700 (PDT)
Message-ID: <b74a151d-257f-46d0-885e-30498ddc2619_at_b4g2000pra.googlegroups.com>

On Aug 27, 6:41 am, mhoys <matthias.h..._at_gmail.com> wrote:
> 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

Mattias,

No go there:

CREATE OR REPLACE PROCEDURE art (

  p_message_id       NUMBER,
  p_body             CLOB

)
IS
BEGIN
UPDATE clobs SET clob_data = p_body WHERE cid = p_message_id; END;
 /

Procedure created.

BEGIN
  art(1,'updated');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM clobs;

no rows selected Received on Fri Aug 27 2010 - 10:30:41 CDT