请教关于用EXECUTE IMMEDIATE 创建索引的问题。

Question

Create Index CASENO_BA on T_CASE (CASENO) TABLESPACE YACDTEST这条创建索引的语句，单独执行，没任何问题。
EXECUTE IMMEDIATE 'Create Index CASENO_BA on T_CASE (CASENO) TABLESPACE YACDTEST';
但是如果这样执行，则报无效的SQL语句，

Answer 1

无效的sql语句？这是pl sql语法，你在前后加了begin end;了吗

Answer 2

begin
EXECUTE IMMEDIATE 'Create Index CASENO_BAa on a(age) TABLESPACE SYSTEM ';
end;
我这样在 PL/SQL里执行没有问题....
你登录了?

Answer 3

declare
varFromIndexName nvarchar2(500);  --源库中相对应表的索引名称
varTmp nvarchar2(500);
varFromIndexColumnName nvarchar2(500);  --源库中相对应的表的索引的字段名
varInteger integer;
v_sql nvarchar2(500);Cursor curFromIndex is  select t.*, i.index_type from [email protected] t, [email protected] i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name =  'T_CASE' order by t.index_name;
record_curFromIndex curFromIndex%rowtype;
begin
     open curFromIndex;
     varFromIndexName:='';
     varTmp :='';
     varFromIndexColumnName:='';
     varInteger:=1;
     loop
     fetch curFromIndex into record_curFromIndex;
     exit when curFromIndex%notfound;
         if varInteger = 1 then
            varTmp := record_curFromIndex.index_name;
            varFromIndexName:=record_curFromIndex.index_name;
            varFromIndexColumnName:=record_curFromIndex.column_name;
         else
               varTmp := record_curFromIndex.index_name;
               if (VarTmp <> varFromIndexName) then
                  varInteger:=0;
                  v_sql:= 'Create Index ' || varFromIndexName || ' on T_CASE (' || varFromIndexColumnName || ') TABLESPACE YACDTEST';
                  dbms_output.put_line(v_sql);
                  EXECUTE IMMEDIATE  'Create Index ' || varFromIndexName || ' on T_CASE (' || varFromIndexColumnName || ') TABLESPACE YACDTEST';--执行到这里，就报无效SQL语句了，但把这粘出来就能执行
                  execute immediate 'commit';
               else
                  varFromIndexColumnName:=varFromIndexColumnName || ',' || record_curFromIndex.column_name;
               end if ;         end if ;         varInteger:=varInteger+1;     end loop;
     close curFromIndex;end;

Answer 4

declare
varFromIndexName nvarchar2(500);  --源库中相对应表的索引名称
varTmp nvarchar2(500);
varFromIndexColumnName nvarchar2(500);  --源库中相对应的表的索引的字段名
varInteger integer;
v_sql nvarchar2(500);Cursor curFromIndex is  select t.*, i.index_type from [email protected] t, [email protected] i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name =  'T_CASE' order by t.index_name;
record_curFromIndex curFromIndex%rowtype;
begin
     open curFromIndex;
     varFromIndexName:='';
     varTmp :='';
     varFromIndexColumnName:='';
     varInteger:=1;
     loop
     fetch curFromIndex into record_curFromIndex;
     exit when curFromIndex%notfound;
         if varInteger = 1 then
            varTmp := record_curFromIndex.index_name;
            varFromIndexName:=record_curFromIndex.index_name;
            varFromIndexColumnName:=record_curFromIndex.column_name;
         else
               varTmp := record_curFromIndex.index_name;
               if (VarTmp <> varFromIndexName) then
                  varInteger:=0;
                  v_sql:= 'Create Index ' || varFromIndexName || ' on T_CASE (' || varFromIndexColumnName || ') TABLESPACE YACDTEST';
                  dbms_output.put_line(v_sql);
                  EXECUTE IMMEDIATE  'Create Index ' || varFromIndexName || ' on T_CASE (' || varFromIndexColumnName || ') TABLESPACE YACDTEST';--执行到这里，就报无效SQL语句了，但把这粘出来就能执行
                  execute immediate 'commit';
               else
                  varFromIndexColumnName:=varFromIndexColumnName || ',' || record_curFromIndex.column_name;
               end if ;         end if ;         varInteger:=varInteger+1;     end loop;
     close curFromIndex;end;

Answer 5

begin
EXECUTE IMMEDIATE 'Create Index CASENO_BA on T_CASE (CASENO) TABLESPACE YACDTEST';
end;

Answer 6

要加上begin end 否则execute immediate无法被识别为程序块

Answer 7

先把exec...那两句注释掉，查看dbms_output.put_line(v_sql);输出的语句是否正常
问题很可能出在这里，即参数的值上

Answer 8

dbms_output.put_line(v_sql); 输出的SQL语句完全正常，输出的我粘出来能够正常执行的