Oracle comments within an anonymous block-CodePudding

I have the following code, which appears to work fine. When I add a comment within the anonymous block I get get a syntax error at line 27. The call is to a function called ddl.

How can I call the ddl function and remove the 2 lines of code directly under ddl call?

My goal is to shorten the code by calling ddl function for all the ALTER table commands.

Thanks to all who answer.


CREATE TABLE PARTITION_RETENTION (
   seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    TABLE_NAME VARCHAR2(30),
    RETENTION INTERVAL DAY(3) TO SECOND(0),
 CONSTRAINT
partition_retention_pk primary key (table_name),
CONSTRAINT CHK_NON_ZERO_DAYS CHECK (
        RETENTION > INTERVAL '0' DAY
    ),
    CONSTRAINT CHK_WHOLE_DAYS CHECK (
        EXTRACT(HOUR FROM RETENTION) = 0
        AND EXTRACT(MINUTE FROM RETENTION) = 0
        AND EXTRACT(SECOND FROM RETENTION) = 0
    )
);

insert into PARTITION_RETENTION (TABLE_NAME, RETENTION) 
select 'T1', interval '10' day from dual union all
select 'T3', interval '15' day from dual union all
select 'T4', 15 * interval '1' day from dual union all
select 'T5', 5 * interval '1 00:00:00' day to second from dual;

CREATE TABLE t1 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/

INSERT into t1 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(1,'DAY') from dual
union all
select dt.dt   interv, interv from dt
where dt.dt   interv < date '2022-02-01')
select dt from dt;
/



DECLARE
    CANNOT_DROP_LAST_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);

    CANNOT_DROP_ONLY_ONE_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_ONLY_ONE_PARTITION, -14083);

   ts TIMESTAMP;
 
   CURSOR TablePartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, p.HIGH_VALUE, t.INTERVAL, RETENTION, DATA_TYPE
    FROM USER_PART_TABLES t
        JOIN USER_TAB_PARTITIONS p USING (TABLE_NAME)
        JOIN USER_PART_KEY_COLUMNS pk ON pk.NAME = TABLE_NAME
        JOIN USER_TAB_COLS tc USING (TABLE_NAME, COLUMN_NAME)
        JOIN PARTITION_RETENTION r USING (TABLE_NAME)
    WHERE        pk.object_type     = 'TABLE' AND
   t.partitioning_type = 'RANGE' AND 
    REGEXP_LIKE (tc.data_type, '^DATE$|^TIMESTAMP.*'); 

BEGIN

    FOR aPart IN TablePartitions LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts < SYSTIMESTAMP - aPart.RETENTION THEN
            BEGIN
                – ddl('alter table '||aPart.TABLE_NAME||' drop partition '||aPart.partition_name);

               EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                 DBMS_OUTPUT.PUT_LINE('Dropped partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
            EXCEPTION
                WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN
                    DBMS_OUTPUT.PUT_LINE('Cannot drop the only partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                    EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                    DBMS_OUTPUT.PUT_LINE('Truncated partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                WHEN CANNOT_DROP_LAST_PARTITION THEN
                    BEGIN
                        DBMS_OUTPUT.PUT_LINE('Drop last partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aPart.PARTITION_NAME;
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';            
                    EXCEPTION
                        WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN 
                            -- Depending on the order the "last" partition can be also the "only" partition at the same time
                            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';
                            DBMS_OUTPUT.PUT_LINE('Cannot drop the only partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                            DBMS_OUTPUT.PUT_LINE('Truncated partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                    END;
            END;
        END IF;
    END LOOP;
END;

CodePudding user response：

In Oracle, there are two kinds of comments; have a look at the example:

SQL> declare
  2    l_cnt number;
  3  begin
  4    -- this is one-line comment
  5    select count(*) into l_cnt from dual;
  6    /* if you want, you can
  7       comment many lines. Note that
  8       those comments can't be "nested"
  9    */
 10    null;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>