# Unrecognized bind variable passed to the bind call

**URL:** <https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339>\
**Category:** General\
**Tags:** node-red-contrib-oracledb-2\
**Created:** [22 August 2024 16:14 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339 "2024-08-22T16:14:13Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 16:14 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/1 "2024-08-22T16:14:13Z")

</div>

I have a flow using a template to send an Oracle SQL query  
Here is the relevant section

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/b/fbbd674d738eb106e53d0f955288d0db3ec7cdcb.png)  
The resulting query from the template is created just fine and if I copy/paste it in oracle SQL Developer it executes fine. So no issues there.  
However, the query never get executed because I get this error:  
_"Oracle query error: ORA-01036: unrecognized bind variable Student PIDM passed to the bind call_  
_Help: [ORA-01036 - Database Error Messages](https://docs.oracle.com/error-help/db/ora-01036/)"_  
BTW I'm using node-red-contrib-oracledb-2.

This is the mustache template.

```auto
INSERT INTO SHRDGIH ( 
    SHRDGIH_PIDM,
    SHRDGIH_DGMR_SEQ_NO,
    SHRDGIH_HONR_CODE,
    SHRDGIH_ACTIVITY_DATE,
    SHRDGIH_TRANSC_PRT_IND,
    SHRDGIH_COMMENCE_PRT_IND,
    SHRDGIH_USER_ID,
    SHRDGIH_DATA_ORIGIN 
) VALUES (
    {{payload.Student PIDM}},
    {{payload.Degree Sequence}},
    '{{payload.Institutional Honors}}',
    SYSDATE,
    '{{payload.Transcript Indicator}}',
    '{{payload.Commencement Report}}',
    USER,
    'GradAutomation'
)

```

Any ideas to help solve the issue would be appreciated.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [22 August 2024 16:45 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/2 "2024-08-22T16:45:56Z")

</div>

I am not going to be able to help with Oracle, but first check what is coming out of the template to make sure it looks correct.

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 16:51 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/3 "2024-08-22T16:51:23Z")

</div>

Hi Colin,  
Thanks for your reply. As I mentioned in my post the template result is OK. If I copy/paste the result into the database if works.  
The issue is not the created result. It seems like something else is expecting me to declare things differently, perhaps.  
I've been banging my head for an entire day but cannot find why I get that error.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [22 August 2024 17:12 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/4 "2024-08-22T17:12:54Z")

</div>

> [@ralvez](#):
>
> ```auto
> {{payload.Student PIDM}},
> {{payload.Degree Sequence}},
> 
> ```

I suspect the issues are here: spaces in the property names.

---

<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:** [22 August 2024 17:24 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/5 "2024-08-22T17:24:57Z")

</div>

> [@ralvez](#):
>
> Thanks for your reply. As I mentioned in my post the template result is OK. If I copy/paste the result into the database if works.

It seems highly unlikely the output of that template would produce what you expect due to the space in the mustache replacements `{{ }}`

Please add a debug to the output of the template node BEFORE and AFTER and show us what you get for both debug nodes.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [22 August 2024 17:55 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/6 "2024-08-22T17:55:20Z")

</div>

Just to note, the template node does not care about spaces in property names  
e.g.

 ![chrome_screenshot_22 Aug 2024 18_54_49 BST](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/1/11fb3ab2a6a3a160836c1f817ceff39cf7c127e7.png)  
template

```auto
This is the payload["id var"]: {{payload.id var}}

```

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 17:59 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/7 "2024-08-22T17:59:17Z")

</div>

@bakman2, Steve-Mcl,

Thanks for your reply.  
Here is the result of the template:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/9/a9137180b5b396cad3a66b0d7fa9f61392b2ef17.png)

As you can see the data is correctly formatted. I can paste it into the db and it works.

The before/after template debug returns:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/6/16cff0ee07569a691260afd860f2e356124fd64c.png)

