What is PHP?
What is Oracle's involvement with PHP
Where can I get more information?
What are the ORA, OCI8 and PDO_OCI extensions
How do I configure PHP to use Oracle?
How do I connect to Oracle with the OCI8 extension?
How do I connect to Oracle with the PDO_OCI driver for the PDO extension?
What are Valid Connection Strings for OCI8 and PDO_OCI?
Why do I get the error "Call to undefined function: oci_connect() / ocilogon()"?
Help I'm not seeing any errors or anything at all!
What if PHP OCI8 is not installed or not found?
Where do I set Oracle environment variables?
What's the difference between oci_connect() and OCILogon(), or between oci_parse() and OCIParse()?
How do I SELECT, INSERT, UPDATE and DELETE data from PHP?
How are database transactions handled in PHP?
How are database errors handled in PHP?
How do I call stored procedures from PHP?
Does PHP offer Oracle connection pooling?
How do I manage connections in OCI8?
How do I check my SQL statements are working?
How can I insert strings containing quotes?
How do I fetch results using associative arrays where the same column name occurs?
What is pre-fetching in OCI8?
How do I use Oracle bind variables in OCI8?
How do I upload LOBS with OCI8?
Should I upgrade to PHP 5?
Can I do Operating System Authentication?
Can I do authentication using AS SYSDBA or AS SYSOPER?
Are NCHAR and NCLOB supported in PHP?
What is Zend Core for Oracle?
What is Zend Server?
How do I install/upgrade OCI8 on Oracle Application Server or Oracle HTTP Server?
What is PHP?
PHP is an open-source, interpreted, HTML-centric, server-side scripting language. PHP is especially suited for Web development and can be embedded into HTML pages. PHP is comparable to languages such as JSP (Java Server Pages) and Oracle's PSP (PL/SQL Server Pages).
PHP is a recursive acronym for "PHP Hypertext Preprocessor."
To test if PHP is working, create a simple PHP document, say hello.php:
If PHP is working, you will see "Hello, world!" below:
Execute hello.php from the command line (php hello.php) or open it from a web browser (http://localhost/hello.php) to see the output.
What is Oracle's involvement with PHP
Oracle is committed to helping the PHP community make an impact on large enterprise developments by delivering stable production environments, native integration with the Oracle Database, and PHP support in the Oracle Application Server. Oracle makes direct contributions to the open source PHP project and assists the PHP community in maintaining PHP's Oracle drivers.
Where can I get more information?
There are many good references:
The PHP Developer Center on OTN has many good resources.
The OTN PHP Discussion forum can help answer questions
The technical note on Installing PHP and the Oracle Instant Client for Linux and Windows is also on OTN.
A free book on how to use PHP and Oracle from Oracle discusses many details about OCI8 best practices.
Various books on PHP and Oracle can be purchased from bookstores.
The PHP OCI8 Reference Manual is the core place for documentation. User annotations give tips and tricks.
The OCI8 test suite in the PHP Source code shows the expected behavior of OCI8 calls in detail.
What are the ORA, OCI8 and PDO_OCI extensions
OCI8 extension: Oracle Call Interface functions
OCI8 is the recommended PHP driver for Oracle. In use at many high profile sites, it provides an easy to use, reliable interface for creating high performance web applications. It supports SQL and PL/SQL calls, and among other features has support for LOBs and collections. The latest release, OCI8 1.3, supports advanced Oracle features such as connection pooling ("DRCP"), allowing better use of database resources even when PHP applications are spread across multiple web tier severs. DRCP scales to tens of thousands of concurrent connections.
PDO_OCI extension: "PHP Data Object" functions
PDO_OCI is the Oracle driver for the PDO data abstraction interface in PHP. While PDO is gaining a little popularity, PDO_OCI isn't as mature as OCI8 and doesn't support some of the features that OCI8 does. The implementation of PDO_OCI8 is separate from OCI8 and provides a different user API.
ORA extension: Oracle functions
The obsolete ORA extension is no longer included in PHP distributions. It had limited functionality and was implemented using a now obsolete API to the Oracle database.
How do I configure PHP to use Oracle?
The OCI8 and PDO_OCI extensions are included in various PHP distributions, starting with the PHP source code and pre-built binaries for Windows available from www.php.net. Various other packages of PHP are built from the source code release. There are PHP RPMs for Linux on oss.oracle.com, which are also available via up2date to Unbreakable Linux Network subscribers. The Oracle drivers are also included in Zend Core for Oracle and its replacement, Zend Server.
If not using a pre-built package, the basic steps to install OCI8 are:
Download PHP from www.php.net and download Oracle Instant Client.
On Windows, edit your php.ini file and uncomment the following line: extension = php_oci8.dll
Ensure that your extension_dir parameter (in php.ini) contains the directory where the above extension library is located.
On non-Windows platforms rebuild PHP using the following configuration option: --with-oci8=shared,instantclient,/path/to/instant/client/libs
Edit your php.ini file and add: extension = oci8.so
Ensure that your extension_dir parameter (in php.ini) points to the location where oci8.so was installed.
Set environment variables required by Oracle, such as PATH (Windows) or LD_LIBRARY_PATH (on Linux)
Restart you webserver.
There are a number of ways PHP can be configured which allows maximum flexibility. The PHP Documentation gives installation steps for various platforms. Oracle has comprehensive PHP Oracle install instructions covering many possible scenarios on Windows and Linux. The Oracle technical note on installing PHP and Oracle using the small foot-print, free Oracle Instant Client libraries is very popular.
How do I connect to Oracle with the OCI8 Extension?
Using the OCI8 extension:
The example above connects to the Oracle database "XE" on the computer "myhost".
The connection string can be an "Easy Connect" string, an Oracle Net alias from the tnsnames.ora file, or a hard coded Oracle Net connection string.
PHP will share/re-use connections if the same user credentials are used more than once in a script or httpd server session. You can use the oci_new_connect() function to ensure a new session is used. Call the oci_pconnect() function to make a persistent connection that isn't closed at the end of the script (making the reconnection in the next script much faster).
How do I connect to Oracle with the PDO_OCI driver for the PDO extension?
With the PDO_OCI driver:
What are Valid Connection Strings for OCI8 and PDO_OCI?
The connection string can be an "Easy Connect" string, an Oracle Net alias from the tnsnames.ora file, or a hard coded Oracle Net connection string:
1. If Oracle 10g or 11g libraries are used by PHP, try the Easy Connect syntax. No long connection string is needed. No tnsnames.ora network configuration file is required. To connect to the MYDB database service running on mymachine use:
$c = oci_connect('myusername', 'mypassword', 'mymachine.mydomain/MYDB');
$d = new PDO('oci:dbname=myhost/XE', 'myusername', 'mypassword');
If you are using the free Oracle XE database on the same machine as your web server, the connection string would be:
$c = oci_connect('myusername', 'mypassword', '127.0.0.1/XE');
$d = new PDO('oci:dbname=127.0.0.1/XE', 'myusername', 'mypassword');
The Easy Connect syntax only works if PHP is linked with Oracle 10g or more recent libraries. It works even if the database is another version. If PHP is using Oracle 9i libraries this type of connection string will not work. See Oracle's Using the Easy Connect Naming Method documentation for the syntax.
If you see the error "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor", then the service name component is incorrect. On the database server, run
lsnrctl services
to find the service names that will be recognized.
2. A user chosen net service name is often used to identify which database to connect to. By default it is read from the environment variable ORACLE_SID or it can be explicitly given as the third parameter in an OCI8 connection call. The net service name MYDB that is used in Oracle's command-line SQL*Plus utility like:
sqlplus myusername/mypassword@MYDB
can be used in PHP like:
$c = oci_connect('myusername', 'mypassword', 'MYDB');
$d = new PDO('oci:dbname=MYDB', 'myusername', 'mypassword');
The net service name is commonly mapped to an actual database by an entry in a tnsnames.ora file :
MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.mydomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYDB)
)
)
The default file location is $ORACLE_HOME/network/admin/tnsnames.ora. On some operating systems other locations (e.g. $HOME/.tnsnames.ora) will be checked too. If your tnsnames.ora is in a non-standard location you can set the environment variable TNS_ADMIN to the directory containing it. For example if you are using /home/myuser/tnsnames.ora, do this before starting Apache:
TNS_ADMIN=/home/myuser
export TNS_ADMIN
If the net service name used in oci_connect() cannot be found in the tnsnames.ora, or the tnsnames.ora is not found at all by PHP, you may get an error when logging in: Warning: ocilogon(): _oci_open_server: ORA-12154: TNS:could not resolve service name
The error ORA-12154 can also occur if you have a file $ORACLE_HOME/network/admin/sqlnet.ora and it specifies a NAMES.DEFAULT_DOMAIN value. Unqualified net service names in oci_connect() calls will have sqlnet.ora's value for NAMES.DEFAULT_DOMAIN appended to the alias. For example if sqlnet.ora had:
NAMES.DEFAULT_DOMAIN = au.oracle.com
then 'oci_connect("myusername", "mypassword", "mydb")' causes Oracle to look for the alias "MYDB.AU.ORACLE.COM = . . ." in tnsnames.ora. A quick solution is to change the tnsnames.ora entry to:
MYDB.AU.ORACLE.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.mydomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYDB)
)
)
3. If Easy Connect has been disabled, or if you are using Oracle 8i or 9i libraries, another alternative is to hardcode the full connection string in PHP:
$db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST = mymachine.mydomain)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)
(SERVICE_NAME=MYDB)))";
$c = oci_connect('myusername', 'mypassword', $db);
$d = new PDO("oci:dbname=$db", 'myusername', 'mypassword');
Why do I get the error "Call to undefined function: oci_connect() / ocilogon()"?
Check phpinfo() output and make sure there is a section titled OCI8. Load this script in your browser:
The pre-built Windows php_oci8.dll and php_pdo_oci.dll DLLs need Oracle 10.2 client libraries or greater. The php_oci8_11g.dll needs Oracle 11.1 client libraries or greater.
Make sure there is no library clash if multiple versions of Oracle are installed on the machine. On Windows, set PATH or put the Oracle Instant Client libraries in the PHP extension directory.
Make sure that Oracle environment variables are set in the process that starts the webserver. Using PutEnv() in PHP scripts is error prone and non-portable because scripts may be executed after library initialization has already occurred.
There is a section on Common PHP Oracle Connection Errors in the Underground PHP and Oracle manual.
Help I'm not seeing any errors or anything at all!
Check the web server error file to make sure the OCI8 or PDO_OCI extensions are being loaded by using the phpinfo() function
If there are no issues with the extension, edit your php.ini file and set:
display_errors = On
or configure the error log file (and remember to check it!). This will show Oracle errors as they occur, unless you have explicitly prefixed Oracle functions calls with "@". For security reasons, do not run production systems with display_errors set to On.
During development set php.ini's error_reporting directive to
error_reporting = E_ALL | E_STRICT.
Test all return values from OCI8 and PDO_OCI functions to prevent hidden problems and misleading results and use oci_error() to fetch the error message.
What if PHP OCI8 is not installed or not found?
Commonly seen errors are:
The error "Unable to load dynamic library php_oci8.dll" when starting the web server.
The error "The dynamic link library OCI.dll could not be found in the specified path" when starting the web server.
The error "PHP Fatal error: Call to undefined function oci_connect()" or "Call to undefined function OCILogon()" when running a script.
The error "OCIEnvNlsCreate() failed. There is something wrong with your system" when running a script.
PHP must be configured to locate the php_oci8.dll (on Windows) or oci8.so (if using a shared library on Linux) files. PHP must also be able to find Oracle's client libraries.
Make sure that your php.ini file is being found correctly. Create the following script "phpinfo.php" where your web server can read it:
Load phpinfo.php in a browser. If the "Configuration File (php.ini) Path" line shows only a directory path and does not end with "php.ini", then your configuration file is not being read. Move your php.ini to the directory shown and restart your web server.
Check the phpinfo() output and make sure the value of the "extension_dir" directive contains the directory with php_oci8.dll e.g. "c:\php-5.2.9\ext" or the location of oci8.so (if using a shared library on Linux). Check the phpinfo() output and make sure "extension=php_oci8.dll" is uncommented (remove the leading semi-colon). On Linux, if using a shared library, php.ini should contain "extension=oci8.so".
Make sure the Oracle environment variables are set correctly before starting the web server (see the next topic).
Make sure there is no clash if multiple Oracle versions or libraries have been installed.
Windows users will need Oracle 10.2 or 11.1 libraries.
The Oracle directory that PHP links with needs to be readable by the owner of the web server process. If you configure PHP to use Oracle 10gR2 libraries from an ORACLE_HOME style install, and Apache crashes at startup, check the directory permissions on the Oracle software. Oracle 10gR2 is shipped with a more restrictive set of directory permissions than previous Oracle releases. The 10.2.0.2 patchset contains $ORACLE_HOME/install/changePerm.sh to re-set permissions on files required by external programs like PHP. With 10.2.0.2 you may need to add the directories lib32, ldap, ldap/adminm, and srvm/lib32 to the RELAX_DIR_ALL variable in this script before running it. If you don't want to change your ORACLE_HOME permissions, build PHP with Oracle Instant Client.
Linux users might see an Apache error about being unable to load libclntsh.so, but are more likely to notice problems when compiling PHP. The compiler will fail with the error 'Cannot find file "ocidfn.h"' or 'Cannot find file "oci.h"'. If you have Oracle installed but are missing the Oracle header files, do a "Client" install of Oracle. In Oracle 9i download "Oracle9i Database Release 2", run the installer and choose the "Client" option. In Oracle 10g download the "Oracle Database 10g Client Release", run the installer and choose the "Administrator" option. Instead of doing a full Oracle client install, it's convenient and fast to build PHP using the free, small Oracle Instant Client. See the OTN article Installing PHP and the Oracle Instant Client for Linux and Windows.
On Linux, consider using a prebuilt version of PHP, such as the PHP RPMs.
Where do I set Oracle environment variables?
Setting all Oracle environment variables before the web server starts is the only safe way to get PHP talking to Oracle. Setting the variables in PHP scripts or the httpd.conf file mostly does not work. There are numerous mail list and forum posts because of general confusion about the environment. The behavior on Windows and Linux also differs.
The environment needs to be set so PHP can find Oracle's libraries and message files. Environment variables may also be needed to identify the default local database, needed for locating configuration files for remote database access, or needed to set Oracle's globalization parameters correctly.
Using PHP's PutEnv() function is unlikely to work. Using Apache's SetEnv directive is sometimes insufficient.
If Apache starts but gives errors about Oracle libraries, and/or OCI8 function calls fail, try looking at PHP's environment by using the phpinfo() script. Look at the "Environment" section (not the "Apache Environment" section) of phpinfo() output.
The Underground PHP and Oracle Manual has a section "Setting Oracle Environment Variables for Apache" with more information.
An example shell script for starting Apache is "start_apache" :
#!/bin/sh
ORACLE_HOME=/u01/app/oracle/product/9.2
ORACLE_SID=orcl
export ORACLE_HOME ORACLE_SID
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
echo "Oracle Home: $ORACLE_HOME"
echo "Oracle SID: $ORACLE_SID"
echo Starting Apache
#export > /tmp/envvars
./apachectl start
Depending where your database server is and how you connect to it you may also want to set TWO_TASK or TNS_ADMIN (see the next topic). To ensure the correct character set is used, you may also want to set NLS_LANG.
Apache, PHP and Oracle error messages that use phrases like "could not load" or "could not resolve" all point to environment setup problems. If you think you are seeing this, try uncommenting the "export" line in "start_apache". After the script is run, the file /tmp/envvars will contain the environment variables Apache knows about. This is useful to solve problems starting Apache at machine boot time - the environment may be very different to starting Apache in your user shell.
Triple-check the environment and path configuration. On UNIX check that LD_LIBRARY_PATH (or equivalent, e.g DYLD_LIBRARY_PATH on Mac OS X, SHLIB_PATH on HP-UX etc) contains $ORACLE_HOME/lib or your Instant Client library directory. On Windows the PATH variable may need to contain %ORACLE_HOME%\bin or the directory containing Oracle Instant Client.
If you use Oracle Instant Client, you do not need to set ORACLE_HOME.
Some messages like the runtime "OCIEnvNlsCreate() failed. There is something wrong with your system" or the error "ocilogon(): _oci_open_session: OCIHandleAlloc? OCI_HTYPE_SVCCTX: OCI_INVALID_HANDLE" are symptoms of an incorrect ORACLE_HOME.
If oci_connect() gives "ORA-12705 The NLS parameter value specified in an alter session statement is not valid or not implemented", the environment might not consistently use one Oracle installation. Try setting ORA_NLS to the NLS data file directory in the ORACLE_HOME used to compile PHP, e.g. $ORACLE_HOME/ocommon/nls/admin/data.
To give an example of the problems if environment variables are not set correctly, I did some tests to see what effect the basic environment in a standard installation had on a PHP 4 login call:
$mycon = ocilogon("myusername", "mypassword", "MYDB");
I used Red Hat Linux AS 2.1, Apache 1.3 and PHP 4.3.3.
With no ORACLE_HOME set prior to "apachectl start" and no "putenv('ORACLE_HOME=/usr/oracle/MYDB')" in the PHP script I got: Warning: ocilogon(): _oci_open_server: Error while trying to retrieve text for error ORA-12154
This shows the connection failed, and the message files (which are located under the Oracle home directory) could not be found. The Oracle environment is incorrect.
With "putenv('ORACLE_HOME=/usr/oracle/MYDB')" in the PHP script but no ORACLE_HOME set, I got: Warning: ocilogon(): _oci_open_server: ORA-12154: TNS:could not resolve service name
The connection still failed, but now the correct message text could be read from the message file. The Oracle environment is still not correct.
With ORACLE_HOME set prior to "apachectl start" and removing the "putenv()" script call, the connection succeeded. This is the recommended configuration.
With ORACLE_HOME set correctly prior to "apachectl start" and with "putenv()" using an invalid ORACLE_HOME directory, the connection succeeded. I also tried this on Windows. This time the connection failed with the same message as my first test above.
I had a similar set of results when I replaced the PHP putenv() call with an Apache httpd.conf directive "setenv ORACLE_HOME /usr/oracle/MYDB". Having said all this, some variables used only at script run time can be set in your PHP scripts. In a working configuration, the following code changed my default connection and connected to MYDB:
putenv("TWO_TASK=MYDB");
$mycon = oci_connect("myusername", "mypassword");
The environment variables TNS_ADMIN, NLS_DATE_FORMAT (and there may be others) can probably be set in scripts too.
What's the difference between oci_connect() and OCILogon(), or between oci_parse() and OCIParse()?
Some PHP extensions had their function names standardized in PHP 5 to use underscore separation. Both naming styles can still be used in OCI8, but the new style is recommended. New names include:
oci_connect()
oci_parse()
oci_bind_by_name()
oci_execute()
oci_fetch_array()
Old names include
OCILogon()
OCIParse()
OCIBindByName()
OCIExecute()
OCIFetchInto()
Function names in PHP are case insensitive
A complete list of functions is found in the PHP OCI8 Manual
How do I SELECT, INSERT, UPDATE and DELETE data from PHP?
The following example demonstrates how data can be SELECTed and manipulated via INSERT, UPDATE and DELETE statements. The table is assumed to have been previously created:
create table mytable (id number, val varchar2(20));
The PHP code is:
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "
foreach ($row as $item) {
echo "
}
echo "
".PHP_EOL;
}
echo "";
// Delete the row
$stid = oci_parse($conn, "delete from mytable where id = :i_b");
$id = 1;
oci_bind_by_name($stid, ":i_b", $id);
oci_execute($stid);
oci_free_statement($stid);
oci_close($conn);
?>
Using bind variables is recommended for performance.
Tables can be created using the oci_parse() and oci_execute() pair of calls. However, as with all Oracle applications, DDL and DCL statements are only recommended during application installation and not during application runtime.
How are database transactions handled in PHP?
By default, PHP will autocommit whenever oci_execute() returns successfully. Passing OCI_DEFAULT as the second parameter to the oci_execute() function call begins a transaction. The oci_commit() and oci_rollback() functions can then be used to control the transaction.
Note that when oci_commit() or oci_rollback() are used, or oci_execute() autocommits, all transactions on other statement handles for the connection are committed or rolled back. You cannot use a mix of autocommit/explicit control on the same connection handle. If you want to do that you need to use ociNLogon() to get a separate handle.
PHP will rollback any uncommitted transaction at the conclusion of the script.
How are database errors handled in PHP?
When OCI8 functions return failure, the oci_error() function can be used to obtain an array with error code, message, offset and SQL text:
$stid = oci_parse($conn, "select column_does_not_exist from employees");
$rc = oci_execute($stid);
if (!$rc) {
$err = oci_error($stid);
echo "Code: " . $err["code"] . "
";
echo "Message: " . $err["message"] . "
";
echo "Position: " . $err["offset"] . "
";
echo "Statement: " . $err["sqltext"] . "
";
exit;
}
For connection errors, do not pass a parameter to oci_error():
$conn = oci_connect("hr", "not_my_password", "localhost/XE");
if (!$conn) {
$m = oci_error();
echo $m["message"];
exit;
}
How do I call stored procedures from PHP?
The following example shows how to call a stored procedure and return a value. The stored procedure is assumed to have been previously created:
create procedure proc1(p1 IN number, p2 OUT number) as
begin
p2 := p1 + 10;
end;
The PHP code is:
Does PHP offer Oracle connection pooling?
The OCI8 1.3 extension supports connection pooling with Oracle 11g Database Resident Connection Pooling (DRCP). This allows even small computers to maximize use of database resources. DRCP scales to tens of thousands of connections, allowing applications to grow without needing special connection management.
PHP uses a multi-process model so shared connection pooling isn't possible natively in PHP. You can open "persistent" Oracle connections with the oci_pconnect() function. A persistent connection will be kept open for a process, but it will not allow connections to be shared between different processes
Third-party tools such as SQL Relay can be used to enable connection pooling for Oracle and other databases.
How do I manage connections in OCI8?
Connection management was greatly improved with the PHP 5.1 OCI8 driver. Many bugs were fixed and php.ini options for optimizing the behavior of persistent connections were introduced. The latest OCI8 1.3 driver introduced support for Oracle 11g Database Resident Connection Pooling (DRCP), allowing connections to be pooled, even when multiple Apache mid-tiers are used. DRCP scales to tens of thousands of connections. OCI8 1.3 also handles server downtime better. It can be installed with earlier versions of PHP.
Optimizing database connections in a frequently used script can improve performance and prevent resource shortages. These are general tips for managing connections:
Use OCI8 1.3 with Oracle DRCP. See the whitepaper PHP Scalability and High Availability.
Set oci8.persistent_timeout to expire idle persistent connections. The expiry is not asynchronous: a PHP script must invoke the PHP engine for the timeout to be triggered.
Avoid multiple connections to Oracle in a single script.
Use the "persistent" oci_pconnect() call to minimize the number of physical connection requests to the database. It has some limitations. The DB sessions will stay open as long as Apache is running, which can consume resources unless DRCP is available. If the DBA terminates open sessions with ALTER SYSTEM KILL SESSION or worse, terminates the Oracle shadow process with an operating system kill command, oci_pconnect() may succeed but subsequent OCI8 calls may still fail. The Oracle FAN support in OCI8 1.3 mitigates these particular problems in most cases. It is still good practice for scripts to check for errors and handle them gracefully since database machines may possibly go offline in the middle of a script.
Use Oracle Database Resource Manager (which replaces User Profiles) to limit the resources used by any one connection and to terminate idle persistent connections.
Use Oracle Net configuration parameters such as SQLNET.EXPIRE_TIME to free up unused database resources.
If DRCP is not available, use the Apache MaxRequestsPerChild setting to refresh the Apache processes after they have managed a certain number of requests, depending on site load. An Apache "graceful restart" nightly will also close open connections and allow night batch jobs to utilize more database server memory.
[Acknowledgment: Massimo Squillace contributed significantly to the original version of this section]
How do I check my SQL statements are working?
Getting no, or incorrect, results is surprisingly often due to executing the wrong statement.
Test SQL statements in SQL*Plus or SQL Developer to confirm correctness before executing them in PHP.
During development, echo each complete SQL statement from PHP to check it is well formed and all variables are correctly expanded. Quoting errors or misunderstandings with PHP's variable syntax in strings can cause incorrect statements to be executed.
Add PHP code to check the return values from each OCI8 call. Use the appropriate oci_error() call to get any error messages.
When statements are entered in tools like SQL*Plus, a semi-colon is often used to tell the tool that the statement is complete and can now be executed. However the semi-colon is not considered part of the statement and is not sent to the database. In PHP do not add a semi-colon to a SQL statement otherwise an Oracle error will occur. This example is a valid query:
$sql = "SELECT * FROM EMP_DETAILS_VIEW";
Oracle's inbuilt scripting language PL/SQL has different syntax to SQL and a final semi-colon is required:
$plsql = "BEGIN DBMS_OUTPUT.PUT_LINE('Hello, World!'); END;";
How can I insert strings containing quotes?
Inserting strings containing single quotes can be handled in several ways:
1. Use bind variables. This also protects against "SQL Injection" security issues and can greatly improve database performance. It is also the least likely to cause coding errors:
$name = "O'Reilly";
$stid = oci_parse($mycon, 'INSERT INTO CUSTOMERS (NAME) VALUES (:nm)');
oci_bind_by_name($stid, ':nm', $name, -1);
oci_execute($stid);
This is the recommended method.
2. Double every single quote:
$name = "O'Reilly";
$name = str_replace("'", "''", $name);
$stid = oci_parse($mycon, "INSERT INTO CUSTOMERS (NAME) VALUES ('".$name."')");
This is not recommended.
How do I fetch results using associative arrays where the same column name occurs?
Oracle table column names are cases insensitive by default but when fetched into associative arrays, PHP stores the names in upper case so upper case keys must be used to extract the data:
$query = 'select dname from dept';
$stid = oci_parse($conn, $query);
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC))) {
echo $row['DNAME']."
\n";
}
If the table was created with case sensitive column names this may not be true. Use var_dump($row) to see the column names and data structure actually returned by oci_fetch_array(). PHP has an array_change_key_case() function to change the case of array keys if needed.
Table name prefixes are not included in associative array keys. If a column name is used more than once in a query, use column aliases to distinguish the columns. Otherwise there will only be one associative array entry. If the aliases T1N and T2N were not used in the example below, only results from one of the NAME columns would be returned:
$query = 'select myt1.name as t1n, myt2.name as t2n from myt1, myt2';
$stid = oci_parse($conn, $query);
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC))) {
echo $row['T1N']." ".$row['T2N']."
\n";
}
What is pre-fetching in OCI8?
PHP OCI8 uses prefetching to return multiple rows in one operation and reduce network traffic to the database. Setting the prefetch row count with oci_set_prefetch() or the php.ini value oci8.default_prefetch controls the number of rows that Oracle internally caches. Adjusting the pre-fetch size can dramatically improve performance. Recent versions of OCI8 default the prefetch size to 100 rows. Older versions use 10. No matter what value, OCI8 still returns one row at a time to the user script: the buffering is done internally.
An example that sets the prefetch row count to 60 is:
You can also tune statement caching with oci8.statement_cache_size to improve performance of commonly executed statements.
If you have linked PHP with Oracle 11.2 client libraries, pre-fetching also works when fetching from REF CURSORS. The default prefetch size will automatically be used, or it can be set per-statement:
How do I use Oracle bind variables in OCI8?
Bind variables are strongly recommended because they help database performance and can improve application security.
Variables used for binding need to be accessible at the time when oci_execute() is called, not just when oci_bind_by_name() is executed. PHP's internal variable reference counting and copy-on-write behavior needs to be taken into account when coding.
If oci_bind_by_name() is called inside a wrapper function or method and the PHP variable passed to oci_bind_by_name() is local to the wrapper function there may be a problem. The variable needs to be in scope when oci_execute() is later called. Otherwise an Oracle error such as "ORA-01460: unimplemented or unreasonable conversion requested" may occur or, confusingly, it can appear as if no value was set for an "OUT" bind variable that you expect returned to the script.
The sample below is a variation on this. The variable "$val" is local to the "foreach" command. When oci_execute() is later called, no user data value is found and the code returns no records:
$dn, ':loc' => $lc);
$stid = oci_parse($conn, $qs);
foreach ($ba as $key => $val) {
oci_bind_by_name($stid, $key, $val, -1);
}
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC))) {
foreach ($row as $mv) {
echo $mv." ";
}
echo "
\n";
}
?>
Changing the oci_bind_by_name() call fixes the problem:
. . .
oci_bind_by_name($stid, $key, $ba[$key], -1);
. . .
PHP numbers are converted to and from strings when they are bound. This means the length parameter must generally be given to oci_bind_by_name() when returning a numeric data value in a bind variable. The length is the number of digits that will be returned.
Don't pass variables to oci_bind_by_name() by reference. Sometimes you see this obsolete syntax in older articles or documentation:
oci_bind_by_name($stid, ":mybindvar", &$val, 12);
It can cause problems. If you have set the E_STRICT error reporting level, then you will get an error if you attempt this.
How do I upload LOBS with OCI8?
When uploading large objects, watch out for size limitations either in the HTML script:
or in the LimitRequestBody directive in httpd.conf, or in php.ini's upload_max_filesize directive.
Here is an example script that uploads a JPG image into a BLOB column:
savefile(...) reads from the uploaded file.
// If the data was already in a PHP variable $myv, the
// $lob->save($myv) function could be used instead.
if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) {
oci_commit($conn);
}
else {
echo "Couldn't upload Blob\n";
}
$lob->free();
oci_free_statement($stid);
// Now query the uploaded BLOB and display it
$query = 'SELECT BLOBDATA FROM BTAB WHERE BLOBID = :MYBLOBID';
$stid = oci_parse ($conn, $query);
oci_bind_by_name($stid, ':MYBLOBID', $myblobid);
oci_execute($stid, OCI_DEFAULT);
$arr = oci_fetch_assoc($stid);
$result = $arr['BLOBDATA']->load();
// If any text (or whitespace!) is printed before this header is sent,
// the text won't be displayed and the image won't display properly.
// Comment out this line to see the text and debug such a problem.
header("Content-type: image/JPEG");
echo $result;
oci_free_statement($stid);
oci_close($conn); // log off
}
?>
Should I upgrade to PHP 5?
PHP 4 is no longer supported and upgrading is recommended. If PHP 4 is needed for business reasons, I strongly suggest you upgrade at least the OCI8 extension, using PECL. Even if you have PHP 5.2, I recommend upgrading to the latest OCI8 version from PECL. This new version of OCI8 will be included in PHP 5.3 by default.
The OCI8 extension function names were standardized in PHP 5. Most changes were the addition of underscores, for example OCIBindByName() is now oci_bind_by_name(). A couple of function names are very different e.g. ocilogoff() is now an alias for the new oci_close(). The biggest changes were simplifications to data fetching functions. Refer to the manual for their new syntax.
Both old and new names can be used so your scripts do not need to be recoded. The OCI8 Documentation has entries for old and new names. User contributed comments for the same functionality may be under either one.
Another name that has changed in PHP 5 on Windows is the name of the directory containing the extension DLLs. If starting the web server gives you the error "Unable to load dynamic library 'c:\PHP\extension\php_oci8.dll'" edit your php.ini file and change the value of the "extension_dir" directive from "extension" to "ext".
If you are upgrading to PHP 5.1.2 onwards and using the re-factored OCI8 extension for the first time, check the documentation on the new OCI8 php.ini directives. In particular check oci.old_oci_close_semantics. In the re-factored extension the oci_close() function will actually close a connection unless the directive is set. Previously the call was a no-op.
Can I do Operating System Authentication?
OCI8 1.3 supports Oracle External Authentication. Instead of storing a database username and password in PHP scripts, credentials can be authenticated by an outside system such as Oracle Wallet. The operating system user running the Apache process could be granted read access to the wallet using Access Control Lists.
To use external authentication, first configure the database to use an external authentication method. Refer to Oracle documentation for details. In your PHP script, pass the username as "/", pass an empty password, and set the OCI_CRED_EXT flag as the fifth parameter in a connection call:
$c = oci_connect("/", "", $db, null, OCI_CRED_EXT);
Can I do authentication using AS SYSDBA or AS SYSOPER?
It is possible to connect AS SYSDBA or AS SYSOPER in PHP only with OCI8 extension. The oci8.privileged_connect parameter must also first be set On. It is most likely to be useful when PHP is used as a command-line script language. Connection can be made with:
$c = oci_connect("/", "", null, null, OCI_SYSDBA);
You can use external authentication with privileged connections:
$c = oci_connect("/", "", $db, null, OCI_CRED_EXT+OCI_SYSOPER);
I don't recommend you enable oci8.privileged_connect as it removes a layer of security. If anyone can execute arbitrary oci_connect() commands they may be able to gain privileged access to your database.
Are NCHAR and NCLOB supported in PHP?
There is no NCHAR or NCLOB support in the OCI8 extension.
The current PHP implementations of oci8.c always use SQLCS_IMPLICIT for the character set form when it calls Oracle's OCI. The character set form would need to be SQLCS_NCHAR to support NCHAR and NCLOB, and other data handling changes may be needed in PHP's code.
What is Zend Core for Oracle?
Zend Core for Oracle (ZCO) is a pre-built PHP 5 distribution that includes the PHP OCI8 1.2 extension and Oracle 10g Instant Client libraries. It has a web GUI management console.
The product includes tight integration with Oracle Database client libraries, and native support for XML and Web services, while also supporting increased adoption of service-oriented architecture (SOA). It delivers a rapid development and deployment foundation for database-driven applications.
Oracle and Zend announced the general availability of Zend Core for Oracle on October 11, 2005.
Support and hot fix updates are available from Zend.
ZCO is being replaced by Zend Server.
What is Zend Server?
Zend Server is a complete, enterprise-ready Web Application Server for running and managing PHP applications that require a high level of reliability, performance and security. It includes out-of-box connectivity for Oracle databases on Windows and Linux, as well as application monitoring, problem diagnostics and application performance optimization. It has full support and software updates available from Zend.
After an extensive private and public Beta cycle, Zend Server was declared production on April 7, 2009
Zend Server includes the OCI8 1.3 extension (which support Oracle 11g Database Resident Connection Pooling) and bundles the Oracle 11g Instant Client libraries.
How do I install/upgrade OCI8 on Oracle Application Server or Oracle HTTP Server?
Oracle Application Server 10g's HTTP server includes PHP 4 and the OCI8 extension. Users often want to upgrade to recent versions of PHP and OCI8.The Underground PHP and Oracle Manual has a section with steps for this. The delicate part of re-installing PHP on OHS is avoiding Oracle library version clashes. Ideally PHP OCI8 should be built and run with the same version of Oracle libraries.
There is an article at http://www.oracle.com/technology/products/ias/ohs/htdocs/php_ohs.htm which shows how to install PHP on OHS 11g
No comments:
Post a Comment