Returning Oracle out paramter array

[JediDan]

Hello all,

I need to deal with an array being passed back from an Oracle stored procedure which has several out parameters returning arrays (in addition to several in parameters and regular out parameters). Is this possible?

At the moment, the code looks something like this:

$Oracle = OCILogon(...);

$curs = OCINewCursor($Oracle);

$proc = OCIParse($Oracle,'begin pack_test.sp_test(...,:curs); end');

OCIBindByName($proc,":curs",$curs,-1,OCI_B_CURSOR);

OCIExecute($proc);
OCIExecute($curs);

I am getting "wrong number or types of arguments" errors. The actual proc has so many parameters, it's just too lengthy to post here.

Any suggestions appreciated.

 

[sleipnir214]

Which function invocation is generating that error? How exactly does your code invoke that function?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[JediDan]

Ok, here's my complete code. I made a sample program.

PL/SQL package spec:

package pack_test is

type array_numbers is table of pls_integer index by binary_integer;

out_numarray array_numbers;

procedure test(x number,thearray out pack_test.out_numarray%type);

end;

PL/SQL package body:

package body pack_test is

procedure test(x number,thearray out pack_test.out_numarray%type) is

begin
    thearray(0) := x;
    thearray(1) := x + 1;    
end;

end;

The PHP:

<?
	$Oracle = OCILogon('SCOTT','TIGER','TESTDB');
	$array = OCINewCursor($Oracle);
	$stmt = OCIParse($Oracle,"begin pack_test.test('10',:array); end;");	OCIBindByName($stmt,":array",$array,-1,OCI_B_CURSOR);
	OCIExecute($stmt);
?>

Finally, the PHP errors:[pre]
Warning: ociexecute(): OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'TEST' ORA-06550: line 1, column 7: PL/SQL: Statement ignored in /home/daniear/public_html/oracletest.php on line 10[/pre]

Thanks for helping.

 

[sleipnir214]

I'm not that hip to using PHP with Oracle and procedures, but there is one thing in the online documentation for ocibindbyname():

The type variable tells oracle, what kind of descriptor we want to use. Possible values are: OCI_B_FILE (Binary-File), OCI_B_CFILE (Character-File), OCI_B_CLOB (Character-LOB), OCI_B_BLOB (Binary-LOB) and OCI_B_ROWID (ROWID)


OCI_B_CURSOR doesn't show up in that list.


Want the best answers? Ask the best questions!

TANSTAAFL!!