Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
  Phone
  800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

    Privacy Policy

 

   
 

ss

Oracle Tips by Burleson 

Handling Errors

OCI errors are caught by the oci_error()function. The description of this function is shown below:

$err =oci_error( $handle);

The handle argument to the oci_error() function is not mandatory. The OCI_Session class from Chapter 2 shows that after the connection attempt, the following PHP block checks the outcome:

              if (!empty($this->db))
                 $this->err=NULL
;
              else  {
                 $this->err=oci_error();
              }

If the connection attempt is unsuccessful, the database handle does not exist and cannot be used for error checking. So, the logical rule is that the handle should not be used to check for unsuccessful connection attempts. To check for connection errors, the oci_error() function should be invoked without an argument.

What does the oci_error()function return? It returns an array, specifying the error code and message, the error offset and the original SQL statement causing the error. Below is an example demonstrating the use of the oci_error() function:

Example 14

    $ cat example14.php
    #!/usr/local/bin/php
    <?php
        $db=oci_new_connect("scott","tiger");
        $SQL="select * from non_existing_table";
        $sth=oci_parse($db,$SQL);
       
@oci_execute($sth);
        if (!defined($sth)) {
           $err =oci_error($sth);
           var_dump($err
);
       
}

So, what is the big idea here?  A select from a non-existing table is being executed in order to see the structure of the resulting $err array returned by the oci_err() function. The var_dump()function used in the process dumps the structure of the variable in the format understandable to the user. For Perl enthusiasts, the var_dump() function is the PHP version of the Data::Dumper module. The following is the output of the script:

    $ example14.php
array(4) {
 ["code"]=>int(942)
 ["message"]=>string(40) "ORA-00942: table or view does not
                          exist"
 ["offset"]=>int(14)
 ["sqltext"]=>string(32) "select * from non_existing_table"
}

This output reveals the following information:

  • The variable $erris an array with four elements.

  • The first element is accessible by the key ‘code’ and its value is number 942.

  • The second value is accessible by the key ‘message’ and the value is string “ORA-00942: table or view does not exist”.

  • The third value is accessible by the key ‘offset’, and its value is the number 14. This is the character before the name of the non-existing table.

  • The fourth member is the problematic SQL message causing the error in the first place.

Also worth noting is the way error messages are suppressed in the call to the oci_execute()function. Without the preceding error suppression operator (@), the OCI error message is printed on the error output, like the following:

$ example14.php

PHP Warning:  oci_execute(): OCIStmtExecute: ORA-00942: table or view does not exist

 in /home/mgogala/work/PHP/example14.php on line 6
array(4) {
  ["code"]=>......
.......

This is not the desired result, so the error is suppressed by using the error suppression operator @.  The oci_error() function can catch all kinds of Oracle errors. Most of the SQL errors are caught by examining the SQL statement handle, created by the oci_parse()function.

Errors during the commit or rollback are caught by examining the database handle. The commit and rollback are global database operations that affect much more than a single database handle. Therefore, to examine problems with the commit or rollback, examine the database handle.



See code depot for complete scripts


The above book excerpt is from:

Easy Oracle PHP

Create Dynamic Web Pages with Oracle Data

ISBN 0-9761573-0-6   

Mladen Gogala

http://www.rampant-books.com/book_2005_2_php_oracle.htm

 

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2011 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter