2

i am having a bit of trouble working out how to use PHP and call an oracle stored procedure.

Running the following command directly on SQL developer gives me the results I need:

 variable O_username varchar2;
 EXECUTE LOGINADMIN('John','John', 'Attractions', :O_username);

  print O_username;

However on PHP I am having trouble getting the results I require. This is my PHP code.

include 'connection.php';


 $username = ($_POST['uname']);
    $password = ($_POST['pass']);
    $role = ($_POST['admin_role']);
$query = "EXECUTE LOGINADMIN(:bind1,:bind2, :bind3, :bind4);
";
oci_bind_by_name($stmt, ":bind1", $username);
oci_bind_by_name($stmt, ":bind2", $password);
oci_bind_by_name($stmt, ":bind3", $role);
oci_bind_by_name($stmt, ":bind4", $result, 50);

$stmt = oci_parse($conn, $query);
oci_execute($stmt);
while ($row=oci_fetch_row($stmt))

$count = oci_num_rows($stmt);
ECHO $stmt;
ECHO $result;

echo OCI_RESULT ($stmt);
if ($count > 0 && $role == 'Attractions' ) {
        echo ("<SCRIPT LANGUAGE='JavaScript'>
        window.alert('Logged Succesfully!')
        window.location.href='attractionshome.php'
        </SCRIPT>");
           $_SESSION['attractionlogin'] = $username;
        exit();

It looks like I am having some trouble getting the input out so $result can see there is a variable for this.

thank you,

Improve this question

3 Answers 3

Reset to default
1

You must bind variables before calling oci_execute:

$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ":bind1", $username);
oci_bind_by_name($stmt, ":bind2", $password);
oci_bind_by_name($stmt, ":bind3", $role);
oci_bind_by_name($stmt, ":bind4", $result, $maxlength, SQLT_CHR);
oci_execute($stmt);

See Example #11 Binding parameters for a PL/SQL stored procedure in the manual.

Also note that your OUT variable call (:bind4) is incorrect; the fourth parameter should be maxlength. In the code above I've included it but you must set it based on the size of data to be returned! From the manual:

You must specify maxlength when using an OUT bind so that PHP allocates enough memory to hold the returned value.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi thanks for your help much appreciated. However I have moved the oci_execute below the binds. Also added the $maxlength. However I am slightly unclear what should be defined for the $maxlength. Is it possible for you to provide an example. The datatype which is being returned is nvarchar2 size 50.
@WAQASSHABIR A comment from the example I linked: "The second procedure parameter is an OUT bind. The default type will be a string type so binding a length 40 means that at most 40 digits will be returned.". If the field you return has a size of 50 I would say use 50 for $maxlength (but you should test this of course).
Hi Tim, still no luck I have updated my code. thanks
@WAQASSHABIR Instead of EXECUTE ..., try wrapping your code like in the examples: begin ... end;.
same issue no luck :(
0

You first need to ensure that your stored procedure is correct, and it returns the expected result:

create or replace PROCEDURE loginadmin
(
v_username    IN STAFF.USERNAME%TYPE,
v_password    IN STAFF.PASSWORD%TYPE,
v_admin_role  IN ADMIN.ADMIN_ROLE%TYPE,
O_Username    OUT STAFF.USERNAME%TYPE
)
AS
  BEGIN
  SELECT STAFF.USERNAME INTO O_Username
  FROM STAFF 
  INNER JOIN ADMIN
  ON STAFF.NINO = ADMIN.NINO
  WHERE STAFF.USERNAME = v_username AND STAFF.PASSWORD = v_password and ADMIN.ADMIN_ROLE = v_admin_role;
END;
 /

Then execute it to test its result! 

variable O_Username varchar2;
execute loginadmin ('testUsername', 'testPassword', 'testRole', :O_Username);
print O_Username;

Then in your php code, make sure you include the length as @tim have indicated. (in my example is 40). Then try to execute your stored procedure with a begin!

include 'connection.php';
$username = $_POST['uname'];
$password = $_POST['pass'];
$role = $_POST['admin_role'];
$query = 'begin loginadmin (:bind1,:bind2, :bind3, :bind4); end;';

$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ':bind1', $username);
oci_bind_by_name($stmt, ':bind2', $password);
oci_bind_by_name($stmt, ':bind3', $role);
oci_bind_by_name($stmt, ':bind4', $result, 40);
oci_execute($stmt);

Then have your code logic to check if the result matches the username and the role is Master for example and you know the rest!

if ($result == $username && $role == 'Master' ) {
        echo ("<SCRIPT LANGUAGE='JavaScript'>
        window.alert('Logged Succesfully!')
        window.location.href='master.php'
        </SCRIPT>");
           $_SESSION['attractionlogin'] = $username;
        exit();
    }
Improve this answer

Comments

0 
1 create store procedure 
create or replace PROCEDURE mysp (myrc out sys_refcursor) as
begin
     open myrc for select driver_id,login from drivers ;
     myrc:=myrc;

END mysp;

2 file php

    include 'config_conection.php';
        $conexion = oci_connect($user, $pass, $db);
        if(!$conexion){
            $e = oci_error();
            echo "error";
        }

    $driver_id=1826;


    $curs = oci_new_cursor($conexion);
    $stid = oci_parse($conexion, "begin mysp(:myrc); end;");
    oci_bind_by_name($stid, ":myrc", $curs, -1, OCI_B_CURSOR);
    oci_execute($stid);

    oci_execute($curs);  // Ejecutar el REF CURSOR como un ide de sentencia normal
    while (($row = oci_fetch_array($curs, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
        echo $row['DRIVER_ID'] . "<br />\n". $row['LOGIN'] . "<br />\n";
    }

    oci_free_statement($stid);
    oci_free_statement($curs);
    oci_close($conn)
Improve this answer

1 Comment

Please ellaborate a little bit more on the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.