Re: 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