Hey guys and friends ,
My Oracle Developer manager let me to create a user and, whom has just been granted "select" privilege from another user's all of tables to. At the same time I found there're approximately 200 number of tables on specific user, so it's impossible/inconvenient to grant per table to new user.
I consider it for a while and determine to write a piece of PL/SQL code for accomplishing this business requirement.
SET serveroutput ON
SET linesize 400
SET pagesize 400
DECLARE
v_sql varchar2(100);
v_cnt number;
v_flag number;
CURSOR c_dba_tables IS
SELECT table_name
FROM dba_tables
WHERE owner = 'TEST'
ORDER BY table_name
;
v_dba_tables c_dba_tables%ROWTYPE;
BEGIN
SELECT count(*) INTO v_cnt FROM dba_tables WHERE owner = 'TEST';
v_sql := 'GRANT SELECT ON test';
v_flag := 0;
OPEN c_dba_tables;
LOOP
FETCH c_dba_tables INTO v_dba_tables;
EXIT WHEN c_dba_tables%NOTFOUND;
v_sql := v_sql ''' || '.' '' || v_dba_tables.table_name || ''' TO pengcf;';
EXECUTE IMMEDIATE v_sql;
v_flag := v_flag + 1;
DBMS_OUTPUT.put_line(v_dba_tables.table_name || ' granted successfully.');
END LOOP;
CLOSE c_dba_tables;
DBMS_OUTPUT.put_line('Totally ' || v_flag || ' tables granted to pengcf.');
DBMS_OUTPUT.put_line('Acutally there are ' || v_cnt || ' tables on test.');
END;
/
ERROR:
ORA-01756: quoted string not properly terminated
Could anyone else help me to trouble-shooting it?
Thank you very much.
Best Regards
Quanwen Zhao