How to Call an Oracle Procedure from PHP

23 May 2023 360 Reading time: 3 minute

Oracle procedures provide a powerful way to encapsulate business logic within the database. If you're working with PHP and need to invoke an Oracle procedure, this guide will walk you through the process. We'll cover the necessary steps and provide an example code snippet to help you get started.

Step 1: Set up the Oracle Database Connection

Before calling an Oracle procedure from PHP, ensure that you have a working database connection established. Use the appropriate credentials and connection details, such as the host, port, SID, username, and password, in your PHP code. Here's an example of establishing an Oracle database connection using the oci_connect function:

<?php
$connection = oci_connect('username', 'password', 'host:port/SID');
if (!$connection) {
    $error = oci_error();
    echo "Connection failed: " . $error['message'];
    exit;
}
?>

Step 2: Prepare the Oracle Procedure Call

Once the database connection is established, you need to prepare the Oracle procedure call statement. This involves creating an OCI statement using the oci_parse function and binding any necessary input or output parameters. Here's an example:

<?php
$procedureCall = 'BEGIN my_procedure(:param1, :param2, :result); END;';
$statement = oci_parse($connection, $procedureCall);
oci_bind_by_name($statement, ':param1', $param1);
oci_bind_by_name($statement, ':param2', $param2);
oci_bind_by_name($statement, ':result', $result, 100); // Assuming a VARCHAR2 output parameter
?>

In the example above, :param1 and :param2 are input parameters, while :result is an output parameter. Adjust the parameter names and data types according to your specific procedure.

Step 3: Execute the Oracle Procedure Call

After preparing the procedure call, you can execute it using the oci_execute function. This will invoke the Oracle procedure and perform any required processing. Here's the corresponding code:

<?php
if (oci_execute($statement)) {
    echo "Procedure executed successfully.";
} else {
    $error = oci_error($statement);
    echo "Procedure execution failed: " . $error['message'];
    exit;
}
?>

In the code snippet above, we check the return value of oci_execute to determine if the procedure execution was successful. If an error occurs, the oci_error function is used to retrieve the error message.

Step 4: Process the Procedure Results

Finally, you can process any results returned by the Oracle procedure. For example, if your procedure has an output parameter that retrieves a value, you can use it as needed. Here's an example of retrieving and displaying the result:

<?php
echo "Result: " . $result;
?>

Adjust the result processing based on your procedure's output parameters and business logic requirements.

Full Example Code

Here's the complete example code that demonstrates calling an Oracle procedure from PHP:

<?php
$connection = oci_connect('username', 'password', 'host:port/SID');
if (!$connection) {
    $error = oci_error();
    echo "Connection failed: " . $error['message'];
    exit;
}

$procedureCall = 'BEGIN my_procedure(:param1, :param2, :result); END;';
$statement = oci_parse($connection, $procedureCall);
oci_bind_by_name($statement, ':param1', $param1);
oci_bind_by_name($statement, ':param2', $param2);
oci_bind_by_name($statement, ':result', $result, 100); // Assuming a VARCHAR2 output parameter

if (oci_execute($statement)) {
    echo "Procedure executed successfully.";
} else {
    $error = oci_error($statement);
    echo "Procedure execution failed: " . $error['message'];
    exit;
}

echo "Result: " . $result;

oci_free_statement($statement);
oci_close($connection);
?>

Remember to adjust the code according to your specific database connection details, procedure signature, and business requirements.

That's it! You now have a basic understanding of how to call an Oracle procedure from PHP. Feel free to explore and expand upon this example to suit your application's needs.

Similar articles