How Do I Generate SQL?
January 28th, 2007 By dm1959
I want to do something simple and am learning..
I want to be able to create and format a file using plsql file..
example query:
select privilege,table_name,grantee from user_tab_privs order by table_name, privilege;
5 rows of data
select, account,user1
select, account,user2
select, account,user3
insert, account, user1
insert, account,user2
update,account,user1
I would like to format it out such that it looks like this into a privs.sql file
GRANT select on account to user1,user2,user3; GRANT insert on account to user1, user2; GRANT update on account to user1;
I have started the following but not sure who to build onto one line and loop it the best.
Thanks for newbie help….:>) We all have to start somewhere.
spool User_table_privs.sql
set serveroutput on format wrap
-- Run this for the schema
DECLARE
-- Privileges cursor
CURSOR privs_cur IS
select privilege,table_name,grantee from user_tab_privs
order by table_name, privilege;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
FOR privs_rec IN privs_cur LOOP
BEGIN
dbms_output.put_line(
'GRANT '|| privs_rec.privilege ||' '
|| privs_rec.table_name || ' ' || privs_rec.grantee || ';');
EXCEPTION
WHEN others THEN
dbms_output.put_line('Bummer (' || sqlerrm || ')' );
END;
END LOOP;
END;
/
spool off;
January 28th, 2007 at 10:07 pm
You do not need PL/SQL for this. You can easily do it by spooling the output of the following query:
SELECT 'GRANT ' || privilege || ' ON ' || TABLE_NAME || ' TO ' || grantee || ';' FROM user_tab_privs ORDER BY TABLE_NAME, privilege;