問題描述
如何編寫一個程序來自動執行一組查詢 (How to write a procedure to execute set of queries automatically)
我已經編寫了一個程序來授予對特定模式的所有表的權限以休息其他模式。
create or replace PROCEDURE GRANTS_PROC
IS
CURSOR GRANTS_CURSOR
IS
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON "'
||T.OWNER
||'"."'
||TABLE_NAME
||'" TO '
||(SELECT rtrim(listagg(U.username||',')
within group (order by U.username),',') USERNAME
FROM ALL_USERS U
WHERE U.USERNAME!=T.OWNER
AND U.USERNAME IN
('AAA','BBB','CCC','DDD','EEE','FFF','GGG','HHH','III'))||';' FINAL_TXT
FROM ALL_TABLES T
WHERE T.OWNER IN
('AAA','BBB','CCC','DDD','EEE','FFF','GGG','HHH','III')
ORDER BY T.OWNER,UPPER(T.TABLE_NAME);
BEGIN
‑‑DBMS_OUTPUT.PUT_LINE('CURSOR_GRANTS.FINAL_TXT');
‑‑QRY_TEXT:='ABC';
FOR CURSOR_GRANTS IN GRANTS_CURSOR
LOOP
DBMS_OUTPUT.PUT_LINE(CURSOR_GRANTS.FINAL_TXT);
EXECUTE IMMEDIATE CURSOR_GRANTS.FINAL_TXT;
END LOOP;
END;
/
上面的程序編譯成功，但是在執行時它沒有進入FOR循環運行EXECUTE IMMEDIATE塊，而是編譯了PL/SQL程序成功地。
可以做些什麼來修復我的程序並使其正常工作？
參考解法
方法 1:
All_USERS and ALL_TABLES should be changed to DBA_USERS and DBA_TABLES, Hence my procedure works.
Because the all_tables view shows the tables that the owner of the stored procedure has privileges on, so, unless the stored procedure is being created by a privileged user, or a user that already has those grants (with grant option so they can actually give the privileges), there may be nothing in all_tables that qualifies.
(by sabarish jackson、sabarish jackson)