从PHP运行Oracle过程会返回错误数量或类型的参数错误

Question

I am trying to troubleshoot one piece of PHP code that simply calls one of the Oracle procedures. Now, I don't know a thing about Oracle procedures, I just need this procedure to be called with 2 parameters and return hardcoded value. I started with create a package and a dummy procedure:

CREATE OR REPLACE 
PACKAGE TESTAPI1 AS 
  PROCEDURE GETTEST;
END EMAILAPI1;

CREATE OR REPLACE PROCEDURE GETTEST(p1 IN VARCHAR, p2 IN CLOB) AS 
BEGIN
  NULL;
END GETTEST;

PHP code that calls this procedure looks like this:

$stid = oci_parse($this->conn, "begin testapi1.gettest(:p1, :p2); end;");
$clob = oci_new_descriptor($this->conn, OCI_D_LOB); 

oci_bind_by_name($stid, ':p1', "some string", 4000, SQLT_CHR);
oci_bind_by_name($stid, ':p2', $clob, -1, SQLT_CLOB);

$r = oci_execute($stid);

And after running this code, I'm getting this error:

 [code] => 6550
    [message] => ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETTEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
    [offset] => 6
    [sqltext] => begin testapi1.gettest(:p1, :p2); end;

Can anybody tell me what's wrong this code?

Answer 1

I noticed some issues which I hope will assist you:

1. your package identifiers must match
2. your package procedure must include a parameter list so that the signature matches the procedure.

See below for changes. This code now compiles w/o errors.

CREATE OR REPLACE
PACKAGE TESTAPI1 AS
  PROCEDURE GETTEST(p1 VARCHAR, p2 CLOB);
END TESTAPI1;
/

CREATE OR REPLACE PROCEDURE GETTEST(p1 IN VARCHAR, p2 IN CLOB) AS
BEGIN
  NULL;
END GETTEST;
/