CODEWITHSUNDEEP
sqlplusdynamic-sqloracle19c
Moribundus
Moribundus

Reputation: 71

for loop with dynamic table name and execute immediate

in my Procedure there is the following code line

  for i in (select schema_name, table_name, restricted_columns
            from GRANTED_TABLES_FOR_ROLE
            where restricted_columns = 0) loop
    execute immediate 'grant select on ' || i.schema_name || '.' || i.table_name || ' to ROLE_NAME';
  end loop;

because i want to create the table "GRANTED_TABLES_FOR_ROLE" earlier in my procudere i can't create the procedure without the "GRANTED_TABLES_FOR_ROLE" existing.

is there any way to make the code above dynamic so i can set a variable for the table "GRANTED_TABLES_FOR_ROLE"?

how i can achieve this?

thanks for your help!

Upvotes: 1

Views: 981

Answers (1)

Brian Leach
Brian Leach

Reputation: 2101

I believe this is a case where you will need to use a dynamic cursor:

 DECLARE
     TYPE trec IS RECORD
     (
         schema_name           VARCHAR2 (30)
       , table_name            VARCHAR2 (30)
       , restricted_columns    VARCHAR2 (30)
     );
 
     l_rec         trec;
     l_sqlstment   VARCHAR2 (500)
         := q'[SELECT schema_name, table_name, restricted_columns
                 FROM <<tablename>>
                WHERE restricted_columns = 0 ]';
     l_cursor      SYS_REFCURSOR;
 BEGIN
     l_sqlstment   :=
         REPLACE (l_sqlstment, '<<tablename>>', 'granted_tables_for_role');
 
     OPEN l_cursor FOR l_sqlstatement;
 
     LOOP
         FETCH l_cursor INTO l_rec;
 
         EXIT WHEN l_cursor%NOTFOUND;
         dbms_outout.put_line (l_rec.schema_name);
         dbms_outout.put_line (l_rec.table_name);
         dbms_outout.put_line (l_rec.restricted_columns);
 
         EXECUTE IMMEDIATE   'grant select on '
                          || l_rec.schema_name
                          || '.'
                          || l_rec.table_name
                          || ' to ROLE_NAME';
     END LOOP;
 END;

Upvotes: 2

Related Questions