PHP PL/SQL Function Return
Somebody wanted an example of how to capture the return value of a PL/SQL function in a PHP script running against the Oracle Database. The trick is embedding the call inside an anonymous block program unit, like a prior example that I posted on handling an OUT mode variable in a PL/SQL Procedure.
Here’s the sample PHP code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | <?php // Attempt to connect to your database. $c = @oci_connect("student", "student", "localhost/orcl"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { // Initialize incoming message whether or not parameter sent. $msg_in = (isset($_GET['msg'])) ? $_GET['msg'] : "Cat got your keyboard?"; // Set the call statement, like a SQL statement. $sql = "BEGIN :b := echo(:a); END;"; // Prepare the statement and bind the two strings. $stmt = oci_parse($c,$sql); // Bind local variables into PHP statement, you MUST size OUT only variables. oci_bind_by_name($stmt, ":a", $msg_in); oci_bind_by_name($stmt, ":b", $msg_out, 80, SQLT_CHR); // Execute it and print success or failure message. if (oci_execute($stmt)) { print $msg_out; } else { print "Sorry, I can't do that Dave..."; } // Free resources. oci_free_statement($stmt); oci_close($c); } ?> |
As noted by Chris, you should size input parameters too. It could be qualified as a best practice when code re-executes with different values without rebinding.
It depends on this echo function:
1 2 3 4 5 6 | CREATE OR REPLACE FUNCTION echo ( message IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN RETURN 'Message ['||message||'] received.'; END; / |
Hope that this is what they needed, or you’re looking for as an answer to a search.