=====
CS 328 - Week 12 Lecture 1 - 2026-04-13
=====
=====
TODAY WE WILL
=====
* announcements
* PHP and OCI: calling a stored function!
* what if your SQL or PL/SQL changes your database?
(oci_commit)
* PHP and OCI: calling a stored procedure!
* prep for next class
=====
* IT IS PREREGISTRATION for SUMMER 2026/FALL 2026!
* preregistration for SUMMER 2026 is open NOW for
***EVERYONE***!!!
* preregistration for FALL 2026 begins at your
registration appointment in your student center
(sometime between April 13 - April 24)
=====
UPCOMING SCHEDULE
=====
* TODAY - more PHP-and-OCI (the newly-intro'd parts will
NOT be part of Exam 2)
* 11:59 pm Monday, April 13
* any final improved versions of problems from
Homeworks 6-9 are DUE, so that...
* 12:01 am Tuesday, April 14
* selected EXAMPLE SOLUTIONS for Homeworks 6-9
can be made reachable on Canvas, for Exam 2 study use
* Wednesday, April 15 - Exam 2, during lecture, in SH 108
* Exam 2 - Bonus must be submitted *ON CANVAS* by 3:00 pm
on Wednesday, April 15
* Thursday, April 16
* There WILL be a Week 12 lab exercise!
* (Homework 10 comes out AFTER the Week 12 Labs)
=====
how to call a PL/SQL stored function from PHP using OCI
=====
* as when you call a PL/SQL stored function in SQL*Plus,
you neeeeeed to provide a "place" to put the stored function's
result when you call a stored function from PHP using OCI
this will be a bind variable -- here, in particular,
an OUTPUT bind variable
* and: for both stored procedures and stored functions,
you must put BEGIN and END into the string containing
the desired command,
you must end that subroutine call with a semicolon, and
* WEIRD PART: you MUST follow that END with a ; INSIDE the
string...!!!!!!!!!
$get_mgr_str =
"BEGIN :empl_mgr := get_manager(:empl_name); END;";
* then, you call oci_parse as usual to get a statement object:
$get_mgr_stmt = oci_parse($conn, $get_mgr_str);
* BEFORE you execute, call oci_bind_by_name for EACH
(input) bind variable you wish to BIND a value to:
oci_bind_by_name($get_mgr_stmt, ":empl_name", $chosen_empl_name);
* ALSO BEFORE you execute, you need to call oci_bind_by_name for
each OUTPUT bind variable -- here, that's the bind variable for
what the function call returns -- NOT to give it a value BEFORE
execution,
BUT to tell OCI *WHERE* to put what the function returns!
(telling OCI what PHP variable [or PHP expression-that-can-be-assigned-to]
to put the function's returned value!)
* for such an OUTPUT bind variable, must call the FOUR-argument version
of oci_bind_by_name --
* the first two arguments are the usual arguments --
the statement object involved, and
a string containing the name of the bind variable involved
* the THIRD argument is the the PHP VARIABLE (or
PHP-expression-that-can-be-assigned-to) that will be
SET/ASSIGNED the value of that output bind variable --
the function's returned value -- when this statement is
executed
* (that is, instead of this being a PHP expression that
has a value, this is a PHP expression that you
want to GIVE a value!)
* the FOURTH argument is letting PHP know the maximum SIZE
it needs for that third argument --
* by default, it makes this returned value a string, and so
generally want its max number of characters, or, for
a numeric value, the number of characters
if that number were expressed as a string...!
* so, for example:
oci_bind_by_name($get_mgr_stmt, ":empl_mgr", $chosen_empl_mgr, 15);
* and now execute as usual using oci_execute!
(and AFTER the oci_execute call -- the output bind variable's value
WILL be in the PHP variable included in its oci_bind_by_name statement!)
* SEE: 328lect12-1-funct.php,
and the handout
"Basics of Calling PL/SQL Subroutines from PHP using OCI"
=====
how can you request a database commit or a database rollback
from PHP using OCI?
oci_commit and oci_rollback !
=====
* when you use PHP and OCI
and ask OCI to ask Oracle to do an action that changes the database
(e.g., a SQL insert, update, or delete, or a call of a PL/SQL stored procedure
or function that happens to change the database),
IF you have used OCI_DEFAULT as the 2nd argument of oci_execute,
you are saying you will SPECIFY when the latest changes should be committed,
(hopefully/presumably when all of the steps of a logical transaction have
been successfully completed!)
* this gives you the opportunity to rollback any changes from partial
transactions that fail, if needed!
* BUT: it ALSO means that if you forget to EXPLICITLY commit any changes,
they will not ACTUALLY change the database;
* SO: to ask that a commit; be done to the database you are
currently connected to:
oci_commit($conn);
and for a rollback:
oci_rollback($conn);
...be sure to do this BEFORE you call oci_close for
that connection!!!
* SEE: 328lect12-1-insert.php,
and the handout
"Basics of PHP - OCI function oci_commit"
=====
calling a PL/SQL stored procedure using OCI
=====
* no returned value to worry about, here!
* basic string for the procedure call:
$proc_call_str = "BEGIN proc_name(:bind_var1, ...); END;";
$proc_call_stmt = oci_parse($conn, $proc_call_str);
oci_bind_by_name($proc_call_stmt, ":bind_var1", $desired_val);
... (bind EACH variable!)
oci_execute($proc_call_stmt, OCI_DEFAULT);
// depending on the procedure! IS db changed? DOES the procedure do a commit?
// IF it changes things and does NOT commit, remember:
oci_commit($conn);
// and free your statement, close your connection
* SEE: 328lect12-1-proc.php,
and the handout
"Basics of PHP - OCI function oci_commit"