# Insert data to oracle database with oracledb-mod

**URL:** <https://discourse.nodered.org/t/insert-data-to-oracle-database-with-oracledb-mod/82611>\
**Category:** General\
**Created:** [4 November 2023 23:30 UTC](https://discourse.nodered.org/t/insert-data-to-oracle-database-with-oracledb-mod/82611 "2023-11-04T23:30:39Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![robsonsilva](https://avatars.discourse-cdn.com/v4/letter/r/d6d6ee/32.png) [@robsonsilva](https://discourse.nodered.org/u/robsonsilva)\
**Post date:** [4 November 2023 23:30 UTC](https://discourse.nodered.org/t/insert-data-to-oracle-database-with-oracledb-mod/82611/1 "2023-11-04T23:30:39Z")

</div>

Hello everyone.

First of all I'm a newbie using node-red. I'm trying to insert some data into oracle database using oracledb-mod. As you can see on prints I'm facing some errors to just to insert. I had success to select data.  
I'm broke the code into small step to understand node-red structure, but I dont have success yet.  
Following the docs, msg.payload is an array in oracledb-mod, but it doesn't work for me.

I'm glade if someone could help me.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/5/95c1acc2a093759b0c5241d590b6b0bb9add8732.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/9/89144cd36b2ecd682c4bdd19ecdb7c1d63c4b059.png)

---

<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:** [5 November 2023 01:42 UTC](https://discourse.nodered.org/t/insert-data-to-oracle-database-with-oracledb-mod/82611/2 "2023-11-05T01:42:28Z")

</div>

The help states

> **msg.payload** : array containing the fields to be used inside the query, first element in the array corresponds with the first `:fieldname` parameter in the query etc.

Which suggests you change the `msg.payload[x]` in the `values` to `:fieldname` but that makes little sense.

Perhaps try `:0, :1, :2` or perhaps `:1, :2, :3`?

Failing try a different oracle node?

---

<div class="post-metadata">

**Author:** ![robsonsilva](https://avatars.discourse-cdn.com/v4/letter/r/d6d6ee/32.png) [@robsonsilva](https://discourse.nodered.org/u/robsonsilva)\
**Post date:** [5 November 2023 15:40 UTC](https://discourse.nodered.org/t/insert-data-to-oracle-database-with-oracledb-mod/82611/3 "2023-11-05T15:40:39Z")

</div>

Hello @Steve-Mcl  
Thank you sugget, I try somthing litle diferent. I used a function node to return msg.query as documentation .

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/e/5e26d37ee5e6e93fe149829a6ee20ac26c3cdec5.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/5/850cd227c80640ce6a469c963dec264125ab223f.png)

Now I can insert data by query string.

Thanks a lot.

---

<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:** [5 November 2023 15:54 UTC](https://discourse.nodered.org/t/insert-data-to-oracle-database-with-oracledb-mod/82611/4 "2023-11-05T15:54:04Z")

</div>

Just be careful. Dynamic queries are susceptible to [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection). You would be far better of trying to use parameters.

Did you try using what i suggested:

Looking at that screenshot suggests there is another place you may need to setup - did you enter anything in the "Field Mappings"?

At a guess (I have never used this node - as I do not use oracle), you tell the Field Mappings where the data is in your payload then use the field mappings like this...

```auto
INSERT INTO automacao.variaveis
(codigo, datger, valor)
VALUES (:codigo, :datger, :valor)

```

---

<div class="post-metadata">

**Author:** ![robsonsilva](https://avatars.discourse-cdn.com/v4/letter/r/d6d6ee/32.png) [@robsonsilva](https://discourse.nodered.org/u/robsonsilva)\
**Post date:** [5 November 2023 17:51 UTC](https://discourse.nodered.org/t/insert-data-to-oracle-database-with-oracledb-mod/82611/5 "2023-11-05T17:51:30Z")

</div>

Thank you about your concern and clue by SQL Injection.  
I dont know how "Field Mappings" works, but I try doing what you said. I'm not sure if it make sense.  
My function returns an array.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/e/2e1119e680bf2070fdff308efeeaf72c87708c68.png)

OracleDB setup  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/1/21ba5aed65bb5dd672b7f4b02ff9df5869dcba24.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/a/3acd51984068a38b37df60e8c14fa68d8cc9434f.png)

Output

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/f/af5f4b4c9855503d627c4919a6083116943ad03b.png)

I'm trying diferents ways to solve this problem, if you find any error on it let me know.

Regards.

---

<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:** [4 January 2024 17:52 UTC](https://discourse.nodered.org/t/insert-data-to-oracle-database-with-oracledb-mod/82611/6 "2024-01-04T17:52:00Z")

</div>

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