Re: CLOB Anyone?

From: The Magnet <art_at_unsu.com>
Date: Fri, 27 Aug 2010 08:37:02 -0700 (PDT)
Message-ID: <ac342850-b588-4d25-8fed-f2b71344ec8a_at_f20g2000pro.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

Ok, may have forgotten to mention this: This updated works fine when I call it directly from SQLPLUS, but, when the application is run using the website, no update takes place.

Does that help at all? Received on Fri Aug 27 2010 - 10:37:02 CDT