Search code examples
sqlnode.jsoraclenode-oracledb

Oracle and Nodejs - can't bind parameters in SQL statement

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?

Solution

  • 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 }

});