It's not a good practice to pass values for IN condition as string concatenation. First things, of course, security and correctness, but next point is performance.
Each time you call the statement database engine parses it, builds a query plan and after that performs actions specified in SQL statement.
If you build up query text from scratch each time then all three stages executed each time.
But if you use bind variables all times query looks the same so database uses a cached query plan what speeds up query execution. Even you can call oci_parse() only once and reuse $stmt variable with different set of supplied parameters.
So, for the best performance you must use bind variable and fill it with array using oci_bind_array_by_name.
Additional thing is that retrieving results using oci_fetch_all may perform faster than reading result set row by row, but it depends on logic of processing results.
Update
Seems that passing array parameters works only if you going to execute PL/SQL block and can't use it with SQL statements. But another possibility is to use collections to pass list of parameter values. It's possible to satisfy conditions of the question even with arrays, but this way is less elegant.
Besides of a different ways to query a database there are such thing as system settings. In case of PHP there are some parameters in php.ini file which controls interaction with Oracle. One of them (oci8.statement_cache_size) related to a query caching and performance.
Examples
All examples uses the same data setup in Oracle.
To pass data I choose predefined SYS.ODCIVarchar2List type, but it's also possible to define custom type with same characteristics (demonstrated in data setup example).
Below is code to demostate data scheme setup and principle of using collections in DML.
SQLFiddle
create table myTable(value varchar2(100), key varchar2(100))
/
insert into myTable(value, key)
select * from (
select 'apple', 'apple_one' from dual union all
select 'apple', 'apple_two' from dual union all
select 'banana', 'banana_one' from dual union all
select 'orange', 'orange_one' from dual union all
select 'orange', 'orange_two' from dual union all
select 'potato', 'potato_one' from dual
)
/
create or replace type TCustomList as table of varchar2(4000)
/
create or replace package TestPackage as
type TKeyList is table of varchar2(1000) index by binary_integer;
function test_select(pKeyList in out TKeyList) return sys_refcursor;
end;
/
create or replace package body TestPackage is
function test_select(pKeyList in out TKeyList) return sys_refcursor
is
vParam sys.ODCIVarchar2List := sys.ODCIVarchar2List();
vCur sys_refcursor;
vIdx binary_integer;
begin
vIdx := pKeyList.first;
while(vIdx is not null) loop
vParam.Extend;
vParam(vParam.last) := pKeyList(vIdx);
vIdx := pKeyList.next(vIdx);
end loop;
open vCur for
select * from myTable where value in (select column_value from table(vParam))
;
return vCur;
end;
end;
/
Queries to demonstrate collections:
--select by value list
select * from myTable
where value in (
select column_value
from table(Sys.ODCIVarchar2List('banana','potato'))
)
/
--same with custom type
select * from myTable
where value in (
select column_value
from table(TCustomList('banana','potato'))
)
/
--same with demonstration of casting
select * from myTable
where value in (
select column_value
from table(cast(TCustomList('banana','potato') as Sys.ODCIVarchar2List))
)
/
Example 1 - call from PHP using collections
<?php
$keyList = array('apple', 'potato');
$conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME");
$stmt = oci_parse($conn, "SELECT * FROM myTable where value in (select column_value from table(:key_list))");
$coll = oci_new_collection($conn, 'ODCIVARCHAR2LIST','SYS');
for ($i=0; $i < count($keyList); $i++) {
$coll->append($keyList[$i]);
}
oci_bind_by_name($stmt, 'key_list', $coll, -1, OCI_B_NTY);
oci_execute($stmt);
while($row = oci_fetch_array($stmt, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}
"; // Print the values
}
echo "---
";
$coll->free();
//-- Run statement another time with different parameters
//-- without reparsing.
$coll = oci_new_collection($conn, 'ODCIVARCHAR2LIST','SYS');
$coll->append('banana');
oci_bind_by_name($stmt, 'key_list', $coll, -1, OCI_B_NTY);
oci_execute($stmt);
while($row = oci_fetch_array($stmt, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}
"; // Print the values
}
echo "---
";
$coll->free();
oci_free_statement($stmt);
oci_close($conn);
?>
Example 2 - Call from PHP using array and package
<?php
$keyList = array('apple', 'potato');
$conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME");
$stmt = oci_parse($conn, "begin :cur := TestPackage.test_select(:key_list); end;");
$curs = oci_new_cursor($conn);
oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR);
oci_bind_by_name($stmt, "cur", $curs, -1, OCI_B_CURSOR);
oci_execute($stmt);
oci_execute($curs);
while($row = oci_fetch_array($curs, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}
"; // Print the values
}
echo "---
";
//-- Run statement another time with different parameters
//-- without reparsing.
$keyList = array('banana');
oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR);
oci_execute($stmt);
oci_execute($curs);
while($row = oci_fetch_array($curs, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}
"; // Print the values
}
echo "---
";
oci_free_statement($stmt);
oci_close($conn);
?>
Example 3 - call from PHP using array and anonymous block
<?php
$keyList = array('apple', 'potato');
$conn = oci_pconnect("user_name", "user_password", "SERVER_TNS_NAME");
$stmt = oci_parse($conn, "
declare
type TKeyList is table of varchar2(4000) index by binary_integer;
pKeyList TKeyList := :key_list;
vParam sys.ODCIVarchar2List := sys.ODCIVarchar2List();
vIdx binary_integer;
begin
-- Copy PL/SQL array to a type which allowed in SQL context
vIdx := pKeyList.first;
while(vIdx is not null) loop
vParam.Extend;
vParam(vParam.last) := pKeyList(vIdx);
vIdx := pKeyList.next(vIdx);
end loop;
open :cur for select * from myTable where value in (select column_value from table(vParam));
end;
");
$curs = oci_new_cursor($conn);
oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR);
oci_bind_by_name($stmt, "cur", $curs, -1, OCI_B_CURSOR);
oci_execute($stmt);
oci_execute($curs);
while($row = oci_fetch_array($curs, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}
"; // Print the values
}
echo "---
";
//-- Run statement another time with different parameters
//-- without reparsing.
$keyList = array('banana');
oci_bind_array_by_name($stmt, "key_list", $keyList, 2, 100, SQLT_CHR);
oci_execute($stmt);
oci_execute($curs);
while($row = oci_fetch_array($curs, OCI_ASSOC)) {
echo "{$row['KEY']}, {$row['VALUE']}
"; // Print the values
}
echo "---
";
oci_free_statement($stmt);
oci_close($conn);
?>
