Oracle bulk insert fails with ORA-01790 for null and non varchar2 type values #11362
Description
Issue description
Oracle bulk insert fails with ORA-01790 for null and non varchar2 type values
Expected Behavior
The following code should execute without an error:
await Promise.all(
dataSources.map(async (dataSource) => {
const users = [{id: 1, memberId: 1,}, {id: 2, memberId: null}, {id: 3, memberId: 3}]
await dataSource.manager
.createQueryBuilder()
.insert()
.into(User)
.values(users)
.execute()
const count = await dataSource.getRepository(User).count()
expect(count).to.equal(3)
}
),
))Actual Behavior
query: CREATE TABLE "user" ("id" number GENERATED BY DEFAULT AS IDENTITY, "memberId" number, CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"))
query failed: INSERT INTO "user"("id", "memberId") SELECT :1, :2 FROM DUAL UNION ALL SELECT :3, :4 FROM DUAL UNION ALL SELECT :5, :6 FROM DUAL -- PARAMETERS: [1,1,2,null,3,3]
error: Error: ORA-01790: expression must have same datatype as corresponding expression
Steps to reproduce
@Entity({
name: "user",
})
export class User {
@PrimaryGeneratedColumn()
public id: number
@Column({ type: "number", nullable: true })
public memberId: number | null
} const users = [{id: 1, memberId: 1,}, {id: 2, memberId: null}, {id: 3, memberId: 3}]
await dataSource.manager
.createQueryBuilder()
.insert()
.into(User)
.values(users)
.execute()
const count = await dataSource.getRepository(User).count()
expect(count).to.equal(3)My Environment
| Dependency | Version |
|---|---|
| Operating System | Windows11 |
| Node.js version | v22.14.0 |
| Typescript version | 5.7.3 |
| TypeORM version | 0.3.21 |
Additional Context
Oracle seems to treat NULL as VARCHAR2 type, causing the statement to fail.
This seems just the case for perpared statements.
So the plain statement executes without an error:
INSERT INTO "user"("id", "memberId") SELECT 1, 1 FROM DUAL UNION ALL SELECT 2, null FROM DUAL;
But when I execute it as an perpared statement, I get an Error:
DECLARE
v_sql VARCHAR2(500);
BEGIN
v_sql := 'INSERT INTO "user"("id", "memberId")
SELECT :1, :2 FROM DUAL
UNION ALL
SELECT :3, :4 FROM DUAL
EXECUTE IMMEDIATE v_sql USING 1, 1, 2, NULL;
END;
/
Same is true, if I use INSERT ALL logic
DECLARE
v_sql VARCHAR2(500);
BEGIN
v_sql := 'INSERT ALL
INTO "user"("id", "memberId") VALUES (:1, :2)
INTO "user"("id", "memberId") VALUES (:3, :4)
SELECT * FROM DUAL';
EXECUTE IMMEDIATE v_sql USING 1, 1, 2, NULL;
END;
/
The trick is, that the parameter muss be bind to a number type, so that the statement executes:
DECLARE
v_sql VARCHAR2(500);
BEGIN
v_sql := 'INSERT INTO "user"("id", "memberId")
SELECT :1, :2 FROM DUAL
UNION ALL
SELECT :3, :4 FROM DUAL
EXECUTE IMMEDIATE v_sql USING 1, 1, 2, TO_NUMBER(NULL);
END;
/
Alternatively it also executes, when we directly set NULL in the statement
DECLARE
v_sql VARCHAR2(500);
BEGIN
v_sql := 'INSERT INTO "user"("id", "memberId")
SELECT :1, :2 FROM DUAL
UNION ALL
SELECT :3, NULL FROM DUAL
EXECUTE IMMEDIATE v_sql USING 1, 1, 2;
END;
/
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.