Oracle Stored Procedure
Somebody felt that I’d short changed Oracle by providing only an example for calling a stored procedure in MySQL. So, here’s an equivalent post to the MySQL sample that works in an Oracle database with PHP.
The largest difference between the two approaches is that Oracle is transactional by default while MySQL isn’t. However, the use of savepoints and rollbacks are shown in the procedure, which are the appropriate transaction controls in Oracle.
Here are the detailed steps, even though there are other blog entries with information on related subjects.
1. Sign on as the system
user, and create a new user. Users in Oracle have their own schema or work area, and they don’t require a database like MySQL or SQL Server.
SQL> CREATE USER student IDENTIFIED BY student; SQL> GRANT CONNECT, resource, CREATE any VIEW TO student; |
2. Create a create_oracle_procedure.sql
file with the following contents:
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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | -- Conditionally drop the objects to make this rerunnable. BEGIN FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('A','B')) LOOP EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS'; END LOOP; FOR i IN (SELECT sequence_name FROM user_sequences WHERE sequence_name IN ('A_SEQ','B_SEQ')) LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name; END LOOP; END; / -- Create the tables and sequences. CREATE TABLE a ( a_id NUMBER CONSTRAINT a_pk PRIMARY KEY , a_text VARCHAR2(12)); CREATE SEQUENCE a_seq; CREATE TABLE b ( b_id NUMBER CONSTRAINT b_pk PRIMARY KEY , a_id NUMBER , b_text CHAR(12) , CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id)); CREATE SEQUENCE b_seq; -- Create a stored procedure with IN-only (default) mode parameters. CREATE OR REPLACE PROCEDURE double_insert ( input_a VARCHAR2, input_b VARCHAR2) IS BEGIN SAVEPOINT starting_point; INSERT INTO a VALUES (a_seq.NEXTVAL, input_a); INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK TO starting_point; RETURN; END; / -- Define a couple local session variables. VARIABLE text1 VARCHAR2(12) VARIABLE text2 VARCHAR2(12) -- Assign values to session variables. BEGIN :text1 := 'This is one.'; :text2 := 'This is two.'; END; / -- Call the local procedure. EXECUTE double_insert(:text1,:text2); -- Select the data set. SELECT * FROM a; SELECT * FROM b; |
3. Quit the session as the system
user. You can simply reconnect to the new schema like this:
SQL> CONNECT student/student@orcl |
4. Run the file from the relative directory where you started the sqlplus
executable.
SQL> @create_oracle_procedure.sql |
You see the following query results at the end of the script:
A_ID A_TEXT ---------- ------------ 1 This IS one. B_ID A_ID B_TEXT ---------- ---------- ------------ 1 1 This IS two. |
5. Write the following into a call_oracle_procedure.php
:
<?php // Attempt to connect to your database. $c = @oci_connect("student", "student", "orcl"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { // Declare two variables for the test procedure call. $val1 = "Hello Hal!"; $val2 = "Hello Dave!"; // Set the call statement, like a SQL statement. $sql = "BEGIN double_insert(:a,:b); END;"; // Prepare the statement and bind the two strings. $stmt = oci_parse($c,$sql); // Bind local variables into PHP statement. oci_bind_by_name($stmt, ":a", $val1); oci_bind_by_name($stmt, ":b", $val2); // Execute it and print success or failure message. if (oci_execute($stmt)) { print "Congrats! You've executed a Oracle stored procedure from PHP!"; } else { print "Sorry, I can't do that Dave..."; } // Free resources. oci_free_statement($stmt); oci_close($c); } ?> |
6. Run the call_oracle_procedure.php
from the command line, like this:
php call_oracle_procedure.php |
7. Now you can requery the database to see the inserts made by the PHP program.
SQL> SELECT * FROM a; A_ID A_TEXT ---------- ------------ 1 This IS one. 2 Hello Hal! SQL> SELECT * FROM b; B_ID A_ID B_TEXT ---------- ---------- ------------ 1 1 This IS two. 2 2 Hello Dave! |
There’s now a sample file for both approaches. As always, I hope this helps some folks.