Pdo_oci in PHP 7

[wwebdevelop]

I was two days trying to install the extension pdo_oci in PHP7 on a CentOS 7, however when running the command "pecl install pdo_oci" returns me a message saying that the extension is deprecated for version 7 of PHP.

When trying to compile PHP extension as the PHP manual also generates errors.

The concern is, how Yii works with PDO believe that there is no support today for Oracle database. In fact PHP 7 does not support the Oracle database via pdo_oci

I spoke via chat with Wez Furlong, creator of pdo_oci, and he said he no longer works on it for years.

Accessing the page PECL, there is a warning that pdo_oci is no longer maintained: https://pecl.php.net/package/PDO_OCI

Only with the OCI8 extension could not connect to the oracle. Is there any way to do it?

I think that would be the time Yii incorporate into your core something that circumvents this issue. Or in the case of Oracle, using the OCI8 - that developed and actively maintained by Oracle.

[samdark]

Have you tried PHP PDO drivers such as https://github.com/yajra/pdo-via-oci8 ?

[wwebdevelop]

I'll try this: https://github.com/apaoww/yii2-oci8 ... Is it good?

But anyway, I think this issue critical to the global adoption of yiiframework

[samdark]

I have no idea if it's good. If it is, please confirm it here. We'll add it to offical guide.

[wwebdevelop]

I tested both and had problems in CRUD and to connect to Oracle.

Unfortunately, they are not fully ripe libraries.

PHP 7 and YiiFramework without access to Oracle database via PDO layer :)

[kathysledge]

@wwebdevelop you can compile the extension

[wwebdevelop]

Hello df2, I tried to compile with PHP but gave error.
PHP Startup: Unable to load dynamic library '/usr/lib64/php/modules/pdo_oci.so' - /usr/lib64/php/modules/pdo_oci.so: undefined symbol: php_pdo_register_driver in Unknown on line 0

[kathysledge]

@wwebdevelop if it matches your setup you can try this:

/usr/local/php7/lib/php/extensions/no-debug-non-zts-20151012/oci8.so
Download oci8.so.zip

[wwebdevelop]

The OCI8 extension is installed.

I can not is to install the pdo_oci

[SilverFire]

Try this manual. I did not test it on PHP7, but maybe it will work

[wwebdevelop]

