I am looping through a delimited file and extracting an ID from one field which I need to pass to a function which will return another value that I will append to the line and write to another file. The function accepts a VARCHAR2 and also returns a VARCHAR2.

It errors on the SQL with "Error in sql query: ORA-00911: invalid character" as I suspect the variable is not being resolved before being passed. The query is simple enough and works fine from SQL*Plus when passed a real ID number in quotes:

SELECT get_medicaid_id({LOOP(CurrentValueX)})
FROM DUAL;

Surely I must be missing something simple here? Do I need to build a string first using STRING functions or something?
I didn't see a "translate value to constant in variable when running" checkbox as I would have expected on the SQL tab either.

What do you get if you write this ?

SELECT {LOOP(CurrentValueX)}
FROM DUAL;

The same error: Error in sql query: ORA-00911: invalid character. I'm going to structure my process differently anyway for efficiency as doing a query for every line will be slow but I wanted to experiment with calling a stored procedure/function anyway.

So, any info is appreciated as I suspect this issue will be useful for others as well.

Ok. I don't have Oracle here, so cannot test. It could be as you say that the variable is not resolved. Another possibility is that there are some characters there that oracle doesn't like, like invisible control characters. Maybe add a Trim function. I would write {LOOP(CurrentValueX)} to a job variable in that task preceding, and see if there is something funky with the input.

Great suggestions Thomas, I have already done them though. All looks as expected.

False Alarm! I realized I was using the wrong connection. When I switched to the right one, all worked as expected (although slow, also as expected). Sorry about that folks.