看看这个sql为何执行错误！

Remen

SQL> execute immediate 'alter session set current_schema = inv';
BEGIN immediate 'alter session set current_schema = inv'; END;
                *
ERROR at line 1:
ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "alter session set current_schema = inv" when
expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "alter session set current_schema = inv" to
continue.

Remen

上面的只是其中的一句话整个的sql如下：declare username varchar2(100) default 'SYS';
begin
for tablelist in (select owner,table_name from dba_tables
                  where owner not in ('SYS','SYSTEM') order by 1 )
loop
if tablelist.owner<> username then
execute immediate 'alter session set current_schema = '||tablelist.owner||'';
username = tablelist.owner;
end if;
execute immediate 'grant all on '||tablelist.owner||'.'||tablelist.table_name ||' to apps with grant option';
end loop;
end;但是执行的时候报错：username = tablelist.owner;
         *
ERROR at line 8:
ORA-06550: line 8, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;
ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "END"

Remen

上面的错误解决了，应该是username ：= tablelist.owner;但是出现了其他的错误SVRMGR> connect internal
Connected.
SVRMGR> @b.sql
ORA-01929: no privileges to GRANT
ORA-06512: at line 10
SVRMGR>

Remen

我的目的是吧所与owner得table的所有权付给apps，并授权grant权限，但是就是不知道执行sqlplus得时候应该用那个用户执行？apps我也试过了，提示不能授权给自己

Remen

解决方法如下：declare
sqltext varchar2(400);
c integer;
begin
for userlist in (select user_id,username from all_users where username not in ('SYS','SYSTEM','APPLSYS','APPS'))
loop
for tablelist in (select owner,table_name from dba_tables where owner = userlist.username and table_name not like '%IOT_OVER%')
loop
sqltext := 'grant all on '||tablelist.owner||'.'||tablelist.table_name ||' to apps with grant option';
c := sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user( c,sqltext,dbms_sql.native,userlist.user_id);
sys.dbms_sys_sql.close_cursor(c);
end loop;
end loop;
end;
/