英文:
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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论