I have this query
select * from foo where id in (:ListOfIds)
i call this method many times, but each call has different values for example
select * from foo where id in (1,2,5)
select * from foo where id in (3,4,6)
So how can i pass the list to this query ?
In the case that the maximum size of the IN list is known, and it isn't too big, it is best to use a bind variable per potential list item. For any values that the app doesn't know, bind a null.
For example, the SQL statement could be:
sql = `select * from foo where id in (:v1, :v2, :v3, :v4)`;
Then, if you only have three data items, you would bind:
binds = [30, 60, 90, null];
const results = await connection.execute(sql, binds);
In an app where this query is frequently invoked, you get the advantage this efficiently uses the internal statement cache, which improves performance of repeated queries.
Another solution is to use binds (for security) but build up the exact SQL string like:
binds = ['Christopher', 'Hazel', 'Samuel'];
sql = `select first_name, last_name from employees where first_name in (`;
for (var i=0; i < binds.length; i++)
sql += (i > 0) ? ", :" + i : ":" + i; sql += ")";
// "sql" becomes "select first_name, last_name from employees where first_name in (:0, :1, :2)"
But, depending how often this query is executed, and how changeable the number of bind values is, you can end up with lots of 'unique' query strings. So you might not get statement caching benefits that executing a fixed SQL statement would give - and you could push out other statements from the cache, causing repeated executions of them to be inefficient.
Regarding the JavaScript itself, last time I checked, this simple for loop was faster than map/join solutions.
For very large numbers of bind values, you can try this:
const sql = `SELECT first_name, last_name
FROM employees
WHERE first_name IN (SELECT * FROM TABLE(:bv))`;
const inlist = ['Christopher', 'Hazel', 'Samuel']; // a very large list
const binds = { bv: { type: "SYS.ODCIVARCHAR2LIST", val: inlist } };
const result = await connection.execute(sql, binds);
console.dir(result, { depth: null });
Note it uses an object type which takes some extra round-trips, so this solution is more suited for the cases where the earlier solutions are not viable,.
Reference:
Do not manually concat strings of a SQL query with parameters, this may lead to SQL Injection vulnerabilities.
I've created a tagged template literal sql to write my SQL queries.
const query1 = sql`select * from table where a in (${[1, 2, 3]})`
query1 === {
query: "select * from table where a in (:0, :1, :2)",
parameters:[1,2,3]
}
const query2 = sql`
select *
from table
where name like ${'some str'}
and a in (${[1, 2, 3]})
and b = ${100}
and c in (${['val1', 'val2']})
`
query2 === {
query: "select * from table where name like :0 and a in (:1, :2, :3) and b = :4 and c in (:3, :4)",
parameters: ["some str", 1, 2, 3, 100, "val1", "val2"]
}
const [result1, result2] = await Promise.all([
connection.execute(query1.query, query1.parameters),
connection.execute(query2.query, query2.parameters)
])
interface ParameterizedSQL {
query: string;
parameters: any[];
}
export function sql(queryParts: TemplateStringsArray, ...parameters: any[]): ParameterizedSQL {
if ((queryParts.length - 1) === parameters.length) {
return {
query: queryParts.map((part, index) => index < parameters.length ? `${part}${parameterIndexes(parameters, index)}` : part).join(''),
parameters: parameters.flat(),
};
}
throw new Error("Invalid number of parameters.");
}
function parameterIndexes(parameters: any[], index: number): string {
const newIndex = parameters.slice(0, index).reduce((p, c) => p + (Array.isArray(c) ? c.length : 1), 0);
const parameter = parameters[index];
if (Array.isArray(parameter)) {
const indexes = new Array<number>(parameter.length).fill(index).map((e, i) => e + i);
return ':' + indexes.join(', :');
}
else {
return ':' + newIndex;
}
}
function sql(queryParts, ...parameters) {
if ((queryParts.length - 1) === parameters.length) {
return {
query: queryParts.map((part, index) => index < parameters.length ? `${part}${parameterIndexes(parameters, index)}` : part).join(''),
parameters: parameters.flat(),
};
}
throw new Error("Invalid number of parameters.");
}
function parameterIndexes(parameters, index) {
const newIndex = parameters.slice(0, index).reduce((p, c) => p + (Array.isArray(c) ? c.length : 1), 0);
const parameter = parameters[index];
if (Array.isArray(parameter)) {
const indexes = new Array(parameter.length).fill(index).map((e, i) => e + i);
return ':' + indexes.join(', :');
}
else {
return ':' + newIndex;
}
}
Do you have a comma-delimited list of numbers in a single string or do you have an array of numbers?
Here's an example of how you can handle a comma delimited list of numbers with a custom type and table function. First, run this in the database using a tool like SQL Developer:
create or replace type number_ntt as table of number;
/
create or replace function string_to_number_ntt(
p_string in varchar2,
p_separator in varchar2 := ','
)
return number_ntt
is
l_elements apex_application_global.vc_arr2;
l_retval number_ntt := number_ntt();
begin
l_elements := apex_util.string_to_table(p_string, p_separator);
for x in 1 .. l_elements.count
loop
l_retval.extend();
l_retval(l_retval.count) := l_elements(x);
end loop;
return l_retval;
end string_to_number_ntt;
/
Note that the function makes use of the apex_util package which is likely already installed in the DB.
Once that's in place, you can use it as follows:
const oracledb = require('oracledb');
const config = require('./dbConfig.js');
let conn;
oracledb.getConnection(config)
.then((c) => {
conn = c;
return conn.execute(
`select employee_id,
last_name,
department_id
from employees
where department_id in (
select column_value
from table(string_to_number_ntt(:depts))
)`,
{
depts: '30, 60, 90'
},
{
outFormat: oracledb.OBJECT
}
);
})
.then(result => {
console.log(result.rows);
})
.catch(err => {
console.log(err);
})
.then(() => {
if (conn) { // conn assignment worked, must close
return conn.close();
}
})
.catch(err => {
console.log();
});
Example results:
$ node my-script.js
[ { EMPLOYEE_ID: 114, LAST_NAME: 'Raphaely', DEPARTMENT_ID: 30 },
{ EMPLOYEE_ID: 115, LAST_NAME: 'Khoo', DEPARTMENT_ID: 30 },
{ EMPLOYEE_ID: 116, LAST_NAME: 'Baida', DEPARTMENT_ID: 30 },
{ EMPLOYEE_ID: 117, LAST_NAME: 'Tobias', DEPARTMENT_ID: 30 },
{ EMPLOYEE_ID: 118, LAST_NAME: 'Himuro', DEPARTMENT_ID: 30 },
{ EMPLOYEE_ID: 119, LAST_NAME: 'Colmenares', DEPARTMENT_ID: 30 },
{ EMPLOYEE_ID: 103, LAST_NAME: 'Hunold', DEPARTMENT_ID: 60 },
{ EMPLOYEE_ID: 104, LAST_NAME: 'Ernst', DEPARTMENT_ID: 60 },
{ EMPLOYEE_ID: 105, LAST_NAME: 'Austin', DEPARTMENT_ID: 60 },
{ EMPLOYEE_ID: 106, LAST_NAME: 'Pataballa', DEPARTMENT_ID: 60 },
{ EMPLOYEE_ID: 107, LAST_NAME: 'Lorentz', DEPARTMENT_ID: 60 },
{ EMPLOYEE_ID: 100, LAST_NAME: 'King', DEPARTMENT_ID: 90 },
{ EMPLOYEE_ID: 101, LAST_NAME: 'Kochhar', DEPARTMENT_ID: 90 },
{ EMPLOYEE_ID: 102, LAST_NAME: 'De Haan', DEPARTMENT_ID: 90 } ]
If you have an array of numbers you can convert them to a comma delimited list of numbers as follows:
[30, 60, 90].join(',')
Here's an example of what your execute statement would look like:
return conn.execute(
`select *
from foo
where id in (
select column_value
from table(string_to_number_ntt(:ListOfIds))
)`,
{
ListOfIds: ListOfIds.join(',')
},
{
outFormat: oracledb.OBJECT
}
);
I assiume you are working with node-oracledb as I'm doing right now. I wasn't able to solve it with the executeMany functionality even though I tried but in a github issue it says that they don't support it as of 2 years ago.
So here is my solution without having to know the amount of elements beforehand. You should wrap try catch accordingly as well as closing connections again. I didn't want to bloat the example. Read their documentation!
let queries = []
values.forEach(value => {
const query = {
sqlQuery: `SELECT * FROM MY_TABLE where MY_COLUMN = (:value)`,
binds: {
value
}
}
queries.push(query)
})
const connection = await getConnection()
const results = []
for (let i = 0; i < queries.length; i+=1) {
const result = await connection.execute(queries[i].sqlQuery, queries[i].binds)
results.push(result.rows[0])
}
return results
