create procedure test (tablename in varchar2)
as
v_sql string(1000);
begin
v_sql:='create table ....';
execute immediate v_sql;end test;
as
v_sql string(1000);
begin
v_sql:='create table ....';
execute immediate v_sql;end test;
解决方案 »
- solaris10+ORACLE RAC 下SQL语句硬解析,CPU耗时很长
- 关于oracle中能连接的最大用户数的问题
- Oracle 10g 掉电后,不能启动了。
- 在线等: 关于表空间
- 查询语句中带有BLOB字段的速度很慢
- oracle的存储过程如何使用游标把查询的测试集返回到客户端
- 各位高手,请教Oracle查询问题!
- 在线等!!!11急! ORA-01400: cannot insert NULL into ("LIANG"."ORDER_DETAIL"."ORDER_ID")
- oracle过程用什么语句直接判断insert into插入数据是否成功(有数据)?
- oracle 入门菜鸟求问个问题
- 一个专业性问题
- 请教高手!关于oracle8的密码问题!
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