@@@@I have written a procedure to grant permissions on all the tables of a particular schema to rest other schemas.
create or replace PROCEDURE GRANTS_PROC ( QRY_TEXT OUT VARCHAR )
IS QUERIES_TXT VARCHAR2(3000);
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);
QRY_TEXT:='LOOP';
QRY_TEXT:=CURSOR_GRANTS.FINAL_TXT;
EXECUTE IMMEDIATE CURSOR_GRANTS.FINAL_TXT;
EXECUTE IMMEDIATE CURSOR_GRANTS.TXT;
QRY_TEXT:=CURSOR_GRANTS.FINAL_TXT;
--INTO QUERIES_TXT;
--DBMS_OUTPUT.PUT_LINE(QUERIES_TXT);
END LOOP;
END;
/
The above procedure compiled successfully, but while execution it is not getting into FOR loop to run the EXECUTE IMMEDIATE block but PL/SQL procedure compiled successfully.
What could be done to fix my procedure and make it work?