1

I have a function like this (unfortunately, I cannot change it to send one array instead of multiple parameters):

CREATE FUNCTION (p1_ IN OUT VARCHAR2(100), p2_ IN OUT VARCHAR2(100), <...>, p10_ IN OUT VARCHAR2(100)
IS
BEGIN
    gather_value(p1_);
    gather_value(p2_);
    <...>
    gather_value(p8_);
END;

To clean it up a little bit, I would prefer to change the body as follows:

IS
stmt VARCHAR2(1000);
BEGIN
    FOR i IN 1 .. 8
    LOOP
        stmt := 'gather_value(p:1_);'
        EXECUTE IMMEDIATE stmt USING i;
    END LOOP;
END;

However, in that case no such parameters are found. Is there a way to work it around?

paulauskas96
  • 19
  • 3
  • The only way I see you doing that is by putting all the parameters in an array (inside the body) ,,, and then using `for loop` and execute `immediate` `using` that array and the current `i` index... – PKey Mar 06 '18 at 13:42
  • It was my first idea, but as I need to return their values (as they're out parameters), 8 assignment statements would be needed anyway. – paulauskas96 Mar 06 '18 at 13:44
  • There's no clear workaround to provide without understanding *why* you cannot change your function parameter signature. – Michael O'Neill Mar 06 '18 at 14:17
  • 1
    Given that you have those 8 parameters and can't change them, the original static code is preferable to the fancy dynamic PL/SQL loop version anyway (IMHO). – Tony Andrews Mar 06 '18 at 14:27
  • The 8 individual `out` parameters make a loop approach impossible. You can't loop over a set of parameters like this. – William Robertson Mar 06 '18 at 15:39

0 Answers0