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

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