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!

How to write a procedure to execute set of queries automatically

3123956Nov 18 2015 — edited Nov 23 2015

@@@@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?

Comments

Timo Hahn

This is a known bug (22755768). You have to file  a SR (payable support contract needed and ask for help).

Timo

Robert Šajina

Not the answer i was hoping for..... But thank you for your reply.

@"Timo Hahn"

This is a known bug (22755768).

Where can i find the description of this bug? If Is that even possible?

Timo Hahn

You only get it if you login to support.oracle.com and search for the bug number.

We are not allowed to disclose this information here in the public space.

Timo

Robert Šajina

Thank you very much. I appreciate you help.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 21 2015
Added on Nov 18 2015
16 comments
1,966 views