Re: OCI8 Stored procedure execution
| From: | thies at digicol dot de | Date: | Tue, 13 Jun 2000 08:17:01 +0000 |
| Subject: | Re: OCI8 Stored procedure execution | ||
| References: | 1 2 3 4 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-368@lists.php.net to get a copy of this message | ||
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;" );
> 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