Node-oracledb: Help executing stored procedures

[adonus19]

I have this stored procedure:

VARIABLE o_get_state_heat_map_cur REFCURSOR
VARIABLE O_ERR_CD NUMBER
VARIABLE O_ERR_MSG VARCHAR2

DECLARE
I_QTR_ID NUMBER := 1;
I_CLIENT_ID NUMBER := 1;
i_pol_eff_yr_no NUMBER := 2005;
i_tax_state_cd VARCHAR2(2):= 'AL';

BEGIN

pathTo.myStoredProc (I_QTR_ID,
I_CLIENT_ID,
i_pol_eff_yr_no,i_tax_state_cd,
:o_get_state_heat_map_cur ,
:O_ERR_CD ,
:O_ERR_MSG
) ;
END ;
/
PRINT o_get_state_heat_map_cur
PRINT O_ERR_CD
PRINT O_ERR_MSG

This works when dropped into SQL Developer. However, when I drop it into my code, I get this error:
ORA-00900: invalid SQL statement

To keep this post short, how would I drop this into my Node.js code to get it working? I've been stuck on this for hours and have tried every combination I can think of and get continuous errors. The usual error I get is that I've supplied the wrong number/type of arguments. But, I can't get enough from that error in order to fix.

My Node.js version is 8.9.4
Oracledb version is 12.1.
I'm using instant client.
I'm not sure on the Oracle Database version, but, it's likely current.
My OS is macOS Sierra v10.12.6

Any help is greatly appreciated! I've been stuck on this for nearly a day and can't get it worked out. Thank you!

[adonus19]

Also, I thought it would be important to note that I have also tried taking out the Declaration's values like so:
DECLARE
I_QTR_ID NUMBER := :QTR;
I_CLIENT_ID NUMBER := :CLIENT;
i_pol_eff_yr_no NUMBER := :year;
i_tax_state_cd VARCHAR2(2):= :state;

And then supplying bind variables:
let bindVars = {
QTR: 1,
CLIENT: 1,
policy: 2005,
state: 'AL',
o_get_state_heat_map_cur: { type: oracle.BUFFER, dir: oracle.BIND_OUT },
ERR_CD: { dir: oracle.BIND_OUT, type: oracle.NUMBER },
ERR_MSG: { dir: oracle.BIND_OUT, type: oracle.STRING}
}

I still get the same error of supplying the wrong number of variables/type.

Thanks again!

[anthony-tuininga]

The type of o_get_state_heat_map_cur should not be oracle.BUFFER but oracle.CURSOR. I believe that should resolve your issue. See this example.

[adonus19]

@anthony-tuininga That worked! Thank you so much! Now, however, I get another error when trying to get a response. If I take out the print statements, it runs, but I don't return any values. It just says that the query was successful. However, if I add in the Print statements, I get this error:
PLS-00103: Encountered the symbol "PRINT"

Thanks again for the help!

[adonus19]

@anthony-tuininga Let me qualify that by adding, when I run the initial query with the print statements in the SQL Developer, I get a response with values. So, I know this query should give me data. Thanks again!

[dmcghan]

@adonus19 Remove the following lines of code:

/
PRINT o_get_state_heat_map_cur
PRINT O_ERR_CD
PRINT O_ERR_MSG

Those are relevant to SQL*Plus, but not node-oracledb.

[anthony-tuininga]

Print is not a valid SQL or PL/SQL statement! Can you share the complete code you are executing in Node.js? You would normally need to perform resultSet.getRow() or resultSet.getRows() statement to get the data. You could use console.log() to display the data that is then fetched.

[adonus19]

@anthony-tuininga When I removed all the variable declarations at the start of the procedure call, I was able to get the query working. But again, no response data.

Here's the relevant code:

let sql = `BEGIN
        pathTo.myStoredProc(:QTR,
                                        :CLIENT,
                                        :policy,
                                        :state,
                                        :o_get_state_heat_map_cur,
                                        :ERR_CD,
                                        :ERR_MSG 
                                      ) ;
        END ;`;

