I have two function I am attempting to execute against an Oracle database. One with a single input parameter, one with multiple input parameters. The single input parameter function works just fine, the multiple input function I am stuck on.
Single original function from the Oracle documentation:
FUNCTION myfunction1(input_var IN NUMBER) RETURN VARCHAR2
Calling the single function successfully:
SELECT mypackage.myfunction1(#InputVariable#) AS OutputVariable
This works just fine, but multiple input variables not so much....
Multiple Input function from Oracle documentation: (uses var1 and 2 to compute Boolean response along with two status indicators)
FUNCTION myvalidatefunction(input_var1 IN VARCHAR2,
input_var2 IN VARCHAR2,
status1 OUT VARCHAR2,
status2 OUT VARCHAR2) RETURN BOOLEAN
I have attempted calling this in a number of ways:
SELECT mypackage.myvalidatefunction(#InputVar1#,#InputVar2#) AS OutputVariable
with a variety of single and double quotes such as:
("#InputVar1#","#InputVar2#") OR ('#InputVar1#','#InputVar2#') OR ('"#InputVar1#","#InputVar2#"') or with one or both without quotes, etc... etc...
typically the second variable is returned as the error: (where InputVar2 is ABCDEF)
ORA-00904: "ABCDEF": invalid identifier
Any attempts to encapsulate both variables in double or single quotes usually results in:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'F_VALIDATE_PIN'
At the suggestion of an Oracle dev, I tried putting both inputs into an array and using that as the input variable, but that doesn't work either:
"Complex object types cannot be converted to simple values.The expression has requested a variable or an intermediate expression result as a simple value. However, the result cannot be converted to a simple value..."
So I'm stuck on how to shove two or more inputs into a function when calling in cfquery and am having no luck finding examples or answers to this problem. Again, any function expecting a single input works easily, it's when you need to submit two that things go bad. (and I also tried cfstoredproc to try to call it and provide input parameters but it return an error that it is not a stored procedure, which is true).
Thanks for any tips or direction you can provide!
(PS actual function names have been changed to protect the innocent, I have no access to the Oracle functions, I just get to consume them and they do work on other platforms/applications, so it's my problem to make them work in CF).
Have something to add?