Running a stored procedure in PHP: ORA-01460: requested unrealized or unreasonable conversion

Question

Executing a stored procedure in PHP gives ORA-01460. This is a simplified (the original has more than 48 input values) code in php:

$proc_sql = "BEGIN CREATE_RECORD(:b1, :b2, :b3, :b4, :b5, :b6); END;"; $bind = array("bind 1", "bind 2", "bind 3", "bind 4", "bind 5", "OUT DUMMY"); $stmt = oci_parse($conn, $proc_sql); $i = 1; $outval = ""; foreach($bind as $val){ $tmp =":b".$i; if($i < count($bind)){ oci_bind_by_name($stmt,$tmp,$val); }else{ oci_bind_by_name($stmt, $tmp, $outval, 512); } $i++; } oci_execute($stmt); 

The last line displays a warning. However, if I run the query directly in SQL Developer:

 declare re varchar2(512); begin CREATE_RECORD('bind 1', 'bind 2', 'bind 3', 'bind 4', 'bind 5', re); dbms_output.put_line(re); end; 

Insert completed successfully. This is my first project with a combination of PHP and Oracle. So I don't know if my php is wrong or the problem is elsewhere. Here is the OCI8 information from phpinfo() :

 oci8 OCI8 Support enabled OCI8 DTrace Support disabled OCI8 Version 2.0.8 Oracle Run-time Client Library Version 10.2.0.3.0 Oracle Compile-time Instant Client Version 10.2 Directive Local Value Master Value oci8.default_prefetch 100 100 oci8.events Off Off oci8.max_persistent -1 -1 oci8.old_oci_close_semantics Off Off oci8.persistent_timeout -1 -1 oci8.ping_interval 60 60 oci8.privileged_connect Off Off oci8.statement_cache_size 20 20 

PHP Version 5.5.17 and Oracle 9i Please help me find out why I get this warning. Thanks for reading.

UPDATE

Also the code is below:

 $proc_sql = "BEGIN CREATE_RECORD('bind 1', 'bind 2', 'bind 3', 'bind 4', 'bind 5', :b6); END;"; $stmt = oci_parse($conn, $proc_sql); $outval = ""; oci_bind_by_name($stmt, ':b6', $outval, 512); 

As I said above, this is a simplified version of the executable code of the procedure; in fact, I need to bind 48 IN parameters and one OUT parameter. Do I need to do something with statement_cache_size in OCI8 settings? I read the docs , but can't figure out if it has anything to do with my problem.

Answer 1

Please see this part of the PHP documentation , especially this part:

A bind call tells Oracle which memory address to read data from. For IN, this address is bound, which must contain valid data when oci_execute () is called. This means that the boundary of the variable must remain in the region until executed. If this is not the case, unexpected results or errors, such as "ORA-01460: requested unfulfilled or unreasonable appeal" may occur. For OUT connections, one symptom is not set in the PHP variable.

If I parse your code correctly, you use a local variable inside the loop to cycle through your IN parameters, so you do not satisfy the requirement to have all the values ​​in the scope when you call oci_execute after the loop finishes.