RE: [PHP-DB] OCI8 Stored procedure execution

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 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. -----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