Re: OCI8 Stored procedure execution

On Tue, Jun 13, 2000 at 12:50:27PM -0400, Fred M. Bulah wrote: > > No colons on both the OCIParse() and OCIBindByName I presume? no. OCIParse needs the colons as documented in the Oracle SQL Reference. > > -----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