How to execute "Stored Procedure" Query in node-oracledb if we are not aware of stored procedure bind parameters?

HARSHA LANKA picture HARSHA LANKA · Mar 15, 2016 · Viewed 11.2k times · Source

All the examples in documentation were given with bind variables.But what if, we are going to execute the query(Stored Procedure) which has written by the user.(In this case we will not be aware of what are all the input and output parameters to bind). I am able to execute all the basic ddl and dml queries. But how to execute stored procedure like queries and what will be the way of retrieving?

Will there be any luck if we use "db-oracle"?

Note: I am new to nodejs and node-oracle-db

Answer

Dan McGhan picture Dan McGhan · Mar 15, 2016

Have a look at the following examples:

https://github.com/oracle/node-oracledb/blob/master/examples/plsqlfunc.js https://github.com/oracle/node-oracledb/blob/master/examples/plsqlproc.js

Also, I don't understand why you would not be aware of the input and output parameters to bind to. It would have to be a VERY dynamic situation for that to be true. It would be similar to saying: we don't know the names of the columns of the table we need to query. I'm not saying it doesn't happen or that there aren't unusual circumstances where this could be an issue, just that it's highly unusual.

In either case, whether you don't know the inputs and outputs of stored procedures or even if you don't know the names of the columns, that's where data dictionary views come in. Try running the following queries to begin exploring the views that may be relevant to you:

For procedures:

select *
from all_procedures;

For arguments:

select *
from all_arguments;