使用动态SQL如何创建索引?

huangapple go评论48阅读模式
英文:

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&#39; 之间没有空格:

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 &#39;!BEGIN NULL; END;!&#39;;
END;
/

Error report -
ORA-06550: line 3, column 23:
PLS-00103: Encountered the symbol &quot;!BEGIN NULL; END;!&quot; when expecting one of the following:

   . ( * @ % &amp; = - + ; &lt; / &gt; at in is mod remainder not rem
   return returning &lt;an exponent (**)&gt; &lt;&gt; or != or ~= &gt;= &lt;= &lt;&gt;
   and or like like2 like4 likec between into using || multiset
   bulk member submultiset
The symbol &quot;*&quot; was substituted for &quot;!BEGIN NULL; END;!&quot; to continue.
06550. 00000 -  &quot;line %s, column %s:\n%s&quot;
*Cause:    Usually a PL/SQL compilation error.
*Action:

There should be no space between the q and the &#39;:

DECLARE
BEGIN
  EXECUTE IMMEDIATE q&#39;!BEGIN NULL; END;!&#39;;
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&gt; 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&gt; SET SERVEROUTPUT ON;
SQL&gt;
SQL&gt; DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE q&#39;[
  5        begin
  6          execute immediate &#39; CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 &#39;;
  7          execute immediate &#39; CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 &#39;;
  8          execute immediate &#39; select 1 from dual &#39;
  9          into :l1;
 10        end;
 11        ]&#39;
 12        USING OUT l1;
 13
 14     DBMS_OUTPUT.put_line (&#39;L1 = &#39; || l1);
 15  END;
 16  /
L1 = 1

PL/SQL procedure successfully completed.

SQL&gt;

Seems to be OK.


As your target database is 19c, I tried it there as well; no problem.

SQL&gt; select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL&gt; DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE q&#39;[
  5            begin
  6              execute immediate &#39; CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 &#39;;
  7              execute immediate &#39; CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 &#39;;
  8              execute immediate &#39; select 1 from dual &#39;
  9              into :l1;
 10           end;
 11           ]&#39;
 12        USING OUT l1;
 13
 14     DBMS_OUTPUT.put_line (&#39;L1 = &#39; || l1);
 15  END;
 16  /
L1 = 1

PL/SQL procedure successfully completed.

SQL&gt;

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&gt; DECLARE
  2     l1  INTEGER;
  3  BEGIN
  4     EXECUTE IMMEDIATE &#39;
  5            begin
  6              execute immediate &#39;&#39; CREATE INDEX S_ACCNT_ATT_P1 ON S_ACCNT_ATT ( ROW_ID ) PARALLEL 4 &#39;&#39;;
  7              execute immediate &#39;&#39; CREATE INDEX S_ACCNT_ATT_U1 ON S_ACCNT_ATT ( PAR_ROW_ID , FILE_NAME , FILE_EXT , CONFLICT_ID ) PARALLEL 4 &#39;&#39;;
  8              execute immediate &#39;&#39; select 1 from dual &#39;&#39;
  9              into :l1;
 10           end;
 11           &#39;
 12        USING OUT l1;
 13
 14     DBMS_OUTPUT.put_line (&#39;L1 = &#39; || l1);
 15  END;
 16  /

PL/SQL procedure successfully completed.

SQL&gt;

huangapple
  • 本文由 发表于 2023年2月8日 18:07:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/75384178.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定