Skip to Main Content
  • Questions
  • Is “With clause” not allowed within a cursor?

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Keh Wen.

Asked: January 09, 2020 - 2:19 am UTC

Answered by: Chris Saxon - Last updated: January 10, 2020 - 3:44 am UTC

Category: PL/SQL - Version: 12.1.0.2

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Loading LOB from a file

You Asked

Hi Tom,

We need to drop old partitions in regular basis for some tables partitioned by interval.

To keep 60 days data for the TEST table partitioned by day interval, for instance, I could get partitions name older than two months by using the following SQL and drop them later:


with max_partition as ( select table_owner, table_name, max(partition_position) max_p from dba_tab_partitions where table_name = 'TEST' and table_owner=‘TEST' group by table_owner, table_name )
select t1.table_owner, t1.table_name, t1.partition_name 
       from dba_tab_partitions t1,
            max_partition t2
       where t1.table_name = 'TEST' 
       and t1.table_owner =‘TEST'
       and t1.table_name = t2.table_name
       and t1.table_owner = t2.table_owner
       and t1.partition_position <= (t2.max_p - 60);
   

--- output:
 
TEST    TEST     SYS_P59342
TEST    TEST     SYS_P59403
TEST    TEST     SYS_P59543
TEST    TEST     SYS_P59684
TEST    TEST     SYS_P59784
TEST    TEST     SYS_P59883
TEST    TEST     SYS_P60002
 
 
In order to perform the same task for several tables, I created a table with number of partition that should be keep for each table partitioned by interval.

 
CREATE TABLE TB_PART_RETENTION (
    OWN_NAME VARCHAR2(30),
    TAB_NAME VARCHAR2(30),
    NUM_RETAIN_PARTITION NUMBER);

 
 
Insert into tb_part_retention values (‘TEST’,’TEST’,60);

When I try to compile the following procedure, using the similar "with clause" statement, it fails. The error message says: "SQL Statement ignored" on the line of "With clause" statement.

Is “With clause” not allowed within a cursor?

 
CREATE OR REPLACE 
PROCEDURE proc_drop_partition (p_schema IN VARCHAR2, p_table IN VARCHAR2, 
p_num_retention IN NUMBER) AS

CURSOR c_expired_partition IS
WITH max_partition as 
( SELECT table_owner, table_name, max(partition_position) max_p FROM dba_tab_partitions WHERE table_owner=p_schema AND table_name = p_table GROUP BY table_owner, table_name)
SELECT t1.table_owner, t1.table_name, t1.partition_name
       FROM dba_tab_partitions t1,
            max_partition t2          
       WHERE t1.table_name = p_table AND t1.table_owner=p_schema
       AND t1.table_name = t2.table_name
       AND t1.table_owner = t2.table_owner
       AND t1.partition_position <= (t2.max_p - p_num_retention);                          
 
BEGIN
     FOR i_c_expired_partition in c_expired_partition
     LOOP
         exec_command ('ALTER TABLE'||c_expired_partition.table_owner||'.'||c_expired_partition.table_name||' DROP PARTITION '|| c_expired_partition.partition_name || ' UPDATE INDEXES');
     END LOOP;

END proc_drop_partition;

   
show erros
--
 
Procedure PROC_DROP_PARTITION compilado
 
Errors: check compiler log
Erros para PROCEDURE PROC_DROP_PARTITION:
LINE/COL ERROR
-------- --------------------------------------------------------------------------------------------------
3/5      PL/SQL: SQL Statement ignored
11/9     PL/SQL: ORA-00904: "T1"."PARTITION_NAME": identificador inválido
16/5     PL/SQL: Statement ignored
16/55    PLS-00225: a referência ao subprograma ou ao cursor 'C_EXPIRED_PARTITION' está fora da abrangência
 

Thanks in advance

and we said...

You can use with inside an explicit cursor declaration:

declare
  cursor cur is 
    with rws as (
      select level x from dual
      connect by level <= 10
    )
      select * from rws;
      
begin

  for c in cur loop
    dbms_output.put_line ( c.x );
  end loop;

end;
/

1
2
3
4
5
6
7
8
9
10


The problem is elsewhere in the code. Look carefully at how you're referencing the cursor record inside the loop.

and you rated our response

  (4 ratings)

Reviews

Missing space?

January 09, 2020 - 12:35 pm UTC

Reviewer: Hoek

Shouldn't

exec_command ('ALTER TABLE'||c_expired_partition.table_owner||'.'||c_expired_partition.table_name||'


be

exec_command ('ALTER TABLE '||c_expired_partition.table_owner||'.'||c_expired_partition.table_name||'

Chris Saxon

Followup  

January 09, 2020 - 1:08 pm UTC

Good spot. But that's not why the program isn't compiling!

January 09, 2020 - 1:42 pm UTC

Reviewer: A reader from The Netherlands

    exec_command('ALTER TABLE' || c_expired_partition.table_owner || '.' ||

                 c_expired_partition.table_name || ' DROP PARTITION ' ||
                 c_expired_partition.partition_name || ' UPDATE INDEXES');


is the problem. It is referencing the cursor not the cursor row. This should be

    exec_command('ALTER TABLE' ||i_ c_expired_partition.table_owner || '.' ||
                 i_c_expired_partition.table_name || ' DROP PARTITION ' ||
                 i_c_expired_partition.partition_name || ' UPDATE INDEXES');

Chris Saxon

Followup  

January 09, 2020 - 4:44 pm UTC

Indeed!

January 09, 2020 - 9:03 pm UTC

Reviewer: A reader

Thank Chris for the clarification! By looking carefully the code I found some misspelling and mistakes. After fixing them the procedure is working fine now.

Many thanks!
Connor McDonald

Followup  

January 10, 2020 - 3:39 am UTC

Glad we could help

Works in most situations

January 09, 2020 - 10:19 pm UTC

Reviewer: Gabriel from San Antonio, TX USA

I'm in a similar situation where I need to remove expired data from an interval partitioned table that also happens to be partitioned by day.

I was hoping to make short work of things by adapting the solution posted here but this approach doesn't work if there are gaps in the data. For example, in my data set, a holiday might result in 0 records for a whole day. Meaning the DB will never allocate a partition for that day. Then the PARTITION_POSITIONs will no longer belong to consecutive partitions. Then the
t2.max_p - p_num_retention

no longer returns what we need. On top of that we also receive partial records that are projections of what is expected in the future. Sometimes the date field we key on are 1 day into the future, sometimes months. So that also throws off our PARTITION_POSITIONs. Very poor selection of a partition key, I know, but it's too late to change it now.

Anyway, we're still running 12.1 at the moment so the Transition Point has to be shuffled around every now and then since it can't be dropped.

I though it might be important to point that out for other poor souls like me stuck with erratic dates and older versions of Oracle. This looks like a great solution for everyone else though. Thanks for sharing.
Connor McDonald

Followup  

January 10, 2020 - 3:44 am UTC

For me, I would rarely use partition position for anything like this, because I want to see what real *data* I am planning on dropping. And I can get that from the partition definition, not the position number.

I'll typically probe the HIGH_VALUE information and use that to be 100% confident of the values that this partition can contain.

Because its a LONG, its a little bit of tinkering, but here's some more details on how to take care of that.


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.