Hi I have the following statement that I execute using node-oracle
await connection.execute(`SELECT * FROM TABLE WHERE NAME LIKE '%And%'`)
But now I want to bind a parameter instead of using a hard coded value
const queryText = 'And';
await connection.execute(`SELECT * FROM TABLE WHERE NAME LIKE '%:queryText%'`, {queryText});
it throws Error: ORA-01036: illegal variable name/number
What is the correct way of binding a parameter here, since the documentation doesn't cover this situation?
Try with the following:
const queryText = 'And';
await connection.execute(
"SELECT * FROM TABLE WHERE NAME LIKE :queryText",
{
queryText: { dir: oracledb.BIND_IN, val: '%'+ queryText +'%', type: oracledb.STRING }
});
Use string concatenation:
SELECT * FROM TABLE WHERE NAME LIKE '%' || :queryText || '%'
TABLE is a reserved word and cannot be used as an (unquoted) identifier. The expected syntax is for a table collection expression SELECT * FROM TABLE(an_array_object) which is why you get the ORA-00906 exception as you are missing the brackets for the rest of the table collection expression following the TABLE keyword - instead name your table something else like SELECT * FROM table_name. fiddleHere is a working example.
let queryText = "John"
let sql = "SELECT * FROM TABLE WHERE NAME LIKE :queryText"
let binds = {queryTarget: {dir: oracledb.BIND_IN, val: queryText, type: oracledb.STRING}}
let result = await connection.execute(sql, binds, options)
Do not add '%' like the other people suggested.
