getting error while compiling the stored procedure

Question

I am writing this below stored procedure but i am also getting the exception while compiling the procedure in oracle, below is the procedure

CREATE OR REPLACE PACKAGE BODY TEST_TABLE AS 
PROCEDURE TEST_TABLE         


--This procedure will delete partitions for the following tables:
--TEST_TABLE
BEGIN
  FOR cc IN
  (
  SELECT partition_name, high_value
  FROM user_tab_partitions
  WHERE table_name = 'TEST_TABLE'
  )

LOOP
    EXECUTE IMMEDIATE 'BEGIN               
IF sysdate >= ADD_MONTHS(' || cc.high_value || ', 3) THEN                  
EXECUTE IMMEDIATE                     
''ALTER TABLE TEST_TABLE DROP PARTITION ' || cc.partition_name || '                     
'';               
END IF;    
  dbms_output.put_line('drop partition completed');        
END;';
  END LOOP;

  exception
                when others then
                                dbms_output.put_line(SQLERRM);

END;

END; 
/

and the exception that I am getting it while compiling is Please advise how to overcome from this.

Error(7,1): PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     ( ; is with authid as cluster order using external    deterministic parallel_enable pipelined result_cache The symbol ";" was substituted for "BEGIN" to continue. 

Error(22,25): PLS-00103: Encountered the symbol "DROP" 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 || bulk member    submultiset  

Answer 1

You need to make correct quotation as below( and one more is keyword just after PROCEDURE TEST_TABLE "thanks to Alex who make me awaken" ) :

CREATE OR REPLACE PACKAGE BODY PKG_TEST_TABLE IS 
 PROCEDURE TEST_TABLE IS        

--This procedure will delete partitions for the following tables:
--TEST_TABLE
 BEGIN
  FOR cc IN
  (
  SELECT partition_name, high_value
  FROM user_tab_partitions
  WHERE table_name = 'TEST_TABLE'
  )    
  LOOP
   BEGIN               
     IF sysdate >= ADD_MONTHS(cc.high_value, 3) THEN                  
      EXECUTE IMMEDIATE                     
      'ALTER TABLE TEST_TABLE DROP PARTITION ' || cc.partition_name;                   
       Dbms_Output.Put_Line('Dropping partition is completed.');        
     END IF;
   END;
  END LOOP;

  EXCEPTION WHEN Others THEN Dbms_Output.Put_Line( SQLERRM );

 END TEST_TABLE;

END PKG_TEST_TABLE; 
/

As a little suggestion use a different name for package than procedure such as PKG_TEST_TABLE against confusion.

Edit : of course you need to create a specification part for a package before the body part of the package :

CREATE OR REPLACE PACKAGE PKG_TEST_TABLE IS 
 PROCEDURE TEST_TABLE;
END PKG_TEST_TABLE;
/

Answer 2

The first error message tells you something is missing before BEGIN, and even mentions the two possible options in the 'when expecting' list. You need to change it to:

PROCEDURE TEST_TABLE IS
--                   ^^

Or you can use AS instead of IS if you prefer...

The second error is because you have a string literal embedded in your dynamic SQL and you haven't escaped the single quotes, though you have elsewhere:

...
  dbms_output.put_line(''drop partition completed'');
--                     ^                         ^
END;';

You could use the alternative quoting mechanism instead.

I'm not sure why you're doing two levels of dynamic SQL; you can do the dbms_output() and evaluate cc.high_value statically, and decide whether to make the alter call, with only that part dynamic (as @BarbarosÖzhan has shown, so I wont repeat that!). Or do the high-value check within the cursor query.

I am still getting exception Error(1,14): PLS-00304: cannot compile body of 'TEST_TABLE' without its specification

If you want a package then you have to create its specification before you try to create its body:

CREATE OR REPLACE PACKAGE TEST_TABLE AS 
PROCEDURE TEST_TABLE;
END TEST_TABLE;
/

CREATE OR REPLACE PACKAGE BODY TEST_TABLE AS 
PROCEDURE TEST_TABLE IS
BEGIN
  FOR cc IN
  ...
  LOOP
    ...
  END LOOP;
END TEST_TABLE; -- end of procedure
END TEST_TABLE; -- end of package
/

Having the package name the same as a procedure within it is a bit odd and confusing.

But maybe you didn't actually want a package at all, and were trying to create a standalone procedure, in which case just remove the package-body part:

CREATE OR REPLACE PROCEDURE TEST_TABLE AS
BEGIN
  FOR cc IN
  ...
  LOOP
    ...
  END LOOP;
END TEST_TABLE; -- end of procedure
/

Read more.

I would strongly suggest you get rid of the exception handler, and I've left that out of those outlines - you should let any exception flow back to the caller. You don't know that whoever calls this will even have output enabled, so might well not even see the message you're printing instead. Only ever catch exceptions you can handle and need to handle at that point.