PHP OUT mode Parameter
I saw a post in the OTN forum that asked a simple question and had no simple example as an answer, so I thought it would be nice to provide one. Basically, somebody wanted to know how to call into a stored procedure and return a value with more or less one pass-by-value and another pass-by-reference variable.
This defines a simple echo
procedure, which takes a message and returns a formatted message:
1 2 3 4 5 6 7 | CREATE OR REPLACE PROCEDURE echo ( message IN VARCHAR2 , reply OUT VARCHAR2 ) IS BEGIN reply := 'Message ['||message||'] received.'; END; / |
The following PHP calls the procedure and returns the value. It uses the required connection syntax for the Zend Server Community Edition.
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 | <?php // Attempt to connect to your database. $c = @oci_connect("student", "student", "localhost/xe"); 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 echo(:a,:b); END;"; // Prepare the statement and bind the two strings. $stmt = oci_parse($c,$sql); // Bind local variables into PHP statement, you need to 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); } ?> |
You can then test it with or without a parameter, like this example with a parameter:
http://localhost/message_reply.php?msg="Sample message" |
I put a link in the forum to this, and I hope it helps a few folks.