Oracle PL/SQL: Error when using a FOR LOOP variable in a FUNCTION call

Question

Is there a way to use a FOR LOOP variable in a FUNCTION call. For example, I have a simple function that returns a string. I call the function from a procedure, and pass two parameters which one of the parameters is a variable from a FOR LOOP:

    create or replace package body TEST_JNS is
  --
  FUNCTION f_display_msg(var IN VARCHAR2,
                         num IN NUMBER)
  RETURN VARCHAR2 IS
  BEGIN
    --
    RETURN num || '#: ' || var || ' world';
  END f_display_msg;
  --
  --
  PROCEDURE main
  AS
    l_name VARCHAR2(100);
    l_id NUMBER;
  BEGIN
    l_name := 'hello';
    --

    FOR f1 IN (SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL))) AS "id"
                 FROM DUAL
              CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('1,2,3', '[^,]+')) + 1)

    LOOP
      --      
      dbms_output.put_line(f_display_msg(l_name, f1.id));
      --
    END LOOP;                    
  END main;
  --  
end TEST_JNS;

I get error: component 'ID' must be declared. I also tried adding the FOR LOOP value to a local variable, but get the same result. Any ideas?

score 0 · Answer 1 · answered Apr 03 '17 at 20:02

0

If it helps some one, IOT resolve the issue I just had to remove the double quotes surrounding id As "id".

answered Apr 03 '17 at 20:02

Dev Ngron

135
4
19

2

You did it right. By default, oracle brings code to uppercase before compiles source, quotes used to telling compiler not to change literals case, for heterogenious db links for example. In your case with quote you have two different literals in code ID and id, thats why you got an error. – Seyran Apr 03 '17 at 20:50
Thank for the explanation. Good to know stuff! – Dev Ngron Apr 04 '17 at 16:16

score 0 · Answer 2 · answered Apr 03 '17 at 23:18

0

Rather than using regular expressions to parse a delimited list, why not use a collection:

create or replace package body TEST_JNS is
  FUNCTION f_display_msg(var IN VARCHAR2,
                         num IN NUMBER)
  RETURN VARCHAR2 IS
  BEGIN
    RETURN num || '#: ' || var || ' world';
  END f_display_msg;

  PROCEDURE main
  AS
    TYPE numberlist IS TABLE OF INTEGER;
    l_name VARCHAR2(100) := 'hello';
    v_list numberlist    := numberlist( 1, 2, 3 );
  BEGIN
    l_name := 'hello';
    FOR i IN 1 .. v_list.COUNT LOOP
      dbms_output.put_line( f_display_msg( l_name, v_list(i) ));
    END LOOP;
  END main;
end TEST_JNS;

answered Apr 03 '17 at 23:18

MT0

143,790
11
59
117

Thanks for the tip. I used the regular expression approach because in real life scenario I will receive a string parameter, which I would then separate the values by comma delimited. I'm not sure if with a collection I can accomplish the same. Thanks! – Dev Ngron Apr 04 '17 at 16:15
@DevNgron If you are receiving it from an external language then you can pass in an array to a collection ([Java example](http://stackoverflow.com/a/37161584/1509264)) – MT0 Apr 04 '17 at 19:58

Oracle PL/SQL: Error when using a FOR LOOP variable in a FUNCTION call

2 Answers2