• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Executing Oracle Functions with Multiple Parameters

Community Beginner ,
Aug 23, 2018 Aug 23, 2018

Copy link to clipboard

Copied

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

FROM dual

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

FROM dual

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!

Brad

(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).

Views

272

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
no replies

Have something to add?

Join the conversation
Resources
Documentation