# CLI query succeeds but NR fails

**URL:** <https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082>\
**Category:** General\
**Created:** [18 September 2024 20:04 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082 "2024-09-18T20:04:36Z")\
**Posts on this page:** 14\
**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:** [18 September 2024 20:04 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/1 "2024-09-18T20:04:36Z")

</div>

I have this query:

```auto
DECLARE
    p_term_code_sturec VARCHAR2(6);

BEGIN
    SELECT SHRDGMR_TERM_CODE_STUREC 
    INTO p_term_code_sturec 
    FROM SHRDGMR 
    WHERE SHRDGMR_PIDM = 123456
    AND SHRDGMR_SEQ_NO = 2;

    baninst1.sb_learneroutcome.p_update(
        123456,
        2,
        'GR',
        TO_DATE('10000101', 'YYYYMMDD'),
        TO_DATE('09/30/2024', 'MM/DD/YYYY'),
        '',
        p_term_code_sturec,
        '',
        '',
        'WG',
        '',
        TO_DATE('10000101', 'YYYYMMDD'),
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        USER,
        'GradAutomation',
        NULL,
        NULL
    );
END;

```

If I run it at the command line it works as expected. No issues.  
However, when I add it to my flow (here is my current flow):

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/5/65786d54babaae8b5d8e92c508271884900f0ee6.png)

I get the error "Oracle query error:

```auto
 ORA-01036: unrecognized bind variable 6 passed to the bind call
Help: https://docs.oracle.com/error-help/db/ora-01036/

```

Notice that it complains about variable 6.

My Graduation Updates function has the following declarations:

```auto
msg.payload = {
    StudentPIDM : msg.payload['Student PIDM'],
    DegreeSequence: msg.payload['Degree Sequence'],
    OutcomeStatus: msg.payload['Outcome Status'],
    GraduationStatus: msg.payload['Graduation Status'],
    GraduationDate: msg.payload['Graduation Date']
}

return msg;

```

And my my SHRGMR Update node has the following SQL:

```auto
DECLARE
    p_term_code_sturec VARCHAR2(6);

BEGIN
    SELECT SHRDGMR_TERM_CODE_STUREC 
    INTO p_term_code_sturec 
    FROM SHRDGMR 
    WHERE SHRDGMR_PIDM = :StudentPIDM 
    AND SHRDGMR_SEQ_NO = :DegreeSequence;

    baninst1.sb_learneroutcome.p_update(
        :StudentPIDM,
        :DegreeSequence,
        :OutcomeStatus,
        TO_DATE('10000101', 'YYYYMMDD'),
        TO_DATE(:GraduationDate,'MM/DD/YYYY'),
        '',
        p_term_code_sturec,
        '',
        '',
        :GraduationStatus,
        '',
        TO_DATE('10000101', 'YYYYMMDD'),
        '',
        '',
        '',
        '',
        '',
        '',
        '',
        USER,
        'GradAutomation',
        NULL,
        NULL
    );
END;

```

Is there something I'm doing wrong? I've been going at it for awhile but I cannot see a reason to get an error.  
I'm assuming I'm OK using semicolons at the end of the SQL statements.  
Is there something you guys can see that is a red flag?  
Thanks.

---

<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:** [19 September 2024 05:30 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/2 "2024-09-19T05:30:18Z")

</div>

> [@ralvez](#):
>
> `p_term_code_sturec,`

I think it falls over this ^

---

<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:** [19 September 2024 11:39 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/3 "2024-09-19T11:39:12Z")

</div>

@bakman2,

Thanks for your reply.  
I concur but the bigger question is why?  
I know that Boomi, for example, (a system similar to NR) does not like the semi-colons. Is that the same with NR?  
And if the semi-colons are not acceptable how do I mark the end of a statement?  
Is there a work around to get it to work?

---

<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:** [19 September 2024 11:51 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/4 "2024-09-19T11:51:43Z")

</div>

The colon means a binding, the colon is missing so it cannot bind anything, thus: error.

---

<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:** [19 September 2024 12:16 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/5 "2024-09-19T12:16:15Z")

</div>

@bakman2,

Of course!  
Thanks.

---

<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:** [19 September 2024 14:06 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/6 "2024-09-19T14:06:18Z")

</div>

@bakman2,

Oh boy! This thing is going to drive me to drink 😂  
After adding a proper binding to the variable it's happy to process. However, now it complaints about

```auto
Oracle query error: ORA-01008: value for bind variable placeholder :STUDENTPIDM (position 1) was not provided
Help: https://docs.oracle.com/error-help/db/ora-01008/

```

Looking at the data passed in before the complaint I would think it is correct and the StudentPIDM  
is in fact passed because I see it in the debug node.  
Any ideas?  
Is there any way to see the query being sent in to the DB?

---

<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:** [19 September 2024 14:26 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/7 "2024-09-19T14:26:30Z")

</div>

> [@ralvez](#):
>
> `STUDENTPIDM`

Looks like it is checking for uppercase while you provided lower/uppercase? Did you change the query?

---

<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:** [19 September 2024 14:27 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/8 "2024-09-19T14:27:12Z")

</div>

Nope. The query is unaltered as per the original posted query.

---

<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:** [19 September 2024 14:34 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/9 "2024-09-19T14:34:32Z")

</div>

I noticed that there is a Field Mapping tab. Should I be using it?

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

I mean, is that the "standard" way of doing things in NR?

---

<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:** [19 September 2024 15:46 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/10 "2024-09-19T15:46:47Z")

</div>

It seems like using the Field mappings is what causes the capitalization issue.

---

<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:** [19 September 2024 15:50 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/11 "2024-09-19T15:50:12Z")

</div>

@bakman2,

By removing the declarations in the array of Field mappings the caps issues goes away but there seems to be yet an issue with the specific variable.

```auto

Oracle query error: ORA-01036: unrecognized bind variable Student PIDM passed to the bind call
Help: https://docs.oracle.com/error-help/db/ora-01036/

```

Seems like NR does not like something about that binding. 🤔

---

<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:** [19 September 2024 16:13 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/12 "2024-09-19T16:13:50Z")

</div>

> [@ralvez](#):
>
> `Student PIDM`

It has a space, most likely does not work.

---

<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:** [19 September 2024 16:44 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/13 "2024-09-19T16:44:55Z")

</div>

@bakman2,

Hi. Yes. I see the space but the thing is there is NO space in the declared definition:  
`SudentPIDM: msg.payload['Student PIDM'],`  
I cannot see from where it gets that value.  
Also in the query the value is presented without a space:  
`SHRDGMR_PIDM = :StudentPIDM`

This is rather odd and disconcerting ☹

It is, almost, as if it is reading the` msg.payload` value not the binding declaration.

---

<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:** [18 December 2024 16:45 UTC](https://discourse.nodered.org/t/cli-query-succeeds-but-nr-fails/91082/14 "2024-12-18T16:45:47Z")

</div>

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