How to execute from select result (oracle sql)-CodePudding

I created table with grants list. How I can execute grants from this table ? Something like select * from grants_table; then EXECUTE IMMEDIATE result from select

CodePudding user response：

You could write a loop

begin
  for grant in (select * from grants_table)
  loop
    execute immediate grants.column_with_ddl;
  end loop;
end;

Most likely, you'll want to do some amount of logging/ exception handling/ etc.

CodePudding user response：

If your table is of considerable size you could take advantage of the bulk operations and binds, something among the lines:

DECLARE
  TYPE cursor_ref IS REF CURSOR;
  c1 cursor_ref;
  TYPE grants_t IS TABLE OF grants%ROWTYPE;
  grants_tab grants_t;
  rows_fetched NUMBER;
BEGIN
  OPEN c1 FOR 'SELECT * FROM grants';
  FETCH c1 BULK COLLECT INTO grants_tab;
  rows_fetched := c1%ROWCOUNT;

  DBMS_OUTPUT.PUT_LINE('Number of grants: ' || TO_CHAR(rows_fetched));

  FORALL i IN 1 .. grants_tab.count 
    EXECUTE IMMEDIATE '< some ddl> :1' USING grants_tab(i); 
END;
/