Connecting to an external Oracle instance with DBTNG

No, it doesn't.
It is written with the new 7.x abstraction api.

Sorry greggles, my fault. Probably i do not know enough about the DBTNG project to answer you correctly, i've rapidly checked the docs, and to me seems that: Drupal 6.x + DBTNG + Oracle 7.x module should work.
I think you have to place the oracle 7.x driver folder "oracle" into the dbtng module "database" directory with the others (mysql,pgsql...). And then configure the $db_url in your settings.php properly.

If you can test it and post back the result it would be great.

Thanks, andrea

The main purpose of the Oracle driver module is to enable Drupal to use an Oracle Database as the backend instead of MySQL, but it can also be used together with DBTNG module to integrate an external Oracle Database while continuing to use MySQL or PgSQL or yet another Oracle instance. I've updated the project page with a reference to DBTNG.

I'd just like to report my experience with Andrea's Oracle Driver. It may help others who are thinking of using it , or trying to install it and having problems.

First of all I have a little experience with PHP and none with Drupal. The bulk of my experience is with Java and Tomcat.

I work for a Canadian company that make pension administration systems that are sold around the world and I was given the job of creating a "Client Zone" at work, i.e. a place for our clients to access documentation, patches, current news, etc.

I played around with Drupal and it seemed just the tool for the job. However we are an Oracle shop so if I couldn't make Drupal run on Oracle it would be a real tough sell to my colleagues. So when I heard about Andrea's Oracle Driver I was interested right away.

My software platform is:
Windows XP
Oracle 10g Release 10.2.0.4.0.
XAMPP 1.7.2 which incudes:
Apache 2.2.12 (IPV6 enabled)
MySQL 5.1.37 (Community Server) with PBXT engine 1.0.08-rc
PHP 5.3.0 + PEAR (PEAR, Mail_Mime, MDB2, Zend)
Drupal 6.14 with only the core modules,

I followed the instructions that came with Andreas Oracle driver, and after a few roadblocks I got it working. Here are a few of the issues I came across.

1) Doing the install of Drupal with the Oracle driver I got the warnings:
Warning: openlog() expects parameter 3 to be long, string given in C:\xampp\htdocs\drupal\includes\database.oracle.inc on line 4

Warning: openlog() expects parameter 3 to be long, string given in C:\xampp\htdocs\drupal\includes\install.oracle.inc on line 8

It turned out Andreas used LOG_LOCAL0 as the facility in the call to openlog, but this isn’t defined in Windows, only LOG_USER is. Changing LOG_LOCAL0 to LOG_USER cleared the error.

2) I found the complete lack of log messages annoying, but then I discovered that, out of the box, my php.ini file had logging turned off. Actually I’m not certain of this. I just adjusted a few values and log message began appearing. It could also be that I was looking in the wrong place for log messages.

3) Out of the box, my php.ini file had OCI8 support turned off. This resulted in the Data Configuration screen in the Drupal install being followed by a blank page and, of course, nothing at all appearing in Oracle. I also had to set the Oracle environmental variables (e.g. ORACLE_HOME, LD_LIBRARY_PATH). My installation of Oracle relied on Windows Registry entries and didn’t have then set. See http://www.php.net/manual/en/oci8.setup.php for more information.

4) After finally getting through to Oracle I got the exception:
[30-Nov-2009 15:22:04] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 24344 OCIStmtExecute: OCI_SUCCESS_WITH_INFO: ORA-24344: success with compilation error
(ext\pdo_oci\oci_statement.c:146)' in C:\xampp\htdocs\drupal\includes\database.oracle.inc:31
Stack trace:
#0 C:\xampp\htdocs\drupal\includes\database.oracle.inc(31): PDOStatement->execute(NULL)
#1 C:\xampp\htdocs\drupal\includes\install.oracle.inc(242): oracle_query('create or repla...')
…
And found out that Oracle was choking on carriage returns in the scripts, for example on the php statement:
oracle_query("create or replace package drupal_timestamp
as
...
end;");
it would never get past "drupal_timestamp ".

It turned out that this was a problem caused by carriage returns in the script. They weren't in the original scripts but were placed there when I unzipped the scripts using WinZip (actually Andrea recommends using tar). When I configured WinZip not to add the carriage returns everything ran fine and now I have Drupal running on Oracle.

5) I have noted one more issue on the database side. Drupal creates a table named ACCESS which is invalid. Curiously, Oracle will let you create it but when you go to use it you get Invalid Table Name, e.g.:
SQL> select * from ACCESS;

