|
Some common PL/SQL errors you might encounter are:
* Success with information warnings
* Incorrect end of line terminators on Windows platforms
This section gives a description of what may cause these errors, and ways to resolve them.
PL/SQL Success With Information Warnings
A common PL/SQL error when creating packages, procedures or triggers is:
Warning: oci_execute(): OCI_SUCCESS_WITH_INFO: ORA-24344: success with
compilation error
You can get more information about the problem cause by querying Oracle’s user_errors
table.
The PHP code to check for informational errors and warnings is shown by this example
that creates a procedure referencing a non-existent table and then queries the user_errors
table when an ORA-24344 error occurs:
<?php
$c = oci_connect('hr', 'hrpwd', '//localhost/XE');
// A PL/SQL statement with deliberate error: not_mytab doesn’t exist
$plsql = "create or replace procedure
myproc(d_p in varchar2, i_p in number) as
begin
insert into not_mytab (mydata, myid) values (d_p, i_p);
end;";
$s = oci_parse($c, $plsql);
$r = oci_execute($s);
if (!$r) {
$m = oci_error($s);
if ($m['code'] == 24344) {
// A PL/SQL "success with compilation error"
show_compilation_errors($c);
}
else {
// A normal SQL-style error
echo "Error is ", $m['message'], "\n";
}
}
// Display PL/SQL errors
function show_compilation_errors($c)
{
$s = oci_parse($c, "SELECT NAME || ': ' || ATTRIBUTE
|| ' at character ' || POSITION
|| ' of line ' || LINE || ' - '
|| TEXT
FROM USER_ERRORS
ORDER BY NAME, LINE, POSITION,
ATTRIBUTE, MESSAGE_NUMBER");
oci_execute($s);
print "<table border='1'>\n";
while ($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) {
print "<tr>";
foreach ($row as $item) {
print "<td>".($item?htmlentities($item):" ")."</td>";
}
print "</tr>\n";
}
print "</table>";
}
?>
<pre>
This displays:
Warning: oci_execute(): OCI_SUCCESS_WITH_INFO: ORA-24344: success with
compilation error in plsqlerr.php on line 11
MYPROC: ERROR at character 7 of line 4 - PL/SQL: SQL Statement ignored
MYPROC: ERROR at character 19 of line 4 - PL/SQL: ORA-00942: table or
view does not exist
Looking at the PL/SQL code creating the procedure, character 7 on line 4 of the PL/SQL
code is the INSERT statement. Character 19 is the not_mytab table name.
Alternatively to show the PL/SQL errors, connect to SQL*Plus and use SHOW ERRORS:
SQL> connect hr/hrpwd@//localhost/XE
Connected.
SQL> show errors procedure myproc
Errors for PROCEDURE MYPROC:
LINE/COL ERROR
-------- ---------------------------------------------------------
4/7 PL/SQL: SQL Statement ignored
4/19 PL/SQL: ORA-00942: table or view does not
</pre>
|
