Re: OCI8 Stored procedure execution
| From: | Fred Bulah | Date: | Mon, 12 Jun 2000 22:39:14 +0000 |
| Subject: | Re: OCI8 Stored procedure execution | ||
| References: | 1 2 3 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-360@lists.php.net to get a copy of this message | ||
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;" );
9 OCIBindByName( $stmt, ':name', &$name, 32 );
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.