Re: OCI8 Stored procedure execution

Thanks, your samples worked fine. I created a variation that, once working, would make me confident that I know how the mechanism works: CREATE OR REPLACE PACKAGE my_test_cursor1 AS Procedure get_tbl(ts_name IN VARCHAR2, tbl_name OUT VARCHAR2); CURSOR tbl_cur (ts_name VARCHAR2) IS SELECT table_name FROM user_tables WHERE tablespace_name = ts_name ORDER BY table_name; v_table_name VARCHAR2(64); END my_test_cursor1; / show error CREATE OR REPLACE PACKAGE BODY my_test_cursor1 AS Procedure get_tbl(ts_name IN VARCHAR2, tbl_name OUT VARCHAR2) IS BEGIN IF NOT tbl_cur%ISOPEN THEN OPEN tbl_cur(ts_name); END IF; v_table_name := null; FETCH tbl_cur INTO tbl_name; IF tbl_cur%NOTFOUND THEN CLOSE tbl_cur; END IF; END get_tbl; END my_test_cursor1; / show error the PHP: 1 <?php 2 3 $conn = OCILogon("fredb","fmb22956"); 4 5 $stmt = OCIParse($conn,"begin my_test_cursor1.get_tbl( :ts_name, :tbl_name ); end;"); 6 7 $ts_name = "MY_TABLESPACE_NAME"; 8 9 ocibindbyname($stmt, ":ts_name", &$ts_name, 32); 10 11 ocibindbyname($stmt, ":tbl_name", &$tbl_name, 32); 12 13 ociexecute($stmt); 14 15 // while (OCIFetchInto( $stmt, &$tbl_name )) 16 17 while (OCIFetch( $stmt )) 18 19 { 20 21 var_dump($tbl_name); 22 23 } 24 25 OCIFreeStatement( $stmt ); 26 27 OCILogoff( $conn ); 28 29 ?> It produces the error: Warning: OCIFetch: ORA-24374: define not done before fetch or execute and fetch in /home/fredb/xanboo/htdocs/my_test_cursor1.php on line 17 I am unsure how to get the results in this case. I tried OCIFetch() and OCIFetchInto() and got the same error. Is my package variation workable? thies@digicol.de wrote: > fred, please see my attached sample > > On Tue, Jun 13, 2000 at 12:59:55PM -0400, Fred M. Bulah wrote: > > > > I just tried this 2 ways and got errors on both: > > > > 1 - Removed colon from OCIBindByName() call only => same error. > > 2 - Removed colon from both OCIBindByName() and OCIParse() yields the error: > > test 10: execute stored procedure 'mytest4' > > > > Warning: OCIBindByName: ORA-01036: illegal variable name/number > > in /home/fredb/xanboo/htdocs/testoci10.htm on line 15 > > > > > > Warning: OCIStmtExecute: ORA-06550: line 1, column 16: > > PLS-00201: identifier 'NAME' must be declared > > ORA-06550: line 1, column 7: > > PL/SQL: Statement ignored > > in /home/fredb/xanboo/htdocs/testoci10.htm on line 18 > > > > > > Warning: OCIFetchInto: ORA-24374: define not done before fetch or > > execute and fetch > > in /home/fredb/xanboo/htdocs/testoci10.htm on line 23 > > > > > > > > > > > > -----Original Message----- > > From: Fred M. Bulah [mailto:fredb@corecam.com] > > Sent: Tuesday, June 13, 2000 12:50 PM > > To: thies@digicol.de > > Cc: php-db@lists.php.net > > Subject: RE: [PHP-DB] OCI8 Stored procedure execution > > > > > > > > No colons on both the OCIParse() and OCIBindByName I presume? > > > > -----Original Message----- > > From: thies@digicol.de [mailto:thies@digicol.de] > > Sent: Tuesday, June 13, 2000 4:17 AM > > To: Fred Bulah > > Cc: php-db@lists.php.net > > Subject: Re: [PHP-DB] OCI8 Stored procedure execution > > > > > > On Mon, Jun 12, 2000 at 06:39:14PM -0400, Fred Bulah wrote: > > > > > > Another problem: > > > > > > The following PHP4 code is supposed to retrieve a list of user tables: > > > > > > 1 <?php > > > 2 print "<HTML><PRE>"; > > > 3 print "test 10: execute stored procedure > > > 'mytest4'<br>"; > > > 4 $conn = OCILogon("fredb","fmb22956"); > > > 5 > > > 6 // $stmt = OCIParse( $conn, "select table_name from user_tables" ); > > > 7 > > > 8 $stmt = OCIParse( $conn, "begin mytest4( :name ); end;" ); > > // What about here ^ ? > > > > > > > 9 OCIBindByName( $stmt, ':name', &$name, 32 ); > > > > OCIBindByName( $stmt, 'name', &$name, 32 ); // <- no colon. > > > > > 10 OCIExecute( $stmt ); > > > 11 $nrows = 0; > > > 12 while ( OCIFetchInto( $stmt, &$results, ORA_NUM ) ) > > > 13 { > > > 14 print "results[$nrows]=$results[0]<br>"; > > > 15 $nrows++; > > > 16 } > > > 17 > > > 18 OCIFreeStatement( $stmt ); > > > 19 OCILogOff($conn); > > > 20 print "</PRE></HTML>"; > > > 21 ?> > > > > > > Fails with the following error: > > > > > > test 10: execute stored procedure 'mytest4' > > > > > > Warning: OCIFetchInto: ORA-24374: define not done before fetch or execute > > and > > > fetch > > > in /home/fredb/xanboo/htdocs/testoci10.htm on line 23 > > > > > > The stored procedure definition is: > > > > > > create or replace procedure mytest4( my_data out varchar2 ) > > > is > > > > > > cursor get_data is select table_name from user_tables; > > > begin > > > open get_data; > > > loop > > > dbms_output.put_line (my_data); > > > fetch get_data into my_data; > > > exit when get_data%notfound; > > > end loop; > > > close get_data; > > > end; > > > / > > > > > > I am not at all sure that this is the correct way to do this. > > > Clearly from the result it probably is not. > > > It works fine if I use the embedded SQL on line 6 instead of the stored > > > procedure call and bind on lines 8 and 9. > > > The stored procedure works in sqlplus, but does require the directive "set > > > serveroutput on" prior to invocation in order to see output displayed on > > the > > > screen. > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > Thies C. Arntzen "One Big-Mac, Small Fries and a Coke!" > > Digital Collections Phone +49 40 235350 Fax +49 40 23535180 > > Hammerbrookstr. 93 20097 Hamburg / Germany > > -- > > Thies C. Arntzen "One Big-Mac, Small Fries and a Coke!" > Digital Collections Phone +49 40 235350 Fax +49 40 23535180 > Hammerbrookstr. 93 20097 Hamburg / Germany > > ------------------------------------------------------------------------ > > refcurs.phpName: refcurs.php > Type: application/x-httpd-php > > refcurs.sqlName: refcurs.sql > Type: Plain Text (text/plain)