CODEWITHSUNDEEP
oracle-databaseplsql
contravaluebets
contravaluebets

Reputation: 85

Can we use variables inside FOR-IN loop

Below code is self explanatory. Can we use variables inside a FOR-IN loop in PL/SQL? This is requirement because the query inside the FOR-IN needs to be dynamic in nature.

SET SERVEROUT ON;

DECLARE
   STMNT      varchar2(4000);
   SELECT_SQL varchar2(4000);
BEGIN
   SELECT_SQL := q'[select table_name from all_tables where owner='EMP' and table_name like 'TEMP_%']';
    FOR REC IN (SELECT_SQL) LOOP
      dbms_output.put_line (REC.table_name);
    END LOOP;
END;

Upvotes: 0

Views: 1586

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

No, you cannot use for loop to navigate through a result set returned by a dynamically built query. But, what you cant do is to open a refcursor for that dynamically built query and use LOOP or WHILE loop construct to go through result set. Here is a simple example, though in your situation, there is no need to use dynamic SQL at all:

set serveroutput on;
clear screen;

declare
  l_sql_statement varchar2(4000); -- going to contain dynamically built statement
  l_rcursor       sys_refcursor;  -- ref cursor 
  l_owner         varchar2(100);  -- variable that will contain owner's name 
  l_res_tab_name  varchar2(100);  -- variable we will fetch table name into 
begin
  l_owner := 'NK';
  l_sql_statement := 'select table_name
                        from all_tables 
                       where owner = :1';  -- bind variable 

  open l_rcursor for l_sql_statement
    using dbms_assert.simple_sql_name(l_owner);  -- slight protection from 
                                                 -- SQL injection

  loop
    fetch l_rcursor into l_res_tab_name;  -- fetch table name from the resultset
    exit when l_rcursor%notfound;         -- exit, when there is nothing to fetch
    dbms_output.put_line(l_res_tab_name); -- print table name 
  end loop;
end;

Result:

anonymous block completed

TEST1
TEST2
TMP_TEST
ERR$_T1

Note: Consider, in this situation, not to use dynamic SQL at all, there's really no need for it. Table names and column names are known at compile time, only right side of the predicates changes.

Upvotes: 3

Related Questions