Re: CLOB Anyone?
Date: Fri, 27 Aug 2010 15:00:07 -0700 (PDT)
Message-ID: <52bd9fd4-219d-4ee4-bae3-a4db25688100_at_j18g2000yqd.googlegroups.com>
On Aug 27, 11: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?
Hi,
Trace is your friend.
This seems drastic to most people, but I solve 95% of our application issues putting trace on everything if necessary. If you can identify the host from which the activity is coming, that makes it even easier. Something like...
begin
for cur in (select sid,serial# from v$session where machine =
'&machine') loop
dbms_monitor.session_trace_enable(cur.sid,cur.serial#,binds=>true,waits=>false);
end loop;
end;
/
...then grep -i for the statement in all trace files under udump on the database server. You may even be seeing an exception that the application "gracefully handles" into the waste basket. If so, you will see err= in the trace file.
HTH, Steve Received on Fri Aug 27 2010 - 17:00:07 CDT