RE: [PHP-DB] OCI8 Stored procedure execution / Design question

I removed the OCIFetch() and instead replaced it by for( OCIExecute( $stmt ); !isset( $return_val ); OCIExecute( $stmt ) ) { } where $return_val is a bound output parameter to the stored procedure. The stored procedure sets the value to null once cursor%NOTFOUND condition occurs. This works fine. What this means is that there are at least 2 ways to write stored procedures that return rows: 1. by opening a cursor and returning a ref cursor pointing to it. 2. by opening a cursor and fetching data into an output variable Case 1 uses OCIFetch() to retrieve rows while case 2 uses OCIExecute(). Design Questions: - Which is more efficient? Are there any clear drawbacks? - I have a wrapper class that is a thin layer over the MySQL API. I converted this to be a wrapper over the OCI8 API. The existing wrapper class has a single db_query() interface which directly executed embedded SQL for MySQL (via mysql_db_query). I am now faced with adapting this interface for OCI8 and would like to make it handle both raw SQL and calls to stored procedures and functions. Unfortunately, the mechanism to retrieve data is different depending on whether the operation is a retrieval or a modification. Furthermore, there does not seem to be a way to determine ahead of time whether a stored procedure will return a cursor based results set or not. My only options appear to be: - break it into separate interfaces for retrieval, retrieval by cursor, and modification. - a single interface with some medium-to-complex logic Has anyone faced this issue before? Are there any other possibilities? -----Original Message----- From: thies@digicol.de [mailto:thies@digicol.de] Sent: Wednesday, June 14, 2000 9:06 AM To: Fred M. Bulah Cc: php-db@lists.php.net Subject: Re: [PHP-DB] OCI8 Stored procedure execution On Wed, Jun 14, 2000 at 08:08:36AM -0400, Fred M. Bulah wrote: > > Thies, > All of the examples in the OCIBindByName() online description use variable > names with the colon prefix. I interpreted this to mean that they were > always necessary until your example with cursors. Cursors apparently are an my mistake - obviouly it makes no difference if you use ":name" or "name" in ocibindbyname. (just tried both in my refcurs.php) > exception(and possibly LOB and B_FILE). In this case, I thought that > ts_name had to be bound as a normal bind variable. Not so? I'm trying to ??? > understand completely what the rules are. Point taken aboout OCIFetch() - > it should only be used for explicit (vs implicit) cursors. from the manual: --- OCIFetch() fetches the next row (for SELECT statements) into the internal result-buffer. --- a refcursor is a special case where the reference to a "SELECT statements" is returned via a stored procedure or a nested table. the sample in the manual should be clear enough to understand OCIBindByName the sample (refcurs.php) i sent you shows in addition to that how to return REFCURSORs from stored procedues. tc > > > -----Original Message----- > From: thies@digicol.de [mailto:thies@digicol.de] > Sent: Wednesday, June 14, 2000 6:28 AM > To: Fred Bulah > Cc: php-db@lists.php.net > Subject: Re: [PHP-DB] OCI8 Stored procedure execution > > > > hmm, > > ocibindbyname($stmt, ":ts_name", &$ts_name, 32); > > looks wrong to me. why the colon? > > also note that you should only call ocifetch for cursors! you > stored procedure returns simple varchar2 fiels so your > results are in $ts_name and $tbl_name after ociexecute(). no > need to fetch anything. my sample returned a REFCURSOR, > therefore we needed the fetch (note: on the returned cursor). > > tc > > On Tue, Jun 13, 2000 at 03:25:03PM -0400, Fred Bulah wrote: > > > > 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 > y_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) > > -- > > 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net