let bindVars = {
    QTR: 1,
    CLIENT: 1,
    policy: 2005,
    state: 'AL',
    o_get_state_heat_map_cur: { type: oracle.CURSOR, dir: oracle.BIND_OUT },
    ERR_CD: { dir: oracle.BIND_OUT, type: oracle.NUMBER },
    ERR_MSG: { dir: oracle.BIND_OUT, type: oracle.STRING}
}
const query = (sql, callback) => {
    let pool = oracle.getPool();
    console.log('First pool.connectionsInUse', pool.connectionsInUse);
    console.log('First pool.connectionsOpen', pool.connectionsOpen);
    pool.getConnection((err, conn) => {
        if (err) {
            callback(err);
        }
        let t = process.hrtime();
        conn.execute(sql, bindVars, (err, result) => {
            if (err) {
                conn.close(err => { if (err) console.error(err.message) });
                console.error('Error executing query', err.stack)
                callback(err.stack);
            } else {
                t = process.hrtime(t);
                console.log(`SQL: ${sql}\nQuery response time: ${t[0]}.${t[1]} seconds`);
                conn.close(err => { if (err) console.error(err.message) });
                console.log('Third connectionsInUse', pool.connectionsInUse);
                console.log('Third pool.connectionsOpen', pool.connectionsOpen);
                console.log('Query successful');
                callback(null, result);
            }
        });
    });
}

[dmcghan]

@adonus19 Please take a moment to learn how to add code to GitHub. Then update your previous code so that it displays correctly.

[adonus19]

@dmcghan Apologies! I have fixed the code.
I also need to add the that above code is being called by this function in another file:

connection.query(queryList.getOracle(year, state, ncciClass), (err, results) => {
        if (err) {
            responseObj['generalInfo'] = {
                data: err.stack, status: true
            }
            sendResponse(responseObj, callback);
        } else {
            responseObj['generalInfo'] = {
                data: results.outBinds, status: true
            }
            sendResponse(responseObj, callback);
        }
    });

[adonus19]

I'm looking over this example on how to implement in my code.
https://github.com/oracle/node-oracledb/blob/master/examples/refcursor.js
You guys have been great!

[dmcghan]

@adonus19 I'm a little confused by something... You declare a variable named sql outside of the function query. But query also accepts a parameter named sql (which would take precedence). Was this intentional?

Also, what error are you getting now, if any?

[adonus19]

@dmcghan I apologize for that. My actual code is pretty segregated. I've supplied the essential flow above. The entry point is with this function:

connection.query(queryList.getOracle(year, state, ncciClass), (err, results) => {
        if (err) {
            responseObj['generalInfo'] = {
                data: err.stack, status: true
            }
            sendResponse(responseObj, callback);
        } else {
            responseObj['generalInfo'] = {
                data: results.outBinds, status: true
            }
            sendResponse(responseObj, callback);
        }
    });

That calls another function that retrieves the sql:
queryList.getOracle(year, state, ncciClass)

That function supplies the sql I quoted above. It's all placed in the same spot so that you could see all the relevant code, but, that breaks up the flow a good bit and I can see how that would make this difficult to understand. I hope this has helped. As of right now, I'm getting any errors, but I'm not getting and values in the response from the database query.

[dmcghan]

@adonus19 Couple of things...

callback(err.stack);

That line should pass the error object, not a string (one of the rules for the Node.js style of callbacks).

Next, you're getting a result and then closing the connection. However, your data is coming via a ResultSet out bind. As @anthony-tuininga mentioned, you'd need to invoke the getRow or getRows methods to actually get the data from this object - and you need to do this before closing the connection. See this section of the doc for help.

[adonus19]

@dmcghan That did it! I was under the assumption that if the callback was supplied with a successful call in the result object, the data would be stored there and that best practice was to close the connection immediately. I am now getting a response with the expected values. Thanks so much for all y'all's help!