Oracle and Stored procedures and inouts

Anon

I'm using OCI8 to call a stored procedure.
There are two in vars, and one out.
:rec is the out (and should return a recordset). The ins could be hardcoded, but are not.

It looks like this:

$c1 = ocilogon("xx","xx","slamon");

$stmt = OCIParse($c1,"BEGIN SP_ALERT_CHECK(:rec, :id, :updown); END;");

OCIBindByName($stmt,":rec", &$recordset, 3200);

OCIBindByName($stmt,":updown", $updown, 32);
$updown = 'y';
OCIBindByName($stmt,":id", $id, 32);

$id = 1;

$res = OCIExecute($stmt);

When OCIExecute is called, I get the following:

Warning: OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_ALERT_CHECK' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Anyone got any ideas?

Anon

well, if the first 2 parameters are in you don't need to bind them, just put them in the sql. I've reqorked your code a bit, i hope this helps...

<?php

$c1 = ocilogon("xx","xx","slamon");

$updown = 'y';
$id = 1;

$stmt = OCIParse($c1,"BEGIN SP_ALERT_CHECK(:rec, $id, $updown); END;");
// Bind the record set, the -1 and OCI_B_CURSOR are required I don't know
// where idi you get the other stuff from.
OCIBindByName($stmt,":rec", &$recordset,-1,OCI_B_CURSOR);
//Execute the statment
OCIExecute($stmt);
//Execute your recordset, this step is also required
OCIExecute($recordset);
// now you can do ocifetchinto($recordset,$data) to get values from the record set
?>

Anon

Ok, this sort of works.
I can certainly see the logic in it.
I had to add this line:
$recordset = OCINewCursor($c1);

But that's ok. It ran, and then said there was a problem with the compilation of the query, which was resolved by putting ' round the character variable in the stored procedure. This is also fine, but then it drops back to:
Warning: OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_ALERT_CHECK' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

I know that the parameters passed to the Procedure are fine, because our DBA has checked them about 15 times (I think I'm annoying her - heh heh).
Any other ideas?

Full Code Listing Below

<?php

$c1 = ocilogon("xx","xx","slamon");

$updown = 'y';
$id = 1;

// ADDED A LINE HERE
$recordset = OCINewCursor($c1);

$stmt = OCIParse($c1,"BEGIN SP_ALERT_CHECK(:rec, $id, '$updown'); END;");
// Bind the record set, the -1 and OCI_B_CURSOR are required I don't know
// where idi you get the other stuff from.
OCIBindByName($stmt,":rec", &$recordset,-1,OCI_B_CURSOR);
//Execute the statment
OCIExecute($stmt);
//Execute your recordset, this step is also required
OCIExecute($recordset);
// now you can do ocifetchinto($recordset,$data) to get values from the record set
?>

Anon

yes try changing this line

OCIBindByName($stmt,":rec", &$recordset,-1,OCI_B_CURSOR);

OCIBindByName($stmt,"rec", &$recordset,-1,OCI_B_CURSOR);

let me know if ti worked

Anon

Ok, done that. But still no luck.
Still the same message.
(Warning: OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_ALERT_CHECK' ORA-06550: line 1, column 7: PL/SQL: Statement ignored )
However, AP (our DBA) is changing the out variablen (:rec) from a recordset to a referential cursor.
Do you think this will make a difference?

Anon

well this is the only type of cursor i've worked with so I think this should do the trick. Do you know what are the differences in those. I'm writing a column on oracle & php for this site and I think I should have somthnig on that subject in it. Can you get me some PL/SQL code that works with recordset to try it out.

yavo

Anon

Sure can.
Drop me an email, and I'll send some over.

Anon

Although with the procedure changed to return a Referential Cursor, it still doesn't work.