No success :(

[SilverFire]

What about these:

https://blogs.oracle.com/opal/entry/major_news_php_7_0
https://blogs.oracle.com/opal/entry/php_7_oci8_2_1

[samdark]

These aren't PDO.

[wwebdevelop]

That's right, Samdark.

There's been a misunderstanding. You see, there is nothing wrong with the OCI8 extension. It continues to be maintained and actively increased by Oracle.

Now you see the problem involving Yii. Access to databases of Yii2 was entirely Constructed under the PDO layer.

Only now, with version 7 of PHP, there is no active and valid extension pdo_oci. I spoke via Facebook with Wez Furlong, founder of pdo_oci extension - it does not work more with it.

See: https://pecl.php.net/package/PDO_OCI

Thus, Yii does not work with Oracle databases when running in an environment with PHP 7.

In my humble opinion, Yii should use from the beginning OCI8 and OCI11 extension since they were created by Oracle are actively maintained and have numerous features and optimizations that pdo_oci not have.

Yii in their next releases must implement a solution to work around this limitation, under penalty of having their impaired worldwide adoption. The Lavarel, for example, is supported via OCI8.

For me this is extremely worrying regarding the adoption of the framework for enterprise applications involving Oracle databases.

[samdark]

The link I've posted above (https://github.com/yajra/pdo-via-oci8) is PDO based on oci8 from the same guy who maintains unofficial Laravel driver.

There are more:

• https://github.com/intersvyaz/pdo-oci8
• https://github.com/apaoww/yii2-oci8
• https://github.com/SergioMadness/oci8Pdo
• https://github.com/neutrinobg/yii2-oci2pdo
• https://github.com/crazycodr/pdo-via-oci8
• https://github.com/nineinchnick/pdo_oci8
• https://github.com/alfmel/pdo-via-oci8

[bobonov]

If you read carefully the article posted by SilverFire
https://blogs.oracle.com/opal/entry/major_news_php_7_0
It say that an updated version of oci8 is avail via pecl but the new version of pdo_oci is only in source:

Thanks also to the Oracle staff who have worked on the internal rewrite necessary to make the OCI8 and PDO_OCI extensions work with PHP 7's completely revamped Extension API.

The Oracle Database OCI8 and PDO_OCI extensions are included in the PHP source distribution.

PDO_OCI will remain solely part of the core PHP source distribution.

In fact if you download php7 sources you find it, and looking at code comments you can easily verify that is for php7
The only pitfall is that you need to compile and install it by hand and not via pecl, I give you some steps on how to.

At first you need to install oracle client and sdk, use the full version of the oracle client.
Unfortunately I installed them ages ago and I don't remember the various steps to make them ready to compile pdo_oci and oci8.
By memory the steps I made should be the same you find here https://github.com/tassoevan/pdo-oci-extension#first-step-install-the-oracle-client
DO NOT FOLLOW the compiling instruction of the above link since they are for php5

The instruction to compile pdo_oci are for debian/ubuntu system, but is easy to adapt for other systems.

Php7 source download
mkdir /opt/php7
cd /opt/php7
apt source -y php7.0
Note: ensure you have deb-src enabled in your source.list

Compiling
cd php7.0-7.0.8/ext/pdo_oci
phpize
export ORACLE_HOME=/opt/oracle/instantclient/
./configure
make
make test

Installing
cd module
cp pdo_oci.so /usr/lib/php/20151012/
cd /etc/php/7.0/mods-available
create a file pdo_oci.ini with
; configuration for php pdo_oci module
extension=pdo_oci.so

enable the new php module
phpenmod pdo_oci

restart php fpm or apache if you installed php as apache mod

service php7.0-fpm restart

Works done!
Just a side note, strangely restarting php fpm didn't load the module, while at command line was working. In the end I had to restart the computer to have the module loaded on php-fpm.

Hope it helps others. Maybe some note can be inserted in the manual so the people know that pdo_oci has been revamped by Oracle and yii2 is fully working with php7.

[Foxprodev]

Getting
SQLSTATE[HY000]: OCIEnvNlsCreate: Check the character set is valid and that PHP has access to Oracle libraries and NLS data (/opt/php7/php7.0-7.0.8/ext/pdo_oci/oci_driver.c:619)

The problem is charset in connection string
oci:dbname=//192.168.1.4/DECK;charset=UTF8'

Any ideas?

[bobonov]

Are you sure you are using Basic oracle client ?
The Basic Lite client has only usa charset therefore oracle goes in error at connection time if you use a charset that is not supported.
Check your instant client dir for this file libociei.so (104MB more or less), if you miss it, Basic Lite version installed
Download
Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications
and not
Instant Client Package - Basic Lite: Smaller version of the Basic package, with only English
Just substitute the client, you do not need to recompile.

[Foxprodev]

Yes, of course. I am using Basic client and I've got this file.
I've tried with rpm package too

[bobonov]

Does it connect by using plain connection? (by removing charset=UTF8 so if use default charset)
If yes the problem is with the charset, if no the problem can be something about the configuration.

[Foxprodev]

Yes, it connects without charset, but we are getting incorrect data.

[bobonov]

I'm looking pdo_oci code. In oci_drive.c there is the part that initialize the connection.

/* allocate an environment */
#if HAVE_OCIENVNLSCREATE
if (vars[0] . optval) {
    H->charset = OCINlsCharSetNameToId(pdo_oci_Env, (const oratext *)vars[0] . optval);
if (!H->charset) {
        oci_init_error("OCINlsCharSetNameToId: unknown character set name");
        goto cleanup;
    } else {
        if (OCIEnvNlsCreate(&H->env, PDO_OCI_INIT_MODE, 0, null, null, null, 0, null, H->charset, H->charset) != OCI_SUCCESS) {
            oci_init_error("OCIEnvNlsCreate: Check the character set is valid and that PHP has access to Oracle libraries and NLS data");
            goto cleanup;
        }
}
}
#endif

It seem that is not a problem with an unknown charset but more that the driver is unable to locate the instantclient (gogoled around about OCIEnvNlsCreate)
Did you moved the directory of the instantclient after compiling?
Have you try to set ORACLE_HOME pointing at you client dir?

[Foxprodev]

Yes, I've tried to set ORACLE_HOME and LD_LIBRARY_PATH in php-fpm config and in php-fpm init.d script. Nothing changed for me.

[Foxprodev]

Side note: sqlplus works fine with my ORACLE_HOME

[bobonov]

Saw you answer after posting .
Lets try something else, in you db connection define the on after connect function

        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'oci:dbname=//localhost:1521/XE',
            'username' => 'MYDB',
            'password' => 'password',
            'on afterOpen' => function($event) {
                //$event->sender refers to the DB connection
                $event->sender->createCommand("ALTER SESSION SET NLS_TERRITORY = 'ITALY'")->execute();

Change according you country. The above set various things charset, numeric separator, date format ordering and so on.
Eventually you can change only the charset setting
'php LS_LANG = [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET'
So for italy should be something like (just try UTF8)

 $event->sender->createCommand("ALTER SESSION SET NLS_LANG = 'ITALIAN_ITALY.UTF8'")->execute();

[Foxprodev]

in yii/db/Connection.php

 $stmt=$this->pdo->prepare("alter session set NLS_TERRITORY = 'RUSSIA'");
   $stmt->execute();
 $stmt=$this->pdo->prepare("ALTER SESSION SET NLS_LANG = 'RUSSIAN_RUSSIA.UTF8'");
   $stmt->execute();

Nothing changed.
Additionaly: Looks like there is no NLS_LANG varriable in session.

[bobonov]

Hummm using
$event->sender->createCommand("ALTER SESSION SET NLS_TERRITORY = 'ITALY'")->execute();
solved my char problem...
But you mean you still get the error?

Looks like there is no NLS_LANG varriable in session.
Yes looking deeper on the web it can only be set at environment level.

Are you running your sqlplus with the same user of your php/webser?
Maybe you defined ORACLE_HOME only at your user level.
Check your phpinfo() to see if there is ORACLE_HOME setted

[Foxprodev]

But you mean you still get the error?

Yes. 😞

[Foxprodev]

in phpinfo()

ORACLE_HOME /usr/lib/oracle/12.1/client64
LD_LIBRARY_PATH /usr/lib/oracle/12.1/client64/lib
NLS_LANG    AMERICAN_AMERICA.AL32UTF8

[Foxprodev]

@bobonov Thank you for replies.
NLS_LANG = RUSSIAN_CIS.AL32UTF8
Works for me.

[bobonov]

I had difficult too using the charset via dsn, but in my case no error but oracle was ignoring it.
Probably, but didn't investigate it, the problem is that there is not UTF8 charset in oracle but is AL32UTF8.
When I'll have time I'll make some test.

[vystepanenko]

Hello, @bobonov
I compiled pdo_oci.so via your instruction. And have problem with charset while connecting to Oracle 12c.
I tried to set different enviroment variable like
NLS_LANG=RUSSIAN_CIS.AL32UTF8
NLS_LANG=UKRAINIAN_UKRAINE.AL32UTF8
also tried your suggestion about
$event->sender->createCommand("ALTER SESSION SET NLS_TERRITORY = 'RUSSIA'")->execute();
Nothing help.
Is there any hope that this pdo_oci will work fine with dsn like
"oci:dbname=//localhost:1521/XE;charset=AL32UTF8" ??

ps. We try it in OpenServer+php7 and it work fine when you adding charset to dsn

[vystepanenko]

I can solve the problem :)
We use Oracle Linux 7. OL7 use for service(like php-fpm) not user environment variables but systemctl variables. so for make it work you need to set up this variables
systemctl set-environment NLS_LANG=UKRAINIAN_UKRAINE.AL32UTF8
systemctl set-environment ORACLE_HOME=path to oracle home or to client home
systemctl set-environment LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

[cofirazak]

Solved this problem today. In my case the problem was only in 1 environment variable: ORACLE_HOME.
If i run the script with php-cli, then ORACLE_HOME is set to /usr/lib and everything works fine, so charset=UTF8 in dsn is ok.
But if i run the same script with php-fpm, then ORACLE_HOME is not set, and only this is the reason for charset=UTF8 in dsn to break the runtime.

So my solution was add this:
putenv('ORACLE_HOME=/usr/lib');
to the action method of my controller.
Don't need to set no other variables.
So setting NLS_LANG, LD_LIBRARY_PATH or C_INCLUDE_PATH is redundant.

[stefanlam88]

Did anyone face PHP Startup: Unable to load dynamic library '/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20151012/pdo_oci.so'

i got that file but cant load. know why?

[bobonov]

How did you got the file? Did you compile it? On the target machine did you install the oracle client?

[stefanlam88]

hi bobonov, I managed to compile and get the pdo_oci.so. But when I load and print phpinfo. The php error log shows this.

I get from Php source
https://github.com/php/php-src/tree/master/ext/pdo_oci

I successfully compile with oracle client.

but fail to load. u guys can load?

[bobonov]

Which server (ubuntu, centos...) are you running and which version?
Did you get exactly the source of same php version you have installed on your server?
Is the oracle client present on the target machine? I mean if you compile locally and uploaded the file on the server you need the oracle client on the server as well.

if you do at command line:
ls -la /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20151012/pdo_oci.so
does it find the file?

[stefanlam88]

i m using mac. for local use. not server

[stefanlam88]

version PHP 7.0.13

[stefanlam88]

Yes bro. The file is there.

[Jai-veer]

I am PHP 7.1.30 with apache2.4.34 on Debian. I faced the same error. I installed three packages using dpkg.

• oracle-instantclient12.2-basic_12.2.0.1.0-2_amd64.deb
• oracle-instantclient12.2-devel_12.2.0.1.0-2_amd64.deb
• oracle-instantclient12.2-sqlplus_12.2.0.1.0-2_amd64.deb

Post installation, We need to remove the libphp.so file from apache modules to get it replaced when we recompile and install php. When recompiling use these options,
--with-oci8
--with-pdo-oci
Recompile with ./Configure ... --with-oci8 --with-pdo-oci ... . Post make and make install, restart apache.

I'm using oci in 2019 it still works I think its not true that it php 7+ version won't support Oracle with pdo-oci.

[thattejada]

Thanks @samdark your suggestions were too useful for me