How to execute this procedure and get the result?

Question

I have this oracle procedure:

procedure ACCOUNT_STATMENT(p_customerid masteraccounts.customerid%type,

                             p_date1      date,

                             p_date2      date,

                             p_val1       out number,

                             p_val2       out number,

                             p_grid       out sys_refcursor)

and I'm calling it using PHP:

$DATE1 = "1-1-2013";
$DATE2 = "1-4-2013";
$ID = 53721;
$sth1 = $conn->prepare("CALL ISPPRO.USERPKG.ACCOUNT_STATMENT(:p_customerid, :DATE1, :DATE2, :p_val1, :p_val2, :p_grid)");
            $sth1->bindParam(":p_customerid", $ID, PDO::PARAM_STR);
            $sth1->bindParam(':DATE1', $DATE1, PDO::PARAM_STR); 
            $sth1->bindParam(':DATE2', $DATE2, PDO::PARAM_STR);
            $sth1->bindParam(':p_val1', $p_val1, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 
            $sth1->bindParam(':p_val2', $p_val2, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 
            $sth1->bindParam(':p_grid', $p_grid, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 
            $sth1->execute();
    print_r($conn->errorInfo());
    $result = $sth1->fetchAll();
    print_r($result);
    print_r($p_val1);
    print_r($p_val2);
    print_r($p_grid);

and I get this error:

Array
(
    [0] => HY000
    [1] => 6553
    [2] => OCIStmtExecute: ORA-06553: PLS-306: wrong number or types of arguments in call to 'ACCOUNT_STATMENT'
 (/root/downloads/PDO_OCI-1.0/oci_statement.c:142)
)

What is the problem?

You've got `PDO::PARAM_STR` everywhere. Tried replacing that with something that matches the procedure's parameters? — Mat, Mar 30 '13 at 13:41
@Mat , Yes, I tried few things but none of them working and I don't know how handle `sys_refcursor` in `PHP` — Saleh, Mar 30 '13 at 13:51
possible duplicate of [Procedures of Oracle in php with PDO](http://stackoverflow.com/questions/8121244/procedures-of-oracle-in-php-with-pdo), assuming the refcursor is your only problem (i.e. you know how to properly map the normal types). — Mat, Mar 30 '13 at 13:54
It may be your date format: `"1-4-2013"`. Oracle is expecting a format string matching the `NLS_DATE_FORMAT` database setting. You can just define your format mask in the procedure call with a nested `TO_DATE(:DATE1,'MM-DD-YYYY')` (or whatever your format is). — WoMo, Mar 30 '13 at 15:35

score 0 · Answer 1 · answered Mar 30 '13 at 15:34

0

Have you created bind variable before calling this procedure in php. you are using :p_customerid,::DATE1 for output variable you must create bind variable or you can execucte this procedure in oracle

for example

var i number; \in oracle

answered Mar 30 '13 at 15:34

rashgang

13
1
10

How to execute this procedure and get the result?

1 Answers1