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.......

On Aug 26, 10:23*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
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
--------------------------------------------------------------------------------
clob_data
1> select * from test_clob;
A
--------------------------------------------------------------------------------
updated_clob

On Aug 26, 11:23*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
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

On Aug 27, 6:41*am, mhoys <matthias.h... (AT) gmail (DOT) com> wrote:

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

On Aug 27, 6:41*am, mhoys <matthias.h... (AT) gmail (DOT) com> wrote:

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?

On 27.08.2010 17:37, The Magnet wrote:
Maybe then the problem is in the way you call the update from
application code or there is another error which rolls back the TX. Any
errors in logfiles?

Cheers

robert


--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Aug 27, 8:37*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
$ 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
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...st-job-growth/

Magnet:

Funny how you worked on this so hard for 3 days but forgot to include
some basic things in a simple post to a newsgroup. Like for example
some environments it works and others it does not.

Why don't you produce a reproducible test case in sqlplus where it
works and the same thing in the web environment where it fails.

Then trace both of those test cases ...

John Hurley wrote:
The number of layers (and different technologies) between a website and the Oracle database,
makes producing a reproducible test case a work of days. I've done it once, quite sure I was
wasting my time in preparing an SR, but I was lucky enough to stumble on a known bug with one of
the technologies. A part from interesting learning, I'd still wasted my time because the
webdev's had switched to another technology.

Gerard:

# The number of layers (and different technologies) between a website
and the Oracle database, makes producing a reproducible test case a
work of days.

I was not talking about a test case to submit to Oracle.

He has some PLSQL that he says works directly in sqlplus but fails
being driven from the web environment somehow.

My recommendation is to separate it out and trace both cases ...
trying to sort out what is different where. A couple of people
suggested ( and I do not disagree ... these are just ideas ) that
maybe there is some bad error handling somewhere in the web app. At
this point who knows?

The magnet unfortunately seems to post here without ever really giving
enough information or the whole picture. Been that way for a while.