Execute an Oracle DB stored procedure In NestJS

Question

I am trying to run an Oracle Stored Procedure via my NestJS api. I've been following the NestJS Database documentation but it doesn't specifically give you any help with the syntax needed to call a stored procedure. The location of the Stored Procedure is also a little odd, you have to go into the database, to other users, then to the User, then to that User's procedure folder, where I can then access the Procedure I need (see below pic).

When I try to run the procedure in the Database it shows that it needed startTime and endTime as params, when I hover it gives me the format, which is what I am passing in through my service.

Here is my app.module:

@Module({
  imports: [

  ConfigModule.forRoot({
      envFilePath: ['.env.development.local'],
      isGlobal: true
    }),
    TypeOrmModule.forRoot({
      type: 'oracle',
      host: process.env.OMSRECON_DB_HOST,
      port: parseInt(process.env.OMSRECON_DB_PORT),
      username: 'FAKE_USER',
      password: 'FAKE_PASSWORD',
      database: process.env.OMSRECON_DB_DATABASE,
      sid: process.env.OMSRECON_DB_SID,
      entities: [OmsReconData],
      synchronize: false
    }),
    CustomerOutagesModule,
    UserModule,
    SystemStatusesModule,
    SystemIncidentsModule
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Here is my services module:

@Module({
  imports: [
    TypeOrmModule.forFeature([OmsReconData])
  ],
  controllers: [CustomerOutagesController],
  providers: [CustomerOutagesService]
})
export class CustomerOutagesModule {}

and here is my service.ts:

@Injectable()
export class CustomerOutagesService {
  constructor(
    @InjectRepository(OmsReconData)
    private omsReconRepository: Repository<OmsReconData>
  ) {}

  async getOmsRecondData(startDate: string, endDate: string) {
    const result = await this.omsReconRepository.query(
      `EXEC OMS_RECON.GET_OMS_RECON_DATA @START_TIME=${startDate} @END_TIME=${endDate}`,
    );
    console.log(result);
    return result;
  }
}

I really just need to figure out what syntax I need to run a stored procedure and how to get to the other user stored procedures? Thanks in advance for the help.

Answer 1

I think i would use this

await this.connection.query(
  'EXEC ProcedureName @0', [
    param0
  ]
);

Sorry im not very good with typeorm, but this should at least work (or similar approach)