select in a loop in oracle sql?

Question

My stored procedure is used for getting string values of ids separate by ',' from a string contains names in table AUTH_GROUPS.

for example:

id        name

1         role_1
2         role_2
3         role_3

the input is: 'role_1,role_2,role_3'

the output is: '1,2,3'

CREATE OR REPLACE PROCEDURE PROCEDURE1(P_ERROR_MSG OUT VARCHAR2, P_ROLE_STRING IN VARCHAR2 ) AS 
BEGIN
  DECLARE
 lvOutPut VARCHAR2(2000);
 vId varchar2(1000);
BEGIN
    lvOutPut := '';
  FOR i IN
    (SELECT trim(regexp_substr(P_ROLE_STRING, '[^,]+', 1, LEVEL)) l
    FROM dual
    CONNECT BY LEVEL <= regexp_count(P_ROLE_STRING, ',')+1
    )
  LOOP
    select id into vId from AUTH_GROUPS where NAME = i; --this line got error 'expression is of wrong type'
    lvOutPut := lvOutPut || vId || ',';
  END LOOP;
    P_ERROR_MSG := lvOutPut;
    P_ERROR_MSG := substr(P_ERROR_MSG, 1, LENGTH(P_ERROR_MSG) - 1);
END;
END PROCEDURE1;

But it has an error in the line that I commented. I tried i.1 or i.value but still got errors.

Popeye · Accepted Answer · 2020-06-23 10:25:43Z

2

You need to use the actual column name. i is loop handle name.

....
BEGIN
    lvOutPut := '';
  FOR i IN
    (SELECT trim(regexp_substr(P_ROLE_STRING, '[^,]+', 1, LEVEL)) l -- this is column name to be used inside the loop
    FROM dual
    CONNECT BY LEVEL <= regexp_count(P_ROLE_STRING, ',')+1
    )
  LOOP
    select id into vId from AUTH_GROUPS where NAME = i.l;  -- change here
...

answered Jun 23, 2020 at 10:25

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

harun ugur · Accepted Answer · 2020-06-23 11:10:07Z

/* Formatted on 6/23/2020 2:08:34 PM (QP5 v5.354) */
CREATE OR REPLACE PROCEDURE PROCEDURE1 (P_ERROR_MSG        OUT VARCHAR2,
                                        P_ROLE_STRING   IN     VARCHAR2)
AS
BEGIN
    DECLARE
        lvOutPut   VARCHAR2 (2000);
        vId        VARCHAR2 (1000);
    BEGIN
        lvOutPut := '';

        FOR i IN ( SELECT TRIM (REGEXP_SUBSTR (P_ROLE_STRING,
                                                  '[^,]+',
                                                  1,
                                                  LEVEL))    l
                        FROM DUAL
                  CONNECT BY LEVEL <= REGEXP_COUNT (P_ROLE_STRING, ',') + 1)
        LOOP
            SELECT id
              INTO vId
              FROM AUTH_GROUPS
             WHERE NAME = i.l; --this line got error 'expression is of wrong type'

            lvOutPut := lvOutPut || vId || ',';
        END LOOP;

        P_ERROR_MSG := lvOutPut;
        P_ERROR_MSG := SUBSTR (P_ERROR_MSG, 1, LENGTH (P_ERROR_MSG) - 1);
    END;
END PROCEDURE1;

Collectives™ on Stack Overflow

select in a loop in oracle sql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related