I am facing below error while executing sql query mentioned in the code snippet.
I am already providing all required bind parameters but not able to understand why I need to pass them repeatedly.
Official documentation also mentions that bind parameters needs to appear only once in bind object even if it is used many times in sql.
Error: NJS-098: 16 positional bind values are required but 5 were provided
Below are the node and oracledb versions I am using.
node version – 20.10.0
oracledb version – 6.3.0
import { dashboardRecord, dashboardRecordsRequest, dashboardRecordsResponse } from "datamodel/dashboard/dashboard";
import { logger } from "logger/logger";
import * as OracleDB from "oracledb";
export async function getActiveRequestRecords(dashboardRecordsRequest: dashboardRecordsRequest): Promise<dashboardRecordsResponse> {
const p_entityname: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.entityName, type: OracleDB.STRING };
const p_sortby: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortBy, type: OracleDB.STRING };
const p_sortOrder: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.sortOrder, type: OracleDB.STRING };
const p_startindex: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.startIndex, type: OracleDB.NUMBER };
const p_pagesize: OracleDB.BindParameter = { dir: OracleDB.BIND_IN, val: dashboardRecordsRequest.pageSize, type: OracleDB.NUMBER };
const p_totalrecords: OracleDB.BindParameter = { dir: OracleDB.BIND_OUT, type: OracleDB.NUMBER };
const p_activerecords: OracleDB.BindParameter = { dir: OracleDB.BIND_OUT, type: OracleDB.CURSOR };
const bindParams: OracleDB.BindParameter[] = [
p_entityname,
p_sortby,
p_sortOrder,
p_startindex,
p_pagesize,
p_totalrecords,
p_activerecords
];
const execOptions: OracleDB.ExecuteOptions = { outFormat: OracleDB.OUT_FORMAT_OBJECT, resultSet: true };
const recordsSQL: string = `SELECT
requestnumber,
requeststatus,
requestor,
pendingwith,
processtype,
actiondate
FROM
requests
WHERE
( ( upper(:p_entityname) = 'HR'
AND processtype IN ( 'PROCTYPE1', 'PROCTYPE2' ) )
OR ( upper(:p_entityname) = 'MANAGER'
AND processtype NOT IN ( 'PROCTYPE1', 'PROCTYPE2' ) ) )
AND AND upper(requeststatus) = 'PENDINGAPPROVAL'
ORDER BY
( CASE WHEN lower(:p_sortorder) = 'desc' AND lower(:p_sortby) = 'requestnumber' THEN requestnumber END ) DESC,
( CASE WHEN lower(:p_sortorder) = 'asc' AND lower(:p_sortby) = 'requestnumber' THEN requestnumber END ) ASC,
( CASE WHEN lower(:p_sortorder) = 'desc' AND lower(:p_sortby) = 'processtype' THEN processtype END ) DESC,
( CASE WHEN lower(:p_sortorder) = 'asc' AND lower(:p_sortby) = 'processtype' THEN processtype END ) ASC,
( CASE WHEN lower(:p_sortorder) = 'desc' AND lower(:p_sortby) = 'actiondate' THEN actiondate END ) DESC,
( CASE WHEN lower(:p_sortorder) = 'asc' AND lower(:p_sortby) = 'actiondate' THEN actiondate END ) ASC
OFFSET :p_startindex ROWS FETCH NEXT :p_pagesize ROWS ONLY`;
try {
const dbConnection = await OracleDB.getConnection('requestpool');
const recordsResult = await dbConnection.execute(recordsSQL, bindParams, execOptions);
const recordsResultSet = await recordsResult.resultSet?.getRows();
console.log(recordsResultSet);
} catch (err) {
logger.error(err);
console.error(err);
}
}