How to pass a variable to an Oracle SQL query (function)? - VisualCron - Forum

Community forum

Gary_W
2017-06-13T19:38:33Z
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.
thomas
2017-06-14T09:53:53Z
What do you get if you write this ?

SELECT {LOOP(CurrentValueX)}
FROM DUAL;
Gary_W
2017-06-14T13:11:10Z
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.
thomas
2017-06-14T13:22:39Z
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.

Gary_W
2017-06-14T13:48:26Z
Great suggestions Thomas, I have already done them though. All looks as expected.
Gary_W
2017-06-14T20:43:57Z
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.
Scroll to Top