Re: Oracle CLOB

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Tue, 21 Sep 2010 05:14:54 -0700 (PDT)
Message-ID: <d454ce77-0998-4b72-bb43-151b7720287e_at_h25g2000vba.googlegroups.com>

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 Received on Tue Sep 21 2010 - 07:14:54 CDT