ORACLE Stored Procedure in PHP processing

Question

I have a question regarding stored procedure so I have this procedure saved:

CREATE OR REPLACE PROCEDURE WELTESADMIN.TOTALFABRICATION
    (SUBCONT IN VARCHAR, PROJECT IN VARCHAR, TOTALFABRICATIONRESULT OUT NUMBER) AS
BEGIN
    SELECT FABRICATION.MARKING + FABRICATION.CUTTING + FABRICATION.ASSEMBLY + FABRICATION.WELDING + 
            FABRICATION.DRILLING + FABRICATION.FINISHING
    INTO TOTALFABRICATIONRESULT
    FROM FABRICATION
    WHERE SUBCONT_ID = SUBCONT 
        AND PROJECT_NAME = PROJECT;
END;

And im processing that in PHP such as,

    $subcontValue = "RIYANTO";
    $projectValue = "PROCESSHOUSE";

    $sql = "BEGIN TOTALFABRICATION(:SUBCONT, :PROJECT); END;";
    $stmt = oci_parse($conn, $sql);

    oci_bind_by_name($stmt, ":SUBCONT", $subcontValue);
    oci_bind_by_name($stmt, ":PROJECT", $projectValue);
    oci_define_by_name($stmt, "TOTALFABRICATIONRESULT", $result);

    oci_execute($stmt);

    echo $result;

I am sure that all the variables supplied are correct, connection is ok and processable in SQL PLUS instead, im getting this error in the php side,

Warning: oci_execute(): ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'TOTALFABRICATION' ORA-06550: line 1, column 7: PL/SQL: Statement ignored in C:\xampp\htdocs\WeltesInformationCenter\newEmptyPHP.php on line 39

Sylvain Leroux · Accepted Answer · 2014-09-19 07:56:36Z

1

Your procedure has actually three parameters (two IN and one OUT):

CREATE OR REPLACE PROCEDURE WELTESADMIN.TOTALFABRICATION
    (SUBCONT IN VARCHAR, 
     PROJECT IN VARCHAR, 
     TOTALFABRICATIONRESULT OUT NUMBER) AS

So, from PHP, you should write that:

    $sql = "BEGIN TOTALFABRICATION(:SUBCONT, :PROJECT, :TOTALFABRICATIONRESULT); END;";
    #                                                  ^^^^^^^^^^^^^^^^^^^^^^^

And bind that output parameter to some variable:

oci_bind_by_name($stmt, "TOTALFABRICATIONRESULT", $result, 300);
#                                                          ^^^
#                                                        maxlength

Please note that according to the documentation:

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

answered Sep 19, 2014 at 7:56

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

ORACLE Stored Procedure in PHP processing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related