如何使用ODBC在PHP上调用oracle存储过程

Question

I am trying to call an Oracle stored procedure at a PHP website using ODBC. I have a table named "employees". I want to see the total employee number at the website. Below I am describing the function, procedure and pl/sql to run normally from sql.

FUNCTION:

CREATE OR REPLACE FUNCTION func_totalEmployees 
RETURN number IS
t_emp number;
BEGIN
        select count(*) INTO t_emp from employees;
    RETURN t_emp;
END;

This function will count all entry at employees table. Then store the value at

t_emp

PROCEDURE:

CREATE OR REPLACE PROCEDURE pro_totalEmployees(totalEmployees OUT number) 
IS
BEGIN
    totalEmployees := func_totalEmployees;

END;

This procedure will run our function and output one value using totalEmployees variable.

PL/SQL:

DECLARE
see_totalEmployees number;
BEGIN
    pro_totalEmployees(see_totalEmployees);
        dbms_output.put_line('Total Employees you have: ' || see_totalEmployees);
END;

Now the execution part. This is just simple pl/sql query which output the total number of employees from our procedure using variable see_totalEmployees

That's it!

So at sql, I got the output: Total Employees you have: 107

NOW MY QUESTION:

How can I store this 107 into a PHP variable using ODBC?

Please Help. Thanks in advance

Answer 1

You can try some thing like this,

    <?php
    $Tot_emp = 0;

    // Create connection to db
    $conn = odbc_connect('db_name','user_name','password') or die;

    // Call your procedure
    $stmt = $conn->prepare("CALL sp_returns_string(?)");

    // Bind the output parameter
    $stmt->bindParam(1, $Tot_emp, PDO::PARAM_STR, 4000);    

    // call the stored procedure
    $stmt->execute();        

    // $message is now populated with the output value
    print "$Tot_emp
";
    ?>