CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Diana
Diana

Reputation: 33

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

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

Upvotes: 0

Views: 128

Answers (2)

Connor McDonald
Connor McDonald

Reputation: 11586

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

  • Don't SELECT INTO in cursor
    • this is cause of your problems

A few more notes:

  • use VARCHAR2, not VARCHAR
    • but not here, as you don't need that variable anyway
  • don't use double quotes in Oracle

Should be

CREATE OR REPLACE PROCEDURE drop_part_user
AS
BEGIN
   FOR cc IN (    SELECT PARTITION_NAME
                    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;

Upvotes: 2

Related Questions