赋予create any table 系统权限给用户是可以创建表了,但是为什么会这样的叫呢,直接写语句可以数据表,但把它写成存储过程就不可以创建数据表了,真是奇怪
存储过程中无法使用角色权限直接写语句可以表是因为你拥有某个角色(如dba),这个角色含有create table/create any table权限
请问我这个问题出在哪,创建一个表,序列和触发器,总是提示我ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误: CREATE OR REPLACE PROCEDURE openserviceproc (deptid in char, status in integer, note in varchar2, tablename in varchar2 ) IS lv_deptid char(32); lv_reqdate date; lv_status int; lv_note varchar2(100); lv_tablename varchar2(50);lv_prefix_sms varchar2(10); lv_prefix_log varchar2(10); lv_prefix_att varchar2(10); lv_query_str varchar2(4000);BEGIN lv_deptid := deptid; select sysdate into lv_reqdate from dual; lv_status := status; lv_note := note; lv_tablename := tablename; lv_prefix_sms := 't_sms_'; lv_prefix_log := 't_logs_'; lv_prefix_att := 't_att_'; --创建短信表 lv_query_str := ' CREATE TABLE ' +lv_prefix_sms+lv_tablename+'(' + ' id integer not null,' + ' schoolid char(32),' + ' teacherid integer,' + ' type integer,' + ' mobile varchar2(15),' + ' stu_no varchar2(10),' + ' content varchar2(400),' + ' sendtime date,' + ' primary key(id)' + ')'; execute immediate lv_query_str;
lv_query_str := 'CREATE SEQUENCE '+lv_prefix_sms+lv_tablename+'_seq' + ' INCREMENT BY 1' + ' START WITH 1';
execute immediate lv_query_str;
lv_query_str := 'CREATE OR REPLACE TRIGGER '+lv_prefix_sms+lv_tablename+'_tri'+'BEFORE' + ' INSERT ON '+lv_prefix_sms+lv_tablename + ' FOR EACH ROW ' + ' BEGIN ' + ' SELECT '+lv_prefix_sms+lv_tablename+'_seq'+'.NEXTVAL INTO :NEW.ID FROM DUAL;' + ' END'; execute immediate lv_query_str; END openserviceproc;/
CREATE OR REPLACE PROCEDURE openserviceproc
(deptid in char,
status in integer,
note in varchar2,
tablename in varchar2
)
IS
lv_deptid char(32);
lv_reqdate date;
lv_status int;
lv_note varchar2(100);
lv_tablename varchar2(50);lv_prefix_sms varchar2(10);
lv_prefix_log varchar2(10);
lv_prefix_att varchar2(10);
lv_query_str varchar2(4000);BEGIN lv_deptid := deptid;
select sysdate into lv_reqdate from dual;
lv_status := status;
lv_note := note;
lv_tablename := tablename;
lv_prefix_sms := 't_sms_';
lv_prefix_log := 't_logs_';
lv_prefix_att := 't_att_';
--创建短信表
lv_query_str := ' CREATE TABLE ' +lv_prefix_sms+lv_tablename+'('
+ ' id integer not null,'
+ ' schoolid char(32),'
+ ' teacherid integer,'
+ ' type integer,'
+ ' mobile varchar2(15),'
+ ' stu_no varchar2(10),'
+ ' content varchar2(400),'
+ ' sendtime date,'
+ ' primary key(id)'
+ ')';
execute immediate lv_query_str;
lv_query_str := 'CREATE SEQUENCE '+lv_prefix_sms+lv_tablename+'_seq'
+ ' INCREMENT BY 1'
+ ' START WITH 1';
execute immediate lv_query_str;
lv_query_str := 'CREATE OR REPLACE TRIGGER '+lv_prefix_sms+lv_tablename+'_tri'+'BEFORE'
+ ' INSERT ON '+lv_prefix_sms+lv_tablename
+ ' FOR EACH ROW '
+ ' BEGIN '
+ ' SELECT '+lv_prefix_sms+lv_tablename+'_seq'+'.NEXTVAL INTO :NEW.ID FROM DUAL;'
+ ' END';
execute immediate lv_query_str;
END openserviceproc;/