procedure 编译没问题,调用ERROR(20分)

  • 主题发起人 主题发起人 WTO_LOVER
  • 开始时间 开始时间
W

WTO_LOVER

Unregistered / Unconfirmed
GUEST, unregistred user!
create or replace procedure createtable_pro(tname in varchar2) is
nameindex number(5);
tablename varchar2(20);
sqltext varchar2(300);
begin
select dhd_seq.nextval into nameindex from dual;
tablename:=tname||to_char(nameindex);
sqltext:='create table '||tablename||' (dcid number(3),dcmc varchar2(40))';
execute immediate sqltext;
end createtable_pro;
==========================
CALL createtable_pro('AAAA');
---error----
ORA-01031: insufficient privileges
ORA-06512: at "PDA.CREATETABLE_PRO", line 9
ORA-06512: at line 1
我如何处里???????
 
这是什么乱七八糟的? 存储过程? delphi ? 都不像!
 
sorry?ORACLE 存储过程?
 
sorry?
1,传入一个表名,
2,在表名的尾部加入一个自增值,
3,建立表;
--例如:
CALL createtable_pro('DFW');=====》建立表:DFW1表;
CALL createtable_pro('DFW');=====》建立表:DFW2表;
CALL createtable_pro('DFW');=====》建立表:DFW3表;
ERROR提示:好象说我没权限,个是我有啊?我
在SQL*PLUS 中,CREATE TABLE 。。。没问题啊?




 
我检索了一下,大师们都所用
SYS。DBM_SQL.EXECUTE(CURSOR);
但是,我在PL/SQL DEVELOPER 中调试,ERROR,

Error: PLS-00221: 'EXECUTE' is not a procedure or is undefined
Line: 14
Text: dbms_sql.execute(CURSOR_NAME);

Error: PL/SQL: Statement ignored
Line: 14
Text: dbms_sql.execute(CURSOR_NAME);
调试,调试---?
 
OK!
加了
....
tempnumber number;
.....
begin
.....
tempnumber:=dbms_sql.execute(cursor);
....
end;
-------------------------
不再报错了,但是,还是insufficient privileges
WK,怎么啦,ORACLE?
 
OK?
我解决了?感谢前辈的大师们,
-------------------------------------------
create or replace procedure createtable_pro(tname in varchar2) as
nameindex number(5);
tablename varchar2(20);
sqltext varchar2(300);
CURSOR_NAME INTEGER;
tempnumber number;
begin
select dhd_seq.nextval into nameindex from dual;
tablename:=tname||to_char(nameindex);
sqltext:='GRANT CREATE TABLE TO PDA ';
CURSOR_NAME:=SYS.DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CURSOR_NAME,SQLTEXT,SYS.DBMS_SQL.V7);
tempnumber:=dbms_sql.execute(CURSOR_NAME);

DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
sqltext:='create table '||tablename||' (id number(3),name varchar2(40))';
-- execute immediate sqltext;
CURSOR_NAME:=SYS.DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CURSOR_NAME,SQLTEXT,SYS.DBMS_SQL.V7);
tempnumber:=dbms_sql.execute(CURSOR_NAME);

DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
end createtable_pro;


..======================
结束,BYE
 
更少的代码:不用游标,包函数;
-----------------------
create or replace procedure createtable_pro(tname in varchar2) as
nameindex number(5);
tablename varchar2(20);
sqltext varchar2(300);
begin
select dhd_seq.nextval into nameindex from dual;
tablename:=tname||to_char(nameindex);
sqltext:='GRANT CREATE TABLE TO PDA ';
execute immediate sqltext;
sqltext:='create table '||tablename||' (dcid number(3),dcmc varchar2(40))';
execute immediate sqltext;
end createtable_pro;
 
自娱自乐? 嗯,好!给你加分!
 
多人接受答案了。
 
后退
顶部