how to create index by using dynamic sql?

Question

----------- 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:

Answer 1

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.

Answer 2

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>