Dynamic loop in PL/SQL

Question

I am currently looping through values in PL/SQL with the following:

for c in (select * from example_table where name is not null) loop  
  -- logic  
end loop;

I would like to replace the SQL statement with a dynamic one, for example:

l_sql := 'select * from example_table where || l_col || is not null';  
for c in (l_sql) loop  
  -- logic  
end loop;

Is this possible?

Best regards

Do you want something like this? docs.oracle.com/cd/B28359_01/appdev.111/b28370/… — Namandeep_Kaur
– Namandeep_Kaur, Commented Jan 31, 2019 at 0:56

Kaushik Nayak · Accepted Answer · 2019-01-31 03:27:16Z

2

That's not possible with an implicit cursor loop ( select inside for loop ). You may use the conventional OPEN .. FETCH .. LOOP through a REFCURSOR with a record variable of tablename%ROWTYPE

DECLARE
t_rec  example_table%ROWTYPE;
l_sql  VARCHAR2(1000);
v_cur SYS_REFCURSOR;
l_col varchar2(32) := 'MY_COLUMN';
BEGIN
  l_sql := 'select * from example_table where '|| l_col || ' is not null';  

OPEN v_cur FOR l_sql;
   LOOP
      FETCH v_cur INTO t_rec; --fetch a row 
         EXIT WHEN v_cur%NOTFOUND;

    -- your logic using t_rec columns.
   END LOOP;
CLOSE v_cur;

END;
/

answered Jan 31, 2019 at 3:27

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Dynamic loop in PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related