How to pass an array of strings to a plsql routine in php with pdo

Question

I'm using PHP 5.5 on Windows with the PDO extension. Everything with oracle worked so far even with plsql scripts. The following is simplified code from my plsql package and my php code.

Basically I just want to hand the array $remarks over to the plsql procedure VARRAY_TEST. It's not about the logic of the function, it's about the pdo call itself.

TYPE remarkarray IS VARRAY(64) of varchar2;


PROCEDURE VARRAY_TEST
(
  remarks     IN  remarkarray,
  examplemsg  OUT varchar2 
)
AS

BEGIN

  for remarkcounter in remarks.first ..remarks.last loop
    examplemsg := examplemsg || ' ' || remarks(remarkcounter);
  end loop;

END VARRAY_TEST;

Now comes my PHP-PDO code.

$exampleMsg = "";
$remarks = ["hoho", "haha"];
$query = $this->prepare("begin MY_PACKAGE.VARRAY_TEST(:remarks, :exampleMsg); end;");

try
{
    $query->bindParam(':remarks', $remarks, PDO::PARAM_STR);
    $query->bindParam(':exampleMsg', $exampleMsg, PDO::PARAM_STR, 200);
    $query->execute();
    var_dump($exampleMsg);
}
catch(Exception $ex)
{
    var_dump($ex);
}

Unfortunatelly the following exception is thrown:

class PDOException#50 (9) {
...
  string(257) "SQLSTATE[HY000]: General error: 6550 OCIStmtExecute: ORA-06550: row 1, column 7:
  PLS-00306: Wrong number or types of arguments in call 'VARRAY_TEST'
  ORA-06550: Zeile 1, Spalte 7: PL/SQL: Statement ignored (ext\pdo_oci\oci_statement.c:148)"
...
}

Does anyone have any idea how fix that? Also workarounds are highly appreciated.

Thx in advance.

EDIT:

Tried so far without success:

Removed the last/third parameter altogether from $query->bindParam(':remarks'...

class PDOException#50 (9) {
...
  string(257) "SQLSTATE[HY000]: General error: 6550 OCIStmtExecute: ORA-06550: row 1, column 7:
  PLS-00306: Wrong number or types of arguments in call 'VARRAY_TEST'
  ORA-06550: Zeile 1, Spalte 7: PL/SQL: Statement ignored (ext\pdo_oci\oci_statement.c:148)"
...
}

Added PDO::PARAM_STMT in stead of PDO::PARAM_STR resulting in

class PDOException#50 (9) {
...
  string(144) "SQLSTATE[HY000]: General error: 1008 OCIStmtExecute: ORA-01008: Not all variables seem to have a bound value (ext\pdo_oci\oci_statement.c:148)"
...
}

Any change if you remove PDO::PARAM_STR from the first bind? — Mihai
– Mihai, Commented Jan 4, 2016 at 15:48
Yes, I did, unfortunatelly without succes. I added an EDIT section to reflects all trials. But thanks for your comment. — McK
– McK, Commented Jan 5, 2016 at 6:25

Tagar · Accepted Answer · 2016-01-09 00:02:15Z

3

+50

I looked at the source files of PDO OCI, *.c from https://pecl.php.net/package/PDO_OCI

It does not look PDO supports arrays as far as I can see. Btw, latest PDO_OCI version 1.0 dates back to 2005-11-26 in that page, 11 years ago.

Looking at OCI documentation https://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci05bnd.htm#r7c1-t10 , binding "Array of Structures or Static Arrays" requires OCIBindArrayOfStruct() call. Source code *.c of PDO_OCI does not have calls of this function.

answered Jan 9, 2016 at 0:02

Tagar

15k7 gold badges102 silver badges116 bronze badges

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

1 Comment

McK Over a year ago

Thanks for pointing out your approach how you were searching the sources.

Tagar · Accepted Answer · 2016-01-06 23:48:09Z

1

http://php.net/manual/en/function.oci-bind-array-by-name.php

$c = oci_connect('hr', 'hrpwd', '//localhost/XE'); 
$a = array('abc', 'def', 'ghi', 'jkl'); 
$s = oci_parse($c, "begin mypkg.myinsproc(:p1); end;"); 
oci_bind_array_by_name($s, ":p1", $a, count($a), -1, SQLT_CHR); 
oci_execute($s);

Hope this helps.

answered Jan 6, 2016 at 23:48

Tagar

15k7 gold badges102 silver badges116 bronze badges

2 Comments

McK Over a year ago

thanks, but I already knew that method, but it's not a solution to my problem since we are using PDO. If you can show me an elegant way to mix PDO and the OCI-Extension I'm going to accept that answer of course.

Tagar Over a year ago

Got it. Thanks for clarifying. See my new answer. PDO-OCI does not have implementation for "Array of Structures or Static Arrays".

Collectives™ on Stack Overflow

How to pass an array of strings to a plsql routine in php with pdo

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related