CLI query succeeds but NR fails

General
1

I have this query:

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
image941×395 41.9 KB

I get the error "Oracle query error:

 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:

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:

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.

2

I think it falls over this ^

3

@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?

4

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

5

@bakman2,

Of course!
Thanks.

6

@bakman2,

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

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?

7

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

8

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

9

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

image

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

10

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

11

@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.


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. :thinking:

12

It has a space, most likely does not work.

13

@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 :frowning:

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