Oracle CLOB

#1

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.

#2

On Sep 20, 12:19*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:

Quote:

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

#3

On Sep 21, 7:14*am, Thomas Olszewicki <Thom... (AT) cpas (DOT) com> wrote:

Quote:

On Sep 20, 12:19*pm, The Magnet <a... (AT) unsu (DOT) 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?

#4

The Magnet wrote:

Quote:

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:

How does PHP connect to the database? OCI8?

#5

On Sep 21, 12:22*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:

Quote:

The Magnet 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:

How does PHP connect to the database? * OCI8?

Yes, it uses OCI8. I'm thinking there is a limitation somewhere
there, but I do not know enough about that. Are you familiar with
OCI8?

#6

The Magnet wrote:

Quote:

On Sep 21, 12:22 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) be> wrote:
The Magnet 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:

How does PHP connect to the database? OCI8?

Yes, it uses OCI8. I'm thinking there is a limitation somewhere
there, but I do not know enough about that. Are you familiar with
OCI8?

Yes, but I remember trying to store pictures in the database, without succes.

In the example here (http://wiki.oracle.com/page/PHP+Oracle+FAQ), the blob is loaded from a file.

Don't forget the limits of pure SQL. Maybe you can only update when p_body < 4k.

But you provide only enough information for guess-work. There's quite a number of places where
things can go wrong.

#7

On Sep 22, 1:29*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:

Quote:

The Magnet wrote:
On Sep 21, 12:22 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) be> *wrote:
The Magnet 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:

How does PHP connect to the database? * OCI8?

Yes, it uses OCI8. *I'm thinking there is a limitation somewhere
there, but I do not know enough about that. *Are you familiar with
OCI8?

Yes, but I remember trying to store pictures in the database, without succes.

In the example here (http://wiki.oracle.com/page/PHP+Oracle+FAQ), the blob is loaded from a file.

Don't forget the limits of pure SQL. * Maybe you can only update when p_body < 4k.

But you provide only enough information for guess-work. *There's quite a number of places where
things can go wrong.

That is so strange. Here is the procedure declaration:

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

Thoughts?

#8

The Magnet wrote:

Quote:

On Sep 22, 1:29 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) be> wrote:
The Magnet wrote:
On Sep 21, 12:22 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) be> wrote:
The Magnet 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:

How does PHP connect to the database? OCI8?

Yes, it uses OCI8. I'm thinking there is a limitation somewhere
there, but I do not know enough about that. Are you familiar with
OCI8?

Yes, but I remember trying to store pictures in the database, without succes.

In the example here (http://wiki.oracle.com/page/PHP+Oracle+FAQ), the blob is loaded from a file.

Don't forget the limits of pure SQL. Maybe you can only update when p_body< 4k.

But you provide only enough information for guess-work. There's quite a number of places where
things can go wrong.

That is so strange. Here is the procedure declaration:

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

Thoughts?

The same as before: the update statement can only handle 4K.

Have a look at the manuals on how to use dbms_lob package inside your procedure.

#9

On Sep 22, 3:57*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:

Quote:

The Magnet wrote:
On Sep 22, 1:29 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) be> *wrote:
The Magnet wrote:
On Sep 21, 12:22 pm, "Gerard H. Pille"<g... (AT) skynet (DOT) be> * *wrote:
The Magnet 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:

How does PHP connect to the database? * OCI8?

Yes, it uses OCI8. *I'm thinking there is a limitation somewhere
there, but I do not know enough about that. *Are you familiar with
OCI8?

Yes, but I remember trying to store pictures in the database, without succes.

In the example here (http://wiki.oracle.com/page/PHP+Oracle+FAQ), the blob is loaded from a file.

Don't forget the limits of pure SQL. * Maybe you can only update when p_body< *4k.

But you provide only enough information for guess-work. *There's quite a number of places where
things can go wrong.

That is so strange. *Here is the procedure declaration:

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

Thoughts?

The same as before: the update statement can only handle 4K.

Have a look at the manuals on how to use dbms_lob package inside your procedure.

Well, sorry for being like this. But a CLOB can handle 4GB, right?
So, I would think the problem is between PHP and Oracle, maybe
something in the communication?

#10

The Magnet wrote:

Quote:

Well, sorry for being like this. But a CLOB can handle 4GB, right?
So, I would think the problem is between PHP and Oracle, maybe
something in the communication?

Read my lips: Oracle SQL can only handle 4K.