CODEWITHSUNDEEP
phporacle-databasestored-procedures
Cԃաԃ
Cԃաԃ

Reputation: 1258

Executing stored procedure in PHP : ORA-01460: unimplemented or unreasonable conversion requested

Executing stored procedure in PHP gives ORA-01460. This is the simplified(original has over 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);

Last line produces the warning. However if I run 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 is completed successfully. It's my first project with PHP & Oracle combination. So I don't know if my php is incorrect or the problem lies elsewhere. Here is OCI8 info 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 find out why am I getting this warning. Thank you for reading.

UPDATE

The code below also works:

$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 simplified version of executing procedure code, in reality I need to bind 48 parameters IN and one OUT parameter. Does it have to do something with statement_cache_size in OCI8 settings ? I've read the docs but can't really understand if it has anything to do with my problem.

Upvotes: 6

Views: 1397

Answers (2)

Anjana Silva
Anjana Silva

Reputation: 9221

For those who have problems when binding parameters inside a for-each loop keep reading.

One of the answers here has the following comment, inside a sample code snippet which says about binding parameters inside a loop - which makes sense.

foreach ($arr as $key => $val) { 
   // oci_bind_by_name($stid, $key, $val) does not work
   // because it binds each placeholder to the same location: $val
   // instead use the actual location of the data: $arr[$key]
}

According to the above statement, the following DO NOT work,

$arr = array(':a' => 1, ':b' => 2);
foreach ($arr as $key => $val) {    
    oci_bind_by_name($stid, $key, $val);
}

The following DO work,

$arr = array(':a' => 1, ':b' => 2);
foreach ($arr as $key => $val) {    
    oci_bind_by_name($stid, $key, $arr[$key]);
}

I hope someone will find this useful :)

Upvotes: 0

David Komanek
David Komanek

Reputation: 124

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

A bind call tells Oracle which memory address to read data from. For IN binds that address needs to contain valid data when oci_execute() is called. This means that the variable bound must remain in scope until execution. If it doesn't, unexpected results or errors such as "ORA-01460: unimplemented or unreasonable conversion requested" may occur. For OUT binds one symptom is no value being set in the PHP variable.

If I undestand your code well, you use a local variable inside a loop to cycle through your IN parameters, so you do not satisfy the demand to have all the values in scope when you call oci_execute after the cycle is completed.

Upvotes: 1

Related Questions