Forum Stats

  • 3,826,346 Users
  • 2,260,631 Discussions
  • 7,896,910 Comments

Discussions

NCLOB support in OCI8

1053685
1053685 Member Posts: 19
edited Nov 14, 2013 8:22AM in PHP

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

Tagged:

Best Answer

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,648 Employee
    Answer ✓

    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.

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,648 Employee
    Answer ✓

    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
    1053685 Member Posts: 19

    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.

This discussion has been closed.