# Oracle DB INSERT Not Working

**URL:** <https://discourse.nodered.org/t/oracle-db-insert-not-working/96566>\
**Category:** General\
**Created:** [14 April 2025 17:03 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566 "2025-04-14T17:03:30Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![iMalinko](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/imalinko/32/100172_2.png) [@iMalinko](https://discourse.nodered.org/u/iMalinko)\
**Post date:** [14 April 2025 17:03 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/1 "2025-04-14T17:03:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![omrid](https://avatars.discourse-cdn.com/v4/letter/o/77aa72/32.png) [@omrid](https://discourse.nodered.org/u/omrid)\
**Post date:** [15 April 2025 08:08 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/2 "2025-04-15T08:08:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![iMalinko](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/imalinko/32/100172_2.png) [@iMalinko](https://discourse.nodered.org/u/iMalinko)\
**Post date:** [15 April 2025 11:50 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/3 "2025-04-15T11:50:07Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![omrid](https://avatars.discourse-cdn.com/v4/letter/o/77aa72/32.png) [@omrid](https://discourse.nodered.org/u/omrid)\
**Post date:** [15 April 2025 12:50 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/4 "2025-04-15T12:50:07Z")

</div>

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:

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![iMalinko](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/imalinko/32/100172_2.png) [@iMalinko](https://discourse.nodered.org/u/iMalinko)\
**Post date:** [15 April 2025 14:01 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/5 "2025-04-15T14:01:26Z")

</div>

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"

---

<div class="post-metadata">

**Author:** ![iMalinko](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/imalinko/32/100172_2.png) [@iMalinko](https://discourse.nodered.org/u/iMalinko)\
**Post date:** [15 April 2025 15:18 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/6 "2025-04-15T15:18:53Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![omrid](https://avatars.discourse-cdn.com/v4/letter/o/77aa72/32.png) [@omrid](https://discourse.nodered.org/u/omrid)\
**Post date:** [15 April 2025 20:51 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/7 "2025-04-15T20:51:08Z")

</div>

> [@iMalinko](#):
>
> 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"

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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [15 April 2025 21:08 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/8 "2025-04-15T21:08:06Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/a/da0284d38554ae38355ee95e0714c8ccb3713f33.png)

---

<div class="post-metadata">

**Author:** ![iMalinko](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/imalinko/32/100172_2.png) [@iMalinko](https://discourse.nodered.org/u/iMalinko)\
**Post date:** [22 April 2025 15:44 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/9 "2025-04-22T15:44:32Z")

</div>

Thank you! This resolved my issue.

For my insert query, I had to remove the COMMIT; to be able to properly execute the insert.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 May 2025 15:44 UTC](https://discourse.nodered.org/t/oracle-db-insert-not-working/96566/10 "2025-05-06T15:44:58Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.