CODEWITHSUNDEEP
node.jsoracle-databaseplsqlprocedureknex.js
faervict
faervict

Reputation: 1

Consume Oracle Procedure - Node.js Knex.js

Good morning people! all very well?

I have a problem developing an API that will consume some information in an Oracle database. I developed the API framework in Node.js with a Knex.js query builder. I developed this query to the database directly in the back-end and after running I then set up the Procedure that would make this query directly in the database.

But I can't consume this Procedure from the backend to PLSQL. In the Knex documentation there is no information regarding the consumption of Stored Procedures. Searching forums I saw that some dev used knex.query or knex.execute to execute a Begin and then consume the Procedure through parameters. But when I try to run this way, I get an error saying that knex.query or knex.execute is not a function.

Can someone I know let me know what's wrong? Or is there any other way to do this consumption natively (without using a framework) or is there a framework better prepared for this type of execution?

 const data = await connection.execute(
            `
            BEGIN
                SP_GUIA_PROCEDURE(P_NUMB_GUIA => 000254, P_NUMB_BENEF => '000025448911000');
            END;
            `
        );

**TypeError: connection.execute is not a function**

Thank you very much in advance.

Upvotes: 0

Views: 1366

Answers (1)

Hasan Jamil
Hasan Jamil

Reputation: 91

It seems you are using Knex to get the connection. There is no method named execute() available in Knex. You can invoke the stored procedures using connection.raw(). Also it is a nice practice to use binds as arguments to the procedure. Here is the sample code (with bindings) that can help:

const data = await connection.raw(
      `BEGIN
         SAMPLE_PROCEDURE(:id,:name, :oval);
       END;`,
      {
        id: 11, // Bind type is determined from the data.  Default direction is BIND_IN
        name: {val: 'James', dir: oracledb.BIND_INOUT},
        oval: {type: oracledb.NUMBER, dir: oracledb.BIND_OUT}
      }
    );

SAMPLE_PROCEDURE is the stored procedure and the result is an array containing outbinds.

Upvotes: 1

Related Questions