Open
Description
Issue description
ORA-01008: not all variables bound when reusing query param in multiple places
Expected Behavior
We noticed that starting with typeorm 0.3.24, we started getting "ORA-01008: not all variables bound". The same codebase works fine with version 0.3.17.
What we noticed was that if we reused the same query params in multiple places ( in our case excludePCs) then it fails. If I use individual variables like excludePCs1 and excludePCs2, then it works
e.g query and param
SELECT DISTINCT s.ACCT_NO
FROM PRODUCT irp
JOIN SUB s ON irp.SUB_NO =s.SUB_NO
JOIN ACCT ACT ON ACT.ACCT_NO = s.ACCT_NO
LEFT JOIN SREF sr ON sr.PRODUCT_NO = IRP.PRODUCT_NO
WHERE (ACT.LOGIC_DEL_IND IS NULL OR ACT.LOGIC_DEL_IND ='N')
AND (REGEXP_LIKE(IRP.DOT_NO,:dotNumber_0))
AND IRP.BUSINESS_NO != :excludePCs
AND (sr.NEW_BUSINESS_NO IS NULL OR sr.NEW_BUSINESS_NO != :excludePCs)
ORDER BY coalesce(ACT.UPDT_TS,ACT.CREAT_TS) DESC
FETCH NEXT :limit ROWS ONLYParam:
{
"dotNumber_0":"(^|\\s|\\W)1025808($|\\s|\\W)",
"excludePCs":32,
"limit":50
}Error:
{
"type": "QueryFailedError",
"message": "ORA-01008: not all variables bound",
"stack":
QueryFailedError: ORA-01008: not all variables bound
at OracleQueryRunner.query (/mnt/c/Repos/ss-dig-subm-svc-main/src/driver/oracle/OracleQueryRunner.ts:299:19)
at DataSource.query (/mnt/c/Repos/ss-dig-subm-svc-main/src/data-source/DataSource.ts:541:20)
at DSDBService.runSelectQuery (/mnt/c/Repos/ss-dig-subm-svc-main/src/shared/services/db/db.service.ts:124:25)
at AccountDAO.findAcctByDOT (/mnt/c/Repos/ss-dig-subm-svc-main/src/SUB/dao/daos/account.dao.ts:750:23)
at DSAccountService.findAcctByDotNo (/mnt/c/Repos/ss-dig-subm-svc-main/src/SUB/services/account/DS.account.service.ts:488:37)
at DSAccountService.handleAccountSearch (/mnt/c/Repos/ss-dig-subm-svc-main/src/SUB/services/account/DS.account.service.ts:247:44)
at ClearanceService.handleAccountSearch (/mnt/c/Repos/ss-dig-subm-svc-main/src/clearance/services/clearance.service.ts:135:28)
at ClearanceService.clearance (/mnt/c/Repos/ss-dig-subm-svc-main/src/clearance/services/clearance.service.ts:51:15)
at ProcessService.handleSteps (/mnt/c/Repos/ss-dig-subm-svc-main/src/clearance/services/process.service.ts:79:6)
at ClearanceController.process (/mnt/c/Repos/ss-dig-subm-svc-main/src/clearance/clearance.controller.ts:53:32)
"query": "SELECT DISTINCT s.ACCT_NO FROM PRODUCT irp JOIN SUB s ON irp.SUB_NO =s.SUB_NO JOIN ACCT
ACT ON ACT.ACCT_NO = s.ACCT_NO LEFT JOIN SREF sr ON sr.PRODUCT_NO = IRP.PRODUCT_NO WHERE (ACT.LOGIC_DEL_IND IS NULL OR ACT.LOGIC_DEL_IND ='N') AND (REGEXP_LIKE(IRP.DOT_NO,:1)) AND IRP.BUSINESS_NO != :2 AND (sr.NEW_BUSINESS_NO IS NULL OR sr.NEW_BUSINESS_NO != :2) ORDER BY coalesce(ACT.UPDT_TS,ACT.CREAT_TS) DESC FETCH NEXT :3 ROWS ONLY ",
"parameters": [
"(^|\\s|\\W)1025808($|\\s|\\W)",
32,
50
],
"driverError": {
"type": "Error",
"message": "ORA-01008: not all variables bound",
"stack":
Error: ORA-01008: not all variables bound
at OracleQueryRunner.query (/mnt/c/Repos/ss-dig-subm-svc-main/src/driver/oracle/OracleQueryRunner.ts:214:25)
at DataSource.query (/mnt/c/Repos/ss-dig-subm-svc-main/src/data-source/DataSource.ts:541:20)
at DSDBService.runSelectQuery (/mnt/c/Repos/ss-dig-subm-svc-main/src/shared/services/db/db.service.ts:124:25)
at AccountDAO.findAcctByDOT (/mnt/c/Repos/ss-dig-subm-svc-main/src/SUB/dao/daos/account.dao.ts:750:23)
at DSAccountService.findAcctByDotNo (/mnt/c/Repos/ss-dig-subm-svc-main/src/SUB/services/account/DS.account.service.ts:488:37)
at DSAccountService.handleAccountSearch (/mnt/c/Repos/ss-dig-subm-svc-main/src/SUB/services/account/DS.account.service.ts:247:44)
at ClearanceService.handleAccountSearch (/mnt/c/Repos/ss-dig-subm-svc-main/src/clearance/services/clearance.service.ts:135:28)
at ClearanceService.clearance (/mnt/c/Repos/ss-dig-subm-svc-main/src/clearance/services/clearance.service.ts:51:15)
at ProcessService.handleSteps (/mnt/c/Repos/ss-dig-subm-svc-main/src/clearance/services/process.service.ts:79:6)
at ClearanceController.process (/mnt/c/Repos/ss-dig-subm-svc-main/src/clearance/clearance.controller.ts:53:32)
"errorNum": 1008,
"offset": 223,
"code": "ORA-01008"
},
"errorNum": 1008,
"offset": 223,
"code": "ORA-01008"
}
Actual Behavior
We should be able to reuse the same named query params in multiple places
Steps to reproduce
Use the named param in multiple places in your query
My Environment
| Dependency | Version |
|---|---|
| Operating System | AmazonLinux 2023 |
| Node.js version | 22.14.0 |
| Typescript version | 5.5.3 |
| TypeORM version | 0.3.24 |
Additional Context
No response
Relevant Database Driver(s)
- aurora-mysql
- aurora-postgres
- better-sqlite3
- cockroachdb
- cordova
- expo
- mongodb
- mysql
- nativescript
- oracle
- postgres
- react-native
- sap
- spanner
- sqlite
- sqlite-abstract
- sqljs
- sqlserver
Are you willing to resolve this issue by submitting a Pull Request?
Yes, I have the time, and I know how to start.