Note: You are currently viewing documentation for Moodle 3.6. Up-to-date documentation for the latest stable version of Moodle is likely available here: Installing Oracle for PHP.

Installing Oracle for PHP: Difference between revisions

From MoodleDocs
m (Fixed broken link. Credits goes to Visvanath Ratnaweera (https://moodle.org/mod/forum/discuss.php?d=380452#p1533665))
 
(16 intermediate revisions by 11 users not shown)
Line 1: Line 1:
* http://www.oracle.com/technology/pub/notes/technote_php_instant.html
* http://www.oracle.com/technetwork/articles/technote-php-instant-084410.html
* http://moodle.org/mod/forum/discuss.php?d=65488#p308002 (with attached html document)
* http://moodle.org/mod/forum/discuss.php?d=65488#p308002 (with attached html document)
* http://es.php.net/oci8
* http://es.php.net/oci8


----


'''Important Note:''' Don't forget to enable this variable in your php.ini (or .htaccess)  file:
== Installing Moodle on Windows with Oracle Express Edition ==


(else, all your data will be escaped following MySQL rules, that are incorrect for Oracle)
=== Introduction ===
This section explains how to install Moodle with Oracle Express Edition on Windows. I'm using it for '''debugging''' purpose. It's definitively not a production environment. The goal is to setup easily and quickly a Moodle/Windows/Oracle environment.


    magic_quotes_sybase = On
=== Install Oracle ===
# Download Oracle Express Edition on [http://www.oracle.com/technology/products/database/xe/index.html Oracle web site].
# You will also need the Instant client [http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html from here] (free account sign up needed). Copy everything from the unzipped folder into apache/bin.
# Install both.<br />
# Access to the oracle console at http://127.0.0.1:8080/apex (Login=SYS Password=the_one_you_entered_during_the_installation).
# Create a new user and give it all rights (including DBA).
# Go to the SQL Commands page and grant your new user the dmbs_lock permission...<code sql>
grant execute on dbms_lock to XXXXX;
</code>...where XXXXX is the username of your new user.
# Log out and log in as the new user.
# Run the script found in the Moodle codebase at /lib/dml/oci_native_moodle_package.sql. If you can not run this script directly, you can run its parts (separated by slashes) independently via the SQL Commands page.


Note: Oracle Express Edition 10g is limited at one database called 'XE'.


== Broken versions of OCI8 driver ==
=== Set up your Oracle extension ===
 
PHP v5.2.4 (and perhaps v5.2.3 as well) have shipped with a bug in the OCI8 driver that leaks statement handles. The version of the OCI8 driver with the bug is v1.2.4. Possible workarounds:


* Upgrade to PHP v5.2.5 or later
This document does not explain how to setup apache/php for Oracle. You can have more information on [http://www.oracle.com/technology/tech/php/htdocs/php-oracle-tutorial.html Oracle Documentation].
* Downgrade to PHP v5.2.1 (reported to work)
* Downgrade only the oci8 driver to the one included in PHP 5.2.1 (it worked for us -- iarenaza)
* If you are on linux and/or can compile PECL extensions, install an older OCI8 driver (v1.2.3 seems to work) from the PECL repository http://pecl.php.net/package/oci8


More information at
Edit your phi.ini file, uncomment (remove the leading semicolon) the php_oci8 extension. In a WAMP setup, this is listed as...
* http://bugs.php.net/bug.php?id=42496 -- tracking the PHP OCI8 bug
* http://tracker.moodle.org/browse/MDL-11429 -- diagnostics on the Moodle side


== Installing Moodle on Windows with Oracle Express Edition ==
<code>
extension=php_oci8.dll
</code>


=== Introduction ===
Also, if you are going to use the installation for development/testing purposes, uncomment the following line and set the value to zero. It's not needed for normal operations (it causes a BIG slowdown of the system!).
This section explains how to install Moodle with Oracle Express Edition on Windows. I'm using it for '''debugging''' purpose. It's definitively not a production environment. The goal is to setup easily and quickly a Moodle/Windows/Oracle environment.


=== Install Oracle ===
<code>
Download Oracle Express Edition on [http://www.oracle.com/technology/products/database/xe/index.html Oracle web site].<br />
oci8.statement_cache_size = 0
You will also need the Instant client [http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html from here] (free account sign up needed). Copy everything from the unzipped folder into apache/bin. <br />
</code>
Install both.<br />
Access to the oracle console (http://127.0.0.1:8080/apex). Login: SYS Password: the_one_you_entered_during_the_installation<br />
Create a new user and give it all rights (including DBA).<br />
Note: Oracle Express Edition 10g is limited at one database called 'XE'.


=== Set up your oracle extension ===
You may also need to uncomment php_oracle, php_pdo_oci, php_pdo_oci8 in some set-ups.
This document does not explain how to setup apache/php for Oracle. You can have more information on [http://www.oracle.com/technology/tech/php/htdocs/php-oracle-tutorial.html Oracle Documentation]. On my own machine I used my WAMP installation ([http://www.en.wampserver.com/ Wampserver 2.0]) which allowed me to activate all oracle extensions in few clicks (php_oci8, php_oracle, php_pdo_oci, php_pdo_oci8).


=== Install Moodle ===
=== Install Moodle ===
On the database setup page:<br />
On the database setup page:<br />
Driver: Oracle oci8<br />
Driver: Oracle oci8 (in the config file it should read "oci8po" for a 1.9.x install and "oci" for a 2.0.x install)<br />
Host: empty the field<br />
Host: empty the field<br />
Database: //localhost:1521/XE<br />
Database: //localhost:1521/XE<br />
User: the user that you created<br />
User: the user that you created<br />
Password: the password you gave to the user<br />
Password: the password you gave to the user<br />
=== Configure Apache ===
If you're running Moodle on Oracle with Apache on Linux, you might have issues with PHP being able to see the system environment variables.  To resolve this, you can edit /etc/sysconfig/apache2 and add the following lines to the bottom:<br />
<br />
LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2# Set LANG Variables for UTF-8<br />
NLS_LANG=AMERICAN_AMERICA.AL32UTF8<br />
LANG=en_US.UTF-8<br />
LC_ALL=en_US.UTF-8<br />
LC_COLLATE=en_US.UTF-8<br />
LC_CTYPE=en_US.UTF-8<br />
LC_MESSAGES=en_US.UTF-8<br />
LC_MONETARY=en_US.UTF-8<br />
LC_NUMERIC=en_US.UTF-8<br />
LC_TIME=en_US.UTF-8<br />
NLS_NUMERIC_CHARACTERS='.,'<br />
export LANG LC_ALL LC_COLLATE LC_CTYPE LC_MESSAGES LC_MONETARY LC_NUMERIC LC_TIM NLS_LANG LD_LIBRARY_PATH NLS_NUMERIC_CHARACTERS<br />
<br />
Also need to add those two lines to ~/.bashrc so that the PHP command-line client can see it too. <br />
<br />
== Installing Oracle Express on Ubuntu ==
I needed to test an installation of Oracle with Moodle, here's what I did
* Ubuntu 14.04.1 LTS 64 bit
* Oracle 11g Release 2 Express Edition
* SQL Developer 4.0.3.16
=== Oracle Express and SQL Developer ===
Ignore all other sites and follow these instructions - http://blog.whitehorses.nl/2014/03/18/installing-java-oracle-11g-r2-express-edition-and-sql-developer-on-ubuntu-64-bit/
Log out of Chrome before doing this bit though because Chrome uses the same folder and it will instantly crash.
sudo rm -rf /dev/shm
sudo mkdir /dev/shm
sudo mount -t tmpfs shmfs -o size=4096m /dev/shm
Also, change 4096m to whatever half your memory is.
If you get a fatal error when running sqldeveloper then
gksudo gedit /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper
and add these 2 lines at the top
unset GNOME_DESKTOP_SESSION_ID
unset DBUS_SESSION_BUS_ADDRESS
Found the answer here http://linuxsagas.digitaleagle.net/2014/01/28/fixing-sql-developer-4-0/
After installing and when creating a connection, the default username is SYSTEM and use the password created in the step sudo /etc/init.d/oracle-xe configure
In gnome classic - The menus for Oracle are under Other, sqldeveloper is under Programming
=== Create a user ===
Probably easiest to use the application express:
* Go to "Get Started With Oracle Database 11g Express Edition" - either from the applications -> other menu (in gnome) or via the desktop icon that was created when installing.
* This will open a page in your browser.
* Login as SYSTEM
* Click on the application express tab
* Create a user and password
* Then go to SQL Developer (In Gnome classic - go to applications -> programming -> sqldeveloper) and create and open a connection using the newly created user
=== PHP ===
Follow these instructions - https://help.ubuntu.com/community/PHPOracle
Add extension=oci8.so to both /etc/php5/apache2/php.ini and /etc/php5/cli/php.ini otherwise the client php cron won't work
Restart apache2
Then test with this
<pre>
<?php
error_reporting( -1 );
ini_set( 'display_errors', 1 );
$conn = oci_connect('USERNAME', 'password', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Prepare the statement
$sql = "SELECT * FROM atable"; // Change this.
$stid = oci_parse($conn, $sql);
if (!$stid) {
    $e = oci_error($conn);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
    $e = oci_error($stid);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Fetch the results of the query
print "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print "<tr>\n";
    foreach ($row as $item) {
        print "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
    }
    print "</tr>\n";
}
print "</table>\n";
oci_free_statement($stid);
oci_close($conn);
</pre>


== Related links ==
== Related links ==
* [http://lewiscarr.co.uk/node/4 Installing ORACLE drivers with PHP]
*[http://lewiscarr.co.uk/2009/01/installing-oracle-drivers-with-php/ Installing ORACLE drivers with PHP]
*[[Installing MSSQL for PHP]]
*[[Installing MSSQL for PHP]]
*[[PHP]]
*[[PHP]]
Line 56: Line 176:
*[http://moodle.org/mod/forum/discuss.php?d=65488 Who uses Oracle]
*[http://moodle.org/mod/forum/discuss.php?d=65488 Who uses Oracle]
*[https://docs.moodle.org/en/Step-by-step_Install_Guide_for_Solaris_10_with_Oracle_10 Step by step Install Guide for Solaris 10 with Oracle 10]
*[https://docs.moodle.org/en/Step-by-step_Install_Guide_for_Solaris_10_with_Oracle_10 Step by step Install Guide for Solaris 10 with Oracle 10]
*[http://learningischange.com/2009/05/29/install-moodle-on-an-oracle-database-in-25-minutes-or-less/ Install Moodle on an Oracle Database (in 25 minutes or less)]
*[http://learningischange.com/2009/05/29/install-moodle-on-an-oracle-database-in-25-minutes-or-less/ Install Moodle on an Oracle Database (in 25 minutes or less)]. The related Using Moodle forum discussion is [http://moodle.org/mod/forum/discuss.php?d=124629 here].
*[http://www.mguhlin.org/2009/05/moodle-on-oracle-database.html Moodle on Oracle Database] discussion on Miguel Guhlin's blog


[[Category:Installation]]
[[Category:Installation]]
[[Category:Developer]]
[[Category:XMLDB]]
[[Category:XMLDB]]
[[Category:DB]]
[[Category:DB]]
[[Category:SQL databases]]

Latest revision as of 20:45, 2 January 2019


Installing Moodle on Windows with Oracle Express Edition

Introduction

This section explains how to install Moodle with Oracle Express Edition on Windows. I'm using it for debugging purpose. It's definitively not a production environment. The goal is to setup easily and quickly a Moodle/Windows/Oracle environment.

Install Oracle

  1. Download Oracle Express Edition on Oracle web site.
  2. You will also need the Instant client from here (free account sign up needed). Copy everything from the unzipped folder into apache/bin.
  3. Install both.
  4. Access to the oracle console at http://127.0.0.1:8080/apex (Login=SYS Password=the_one_you_entered_during_the_installation).
  5. Create a new user and give it all rights (including DBA).
  6. Go to the SQL Commands page and grant your new user the dmbs_lock permission...

grant execute on dbms_lock to XXXXX; ...where XXXXX is the username of your new user.

  1. Log out and log in as the new user.
  2. Run the script found in the Moodle codebase at /lib/dml/oci_native_moodle_package.sql. If you can not run this script directly, you can run its parts (separated by slashes) independently via the SQL Commands page.

Note: Oracle Express Edition 10g is limited at one database called 'XE'.

Set up your Oracle extension

This document does not explain how to setup apache/php for Oracle. You can have more information on Oracle Documentation.

Edit your phi.ini file, uncomment (remove the leading semicolon) the php_oci8 extension. In a WAMP setup, this is listed as...

extension=php_oci8.dll

Also, if you are going to use the installation for development/testing purposes, uncomment the following line and set the value to zero. It's not needed for normal operations (it causes a BIG slowdown of the system!).

oci8.statement_cache_size = 0

You may also need to uncomment php_oracle, php_pdo_oci, php_pdo_oci8 in some set-ups.

Install Moodle

On the database setup page:
Driver: Oracle oci8 (in the config file it should read "oci8po" for a 1.9.x install and "oci" for a 2.0.x install)
Host: empty the field
Database: //localhost:1521/XE
User: the user that you created
Password: the password you gave to the user

Configure Apache

If you're running Moodle on Oracle with Apache on Linux, you might have issues with PHP being able to see the system environment variables. To resolve this, you can edit /etc/sysconfig/apache2 and add the following lines to the bottom:

LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2# Set LANG Variables for UTF-8
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
LANG=en_US.UTF-8
LC_ALL=en_US.UTF-8
LC_COLLATE=en_US.UTF-8
LC_CTYPE=en_US.UTF-8
LC_MESSAGES=en_US.UTF-8
LC_MONETARY=en_US.UTF-8
LC_NUMERIC=en_US.UTF-8
LC_TIME=en_US.UTF-8
NLS_NUMERIC_CHARACTERS='.,'

export LANG LC_ALL LC_COLLATE LC_CTYPE LC_MESSAGES LC_MONETARY LC_NUMERIC LC_TIM NLS_LANG LD_LIBRARY_PATH NLS_NUMERIC_CHARACTERS

Also need to add those two lines to ~/.bashrc so that the PHP command-line client can see it too.

Installing Oracle Express on Ubuntu

I needed to test an installation of Oracle with Moodle, here's what I did

  • Ubuntu 14.04.1 LTS 64 bit
  • Oracle 11g Release 2 Express Edition
  • SQL Developer 4.0.3.16

Oracle Express and SQL Developer

Ignore all other sites and follow these instructions - http://blog.whitehorses.nl/2014/03/18/installing-java-oracle-11g-r2-express-edition-and-sql-developer-on-ubuntu-64-bit/

Log out of Chrome before doing this bit though because Chrome uses the same folder and it will instantly crash.

sudo rm -rf /dev/shm
sudo mkdir /dev/shm
sudo mount -t tmpfs shmfs -o size=4096m /dev/shm

Also, change 4096m to whatever half your memory is.

If you get a fatal error when running sqldeveloper then

gksudo gedit /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper

and add these 2 lines at the top

unset GNOME_DESKTOP_SESSION_ID
unset DBUS_SESSION_BUS_ADDRESS

Found the answer here http://linuxsagas.digitaleagle.net/2014/01/28/fixing-sql-developer-4-0/

After installing and when creating a connection, the default username is SYSTEM and use the password created in the step sudo /etc/init.d/oracle-xe configure

In gnome classic - The menus for Oracle are under Other, sqldeveloper is under Programming

Create a user

Probably easiest to use the application express:

  • Go to "Get Started With Oracle Database 11g Express Edition" - either from the applications -> other menu (in gnome) or via the desktop icon that was created when installing.
  • This will open a page in your browser.
  • Login as SYSTEM
  • Click on the application express tab
  • Create a user and password
  • Then go to SQL Developer (In Gnome classic - go to applications -> programming -> sqldeveloper) and create and open a connection using the newly created user

PHP

Follow these instructions - https://help.ubuntu.com/community/PHPOracle

Add extension=oci8.so to both /etc/php5/apache2/php.ini and /etc/php5/cli/php.ini otherwise the client php cron won't work

Restart apache2

Then test with this

<?php

error_reporting( -1 );
ini_set( 'display_errors', 1 );

$conn = oci_connect('USERNAME', 'password', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Prepare the statement
$sql = "SELECT * FROM atable"; // Change this.
$stid = oci_parse($conn, $sql);
if (!$stid) {
    $e = oci_error($conn);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
    $e = oci_error($stid);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// Fetch the results of the query
print "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print "<tr>\n";
    foreach ($row as $item) {
        print "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : " ") . "</td>\n";
    }
    print "</tr>\n";
}
print "</table>\n";

oci_free_statement($stid);
oci_close($conn);

Related links