英文:
how to create index by using dynamic sql?
问题
declare l1 integer;
begin execute immediate q '!
begin
execute immediate ' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT(ROW_ID) PARALLEL 4 ';
execute immediate ' CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT(PAR_ROW_ID, FILE_NAME, FILE_EXT, CONFLICT_ID) PARALLEL 4 ';
execute immediate '
select 1 from  dual '
into :l1;
end;' using out l1;
end;
英文:
----------- it is giving below error ,where i did mistake ---------------
declare l1 integer;
    begin execute immediate q '!
      begin
    	execute immediate ' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT(ROW_ID) PARALLEL 4 ';
    	execute immediate ' CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT(PAR_ROW_ID, FILE_NAME, FILE_EXT, CONFLICT_ID) PARALLEL 4 ';
    	execute immediate '
    select 1 from  dual '
    	into :l1;
      end;!' using out l1;
    end;
------- error -------------------
ORA-06550: line 2, column 27:
PLS-00103: Encountered the symbol "!
begin
execute immediate " when expecting one of the following:
答案1
得分: 1
问题出在错误的 q 引号语法上。这导致了你所看到的错误。
DECLARE
BEGIN
  EXECUTE IMMEDIATE q'!BEGIN NULL; END;';
END;
/
错误报告 -
ORA-06550: 第 3 行, 第 23 列:
PLS-00103: 遇到符号 ""!BEGIN NULL; END!;" 时, 期望以下之一:
. ( * @ % & = - + ; < / > at in is mod remainder not rem
return returning <an exponent (**)> <> or != or ~= >= <= <>
and or like like2 like4 likec between into using || multiset
bulk member submultiset
符号 "*"' 被用来代替 "!BEGIN NULL; END!;" 以继续。
06550. 00000 -  "第 %s 行, 第 %s 列:\n%s"
*Cause:    通常是 PL/SQL 编译错误。
*Action:
应该在 q 和 ' 之间没有空格:
DECLARE
BEGIN
  EXECUTE IMMEDIATE q'!BEGIN NULL; END;';
END;
/
PL/SQL 过程成功完成。
英文:
The issue is the incorrect q-quoting syntax. That is causing the error you're seeing.
DECLARE
BEGIN
  EXECUTE IMMEDIATE q '!BEGIN NULL; END;!';
END;
/
Error report -
ORA-06550: line 3, column 23:
PLS-00103: Encountered the symbol "!BEGIN NULL; END;!" when expecting one of the following:
   . ( * @ % & = - + ; < / > at in is mod remainder not rem
   return returning <an exponent (**)> <> or != or ~= >= <= <>
   and or like like2 like4 likec between into using || multiset
   bulk member submultiset
The symbol "*" was substituted for "!BEGIN NULL; END;!" to continue.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
There should be no space between the q and the ':
DECLARE
BEGIN
  EXECUTE IMMEDIATE q'!BEGIN NULL; END;!';
END;
/
PL/SQL procedure successfully completed.
答案2
得分: 0
代码部分不需要翻译,以下是代码执行的地点和结果:
在哪里以及如何运行这段代码?我在 Oracle 11g 的 TOAD 和 SQL*Plus 中进行了测试。
示例表:
SQL> CREATE TABLE s_accnt_att
  2  (
  3     row_id        NUMBER,
  4     par_row_id    NUMBER,
  5     file_name     NUMBER,
  6     file_ext      NUMBER,
  7     conflict_id   NUMBER
  8  );
Table created.
过程:
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE q'[
  5        begin
  6          execute immediate ' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 ';
  7          execute immediate ' CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 ';
  8          execute immediate ' select 1 from dual '
  9          into :l1;
 10        end;
 11        ]'
        USING OUT l1;
  DBMS_OUTPUT.put_line ('L1 = ' || l1);
END;
/
L1 = 1
PL/SQL procedure successfully completed.
看起来没问题。
因为目标数据库是 19c,所以我在那里也尝试了一下,没有问题。
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE q'[
  5            begin
  6              execute immediate ' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 ';
  7              execute immediate ' CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 ';
  8              execute immediate ' select 1 from dual '
  9              into :l1;
 10           end;
 11           ]'
        USING OUT l1;
  DBMS_OUTPUT.put_line ('L1 = ' || l1);
END;
/
L1 = 1
PL/SQL procedure successfully completed.
因此,也许是你的迁移工具(是哪个工具)不识别 q-quoting 机制。如果是这样,不要使用它,而是使用 双 单引号代替:
SQL> DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE '
  5            begin
  6              execute immediate '' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 '';
  7              execute immediate '' CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 '';
  8              execute immediate '' select 1 from dual ''
  9              into :l1;
 10           end;
 11           ';
        USING OUT l1;
  DBMS_OUTPUT.put_line ('L1 = ' || l1);
END;
/
PL/SQL procedure successfully completed.
希望对你有所帮助。
英文:
How/where exactly did you run that piece of code? I tested it on TOAD and SQL*Plus in Oracle 11g.
Sample table:
SQL> CREATE TABLE s_accnt_att
  2  (
  3     row_id        NUMBER,
  4     par_row_id    NUMBER,
  5     file_name     NUMBER,
  6     file_ext      NUMBER,
  7     conflict_id   NUMBER
  8  );
Table created.
Procedure:
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE q'[
  5        begin
  6          execute immediate ' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 ';
  7          execute immediate ' CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 ';
  8          execute immediate ' select 1 from dual '
  9          into :l1;
 10        end;
 11        ]'
 12        USING OUT l1;
 13
 14     DBMS_OUTPUT.put_line ('L1 = ' || l1);
 15  END;
 16  /
L1 = 1
PL/SQL procedure successfully completed.
SQL>
Seems to be OK.
As your target database is 19c, I tried it there as well; no problem.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE q'[
  5            begin
  6              execute immediate ' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 ';
  7              execute immediate ' CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 ';
  8              execute immediate ' select 1 from dual '
  9              into :l1;
 10           end;
 11           ]'
 12        USING OUT l1;
 13
 14     DBMS_OUTPUT.put_line ('L1 = ' || l1);
 15  END;
 16  /
L1 = 1
PL/SQL procedure successfully completed.
SQL>
Therefore, maybe it is about your migration tool (which one is it) that doesn't recognize the q-quoting mechanism. If that's so, don't use it and double single quotes instead:
SQL> DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE '
  5            begin
  6              execute immediate '' CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 '';
  7              execute immediate '' CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 '';
  8              execute immediate '' select 1 from dual ''
  9              into :l1;
 10           end;
 11           '
 12        USING OUT l1;
 13
 14     DBMS_OUTPUT.put_line ('L1 = ' || l1);
 15  END;
 16  /
PL/SQL procedure successfully completed.
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论