How to feed PARTITION clause dynamically in ORACLE?

Question

I need to know if it is possible to feed the PARTITION clause with the latest partition name of a table, instead of using his value.

Normaly we have:

SELECT * 
  FROM table PARTITION(partition_name);

I need to substitute partition_name value for the subquery that returns that value, as below;

SELECT *
  FROM table1 PARTITION (SELECT partition_name
                           FROM (SELECT partition_position,
                                        partition_name,
                                        last_analyzed,
                                        MAX(partition_position) 
                                            OVER (PARTITION BY table_name) AS max_p
                                   FROM sys.all_tab_partitions
                                  WHERE table_name = 'TABLE1')
                          WHERE partition_position = max_p);

My goal is to pass the Partition name dynamically, or using a subquery - like I showed abobe - or using another solution to achieve this goal.

Best regards

Answer 1

You can use an explicit cursor to fetch rows from a SELECT statement which's constructed through a concatenated string along with the name of the partition that's determined at the beginning of the code block such as

SET SERVEROUTPUT ON

DECLARE
  v_cursor    SYS_REFCURSOR;
  v_table1    table1%ROWTYPE;
  v_partition VARCHAR2(99);
BEGIN
  SELECT partition_name
    INTO v_partition
    FROM (SELECT p.*,
                 ROW_NUMBER() OVER (ORDER BY p.partition_position DESC) AS rn
            FROM user_tab_partitions p
           WHERE table_name = 'TABLE1') 
   WHERE rn = 1;
   
  OPEN v_cursor FOR 'SELECT * FROM table1 partition('||v_partition||')';  
  LOOP
     FETCH v_cursor INTO v_table1;
   
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_table1.col1||'  '||v_table1.col2);
  END LOOP;
  CLOSE v_cursor;
END;
/