I am trying to run an user defined function on oracle db through node.js using oracledb driver. My oracle function is declared in PACKAGE z_1419_pkg as
FUNCTION GET_CUSTOMER_INFO(P_COMPANY_NAME VARCHAR2, P_COMP_CODE VARCHAR2) RETURN QC_UTL_PKG.CURSOR_TYPE
IS
C QC_UTL_PKG.CURSOR_TYPE;
V_COMPANY_NAME COMPANIES.COMPANY_NAME%TYPE := TRIM(P_COMPANY_NAME);
V_COMP_CODE COMPANIES.COMPANY_CODE%TYPE := TRIM(P_COMP_CODE);
BEGIN
C := QC_CV_PKG.COMPANIES_BROWSE(NULL,V_COMP_CODE,V_COMPANY_NAME,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL);
RETURN C;
END;
and my node.js code is
exports.syncOracle = function (req, res) {
var oracledb = require('oracledb');
var bindvars = {
p1: 'BM Dusters',
p2: 'bmduster',
};
oracledb.getConnection({
user : 'uname',
password : 'password',
connectString : 'locahost/TEST' },
function (err, conn) {
if (err) { console.error(err); return; }
console.log('success');
conn.execute('SELECT Z_1419_PKG.GET_CUSTOMER_INFO(:p1, :p2) from dual;' ,bindvars ,function (error, rows) {
if (error) {
console.log('ERROR: ' + error);
}
return res.status(200).json(rows);
});
});
};
but I am getting
ERROR: Error: ORA-00900: invalid SQL statement
Can somebody tell me what is wrong with my code. I could not find any documentation or example on how to use oracle functions with oracledb driver.
Thanks in advance.
When executing an SQL statement through an API such as this you shouldn't put a semi-colon at the end of the statement, so the call to
conn.execute
should be:Best of luck.