Re: CLOB Anyone?
From: joel garry <joel-garry_at_home.com>
Date: Fri, 27 Aug 2010 09:07:54 -0700 (PDT)
Message-ID: <8154cfed-e639-4141-ac89-bdb508484f60_at_l38g2000pro.googlegroups.com>
On Aug 27, 8:37 am, The Magnet <a..._at_unsu.com> wrote:
> 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?
// *Action: For (1), initialize the LOB locator by selecting into the locator
// variable or by setting the LOB locator to empty. For (2), (3),
// (5) and (6)pass the correct type of locator into the routine.
// For (4), remove the trigger body code that updates the LOB value.
Date: Fri, 27 Aug 2010 09:07:54 -0700 (PDT)
Message-ID: <8154cfed-e639-4141-ac89-bdb508484f60_at_l38g2000pro.googlegroups.com>
On Aug 27, 8:37 am, The Magnet <a..._at_unsu.com> wrote:
> 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?
$ oerr ora 22275
22275, 00000, "invalid LOB locator specified"
// *Cause: There are several causes: (1) the LOB locator was never
// initialized; (2) the locator is for a BFILE and the
routine
// expects a BLOB/CLOB/NCLOB locator; (3) the locator is for
a
// BLOB/CLOB/NCLOB and the routine expects a BFILE locator; // (4) trying to update the LOB in a trigger body -- LOBs in // trigger bodies are read only; (5) the locator is for a // BFILE/BLOB and the routine expects a CLOB/NCLOB locator; // (6) the locator is for a CLOB/NCLOB and the routine expects // a BFILE/BLOB locator;
// *Action: For (1), initialize the LOB locator by selecting into the locator
// variable or by setting the LOB locator to empty. For (2), (3),
// (5) and (6)pass the correct type of locator into the routine.
// For (4), remove the trigger body code that updates the LOB value.
Kinda grasping at straws but, maybe you have some kind of trigger involved? Maybe you are going to a different schema than when you run in sqlplus (where things are defined differently, perhaps through a synonym)?
Also, supply versions and connection configuration. There seem to be bugs in some versions of 9 and 10 odbc or oci, some on character sets. See the bug database. You might even ask support.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/aug/25/which-calif-governor-has-had-best-job-growth/Received on Fri Aug 27 2010 - 11:07:54 CDT