ocierror

add a note User Contributed Notes

mlong-php at mlong dot us
04-May-2004 02:45


It seems in 4.3 (and probably other versions), if you call OCIError twice it will fail on the second time (with an empty array). This used to work on previous versions but no longer.



So my prior example of doing if (OCIError won't work and you have to do it like the other contributed example:



$r = @OCIExecute($selectw,OCI_DEFAULT);

if (!$r)

{ 

 $erra=OCIError($selectw); 

 print "Error: ${erra['code']} ${erra['message']}"; 

}

vi at sh dot nu
05-Nov-2003 05:29


Here's an example of how to get the offset from an Oracle statement that errored:



<?



$conn = OCILogon ("user", "password", "database");



$statement = OCIParse ($conn, "select foo, bar from t1 where id = 1");



OCIExecute ($statement, OCI_DEFAULT);



$error = OCIError ($statement);



if ($error["offset"]) {



        $sqltext = substr ($error["sqltext"], 0, $error["offset"]) . 

                '*' .

                substr ($error["sqltext"], $error["offset"]);

        echo $sqltext;

}



?>



Presuming the column "foo" doesn't exist in the table "t1", the above code will produce the following:



PHP Warning:  OCIStmtExecute: ORA-00904: "FOO": invalid identifier

 in test.php on line 7

select *foo, bar from table where id = 1



Note the asterisk next to the word "foo".



This example may seem overly simple, and the error location obvious, but when you have an enormous query, you'll quickly find this functionality very useful.



Daniel Ceregatti

cjbj at hotmail dot com
07-Oct-2003 09:18


The documentation says "If the optional stmt|conn|global is not

provided, the last error encountered is returned".  This is not true

and has been noted in bug http://bugs.php.net/bug.php?id=9510 which

says "ocierror always stores the error in the most appropiate

(parent-)handle.".  Also see bug http://bugs.php.net/bug.php?id=25773



Here are three examples of what to pass to OCIError to retrieve an

Oracle error.



<?php



//

// These examples are independent, not cumulative.  Make only one

// change at a time and then run the script.

//

// Example 1: Login errors:

//            Change the database to something invalid.  The Oracle

//            error should be:

//                 ORA-12154: TNS:could not resolve service name

//

// Example 2: Parsing errors:

//            Once you've corrected the username, password and

//            database for your site and connected OK, the script

//            should give the Oracle error:

//                 ORA-01756: quoted string not properly terminated

//

// Example 3: Execution errors:

//            Change the SQL statement adding a single quote to make

//            'missingquote into 'missingquote'

//            The Oracle error should be: 

//                 ORA-00923: FROM keyword not found where expected



$username  = "scott";

$password  = "tiger";

$database =  "MYDB";



$stmt = "select no_such_column 'missingquote from dual";



// Display OCI error

function PrintOCIError($err)

{

  echo "<pre>".$err['message']."</pre>\n";

  die();

}



$con = @OCILogon($username, $password, $database);

if (!$con) {



  $e = OCIError();  // For OCILogon errors pass no parameter

  PrintOCIError($e);

}



echo "Connected OK\n";



$stid = @OCIParse($con, $stmt);

if (!$stid) {

  $e = OCIError($con);  // For OCIParse errors pass the connection

  PrintOCIError($e);

}



echo "Parsed OK\n";



$r = @OCIExecute($stid);

if (!$r) {

  $e = OCIError($stid); // For OCIExecute errors pass the statement

  PrintOCIError($e);

}



echo "Executed OK\n";



?>

mlong-php at mlong dot us
27-Aug-2002 11:37


This works well for me...where $select is your handle and dodberror is a custom function you've written to handle the error.  This way you need not worry about what each function returns...just use OCIError to see if there was an error or not.



if (OCIError($select))

{ 

 $erra=OCIError($select);

 dodberror("SQL Error: $erra[code] $erra[message]"); 

}

add a note

ocierror

说明