Oracle - arrays with TypeOrm (In a nutshell, how to add a custom constraint)

[EdissonG]

Oracle - arrays with TypeOrm

Expected Behavior

Do not get error ORA-01484: arrays can only be bound to PL/SQL statements

Actual Behavior

I am creating my arrangement as follows in my entity:

   @Column('varchar2',{
        array:true
   })
   sizes: string[];

In my DTO I have the following validation:

  @IsString({ each: true })
  @IsArray()
  sizes: string[];

Steps to Reproduce

1. Enable in the entity a column in array:true and in the DTO add the validations (class-validator class-transformer):
2. In the service send the following:

{
    "title": "Women shirt",
    "sizes": ["SM","M", "L"],
    "gender": "men",
    "slug": "women_shirt"
}

My Environment

Dependency	Version
Operating System	Windows 11
Node.js version	16.16.0
Oracle Database 19c Enterprise	19.0.0.0.0
TypeORM version	0.3.7
@nestjs/typeorm	9.0.1
oracledb	5.4.0

Additional Context

Relevant Database Driver(s)

DB Type	Reproducible
aurora-mysql	no
aurora-postgres	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	no
nativescript	no
oracle	yes
postgres	no
react-native	no
sap	no
spanner	no
sqlite	no
sqlite-abstract	no
sqljs	no
sqlserver	no

I don't know if it's because TypeOrm doesn't add the constraint that Oracle uses to save arrays.

alter table departments_json
  add constraint dept_data_json 
  check ( department_data is json );

Something that has to do with this is calling functions in TypeOrm,

#9291

Thanks you.

[AlexMesser]

Correct me if I'm wrong, but Oracle does not support arrays out of the box. You need to define it like custom type, e.g. CREATE TYPE string_array AS VARRAY(100) OF VARCHAR2(50);. So, in TypeORM you should define arrays for Oracle like:

@Column("simple-array")
sizes: string[]