0

I have many partitions to analyze and don't want to paste every single line to terminal so I'm trying to run a procedure for it, but I got complitaion error:

CREATE OR REPLACE PROCEDURE analyze_table
  BEGIN
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows;
    END;
    /

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE ANALYZE_TABLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3      PLS-00103: Encountered the symbol "BEGIN" when expecting one of
         the following:
         ( ; is with default authid as cluster compress order using
         compiled wrapped external deterministic parallel_enable
         pipelined result_cache accessible rewrite

What is reason for that?

0

You can not place DDL commands directly in PL/SQL code. You can run them as dynamic SQL, for example:

CREATE OR REPLACE PROCEDURE analyze_table
  BEGIN
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    execute immediate 'analyze table SCHEMA.TABLE_NAME partition (PARTITION) validate structure cascade into invalid_rows';
    END;
    /

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.