Node-oracledb: How to do pattern matching queries?

[annjawn]

I am trying to execute a simple query with pattern matching using LIKE in the WHERE clause, however I can't seem to figure out how to do it. This doesn't seem to work

const baseQuery = 
 `select item, item_desc
    from item_master
   where item_level=tran_level
     and item like '%:item%'`;

const binds = { item: '550' };
const result = await conn.execute(baseQuery, binds, {});

It keeps throwing the error below-

[Error: ORA-01036: illegal variable name/number] {
  errorNum: 1036,
  offset: 0
}

I've read the document but this seemingly simple use case of pattern match queries doesn't seem to be documented anywhere. Is pattern matching supported? if yes, then what am I doing wrong?

[sla100]

SQL:

and item like :item

JS:
js const binds = { item: '%550%' };

[cjbj]

Thank you @sla100.

Similar syntax can also be used with REGEXP_LIKE. Techniques for binding in WHERE IN clauses are shown in https://oracle.github.io/node-oracledb/doc/api.html#sqlwherein

[cjbj]

@annjawn Here's a running example:

const oracledb = require('oracledb');
const config = require('./dbconfig.js');

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(config);

    const sql = `select empno, ename from emp where ename like :mybv`;
    const binds = { mybv: '%AM%' };
    const options = { };

    const result = await connection.execute(sql, binds, options);
    console.log(result.rows);

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();

Output is:

[ [ 7876, 'ADAMS' ], [ 7900, 'JAMES' ] ]

[annjawn]

Thanks a lot for the help @cjbj 👍🏽