This content has been marked as final. Show 5 replies
948024 wrote:The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Hello all im trying to grant select privilege of sys tables to admin user
my os windows 7 pro
db ver 18.104.22.168.0
im using command prompt : sqlplus / as sysdba
while running this.
i saved file a.sql
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='SYS') LOOP
EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to admin';
while exectuting this
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 3
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.
nopes.......:( its still giving error
Try similar block at your end and Copy and Paste the Output here. And also try the Grant statements one by one at your terminal to ascertain their working before trying them with Execute Immediate.
set serveroutput on; BEGIN for r in (select owner, table_name from all_tables where owner='SYS') loop dbms_output.put_line('grant select on '||R.owner||'.'||R.table_name||' to admin'); END LOOP; END; / anonymous block completed grant select on SYS.DUAL to admin grant select on SYS.SYSTEM_PRIVILEGE_MAP to admin grant select on SYS.TABLE_PRIVILEGE_MAP to admin
Since, I do not have Grant privileges at my database, I cannot try the output generated.
My Oracle version is 10.0.2.4. Though, I very much doubt if its related to version.
thanks a ton.........:)
grant select on SYS.AQ$_SYS$SERVICE_METRICS_TAB_H to admin
grant select on SYS.AQ$_SYS$SERVICE_METRICS_TAB_G to admin
grant select on SYS.AQ$_SYS$SERVICE_METRICS_TAB_I to admin
grant select on SYS.OLAPTABLEVELS to admin
grant select on SYS.OLAPTABLEVELTUPLES to admin
grant select on SYS.SAM_SPARSITY_ADVICE to admin
grant select on SYS.WRI$_ADV_OBJSPACE_TREND_DATA to admin
grant select on SYS.WRI$_ADV_OBJSPACE_CHROW_DATA to admin
grant select on SYS.UTL_RECOMP_COMPILED to admin
grant select on SYS.UTL_RECOMP_SORTED to admin
PL/SQL procedure successfully completed.