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(
@InjectConnection('omsrecon')
private connection: Connection,
) {}
async getAll() {
const data = await this.connection.manager
.query('EXEC OMSRECON.GET_OMS_RECON_DATA @0 @1', ['20220716', '20220717'])
.then((res) => console.log(res))
.catch((error) => console.log(error));
console.log(data);
return 'test';
}
}
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.
UPDATE** I am not sure if it is actually running the stored procedure, but I am now getting the QueryFailedError: ORA-01036: illegal variable name/number when running my code.
from Execute an Oracle DB stored procedure In NestJS
No comments:
Post a Comment