2 Replies Latest reply on May 22, 2020 5:59 PM by Solomon Yakobson

    PL/SQL Anonymous Block

    0cfaa959-65ef-49ec-858f-38d52bbce5a7

      I am attempting to write simple PL/SQL procedure to rebuild indexes in an Oracle Schema.

      The code works fine when written and executed as a PL/SQL Anonymous Block but throws an error when written and executed as a PL/SQL procedure.

      Please have a look and respond with any gotchas or advice on how to solve this little puzzler.

       

      --IDX_REBUILD: Anonymous Block
      set timing on;
      SET SERVEROUTPUT ON;
      DECLARE
        
      CURSOR Index_Cur IS
        
      SELECT owner, index_name
        
      FROM cjb_indexes
        
      WHERE (owner like 'ZIPPY%')
        
      AND INDEX_TYPE = 'NORMAL'
        
      ORDER BY owner, index_name;
        SQL_STMT VARCHAR2
      (200);
      BEGIN
        DBMS_OUTPUT
      .ENABLE (100000);
        
      FOR IndexRec IN Index_Cur LOOP
        SQL_STMT
      := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
        DBMS_OUTPUT
      .PUT_LINE(SQL_STMT);
        
      EXECUTE IMMEDIATE SQL_STMT;
        DBMS_OUTPUT
      .NEW_LINE;
        
      END LOOP;
      END;
      /

      ....
      ALTER INDEX ZIPPY.R157_SDE_ROWID_UK REBUILD
      ALTER INDEX ZIPPY.R49_SDE_ROWID_UK REBUILD
      ALTER INDEX ZIPPY.R50_SDE_ROWID_UK REBUILD
      ALTER INDEX ZIPPY.R51_SDE_ROWID_UK REBUILD
      ALTER INDEX ZIPPY.R52_SDE_ROWID_UK REBUILD


      PL
      /SQL procedure successfully completed.


      --IDX_REBUILD: PROCEDURE
      set timing on;
      SET SERVEROUTPUT ON;
      CREATE OR REPLACE PROCEDURE CJB_IDX_REBUILD
      IS
        
      CURSOR Index_Cur IS
        
      SELECT owner, index_name
        
      FROM cjb_indexes
        
      WHERE (owner like 'ZIPPY%')
        
      AND INDEX_TYPE = 'NORMAL'
        
      ORDER BY owner, index_name;
        SQL_STMT VARCHAR2
      (200);
      BEGIN
        DBMS_OUTPUT
      .ENABLE (100000);
        
      FOR IndexRec IN Index_Cur LOOP
        SQL_STMT
      := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
        DBMS_OUTPUT
      .PUT_LINE(SQL_STMT);
        
      EXECUTE IMMEDIATE SQL_STMT;
        DBMS_OUTPUT
      .NEW_LINE;
        
      END LOOP;
      /

      execute CJB_IDX_REBUILD;

      Procedure created.

      Elapsed
      : 00:00:00.01
      zippy
      > execute CJB_IDX_REBUILD;
      ALTER INDEX ZIPPY.A108_PK REBUILD
      BEGIN CJB_IDX_REBUILD; END;

      *
      ERROR at line
      1:
      ORA-01418
      : specified index does not exist
      ORA-06512
      : at "CJB.CJB_IDX_REBUILD", line 15
      ORA-06512
      : at line 1

        • 1. Re: PL/SQL Anonymous Block
          AndrewSayer

          0cfaa959-65ef-49ec-858f-38d52bbce5a7 wrote:

           

          I am attempting to write simple PL/SQL procedure to rebuild indexes in an Oracle Schema.

          The code works fine when written and executed as a PL/SQL Anonymous Block but throws an error when written and executed as a PL/SQL procedure.

          Please have a look and respond with any gotchas or advice on how to solve this little puzzler.

           

          --IDX_REBUILD: Anonymous Block
          set timing on;
          SET SERVEROUTPUT ON;
          DECLARE
          CURSOR Index_Cur IS
          SELECT owner, index_name
          FROM cjb_indexes
          WHERE (owner like 'ZIPPY%')
          AND INDEX_TYPE = 'NORMAL'
          ORDER BY owner, index_name;
          SQL_STMT VARCHAR2
          (200);
          BEGIN
          DBMS_OUTPUT
          .ENABLE (100000);
          FOR IndexRec IN Index_Cur LOOP
          SQL_STMT
          := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
          DBMS_OUTPUT
          .PUT_LINE(SQL_STMT);
          EXECUTE IMMEDIATE SQL_STMT;
          DBMS_OUTPUT
          .NEW_LINE;
          END LOOP;
          END;
          /

          ....
          ALTER INDEX ZIPPY.R157_SDE_ROWID_UK REBUILD
          ALTER INDEX ZIPPY.R49_SDE_ROWID_UK REBUILD
          ALTER INDEX ZIPPY.R50_SDE_ROWID_UK REBUILD
          ALTER INDEX ZIPPY.R51_SDE_ROWID_UK REBUILD
          ALTER INDEX ZIPPY.R52_SDE_ROWID_UK REBUILD


          PL
          /SQL procedure successfully completed.


          --IDX_REBUILD: PROCEDURE
          set timing on;
          SET SERVEROUTPUT ON;
          CREATE OR REPLACE PROCEDURE CJB_IDX_REBUILD
          IS
          CURSOR Index_Cur IS
          SELECT owner, index_name
          FROM cjb_indexes
          WHERE (owner like 'ZIPPY%')
          AND INDEX_TYPE = 'NORMAL'
          ORDER BY owner, index_name;
          SQL_STMT VARCHAR2
          (200);
          BEGIN
          DBMS_OUTPUT
          .ENABLE (100000);
          FOR IndexRec IN Index_Cur LOOP
          SQL_STMT
          := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
          DBMS_OUTPUT
          .PUT_LINE(SQL_STMT);
          EXECUTE IMMEDIATE SQL_STMT;
          DBMS_OUTPUT
          .NEW_LINE;
          END LOOP;
          /

          execute CJB_IDX_REBUILD;

          Procedure created.

          Elapsed
          : 00:00:00.01
          zippy
          > execute CJB_IDX_REBUILD;
          ALTER INDEX ZIPPY.A108_PK REBUILD
          BEGIN CJB_IDX_REBUILD; END;

          *
          ERROR at line
          1:
          ORA-01418
          : specified index does not exist
          ORA-06512
          : at "CJB.CJB_IDX_REBUILD", line 15
          ORA-06512
          : at line 1

          Privileges obtained by roles are not inherited by default to stored PL/SQL programs.

          That said, it is not normal to rebuild indexes, especially not in bulk. Not only does it take a lot of effort, it also can cause performance problems. Why exactly are you trying to do this?

          • 2. Re: PL/SQL Anonymous Block
            Solomon Yakobson

            Use:

             

            CREATE OR REPLACE

              PROCEDURE CJB_IDX_REBUILD

                AUTHID CURRENT_USER

                IS

            ...

             

            SY.