They are identical as far as I can see.

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 18:04 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/8 "2024-08-22T18:04:56Z")

</div>

@E1cid,

I concur. I do not think spaces matter because I have another query (update) in the flow and it works fine.  
Is only in this insert query that is not happy.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [22 August 2024 18:07 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/9 "2024-08-22T18:07:54Z")

</div>

> [@ralvez](#):
>
> unrecognized bind variable Student PIDM

This seems strange as msg.query does not have a variable name like that after it is created with the template node. This would suggest that msg.payload is being parsed as a bind var in the oracle node, try deleting or moving msg.payload prior to the oracle node

---

<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:** [22 August 2024 18:17 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/10 "2024-08-22T18:17:10Z")

</div>

> [@E1cid](#):
>
> Just to note, the template node does not care about spaces in property names

Who knew! (I rarely use template node and even rarer do I have spaces in property names!)

> [@ralvez](#):
>
> "Oracle query error: ORA-01036: unrecognized bind variable Student PIDM passed to the bind call

As this looks like an Oracle message AND it states **your variable by name** \*\*"Student PIDM" I suspect it is due to the payload containing these fields BUT you dont use them in the `query`

Try setting `payload` to an empty object or null.

PS, although I dont see it documented, i believe there is a way to use bound variables and avoid SQL Injection hacks. At a guess, you would match the values to properties in the payload.

something like:

```auto
msg.payload = {
   name: "bill o'connell",
   age: 34
}

msg.query = "INSERT INTO Users (name, age) VALUES (:name, :age)"

return msg

```

Note how I didnt have to escape quotes in the names or provide quotes in the values field!

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 18:54 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/11 "2024-08-22T18:54:12Z")

</div>

E1cid,

Given the structure of my code (here it is for your reference),

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

I'm afraid I do not understand your suggestion " try deleting or moving msg.payload prior to the oracle node" because as it stands it is before the oracle node.  
Interestingly, though, if I change the message property from msg.query to msg.payload the error it gives me is different:

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

Notice the reference to parameter 2?

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 18:55 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/12 "2024-08-22T18:55:45Z")

</div>

Steve-Mcl,

Let me explore your idea for a bit. I'm kinda new to NodeRed. 🙂

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [22 August 2024 19:04 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/13 "2024-08-22T19:04:19Z")

</div>

> [@ralvez](#):
>
> I'm afraid I do not understand your suggestion " try deleting or moving msg.payload prior to the oracle node" because as it stands it is before the oracle node.

add a change node prior to the oracle and after the template node.  
and  
set msg.payload to {}  
or  
delete msg.payload  
or  
move mag.payload to msg.whatever

[edit] What node are you actually using, can you provide a link.

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 19:13 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/14 "2024-08-22T19:13:08Z")

</div>

Hi Steve-Mcl,

OK. Assuming I understood the "spirit" of your post, this is what I did.:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/6/7639828db87e1196249978a86f848707d839de28.png)

It returns the _very same_ error as before. ☹

---

<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:** [22 August 2024 19:20 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/15 "2024-08-22T19:20:57Z")

</div>

This WILL be because of spaces in the field names.

Change your payload object to use names like `StudentPIDM` then ONLY put that as the value e.g. `:StudentPIDM`

example.

```auto
msg.payload = {
   StudentPIDM: msg.payload['Student PIDM'],
   etc: msg.payload['etc field'],
   etc: msg.payload['etc field'],
   etc: msg.payload['etc field'],
   etc: msg.payload['etc field'],
   etc: msg.payload['etc field'],
   etc: msg.payload['etc field'],
   etc: msg.payload['etc field']
}

msg.query = `INSERT INTO XXXXX (
    SHRDGIH_PIDM,
    SHRDGIH_DGMR_SEQ_NO,
    SHRDGIH_HONR_CODE,
    SHRDGIH_ACTIVITY_DATE,
    SHRDGIH_TRANSC_PRT_IND,
    SHRDGIH_COMMENCE_PRT_IND,
    SHRDGIH_USER_ID,
    SHRDGIH_DATA_ORIGIN )
