Oracle DB INSERT Not Working

General
1

Hello, all.

I have used several oracledb nodes that I could find but I am only able to use SELECT query for reading data. I am trying to do an INSERT but I keep returning an error. I read the help node about field markings but I'm not sure what that means. Could someone please offer any advice or suggestions? I am able to successfully use SQLPLUS in Command Prompt and perform my INSERT; just can't get it in Node-RED.

2

I am using node-red-contrib-oracledb-mod and doing INSERTs with no issue.
I have cleared all the pre-formatted data fields in the node configuration, and specifying the complete SQL statement in msg.query.
Can you provide a simplified flow which simulates the issue.

3

Thanks for the response. I will post it shortly. In general, I am doing an inject of msg.payload as '1', connected to a template node which has my INSERT that is sent as msg.query then connected to my 'oracledb' node that is from node-red-contrib-oracledb-mod that is configured.

You mentioned you cleared the pre-formatted data fields in the node configuration -- is it required that I have field markings in the oracledb node?

4

Try another thing - exclude/delete the msg.payload property before sending the command. I now remember that for some reason I also got an error when it had a value.
Here is a function node which prepares the SQL and works for me:

delete msg.error;
delete msg.payload;
msg.query = 
`BEGIN
insert into RTDB.EXECUTION_LOG
(TRX_ID,TRX_STATUS,ERROR_CODE,ADDITIONAL_INFO) 
VALUES(${msg.trxId},${msg.trxStatus},${msg.errCode},'${msg.text}');
COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;` ;

return msg;
5

Thank you! I will try that since I wasn't sure if msg.payload was my issue, or I didn't know how to do field markings.

First, I need to figure out what happened with my pallette. I thought maybe I was trying too many nodes so I removed all of them, and then installed node-red-contrib-oracledb-mod again but I have an error when I try to deploy but no error message. It's like the configuration isn't correct but it has the fields correctly but shows "Unconnected"

6

Hello. So, I tried what you suggested and ended up with the error:

Oracle query error: ORA-00933: SQL command not properly ended
Help: https://docs.oracle.com/error-help/db/ora-00933/, errorCode:ORA-00933

However, if I do a SELECT * FROM TABLE then I do get the proper results. So I know the configuration of the oracledb node is correct.

7

there is a bug in node-red-contrib-oracledb-mod v0.6.4, where the configuration shows an error even though it is correct. It was fixed in v0.6.5, but this version was not published correctly to the Node-red catalog, so you need to upgrade the node from command line.
Open a command line (as admin), go to your Node-red home directory, and type from there NPM install node-red-contrib-oracledb-mod@latest, then restart the Node-red server process, and refresh the browser.

As for the SQL error, it seems like a syntax issue (maybe a missing semicolon etc.) I propose you take the SQL I provided, change the field names & values to your needs, and see if it works.

Another possible issue could be that a varchar value includes a single quote, which breaks the enclosure of the varchar in the SQL, hence should be escaped. for example:

let errMsg = "Couldn't write to DB";

// errMsg includes a single quote and cannot be used as-is for a varchar value
// (which is enclosed within single quotes in the SQL, e.g.: VALUES('${errMsg}').

// You need to escape it:

errMsg = errMsg.replaceAll("'","''");

// errMsg will become: Couldn''t write to DB
8

Anyone can request a refresh - I have done so just now. It will be available in the palette manager within 30 mins.

Screenshot 2025-04-15 22.06.31
Screenshot 2025-04-15 22.06.31380×287 11.2 KB