select * from ACCESS
ORA-00903: invalid table name

SQL>

This is because ACCESS is a reserved word in Oracle.

I’ve emailed Andrea about this and so far this hasn’t created problems in Drupal.

Anyway Andrea, thank you for your effort and for all your help during my install.

I would encourage others to try out the Oracle driver and report their experience.

you have to query it like this:

select * from "ACCESS"

This is true for any Oracle reserved world like

select * from users where "UID"= 1

Great job Jim, this was absoltely usefull, being the first Windows installation...

Sorry to re-open this issue, but I was attempting the same thing as greggles and wanted to report on how I did it.

Used the following:

• Started with Oracle/PDO extensions for PHP enabled, along with the Oracle dll files
• Drupal 6.16
• DBTNG 6.x-1.0-unstable3
• Oracle 7.x-1.x-dev

made a new "oracle" folder in sites/all/modules/dbtng/databases, and copied all of the oracle driver's *.inc files over.

changed $db_url to an array, with a default mysql database, and a new oracle database ("oracle://...").

I did have to comment out 2 lines in sites/all/modules/dbtng/oracle/database.inc,
line 24: is a require() that's already loaded by autoload
line 149: is a function only defined in drupal 7

now I can make queries like

  dbtng_set_active('my_oracle_db');
  $query = 'SELECT ... FROM ...';
  $arguments = array();
  $options = array('target' => 'my_oracle_db');
  $results = dbtng_query($query, $arguments, $options);
  ...
  [do something with results]
  ...
  dbtng_set_active('default');

Ok i tested it too.

The following problems should be solved:

1. prefetch.inc the Oracle driver uses it like this:
include_once DRUPAL_ROOT . '/includes/database/prefetch.inc';

and it obviously break in drupal 6.x. I can do a conditional include but i am wondering if
the Oracle driver is the right place to do this. Probably there should be a better way to include the
prefetch.inc in 7.x drupal so that DBTNG can emulate it.

2. the function _db_check_install_needed() is not defined in drupal 6.x so it can be defined by DBTNG module?

3. I noticed that when somenthing goes wrong with a query the driver throws an exception that in drupal 7.x
is handled but in DBTNG is not...

i'm going to ask greggles and mike_p what they think...

1. I'm not sure what the best way to handle this would be. If a conditional include is required, it seems like it would fit better in the Oracle driver than DBTNG, but I agree that a solution in DBTNG would be preferred. I'll try to ping Crell for thoughts on this.

2. In the 6.x version of DBTNG, I've removed the _db_check_install_needed function, since DBTNG for D6 cannot be used to install Drupal, however seeing this use case, I think mocking it in dbtng.module is a reasonable solution.

3. I'd have to look at this and see where exceptions are thrown from, but this could be a sticky issue for DBTNG, and I'm not sure that it should remove exception, but I'm not sure if it would be able to catch all exceptions either without patching core. I'll try to ping someone that is more up on how D7 handle this to get an idea for a solution.

Can you describe the details for this step?

"Started with Oracle/PDO extensions for PHP enabled, along with the Oracle dll files"

I have a Drupal installation using the same versions you describe but I'm getting an error

"Fatal error: Uncaught exception 'PDOException' with message 'could not find driver' in C:\wamp\www\drupalSandboxOracle\sites\all\modules\dbtng\database\database.inc:336"

I'm not sure how to configure PHP properly to use the Oracle drivers. I'm trying to create an application that uses mySQL by default but has the ability to query and run stored procedures on an Oracle database for logging that is shared with another system. Please help or point me in the right direction for more info.

Thanks for your time.

You should search your installation to see if you get a pdo_oci.ddl and find pdo_oci in your php.ini and decomment the line:
;extension=pdo_oci.ddl

Thank you @kscheirer for your description. That's exactly what I'm trying to do. I have everything in place, but I'm getting a "server unexpectedly dropped the connection" when I even try to do something as simple as dbtng_driver(). Could you verify that I have the $db_url values correct? Here's what I have:

<?php
$db_url = array('default' => 'mysql://username:password@localhost/database',
                'my_oracle_db' => 'oracle://username:password@server:port/SID');
?>

Thanks!

looks good to me

Just wanted to note that it appears that _db_check_install seems to have been removed from D7.

Can anyone help me out with getting pdo_oci enabled on a Linux system?

I've followed kscheirer's steps in #9, and I'm getting the same error as mullman in #13 ("Fatal error: Uncaught exception 'PDOException' with message 'could not find driver' in .../sites/all/modules/dbtng/database/database.inc:336").

