Using returned valued from machine step results in invalid number error

Flow Classic
1

Hi All,

I’m having a issue with using a returned value from a machine step in a following machine step. It looks like flow does something with the returned value which results in a Oracle error ‘Invalid message’ in my following query.

What I’m trying to do is.
I have a machine step which gets all of the resources and groups them in a single comma separated string (example: 138,276,294).

image
image763×222 6.02 KB

This SingleValue is used in the following query in the where clause as the value of an IN statement to get all the resources based on the previous found resources_seq.

image
image769×244 7.71 KB

When I execute the flow it states that there is an invalid number. If I replace the :SingleValue with the actual value from the SingleValue parameter (retrieved in debug mode) the flow executes with any errors. So it seems that flow converts the SingleValue parameter into a string or something.
image

I’ve tried adding quotes to the LISTAGG so that the returned value looks like 138’,‘276’,‘294 with additional quotes around the SingleValue in the IN statement (IN (’:SingleValue’)), but this results in the same error.
The query self seems fine to me and in executes in PLSQL Developer without any errors.

Does anyone have a idea how I can resolve this issue?

2

Hi there!

Could you not just but your initial query for the “single value” into the subquery in your second query?

As in:

SELECT p.RESOURCE_SEQ “RESOURCE_SEQ”, p.NAME “DESCRIPTION”
FROM IFSAPP.RESOURCE_PARENT_PERSON_UIV p
WHERE p.RESOURCE_SEQ IN (SELECT RESOURCE_SEQ FROM RESOURCES)
(it seems like you are missing some conditions for the sub-select, but I hope you understand my point).

3

I like Joakim’s idea, it is better to make it as simple as possible.

But to explain the behavior, flow does a thing to prevent what is called SQL injections (imagine if someone would scan a part called ’ drop database… you would not risk flow dropping your entire database. So they query is slightly changed at execution. Something like this could work in your scenario:

image

It is an MS SQL server in this example, but it should be easy to translate to PL.

4

Joakims way is the way to go in your example. It’s also a lot better for performance using the subselect or even a join so Oracle can optimize the query. It would be very inefficient performance wise to do this with actual values (especially if you select ALL ressources like in your example).

IF you ever have a case where this is not possible (e.g. manual multi selection in a user step) you can change the way you write code by not using :SingleValue but { SingleValue } instead. This will 1-1 replace the text before running the query. DO USE WITH CAUTION though. This should be a last resort and only be used if you are the one (in flow) to fill and create that variable. Never use it for a user input provided by the user. In theory any user could write something like 1,2,3; Drop Table xy; …

5

This is a big pet peeve of mine that Oracle has no better way of handling this.

Having said that, this is what I use to do what you want:

SELECT p.RESOURCE_SEQ "RESOURCE_SEQ"
               , p.NAME "DESCRIPTION"
FROM IFSAPP.RESOURCE_PARENT_PERSON_UIV p
WHERE p.RESOURCE_SEQ IN (
    SELECT TRIM(regexp_substr(str, '[^,]+', 1, LEVEL)) AS RESOURCE_SEQ 
    FROM (SELECT {SingleValue} "str" 
          FROM DUAL)
    CONNECT BY INSTR(str, ',', 1, LEVEL - 1) > 0
)

I would like to have a note here that if there is only one value in your list, this query breaks… but works great for 2 or more values.

Buuuuuut, having said that, do what @JoakimLeren says:

SELECT p.RESOURCE_SEQ "RESOURCE_SEQ", p.NAME "DESCRIPTION"
FROM IFSAPP.RESOURCE_PARENT_PERSON_UIV p
WHERE p.RESOURCE_SEQ IN (
    SELECT r.RESOURCE_SEQ 
    FROM RESOURCES r
)