CODEWITHSUNDEEP
node.jsoracle-databasestored-procedures
ramesh
ramesh

Reputation: 43

Pass Object as input parameter to stored procedure with node-oracledb in node js

I have a stored procedure which takes two input parameters and gives one output parameter.
Input parameters one is Oracle custom Type, second one is CHAR type
Output parameter is Number type

PROCEDURE SOMEPROCEDURE(
 P_REC IN RV_SEARCH_CRITERIA_REC,
 V_ITEM_BATCH_ID_FLAG IN CHAR,
 V_RV_BATCH_ID OUT NUMBER)

here RV_SEARCH_CRITERIA_REC is 
TYPE RV_SEARCH_CRITERIA_REC IS OBJECT(
   CUSTOMER_NAME   VARCHAR2(3000)
)

Can some one help me to access the procedure with node-oracle node module?

Upvotes: 3

Views: 3555

Answers (1)

Fabian Rivera
Fabian Rivera

Reputation: 1198

Given this procedure:

CREATE OR REPLACE PROCEDURE myproc (id IN NUMBER, name OUT VARCHAR2) AS
BEGIN
  SELECT last_name INTO name FROM employees WHERE employee_id = id;
END;

You can execute it with the following code:

    . . .
connection.execute(
  "BEGIN myproc(:id, :name); END;",
  {  // bind variables
    id:   159,
    name: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 },
  },
  function (err, result) {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

Try to execute yours in the same way by replacing the procedure name, args and binding.

Check out the docs here: https://oracle.github.io/node-oracledb/doc/api.html

Upvotes: 3

Related Questions