PL/SQL Select inside Loop

Question

I have a PL/SQL statement that should iterate through a select statement, execute a stored procedure and then select something from a table.

The statement looks like the one below:

BEGIN
    FOR r IN (
        select org_id ,name from table2 order by 1
    ) LOOP

        dbms_application_info.set_cl (r.org_id);
        Select prj_id, prj_name 
        INTO l_output
        FROM table1
        WHERE CREATION_DATE>TO_DATE('10/01/2017','DD-MM-YYYY');   
        dbms_sql.return_result(l_output); --error     
    END LOOP;
END;

I don't know how to display all the rows returned by the query. Could someone try to help me figure this out? I searched for a solution but without any luck until now..

PS: Table1 is a view that needs to have the stored procedure initiated before the select

Thanks in advance!

What you mean by display result ? Do you want just print values from inner query? — mkuligowski
– mkuligowski, Commented May 7, 2018 at 10:29
Why is the select query inside the loop? I don't see any column being used in the query. — Kaushik Nayak
– Kaushik Nayak, Commented May 7, 2018 at 11:31

mkuligowski · Accepted Answer · 2018-05-07 11:42:42Z

2

So maybe use the same construction as in outer query?

BEGIN
    FOR r IN (
        select org_id ,name from table2 order by 1
    ) LOOP

        dbms_application_info.set_cl (r.org_id);

        FOR inner_query IN (
          Select prj_id, prj_name 
          FROM table1
          WHERE CREATION_DATE>TO_DATE('10/01/2017','DD-MM-YYYY');   
        ) LOOP
          dbms_output.put_line('prj_id: ' || inner_query.prj_id || ' prj_name:' || inner_query.prj_name);
        END LOOP;  
    END LOOP;
END;

Edit: If you want implicitly return cursor from your procedure through dbms_sql.return_result you should declare variable as ref_cursor:

l_output SYS_REFCURSOR

And then open it for your query:

 OPEN l_output FOR SELECT ...

And finally return with following procedure:

 DBMS_SQL.RETURN_RESULT(l_output);

edited May 7, 2018 at 11:42

answered May 7, 2018 at 10:40

mkuligowski

1,6041 gold badge19 silver badges28 bronze badges

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

8 Comments

Ychdziu Over a year ago

I recommend to avoid structures like "Loop in the loop", cause its very resource demanding and often hard to read and maintain.

rosuandreimihai Over a year ago

I did set the l_output as SYS_REFCURSOR but I have the following error: component 'RETURN_RESULT' must be declared

mkuligowski Over a year ago

@Ychdziu I don't agree that loop in the loop is very resource demanding, as it always depends on what you want to achieve from. Anyway, OP just wants to print values from the query based on the element of the collection, so beyond loop in the loop where he can use an aggregate function which can be harder to read than this.

mkuligowski Over a year ago

@rosuandreimihai what's your database version? RETURN_RESULT is available from 12c onward

mkuligowski Over a year ago

@rosuandreimihai just print it as in my answer using dbms_output.put_line

|

San · Accepted Answer · 2018-05-07 11:07:48Z

2

Procedure dbms_sql.return_result accepts/returns a ref cursor, you need to open the ref cursor before calling this procedure:

    DECLARE
      l_output sys_refcursor;    
    BEGIN
      FOR r IN (SELECT org_id,
                       NAME
                  FROM table2
                 ORDER BY 1) LOOP

        dbms_application_info.set_cl(r.org_id);
        OPEN l_output FOR
          SELECT prj_id,
                 prj_name
            FROM table1
           WHERE creation_date > to_date('10/01/2017', 'DD-MM-YYYY');
        dbms_sql.return_result(l_output);
      END LOOP;
    END;

edited May 7, 2018 at 11:07

answered May 7, 2018 at 10:41

San

4,5381 gold badge15 silver badges20 bronze badges

5 Comments

rosuandreimihai Over a year ago

Hi, the error returned is: identifier 'L_OUTPUT' must be declared

rosuandreimihai Over a year ago

Hi, thank you! But still.. the error that I receive is: component 'RETURN_RESULT' must be declared

San Over a year ago

If you are still getting this error, then I doubt that you do not have access on package 'DBMS_SQL', ask your admin for execute privilege on this package.

mkuligowski Over a year ago

If he wouldn't have access to DBMS_SQL then the error would be: component 'DBMS_SQL' must be declared

Ychdziu Over a year ago

Found out, that dbms_sql.return_result is in DB version 12c and up. In older versions - there are no info about it.

user8406805 · Accepted Answer · 2018-05-07 10:48:23Z

0

BEGIN
    FOR r IN (select org_id ,name from table2 order by 1)
       LOOP
          dbms_application_info.set_cl (r.org_id);
         for x in (Select prj_id, prj_name
                    FROM table1
                   WHERE CREATION_DATE>TO_DATE('10/01/2017','DD-MM-YYYY'))
            loop   
              dbms_output.put_line('prj_id:  '||x.prj_id ||'  prj_name:  '||x.prj_name );
         end loop;     
    END LOOP;
END;

answered May 7, 2018 at 10:48

user8406805

Collectives™ on Stack Overflow

PL/SQL Select inside Loop

3 Answers 3

8 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related