=====
CS 328 - Week 12 Lecture 1 - 2025-04-14
=====

=====
TODAY WE WILL
=====
*   announcements
*   when your PHP OCI *changes* the database: oci_commit
*   PHP and OCI: calling a stored procedure
*   PHP and OCI: calling a stored function
*   prep for next class

=====
SIDE NOTE: if you are considering taking Humboldt courses in SUMMER 2025
=====
*   registration for SUMMER 2025 is now open for ***EVERYONE***
    (opened for everyone TODAY, Monday, APRIL 14!!!!)

*   from:
    https://www.humboldt.edu/student-financial-services/summer-term-2025

    "For the 2025 Summer term, the university is guaranteeing 4 units,
    if enrolled in 6 or more units, will be covered for all continuing
    matriculated undergraduate students. This will cover 4 units of
    the tuition charge and does not include coverage of mandatory
    campus-based fees or the additional non-resident tuition if
    applicable."

    *   see the above link for more information!

=====
UPCOMING SCHEDULE
=====
*   TODAY - more PHP-and-OCI (the newly-intro'd parts will
    NOT be part of Exam 2)

*   11:59 pm TONIGHT, Monday, April 14
    *   any final improved versions of problems from 
        Homeworks 6-9 are DUE, so that...

*   12:01 am Tuesday, April 15
    *   selected EXAMPLE SOLUTIONS for Homeworks 6-9
        can be made reachable on Canvas, for Exam 2 study use

*   Wednesday, April 16 - Exam 2, during lecture, in SH 108
    *   The BONUS submission for Exam 2 be submitted *ON CANVAS* by 3:00 pm
        on Wednesday, April 16

*   Thursday, April 17/Friday, April 18
    *   There WILL be a Week 12 lab exercise!

*   (Homework 10 comes out AFTER the Week 12 Labs)

=====
oci_commit
=====
*   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 tehe 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!)
    
    and you ask for that commit to be done using:

    oci_commit($conn);

    (and yes, you can request a rollback with:

    oci_rollback($conn);

*   also see the posted handout, "Basics of PHP - OCI function oci_commit"

*   (and oci_commit is used in today's posted examples!)

=====
calling a PL/SQL stored procedure using OCI
=====
*   see the posted handout, "Basics of Calling PL/SQL Subroutines from PHP using OCI"

*   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 example insert-dept-2.php

=====
calling a PL/SQL stored function using OCI
=====
*   again, see the posted handout, "Basics of Calling PL/SQL Subroutines from PHP using OCI"

*   basic string for the function call:

    You neeeeed a bind variable for the value that the function
    returns!

    $funct_call_str = "BEGIN :result_var := funct_name(:bind_var1, ...); END;";

    $funct_call_stmt = oci_parse($conn, $funct_call_str);

    oci_bind_by_name($funct_call_stmt, ":bind_var1", $desired_val);
    ... (bind EACH *argument* variable!)

    // for the return value/"output bind variable":
    // call oci_bind_by_name with FOUR arguments:
    // *   the first 2 are the usuals
    // *   the THIRD is the PHP VARIABLE that will be SET/ASSIGNED
    //     the returned value
    // *   the FOURTH is letting PHP know the maximum size it needs
    //     to give that variable -- number of characters, or for a
    //     number the number of characters in its depiction...? urgh...
    //     (using 4 below just to demo)

    oci_bind_by_name($funct_call_stmt, ":result_var", $funct_result, 4);

    oci_execute($funct_call_stmt, OCI_DEFAULT);

    // now $funct_result contains what the function returned!!!

    // depending on the function! IS db changed? DOES the function do a commit?
    //    IF it changes things and does NOT commit, remember:

    oci_commit($conn);

    // and free your statement, close your connection

*   see example insert-dept-3.php