Re: OCI8 Stored procedure execution

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

Attachment: [application/x-httpd-php] refcurs.php
CREATE OR REPLACE PACKAGE info AS TYPE the_data IS REF CURSOR RETURN all_users%ROWTYPE; PROCEDURE output(return_data IN OUT the_data); END info; / CREATE OR REPLACE PACKAGE BODY info AS PROCEDURE output(return_data IN OUT the_data) IS BEGIN OPEN return_data FOR SELECT * FROM all_users; END output; END info; /