# OracleDB query with variable

**URL:** <https://discourse.nodered.org/t/oracledb-query-with-variable/83133>\
**Category:** General\
**Created:** [23 November 2023 16:52 UTC](https://discourse.nodered.org/t/oracledb-query-with-variable/83133 "2023-11-23T16:52:05Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![johnolav](https://avatars.discourse-cdn.com/v4/letter/j/e9bcb4/32.png) [@johnolav](https://discourse.nodered.org/u/johnolav)\
**Post date:** [23 November 2023 16:52 UTC](https://discourse.nodered.org/t/oracledb-query-with-variable/83133/1 "2023-11-23T16:52:05Z")

</div>

Hello everyone, I need some help with a sql query. I'm using the node-red-contrib-oracle-mod node with a complex sql query (about 75 lines). This query is working when I post it in the "SQL query" section inside the oracledb node. This is really static and I need to make it more dynamic. I have an ID that I should put inside the query, to get different results of the query.  
I think the query should been moved out in a function node and then return a msg.query to the oracledb node, but I cannot figure out how to do this by JavaScript.  
I've also tried to understand the field mapping without luck, so if anyone have tips/examples please let me know 😉

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [23 November 2023 19:24 UTC](https://discourse.nodered.org/t/oracledb-query-with-variable/83133/2 "2023-11-23T19:24:53Z")

</div>

You could define a database view to offload the work to oracle and your query becomes a simple select with a where clause.

But in a function node something like this should work

```auto
msg.query = "select blah, blah "
msg query += "from blah, blah "
msg.query += " where blah blah" 
msg.query += "and blahblah = " + msg.something
Etc

```

Note this is not a safe way to include msg.something in the query; I'm not sure how prepared queries work in oracle

---

<div class="post-metadata">

**Author:** ![TerryE](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/terrye/32/65423_2.png) [@TerryE](https://discourse.nodered.org/u/TerryE)\
**Post date:** [25 November 2023 11:02 UTC](https://discourse.nodered.org/t/oracledb-query-with-variable/83133/3 "2023-11-25T11:02:03Z")

</div>

> [@johnolav](#):
>
> node-red-contrib-oracle-mod

Disclaimer: I use the MySQL note a lot and it's been a couple decades since I last used native Oracle myself. The node-RED module documentation sucks, as the [github source](https://github.com/vtulluru/node-red-contrib-oracledb-mod) doesn't include any general howto documentation or examples. However, this module is really just a wrapper around Oracle's node.js library with the _query_ parameter containing the Oracle SQL query, and _payload_ the bind values. This node.js module also has a lot of useful [examples on Github](https://github.com/oracle/node-oracledb/tree/main/examples) that give you a lot of use cases.

In general you should avoid building queries at runtime as this is inefficient and can introduce injection vulnerabilities. Far better to use bind values; browse the Internet / YouTube to learn more.

---

<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:** [24 January 2024 11:02 UTC](https://discourse.nodered.org/t/oracledb-query-with-variable/83133/4 "2024-01-24T11:02:59Z")

</div>

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