try-stored-subroutine

<!DOCTYPE html>
<html>

<!-- try-stored-subroutine.php
     
     adapted from examples from Peter Johnson
     adapted by: Sharon Tuttle
     last modified: 4-24-13
-->

<head>
    <title> Calling Oracle Stored Procedures and Functions from PHP using
            OCI8 </title>
    <meta charset="utf-8" />
</head>

<body>
    <h1> Calling Oracle Stored Procedures and Functions from PHP 
         using OCI8 </h1>

    <?php

        // do you need to ask for username and password?

        if (! array_key_exists('username', $_POST))
        {
            print <<<PASSWORD_FORM
    <form method="post" action="$_SERVER[PHP_SELF]">
PASSWORD_FORM;
?>
        <label> Username: 
                <input type="text" name="username" /> </label> 
                <br />

        <label> Password: 
                <input type="password" name="password" /> 
                </label> <br />
        <input type="submit" value="Log in" />
        </form>
    <?php
    }

    else // you HAVE the username and password
    {
        $username = strip_tags($_POST['username']);

        // do we need to strip or convert anything in the password??

        $password = $_POST['password'];

        // try to connect to Oracle student database on birch

        $db = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
                                   (HOST = cedar.humboldt.edu)
                                   (PORT = 1521)) 
               (CONNECT_DATA = (SID = STUDENT)))";

        // now try to log on to this database

        $conn = oci_connect($username, $password, $db);

        if ($conn == false)
        {
            echo "<p> Oops, could not log into Oracle server </p>";
            require("my-std-footer.html");
            exit;
        }

        //*****-----
        // setting up an Oracle stored procedure call, and then executing it
        //*****-----

        $proc_call = 'begin sp_from_java(:bind1, :bind2, :bind3, '.
	  ':bind4, :bind5); end;';

        $stmt = oci_parse($conn, $proc_call);

        // 4th argument below is apparently the length of the value 
        //    being returned. I rather-randonly
        //    used 10, knowing all 3 of these output parameters have
	//    short little values... 8-)

	oci_bind_by_name($stmt, ":bind1", $out_var1, 10); 
        oci_bind_by_name($stmt, ":bind2", $out_var2, 10); 

        // oddity: oci_bind_by_name NEEDS a variable for its 3rd argument,
        //     even when it is passing a value for an IN parameter...!?

        $arg3 = 20;
        oci_bind_by_name($stmt, ":bind3", $arg3);         // set :bind3 to 20

        $arg4 = "red";
        oci_bind_by_name($stmt, ":bind4", $arg4);         // set :bind4 to "red"

        oci_bind_by_name($stmt, ":bind5", $out_var5, 10);
               
        oci_execute($stmt, OCI_DEFAULT);
    ?>
        <h2> RESULTS of STORED PROCEDURE CALL (sp_from_java) </h2>
        <p>
        out parameter #1 value: <?= $out_var1 ?> <br />
        out parameter #2 value: <?= $out_var2 ?> <br />
        out parameter #5 value: <?= $out_var5 ?> <br />
        </p>

    <?php
        oci_free_statement($stmt);

        //*****-----
        // now call a stored function
        //*****-----

        $funct_call = 'begin :return_value := max_on_hand(:pub_name); end;';

        $stmt = oci_parse($conn, $funct_call);

	oci_bind_by_name($stmt, ":return_value", $ret_val, 10); 

        $this_pub = 'Addison-Wesley';
        oci_bind_by_name($stmt, ":pub_name", $this_pub);

        oci_execute($stmt, OCI_DEFAULT);
    ?>

        <h2> RESULTS of STORED FUNCTION CALL (max_on_hand) </h2>
        <p>
        Max on hand for <?= $this_pub ?>: <?= $ret_val ?>
        </p>

    <?php
        $this_pub = 'Prentice Hall';
        oci_bind_by_name($stmt, ":pub_name", $this_pub);

        oci_execute($stmt, OCI_DEFAULT);
    ?>

        <h2> RESULTS of STORED FUNCTION CALL (max_on_hand) </h2>
        <p>
        Max on hand for <?= $this_pub ?>: <?= $ret_val ?>
        </p>

    <?php
        // close statement and connection...

        oci_free_statement($stmt);
        oci_close($conn);
    }

    require("my-std-footer.html");
?>