PHP catch pl/sql exception from anonymous block-CodePudding

I run an anonymous block in my php application which calls a procedure via propel, located in a package. In the procedure I throw an exception and want to catch in on php side, but don't have doubts doing so.

Here's some example code how it looks:

try {
    $anonymousBlockStr = '
        BEGIN
           MY_PACKAGE.DO_IT(' . $paramStr . '); 
        END;';
    $connectionObj->exec($anonymousBlockStr);
} catch (Exception $e) {
    $myLoggerObj->log($e->getMessage());
}

The package looks like

create or replace PACKAGE MY_PACKAGE
IS

  PROCEDURE DO_IT(MY_PARAM IN VARCHAR2);
  MY_EXCEPTION EXCEPTION;
  
END MY_PACKAGE;

and the package body procedure looks like

create or replace PACKAGE BODY MY_PACKAGE_BODY
AS
  PROCEDURE DO_IT(MY_PARAM IN VARCHAR2, IN_LAYOVER_COLUMN_NAME IN VARCHAR2) AS
        v_stmt           VARCHAR2(1000) := '';
        v_count          NUMBER := 0;
        MY_EXCEPTION     EXCEPTION;
        PRAGMA EXCEPTION_INIT (MY_EXCEPTION, -20100);
    BEGIN
        v_stmt := 'SELECT COUNT(*) FROM dual WHERE 1 = 1;
        EXECUTE IMMEDIATE v_stmt INTO v_count;
        IF v_count > 0 THEN
            raise_application_error (-20100, '1 = 1!');
        END IF;
        exception when MY_EXCEPTIONthen
        sys.dbms_output.put_line('Exception was raised');
        RAISE;
 
  END DO_IT;

END MY_PACKAGE_BODY;

When I directly call the procedure I see the error in my db ide, but I cannot catch it in php in a try-catch-block. How can I forward the exception from the anonymous block to php? If I create other errors in the procedue that lead to oracle included exceptions I see them also in php.

Many thanks in advance!

CodePudding user response：

Use PHP to convert errors to exceptions:

<?php

// Catch PL/SQL exceptions in PHP

// From example in https://www.php.net/manual/en/language.exceptions.php
function exceptions_error_handler($severity, $message, $filename, $lineno) {
    throw new ErrorException($message, 0, $severity, $filename, $lineno);
}
set_error_handler('exceptions_error_handler');

try {
    $c = oci_connect("hr", "welcome", "localhost/XE");

    $plsql = <<<'END'
          declare
            past_due exception;
            pragma exception_init (past_due, -20001);
          begin
            raise_application_error(-20001, 'This is my PL/SQL past_due exception');
          end;
END;

   $s = oci_parse($c, $plsql);
   oci_execute($s);

} catch (Exception $e) {
    print("\nException Caught:\n\n");
    print($e);
}

?>

The output is like:

Exception Caught:

ErrorException: oci_execute(): ORA-20001: This is my PL/SQL past_due exception
ORA-06512: at line 5 in /Users/cjones/exception2.php:24
Stack trace:
#0 [internal function]: exceptions_error_handler(2, 'oci_execute(): ...', '/Users/cjones/p...', 24)
#1 /Users/cjones/public_html/exception2.php(24): oci_execute(Resource id #5)
#2 {main}