在oracle存储过程中,默认是可以直接执行DML和DQL的,但是执行create 或者 drop 这种的DDL则需要借助EXECUTE IMMEDIATE ···
如下备份表语句
--按年备份
SELECT TO_CHAR(SYSDATE, 'YYYY') INTO V_N FROM DUAL;
V_CREATESQL := 'CREATE TABLE TEXT_' || V_N ||
'_T AS SELECT * FROM TEXT';
EXECUTE IMMEDIATE V_CREATESQL;
当执行该语句时,提示ORA-01031: 权限不足 。该用户已赋予DBA权限。
原因:CREATE TABLE想使用CREATE ANY TABLE权限,而CREATE ANY TABLE权限来自DBA角色,默认情况下,虽然在会话环境中可见,但在存储过程中不可见(无效)。
即:ORACLE默认为定义者权限,定义者权限在存储过程中ROLE无效,需要显式授权。
grant CREATE TABLE to [username];