Re: Oracle CLOB
From: The Magnet <art_at_unsu.com>
Date: Tue, 21 Sep 2010 06:42:13 -0700 (PDT)
Message-ID: <da4e52b7-9865-4510-b3bc-60334278b136_at_t3g2000vbb.googlegroups.com>
On Sep 21, 7:14�am, Thomas Olszewicki <Thom..._at_cpas.com> wrote:
> On Sep 20, 12:19�pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > Ok, this is probably simple, what I'm lost. �FYI: �We're on 10gR2.
>
> > I'm trying to update a CLOB column. �The procedure is being called
> > from PHP. �If the INSERT procedure is called then the object gets
> > inserted into the table properly:
>
> > TABLE:
> > �MESSAGE_ID � � � � � � � � � � � � � � � � � � � � NUMBER
> > �SEC_ID � � � � � � � � � � � � � � � � � � � � � � NUMBER
> > �VARIABLES � � � � � � � � � � � � � � � � � � � � �VARCHAR2(500)
> > �NAME � � � � � � � � � � � � � � � � � � � � � � � VARCHAR2(500)
> > �BODY � � � � � � � � � � � � � � � � � � � � � � � CLOB
> > �CREATED � � � � � � � � � � � � � � � � � � � � � �DATE
> > �LAST_CHANGED � � � � � � � � � � � � � � � � � � � DATE
> > �CREATED_BY � � � � � � � � � � � � � � � � � � � � VARCHAR2(100)
> > �CHANGED_BY � � � � � � � � � � � � � � � � � � � � VARCHAR2(100)
> > �STATUS � � � � � � � � � � � � � � � � � � � � � � VARCHAR2(100)
>
> > � INSERT INTO messages
> > � �(message_id, sec_id, variables, name, body, created, last_changed,
> > created_by, changed_by, status)
> > � VALUES (v_message_id, p_sec_id, p_variables, p_name, v_body,
> > SYSDATE, SYSDATE,
> > � � � � � p_created_by, p_changed_by, p_status);
>
> > That seems to work fine, but the UPDATE now, that is not doing
> > anything. �It does not return any errors, it just does not update
> > anything. �If the message I insert is small, say 40 lines, it updates
> > fine. �But say I take a huge HTML page and paste it into the editor a
> > few times and try to update, nothing happens.
>
> > � UPDATE messages
> > � SET sec_id � � � = NVL(p_sec_id, sec_id),
> > � � � variables � �= NVL(p_variables, variables),
> > � � � name � � � � = NVL(p_name, name),
> > � � � body � � � � = NVL(p_body,body),
> > � � � last_changed = SYSDATE,
> > � � � changed_by � = NVL(p_changed_by, changed_by),
> > � � � status � � � = NVL(p_status, status)
> > � WHERE message_id = p_message_id;
>
> > So, I'm lost. �Any suggestions may help. �If I am not providing some
> > information you need, let me know. �And thanks in advance.
>
> Remove NVL(p_body,body), replace it with just p_body.
> Test the empty condition of p_body outside the update statement.
> NVL can take a numeric or varchar2 parameter, but not LOB.
> HTH
> Thomas
Date: Tue, 21 Sep 2010 06:42:13 -0700 (PDT)
Message-ID: <da4e52b7-9865-4510-b3bc-60334278b136_at_t3g2000vbb.googlegroups.com>
On Sep 21, 7:14�am, Thomas Olszewicki <Thom..._at_cpas.com> wrote:
> On Sep 20, 12:19�pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > Ok, this is probably simple, what I'm lost. �FYI: �We're on 10gR2.
>
> > I'm trying to update a CLOB column. �The procedure is being called
> > from PHP. �If the INSERT procedure is called then the object gets
> > inserted into the table properly:
>
> > TABLE:
> > �MESSAGE_ID � � � � � � � � � � � � � � � � � � � � NUMBER
> > �SEC_ID � � � � � � � � � � � � � � � � � � � � � � NUMBER
> > �VARIABLES � � � � � � � � � � � � � � � � � � � � �VARCHAR2(500)
> > �NAME � � � � � � � � � � � � � � � � � � � � � � � VARCHAR2(500)
> > �BODY � � � � � � � � � � � � � � � � � � � � � � � CLOB
> > �CREATED � � � � � � � � � � � � � � � � � � � � � �DATE
> > �LAST_CHANGED � � � � � � � � � � � � � � � � � � � DATE
> > �CREATED_BY � � � � � � � � � � � � � � � � � � � � VARCHAR2(100)
> > �CHANGED_BY � � � � � � � � � � � � � � � � � � � � VARCHAR2(100)
> > �STATUS � � � � � � � � � � � � � � � � � � � � � � VARCHAR2(100)
>
> > � INSERT INTO messages
> > � �(message_id, sec_id, variables, name, body, created, last_changed,
> > created_by, changed_by, status)
> > � VALUES (v_message_id, p_sec_id, p_variables, p_name, v_body,
> > SYSDATE, SYSDATE,
> > � � � � � p_created_by, p_changed_by, p_status);
>
> > That seems to work fine, but the UPDATE now, that is not doing
> > anything. �It does not return any errors, it just does not update
> > anything. �If the message I insert is small, say 40 lines, it updates
> > fine. �But say I take a huge HTML page and paste it into the editor a
> > few times and try to update, nothing happens.
>
> > � UPDATE messages
> > � SET sec_id � � � = NVL(p_sec_id, sec_id),
> > � � � variables � �= NVL(p_variables, variables),
> > � � � name � � � � = NVL(p_name, name),
> > � � � body � � � � = NVL(p_body,body),
> > � � � last_changed = SYSDATE,
> > � � � changed_by � = NVL(p_changed_by, changed_by),
> > � � � status � � � = NVL(p_status, status)
> > � WHERE message_id = p_message_id;
>
> > So, I'm lost. �Any suggestions may help. �If I am not providing some
> > information you need, let me know. �And thanks in advance.
>
> Remove NVL(p_body,body), replace it with just p_body.
> Test the empty condition of p_body outside the update statement.
> NVL can take a numeric or varchar2 parameter, but not LOB.
> HTH
> Thomas
Thomas,
Previously tried that with no luck. I think it is something with the way PHP is connecting to Oracle with a VARCHAR that exceeds a certain number of bytes. Do you know anything about this? Maybe PHP has some limitations? Received on Tue Sep 21 2010 - 08:42:13 CDT