RE: [PHP-DB] OCI8 Stored procedure execution
| From: | Fred M. Bulah | Date: | Tue, 13 Jun 2000 16:59:55 +0000 |
| Subject: | RE: [PHP-DB] OCI8 Stored procedure execution | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-379@lists.php.net to get a copy of this message | ||
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