Hi !
Please can you someone tell me why I cannot open the cursor conditionally.. based on the input argument, I would like to open a specific cursor. I get an error while I run this
CREATE OR REPLACE PROCEDURE GENERATE_GRANTS (OWNNAME IN VARCHAR2, ROLENAME IN VARCHAR2)
AS
SQL_STATEMENT VARCHAR2 (200);
err_code number(10);
err_msg VARCHAR2(200);
SQLQUERY VARCHAR2(2000);
CURNAME VARCHAR2(30);
CURSOR CUR_RW (OWNNAME VARCHAR2, ROLENAME VARCHAR2) IS
SELECT ( decode( object_type, 'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE ON '|| OWNNAME ||'.', 'VIEW','GRANT SELECT ON '|| OWNNAME ||'.', 'SEQUENCE','GRANT SELECT ON '|| OWNNAME ||'.',
'PROCEDURE','GRANT EXECUTE ON '|| OWNNAME ||'.', 'PACKAGE','GRANT EXECUTE ON '|| OWNNAME ||'.', 'FUNCTION','GRANT EXECUTE ON '|| OWNNAME ||'.' )||object_name||' TO '||ROLENAME
) as GRANT_SQL FROM dba_objects WHERE OWNER = OWNNAME AND OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE','FUNCTION' ) ORDER BY OBJECT_TYPE;
CURSOR CUR_R (OWNNAME VARCHAR2, ROLENAME VARCHAR2) IS
SELECT ( decode( object_type, 'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE ON '|| OWNNAME ||'.', 'VIEW','GRANT SELECT ON '|| OWNNAME ||'.' )||object_name||' TO '||ROLENAME
) as GRANT_SQL FROM dba_objects WHERE OWNER = OWNNAME AND OBJECT_TYPE IN ( 'TABLE', 'VIEW' ) ORDER BY OBJECT_TYPE;
BEGIN
IF OP_TYPE = 'READWRITE' THEN
FOR ITEM IN CUR_RW(OWNNAME,ROLENAME)
ELSE OP_TYPE = 'READ' THEN
FOR ITEM IN CUR_R(OWNNAME,ROLENAME)
END IF;
LOOP
DBMS_OUTPUT.PUT_LINE ('Statement is ' || SQL_STATEMENT);
SQL_STATEMENT := ITEM.GRANT_SQL;
BEGIN
EXECUTE IMMEDIATE SQL_STATEMENT;
EXCEPTION
WHEN OTHERS
THEN -- handles all other errors
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE (SQL_STATEMENT);
DBMS_OUTPUT.PUT_LINE (err_code || ' ' || err_msg);
END;
END LOOP;
END;
/