NCLOB support in OCI8

1053685

I'm currently developing a multilingual website with PHP and Oracle and I'm facing a problem concerning NCLOBs. I know that the "The Underground PHP and Oracle Manual" states that all NCHAR-related data types are not supported. However, apparently NCHAR and NVARCHAR2 do work just fine. So I'm wondering how up to date this information is. Anyway, the problem is that I can't write to NCLOB columns via OCI-Lob::write. The call succeeds and returns the number of chars written, but the column stays empty. Is there any hope I can get this to work? If not, is there another library that I could use to connect PHP with the database that supports NCLOBs?

I'm using the following code:

$s = oci_parse($dbc, "INSERT INTO trouble (xnclob) values (EMPTY_CLOB()) returning xnclob into :lob");

$lob = oci_new_descriptor($dbc);

oci_bind_by_name($s, "lob", $lob, -1, SQLT_CLOB);

$result = oci_execute($s, OCI_NO_AUTO_COMMIT);

$lob->write($data); // Succeeds, but column stays empty!

oci_commit($dbc);

The code seems to work fine with BLOBs and normal CLOBs. Out test server is running the following software:

CentOS 6.4 64-Bit

PHP 5.3.3

OCI8 1.4.9

Oracle 11g Release 2 XE

Thanks for the help in advance.

Best regards

David

Christopher Jones-Oracle

The choices are (1) use UTF8 as the base character set and don't use

NCHAR/NVARCHAR/NCLOB or (2) submit patches to PHP OCI8 to add NCLOB

support or (3) use another language.

I wouldn't trust NCHAR/NVARCHAR to work consistently in PHP OCI8,

despite (or because of) there being automatic mapping to non N* types

in some cases.

I know you might be using XE 11.2 just for testing. However, if you

are targeting that edition for production use, the character set is

AL32UTF8 so there isn't a direct need to use NCHAR/NVARCHAR/NCLOB.

From Choosing a Character Set:

"Oracle recommends using SQL CHAR, VARCHAR2, and CLOB data types in [a] AL32UTF8
database to store Unicode character data. Use of SQL NCHAR,NVARCHAR2, and NCLOB

should be considered only if you must use a database whose database character set

is not AL32UTF8."

Regarding CentOS, I would recommend using Oracle Linux which is free

to download & install, has free patches etc, has a faster kernel than

RHEL available, and is the OS we test the DB on.  You can get it from

http://public-yum.oracle.com/

If possible target a newer version of PHP.  PHP 5.3 is in security

fix-only mode, and this will end soon.  I would recommend building

your own PHP, or using Zend Server (either the free or paid edition).

For new development, use PHP 5.5.

Christopher Jones-Oracle

The choices are (1) use UTF8 as the base character set and don't use

NCHAR/NVARCHAR/NCLOB or (2) submit patches to PHP OCI8 to add NCLOB

support or (3) use another language.

I wouldn't trust NCHAR/NVARCHAR to work consistently in PHP OCI8,

despite (or because of) there being automatic mapping to non N* types

in some cases.

I know you might be using XE 11.2 just for testing. However, if you

are targeting that edition for production use, the character set is

AL32UTF8 so there isn't a direct need to use NCHAR/NVARCHAR/NCLOB.

From Choosing a Character Set:

"Oracle recommends using SQL CHAR, VARCHAR2, and CLOB data types in [a] AL32UTF8
database to store Unicode character data. Use of SQL NCHAR,NVARCHAR2, and NCLOB

should be considered only if you must use a database whose database character set

is not AL32UTF8."

Regarding CentOS, I would recommend using Oracle Linux which is free

to download & install, has free patches etc, has a faster kernel than

RHEL available, and is the OS we test the DB on.  You can get it from

http://public-yum.oracle.com/

If possible target a newer version of PHP.  PHP 5.3 is in security

fix-only mode, and this will end soon.  I would recommend building

your own PHP, or using Zend Server (either the free or paid edition).

For new development, use PHP 5.5.

1053685

I am using XE only for testing. Our production database, unfortunately, has a character set of WE8MSWIN1252. But now I'm considering opening another database with UTF8. Seems to make more sense.

Thanks a lot for all the info! This was really helpful.