Hi,
This is my first question, so I'm sorry for my mistakes.
I have a procedure that takes 2 inputs and execute commands. One of the inputs is a role name to grant and the other one is again role name to copy it's grants. I have attached my procedure.
I am executing my procedure like this:
exec myschema.grantroles('role1','role2');
Problem is here:
The cursor C_Main has nearly 19k rows for input role2. So procedure should grant nearly 19k to role1. Yet, at row 3081th, the cursor try to grant select on a view and the view is invalid. Procedure does not grant on this invalid view and procedure says " PL/SQL procedure successfully completed.". I was expecting the procedure to continue to loop until 19k grants to complete. I want to ignore all exceptions. What am I doing wrong?
Thank you in advance.
-----------Procedure------------
CREATE OR REPLACE PROCEDURE MYSCHEMA.GRANTROLES (PV_GRANTEE2GRANT IN VARCHAR2,
PV_GRANTEE2MAP IN VARCHAR2)
AUTHID CURRENT_USER
IS
CURSOR C_MAIN IS
WITH
CMDTAB
AS
(SELECT 'grant '
|| PRIVILEGE
|| ' on "'
|| OWNER
|| '"."'
|| TABLE_NAME
|| '" to '
|| PV_GRANTEE2GRANT CMD
FROM DBA_TAB_PRIVS
WHERE GRANTEE = UPPER (PV_GRANTEE2MAP)
UNION ALL
SELECT 'grant '
|| GRANTED_ROLE
|| ' to '
|| PV_GRANTEE2GRANT CMD
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = UPPER (PV_GRANTEE2MAP)
UNION ALL
SELECT 'grant ' || PRIVILEGE || ' to ' || PV_GRANTEE2GRANT CMD
FROM DBA_SYS_PRIVS
WHERE GRANTEE = UPPER (PV_GRANTEE2MAP))
SELECT CMD
FROM CMDTAB
ORDER BY 1;
BEGIN
FOR LV_MAIN IN C_MAIN
LOOP
EXECUTE IMMEDIATE (LV_MAIN.CMD);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END GRANTROLES;
Edit:
My Oracle v$version output:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0