OCI8 stored procedure returning result set
| From: | Kostya Vasilyev | Date: | Mon, 13 Dec 1999 00:32:40 +0000 |
| Subject: | OCI8 stored procedure returning result set | ||
| Groups: | php.version4 | ||
| Request: | Send a blank email to php-version4+get-7839@lists.php.net to get a copy of this message | ||
Hi --
Sorry if this is an FAQ, but I can't find _working_ example anywhere..
The php site has some code under OCNewCursor, but it does not seem to
work.
I am trying to use an ORACLE stored procedure that returns a result set
as a cursor from PHP. Here is my Oracle code:
CREATE OR REPLACE PACKAGE video_pkg
AS
TYPE type_vc100 IS
TABLE OF VARCHAR2(100)
INDEX BY BINARY_INTEGER;
END video_pkg;
CREATE OR REPLACE PROCEDURE category_GetTest_v1
(
r_cat_desc OUT video_pkg.type_vc100
)
AS
CURSOR category_cursor IS
SELECT descr -- this is varchar2(100)
FROM category
WHERE parent_id = 0
ORDER BY descr;
v_descr VARCHAR2(100);
v_indx BINARY_INTEGER := 0;
BEGIN
OPEN category_cursor;
LOOP
FETCH category_cursor
INTO v_descr;
EXIT WHEN category_cursor%NOTFOUND;
v_indx := v_indx + 1;
r_cat_desc(v_indx) := v_descr;
END LOOP;
CLOSE category_cursor;
END category_GetTest_v1;
And here is my PHP code:
$dbcr = OCINewCursor ($dbconn);
$dbst = OCIParse ($dbconn, "BEGIN category_GetTest_v1(:r_desc);
END;");
OCIBindByName ($dbst, "r_desc", &$dbcr, -1, OCI_B_CURSOR);
OCIExecute ($dbst);
OCIExecute ($dbcr);
What happens is that I get an error at "OCIExecute ($dbst);":
Warning: OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong
number or types of arguments in call to 'CATEGORY_GETTEST_V1' ORA-06550:
line
1, column 7: PL/SQL: Statement ignored in directory.php on line 29
Any ideas on what am I doing wrong?
Thanks!
-- Kostya