把存储过程的代码贴上来,也许不是执行dbms_sql的权限问题
解决方案 »
- 请问PFILE文件在哪里?
- 一个关于CASE语句的问题,在线等!!!!1
- 请教两个select count(1) SQL 查询出结果不一致
- 怎么去设计呢
- Unable to load DLL (oci.dll). 问题!急,在线等待!
- oem的表编辑器中,在自由SQL模式下,如何连续执行两条insert语句?
- 如何授予scott用户set autotrace on权限??
- 在存储过程中,字符串连接的 语句该怎么写?
- Oracle7.3,WinNt4.0/监听会自动停止?
- 上海Oracle认证培训
- 写sql语句
- 默认安装oracle8.05.运行SQL PLUS后,使用帐号internal/oracle登录.无法建表.[更详细状况点击...]
CREATE OR REPLACE PROCEDURE CreateSysTable(SysName varchar2)
IS
v_Cursor NUMBER;
n_Rec INTEGER ;
v_CreateStr VARCHAR2(2000);
v_NumRows INTEGER;
BEGIN
v_Cursor:=DBMS_SQL.OPEN_CURSOR;
BEGIN
select Count(1)
into n_Rec
from dual
where exists(select 1
from cat
where TABLE_NAME = upper('Menu'||SysName)) ;
IF n_Rec = 0 THEN
v_CreateStr:='CREATE TABLE Menu';
v_CreateStr:=v_CreateStr||SysName;
v_CreateStr:=v_CreateStr||'(
MenuName varchar2(20) NOT NULL ,
MenuTitle varchar2(50),
Visi integer default 0 NOT NULL,
Position varchar2(10),
MenuLevel integer,
NewOld varchar2(2),
DllName varchar2(100),
DllParams varchar2(100),
ModeName varchar2(20),
GroupName varchar2(512),
ButtonNum varchar2(2),
ButtonBmp varchar2(20),
ButtonCaption varchar2(10)
)';
DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
END IF;
END;
BEGIN
select Count(1)
into n_Rec
from dual
where exists(select 1
from cat
where TABLE_NAME = upper('OldMenu'||SysName)) ;
IF n_Rec = 0 THEN
v_CreateStr:='CREATE TABLE OldMenu';
v_CreateStr:=v_CreateStr||SysName;
v_CreateStr:=v_CreateStr||'(
MenuName varchar2(20) NOT NULL
)';
DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
END IF;
END;
BEGIN
select Count(1)
into n_Rec
from dual
where exists(select 1
from cat
where TABLE_NAME = upper('Operator'||SysName)) ;
IF n_Rec = 0 THEN
v_CreateStr:='CREATE TABLE Operator';
v_CreateStr:=v_CreateStr||SysName;
v_CreateStr:=v_CreateStr||'(
userid varchar2(24) NOT NULL ,
username varchar2(12),
psw varchar2(32),
administrator integer NOT NULL ,
GroupName varchar2(200),
Area varchar2(20) NOT NULL,
PRIMARY KEY(userid,Area)
)';
DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
END IF;
END;
BEGIN
select Count(1)
into n_Rec
from dual
where exists(select 1
from cat
where TABLE_NAME = upper('Group'||SysName)) ;
IF n_Rec = 0 THEN
v_CreateStr:='CREATE TABLE Group';
v_CreateStr:=v_CreateStr||SysName;
v_CreateStr:=v_CreateStr||'(
GroupName varchar2(30) NOT NULL ,
describe char(60),
UserID varchar2(512)
)';
DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
END IF;
END;
BEGIN
select Count(1)
into n_Rec
from dual
where exists(select 1
from cat
where TABLE_NAME = upper('ControlPro')) ;
IF n_Rec = 0 THEN
v_CreateStr:='CREATE TABLE ControlPro (
dwmc varchar2(32),
modename varchar2(32),
modetype varchar2(1),
buttonName varchar2(20),
ControlType varchar2(10),
ControlName varchar2(20),
fLeft varchar2(4),
fTop varchar2(4),
fHeight varchar2(4),
fWidth varchar2(4),
fColor varchar2(20),
FontName varchar2(16),
FontSize varchar2(4),
FontColor varchar2(20),
FontType varchar2(2),
fText varchar2(50),
fItems varchar2(100),
fCaption varchar2(100),
fShape varchar2(1)
)';
DBMS_SQL.PARSE(v_Cursor,v_CreateStr,DBMS_SQL.V7);
v_NumRows:=DBMS_SQL.EXECUTE(v_Cursor);
END IF;
END;
END;
执行
grant create table to 用户名
或
grant create any table to 用户名
再执行你的存储过程
安装脚本通常会将该包的EXECUTE权限赋予PUBLIC,所以要保证确实有此权限
2。由于内置过程的内部所有角色都是失效的(DISABLED),但是调用DBMS_SQL
的用户需要权限执行该动态命令(你的例子中的串),同时也需要DBMS_SQL上的EXECUTE权限,所以需要显示的授予执行动态命令的权限,如上面半仙所示
grant create table to UserA(假设为你执行动态sql的用户);