我试了一下,按照 jiezhi(西域浪子) 的是这么写的: create or replace procedure create_table(tablename in varchar2) is v_sql varchar2(1000); begin v_sql := 'create table '||tablename||'(col1 number, col2 varchar(10));'; execute immediate v_sql; end create_table; /下面是在pl/sql中的运行结果:SQL> edit Wrote file afiedt.bufline 8 truncated. 1 create or replace procedure create_table(tablename in varchar2) 2 is 3 v_sql varchar2(1000); 4 begin 5 v_sql := 'create table '||tablename||'(col1 number, col2 varchar(10));'; 6 execute immediate v_sql; 7* end create_table; 8 /Procedure created.SQL> call create_table('test'); call create_table('test') * ERROR at line 1: ORA-00911: invalid character ORA-06512: at "ADMIN.CREATE_TABLE", line 6 ORA-06512: at line 1请问这是什么错误?
多谢,不过我试了一下你的例子,这一句编译有问题: 1 create or replace procedure test 2 as 3 v_sql string(1000); 4 begin 5 v_sql:='create table zhj2(id number);'; 6 execute immediate v_sql; 7* end test; 8 /警告: 创建的过程带有编译错误。SQL> show error; PROCEDURE TEST 出现错误:LINE/COL ERROR -------- ------------------------------------------------- 6/10 PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时: :=.(@%;
各位观众: SQL> BEGIN 2 EXECUTE IMMEDIATE 'TRUNCATE TABLE DF_TMP ' ; 3 END; 4 /PL/SQL procedure successfully completed.SQL> ED Wrote file afiedt.buf 1 BEGIN 2 EXECUTE IMMEDIATE 'TRUNCATE TABLE DF_TMP ;' ; 3* END; SQL> / BEGIN * ERROR at line 1: ORA-00911: invalid character ORA-06512: at line 2
补充一下版本 SQL> select * from v$version 2 /BANNER -------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production PL/SQL Release 8.1.5.0.0 - Production CORE Version 8.1.5.0.0 - Production TNS for 32-bit Windows: Version 8.1.5.0.0 - Production NLSRTL Version 3.4.0.0.0 - Production
回复人: taler() ( ) 信誉:98 2003-06-13 12:29:00 得分:0
这样做是可以通过编译,但是在执行时会报'权限不足'的错误,不知道需要什么样的权限
------------------------------------------------------------------------ 是啊是啊,下面是我的运行结果:SQL> edit Wrote file afiedt.buf 1 create or replace procedure create_table(tablename in varchar2) 2 is 3 v_sql varchar2(1000); 4 begin 5 v_sql := 'create table '||tablename||'(col1 number, col2 varchar(10))'; 6 execute immediate v_sql; 7* end create_table; SQL> /Procedure created.SQL> call create_table('test'); call create_table('test') * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "ADMIN.CREATE_TABLE", line 6 ORA-06512: at line 1我的ADMIN用户的权限是DBA,CONNECT,RESOURCE都有
look:
SQL> declare
2 begin
3 create table zhj(id number);
4 end;
5 /
create table zhj(id number);
*
ERROR 位于第 3 行:
ORA-06550: 行3、列1:
PLS-00103: 出现符号"CREATE"在需要下列之一时:
begindeclareexitforgotoif
loopmodnullpragmaraisereturnselectupdatewhile<an identifier>
<a double-quoted delimited-identifier><a bind variable><<
closecurrentdeletefetchlockinsertopenrollbacksavepointsetsql
commit<a single-quoted SQL string>
create or replace procedure create_table(tablename in varchar2)
is
v_sql varchar2(1000);
begin
v_sql := 'create table '||tablename||'(col1 number, col2 varchar(10));';
execute immediate v_sql;
end create_table;
/下面是在pl/sql中的运行结果:SQL> edit
Wrote file afiedt.bufline 8 truncated.
1 create or replace procedure create_table(tablename in varchar2)
2 is
3 v_sql varchar2(1000);
4 begin
5 v_sql := 'create table '||tablename||'(col1 number, col2 varchar(10));';
6 execute immediate v_sql;
7* end create_table;
8 /Procedure created.SQL> call create_table('test');
call create_table('test')
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "ADMIN.CREATE_TABLE", line 6
ORA-06512: at line 1请问这是什么错误?
1 create or replace procedure test
2 as
3 v_sql string(1000);
4 begin
5 v_sql:='create table zhj2(id number);';
6 execute immediate v_sql;
7* end test;
8 /警告: 创建的过程带有编译错误。SQL> show error;
PROCEDURE TEST 出现错误:LINE/COL ERROR
-------- -------------------------------------------------
6/10 PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时:
:=.(@%;
8i及以上支持execute immediate...
以下用dbms_sql来执行动态sql oraclenewbie(超新手) :ouygg(痞子酷)说的就是你的问题
SQL> BEGIN
2 EXECUTE IMMEDIATE 'TRUNCATE TABLE DF_TMP ' ;
3 END;
4 /PL/SQL procedure successfully completed.SQL> ED
Wrote file afiedt.buf 1 BEGIN
2 EXECUTE IMMEDIATE 'TRUNCATE TABLE DF_TMP ;' ;
3* END;
SQL> /
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 2
SQL> select * from v$version
2 /BANNER
--------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.5.0.0 - Production
TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
这样做是可以通过编译,但是在执行时会报'权限不足'的错误,不知道需要什么样的权限
------------------------------------------------------------------------
是啊是啊,下面是我的运行结果:SQL> edit
Wrote file afiedt.buf 1 create or replace procedure create_table(tablename in varchar2)
2 is
3 v_sql varchar2(1000);
4 begin
5 v_sql := 'create table '||tablename||'(col1 number, col2 varchar(10))';
6 execute immediate v_sql;
7* end create_table;
SQL> /Procedure created.SQL> call create_table('test');
call create_table('test')
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ADMIN.CREATE_TABLE", line 6
ORA-06512: at line 1我的ADMIN用户的权限是DBA,CONNECT,RESOURCE都有
grant create any table to user_name