I checked my php.ini and I can't find any reference to the extension pdo_oci there, just oci8.so. Here's my system info:

Red Hat Enterprise Linux AS release 4 (Nahant Update 7)
Apache 2.0
MySQL 5.0.27
PHP 5.2.0
Oracle Instant Client Version 11.1

Also, after setting this up, I am getting the warning referenced in #11 (warning: include_once(DRUPAL_ROOT/includes/database/prefetch.inc) [function.include-once]: failed to open stream: No such file or directory in .../sites/all/modules/dbtng/database/oracle/database.inc on line 24.). But I suppose that's a separate issue around the conditional include.

Thanks for your help.

Sorry, I glossed over that step in my last post - exactly because its different for everyone's servers and setup. Mostly I used instructions from http://wiki.oracle.com/page/PHP+Oracle+FAQ, like #5.

Mikey/andrea - maybe a hook_requirements() to test for proper php configuration? dbtng could test for generic PDO drivers, and then specificically for each installed .inc file's database.

Currently for D6, the DBTNG module requires PHP 5.2 or higher. PDO is included with PHP 5.1 or higher, and while I could look at adding a check for each PDO driver (I'm sure something like this exists in Drupal 7) I don't think it'd be worth the time or effort for the relatively few users that may not have the correct PDO drivers installed.

Karl, such a check would really need to only iterate over the defined DB connection strings. If someone isn't using SQLite and doesn't have the correct drivers installed, I don't want to throw a warning, for example.

Hello Andrea:
At the outset, best compliments on all your contributions to Oracle. I've been trying to get Drupal connect to Oracle since about 2 days. I followed all the steps from the threads that were required. I'm sort of new to Drupal but tons of Oracle experience. I may add some value to the Drupal Oracle connections. May I ask if you have a step by step instructions on Drupal-Oracle connections?

Thanks,

mikey, you're right. I posted a hook_requirements patch in the dbtng queue #761098: Oracle driver compatibility and general 7.x driver issues. I think it's a good place to add tests to help people get feedback during installation.

may be driver verision 7.x-1.10 helps... added support for external db (not necessarily drupal schemas)
still not tested with 6.x

Hi Jim,

Can you illustrate below step:
It turned out that this was a problem caused by carriage returns in the script. They weren't in the original scripts but were placed there when I unzipped the scripts using WinZip (actually Andrea recommends using tar). When I configured WinZip not to add the carriage returns everything ran fine and now I have Drupal running on Oracle.

for me it shows database set up completed but after that a blank page. It does not show fields to configure site. I have attached a screen shot for the same.

I have below configurations -
Drupal 6.24
XAMPP 1.7.3
PHP Version 5.3.1
Oracle 11.1.0.1g
PDO Driver for OCI 8 and later enabled

gauri open another issue your post is off topic here...
check your apache error_log

Hey Andrea,

Thanks. After going through different forums on this site, I was finally able to install drupal 6.24 with with oracle 11g enterprise edition successfully.

Hello,I am using drupal 6 with mysql as a back-end database but i have to fetch some data from an oracle database.
Ths versions are
drupal 6.16
Xamp 1.7.1
PHP 5.2.9
DBTNG 6.x-1.0-rc4
Oracle 7.x-1.x-dev
I have the following error when i try to fetch data.
Fatal error: Class 'DatabaseStatementPrefetch' not found in D:\xampp\htdocs\dhadrupal6\sites\all\modules\dbtng\database\oracle\database.inc on line 673

I m using the following code to fetcg data from oracle db.

  dbtng_set_active('my_oracle_db');
  $query = 'SELECT * FROM "user_detail"';
  $arguments = array();
  $options = array('target' => 'my_oracle_db');
  $results = dbtng_query($query, $arguments, $options);
foreach ($results as $record) {
              print $record;
             }
  dbtng_set_active('default');

Please help me to fix this issue.

Well, I wrote that post a few years ago - it would be pretty surprising if that method still worked.

I just checked out the oracle module, and it looks like it's come a long way since then. I think you can just download the 6.x-1.1 version of oracle module and be done with it. That would mean no hacking of the D7 driver file, and you may not even need the dbtng module.

D7 reached its EOL back in January 2025, and there is no active release for D7 for this module anymore.
Development or support is not planned for D7. All D7-related issues are marked as outdated in a bunch.

Everyone can apply the patches/suggestions above (not tested by the maintainers, tested by the community) to their D7 projects.
If the issue remains relevant for D10+ versions, merge requests with proposed solutions for a new module version (D10+) are welcome in a new follow-up issue.

Thanks!