Re: CLOB Anyone?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 27 Aug 2010 17:40:19 +0200
Message-ID: <8dq4j5F2cgU3_at_mid.individual.net>



On 27.08.2010 17:37, The Magnet 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?

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/
Received on Fri Aug 27 2010 - 10:40:19 CDT

Original text of this message