VALUES (
    :StudentPIDM,
    :etc,
    :etc,
    :etc,
    :etc,
    :etc,
    :etc
)`

return msg

```

* * *

NOTE

Please post CODE and TEXT as text (not screenshots) - that way, I can offer you complete code suggestions (I aint gonna re-write what you put in a screenshot!)

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 19:32 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/16 "2024-08-22T19:32:52Z")

</div>

Hey Steve,  
Changing the name legend from 'Student PIDM' to 'StudentPIDM' may be a bit of a problem because I get the data from the client who in turn uses a piece of software to create the data.

Let me explore if I can do some manipulation of the data on my end to alter the variable names.

Note: Sure, I'll be happy to use formatted code instead of images.

---

<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:** [22 August 2024 20:28 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/17 "2024-08-22T20:28:41Z")

</div>

> [@ralvez](#):
>
> Changing the name legend from 'Student PIDM' to 'StudentPIDM' may be a bit of a problem because I get the data from the client who in turn uses a piece of software to create the data.

You're missing the point.

The sample code I wrote above does the remaining of properties to remove spaces.

It only changes the payload going into the oracle node so that the field names match the bindings. Nothing else changes. The database fields stay the same, the supplied data stays the same. the only thing these data values will be used for is passing values to the database.

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [22 August 2024 20:45 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/18 "2024-08-22T20:45:51Z")

</div>

@Steve-Mcl,

Oh! Of course ... I did not read the code carefully.  
I'll give it a try for sure. Brilliant!

---

<div class="post-metadata">

**Author:** ![ralvez](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@ralvez](https://discourse.nodered.org/u/ralvez)\
**Post date:** [26 August 2024 14:25 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/19 "2024-08-26T14:25:46Z")

</div>

@Steve-Mcl  
Good Day.  
I implemented your code idea.

```auto
msg.payload = {
    StudentPIDM : msg.payload['Student PIDM'],
    DegreeSequence : msg.payload['Degree Sequence'],
    InstitutionalHonors : msg.payload['Institutional Honours'],
    TranscriptIndicator : msg.payload['Transcript Indicator'],
    CommencementReport : msg.payload['Commencement Report']
}

INSERT INTO SHRDGIH ( 
    SHRDGIH_PIDM,
    SHRDGIH_DGMR_SEQ_NO,
    SHRDGIH_HONR_CODE,
    SHRDGIH_ACTIVITY_DATE,
    SHRDGIH_TRANSC_PRT_IND,
    SHRDGIH_COMMENCE_PRT_IND,
    SHRDGIH_USER_ID,
    SHRDGIH_DATA_ORIGIN)
VALUES(
    :StudentPIDM,
    :DegreeSequence,
    :InstitutionalHonors,
    SYSDATE,
    :TranscriptIndicator,
    :CommencementReport,
    USER,
    'GradAutomation'
)

return msg;

```

Oddly enough I get the very same error:

> Oracle query error: ORA-01036: unrecognized bind variable Student PIDM passed to the bind call  
> I'm beginning to wonder if there is some sort of corruption in the files. I mean, I no longer have a bind variable "Student PIDM" but StudentPIDM.

---

<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:** [26 August 2024 14:28 UTC](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339/20 "2024-08-26T14:28:56Z")

</div>

> [@ralvez](#):
>
> unrecognized bind variable Student PIDM passed to the bind call

Somewhere, somehow you are STILL sending that to the DB.

At a guess it is one of the multiple lines you have going into the SQL node

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/8/0878c6feba9972108a9f00628c32bb40cc561875.png)

I realise the above will be out of date now but if you dont share your flows I have no way of knowing.

[Next page](https://discourse.nodered.org/t/unrecognized-bind-variable-passed-to-the-bind-call/90339.md?page=2)