Console hangs on trying to call a procedure

[jonesy-b-dev]

Versions: DB Version Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Platform: win32 Node.js version: v18.18.0
Architecture: x64 oracledb version: 6.2.0 Oracle client version: undefined //Dont know why its undifined

3. Console hangs on trying to call a procedure Other commands like SELECT * FROM CUSTOMERS do just run but running a procedure doesn't

This is the procedure I'm trying to run (Running this in SQL Developer does work)!!

CREATE OR REPLACE PROCEDURE add_contact (
    m_first_name IN VARCHAR2,
    m_last_name IN VARCHAR2,
    m_email IN VARCHAR2,
    m_phone_number IN VARCHAR2
) AS
BEGIN
    INSERT INTO CONTACTS (FIRST_NAME, LAST_NAME, PRIMARY_EMAIL, BUS_PHONE1)
    VALUES (m_first_name, m_last_name, m_email, m_phone_number);

    COMMIT; -- Commit the transaction to save the changes
END add_contact;

I tried calling this procedure with serveral methods:

connection = await oracledb.getConnection(dbConfig);
    const bindVars = {
      p_first_name: 'Jonas',
      p_last_name: 'Bruin',
      p_email: 'jonas.bruin@example.com',
      p_phone_number: '+555-123-4567'
    };

    // Call the stored procedure
    await connection.execute(
      'BEGIN add_contact(:p_first_name, :p_last_name, :p_email, :p_phone_number); END;',
      bindVars,
      { autoCommit: true }
    );

and

        connection = await oracledb.getConnection(dbConfig);
        //console.log(connection);

        // Define the SQL query
        const query = `BEGIN add_contact('Jonas', 'Bruin', 'jonas.bruin@example.com', '+555-123-4567');END;`

        //const query = `SELECT * FROM CONTACTS`

        // Execute the query
        const result = await connection.execute(query);

There are some more implementations that i tried that where in between these, not going to list them all, would become a very large issue.

If we take the second example i can put SELECT * FROM CUSTOMERS in const query it works as expected so we can rule all all connection issues I think.

Full js file of the first implementation: https://gist.github.com/jonesy-b-dev/2dcb6332d954e913d0c598295f0c0383

And the full js file of the second implementation: https://gist.github.com/jonesy-b-dev/45de81f0e6bc756cdea0e8bf305a0425

I have no clue how to solve it. Anyone an idea?

Greets,

Jonas

[sharadraju]

@jonesy-b-dev Thanks for testing node-oracledb. Node-oracledb 6.0 and later versions come with a default Thin mode. That is why the client version returns undefined. You can enable Thick mode and test it out. Have you used earlier versions of node-oracledb?

[jonesy-b-dev]

Thanks for the response @sharadraju ! Still having some issues with enabling Thick mode i added the following line to my code on line 3 oracledb.initOracleClient({ libDir: 'D:\\Oracle' }); This should be the path to my instant client dir. (my tnsnames.ora is located in subfolders of this folder) But I get an error saying it cannot locate the Client library. I checked my env variables on windows and also don't see a PATH variable for oracledb. Could this be the issue?

Not sure how to correctly add the env variable though.

And no I have no used earlier versions of node-oracledb, today is the first time working with it for me.

[anthony-tuininga]

You shouldn't need thick mode for this scenario. A "hang" can occur because of a lock -- possibly because of an earlier attempt that is still "running". You can check the database by looking at the dba_locks table. You can also simply create a brand new table and adjust the procedure to insert into it instead. Finally, you can set the environment variable NODE_ORACLEDB_DEBUG_PACKETS to any value and run your script again. I suspect the "hang" is occurring after the request has been sent to the database. If you see a response from the database (received packet) then something else is going on!

[jonesy-b-dev]

Ok so this is weird. Started up this morning and tested the code. It works. So me happy happy. I make one chance (pasted in the other implementation to see if that works too). And it stopped working, same problem, I run node connection.js to run the file and it hangs, no output and console it taken so have to ctrl c out of it.

So I was from ok whatever so I ctrl z to the implementation that did work, and guess what it stopped working. Same issue.

Is there some kind of bug I discovered?

I checked the table DBA_LOCKS and its empty.

I will try changing the procedure to add to another new table now.

Greets

[jonesy-b-dev]

@anthony-tuininga @sharadraju I found a fix for it, I just have to recompile the procedure every time its been used. I will close the issue now, thanks for the help

[anthony-tuininga]

That should not be necessary. It suggests a missing commit somewhere. Hopefully you find a better solution in time!

[jonesy-b-dev]

yeah, dont need a better fix atm but hope to find a better solution too!