ORACLE : error Error(6,3): PL/SQL: SQL Statement ignored and Error(8,3): PL/SQL: ORA-00933: SQL command not properly ended in procedure

Question

I want to create a procedure, but when i compile those procedure always got an error, i think the procedure is right, whats wrong. This is my procedure :

create or replace PROCEDURE            "DROP_PART_USER" 
AS
H VARCHAR(50); 

BEGIN
    FOR cc IN
    (   
        select PARTITION_NAME INTO H FROM all_tab_partitions 
        WHERE table_name = 'MOB_TRACK_USER' AND partition_name <> 'OLD_DATA_USER' ORDER BY PARTITION_POSITION ASC
        FETCH NEXT 1 ROWS ONLY
    ) LOOP

        --drop partitions older than specified retention preriod
        EXECUTE IMMEDIATE 'ALTER TABLE ' || 'APP_MOBILE_TRACKING' ||'.'||'MOB_TRACK_USER '
             || ' DROP PARTITION ' || cc.partition_name || ' UPDATE GLOBAL INDEXES';       
    END LOOP;

END;

got an error Error(8,3): PL/SQL: ORA-00933: SQL command not properly ended

Answer 1

What version of Oracle are you on? FETCH FIRST etc came along in 12c. If you are on 11, then that syntax will not work and you'd get the error you stated. You query would need to be:

select * from (
SELECT PARTITION_NAME
                    FROM all_tab_partitions
                   WHERE     table_name = 'MOB_TRACK_USER'
                         AND partition_name <> 'OLD_DATA_USER'
                ORDER BY PARTITION_POSITION ASC
)
where rownum = 1