Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Opening the cursor conditionally

USER101Mar 26 2010 — edited Mar 29 2010
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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 26 2010
Added on Mar 26 2010
11 comments
2,363 views