I am trying to run below query in my PL/SQL block

SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'BRD_JOB_DETAILS_TMP' order by high_value;

I am getting the error message : ORA-00997: illegal use of LONG datatype

I need to process table partitions in order of high_valueto get rid the below issue :

ORA-14074: partition bound must collate higher than that of the last partition

CodePudding user response：

Use the numeric column partition_position instead for your order.

SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'BRD_JOB_DETAILS_TMP' order by partition_position;

CodePudding user response：

In case of list partitioning partition number may not be aligned with actual value order. But as long asLONG columns has a lot of restrictions in SQL, the common way to deal with them is to use dbms_xmlgen.getxml[type] to serialize long value into text and then retrieve it as varchar2.

Below is an example:

create table t (
  id date
)
partition by list(id) (
  partition p20200101 values (date '2020-01-01'),
  partition p20200601 values (date '2020-06-01'),
  partition p20200301 values (date '2020-03-01')
)

with
/*In case of date column high_value contains an expression,
not the value itself.
So evaluation is required.*/

function f_evaluate_val (p_val varchar2)
  return date
/*Evaluates high_value as SQL expression*/
as
  l_res date;
begin
  execute immediate
    'select ' || p_val || ' from dual'
    into l_res;
  return l_res;
end;

a as (
  /*wrap with xmltype as long as LONG columns
  are not accessible for functions in SQL*/
  select dbms_xmlgen.getxmltype(q'{
    select
      table_name,
      partition_name,
      high_value
    from user_tab_partitions
    where table_name = 'T'
  }') as x
  from dual
)

select
  t.table_name,
  t.partition_name,
  f_evaluate_val(t.high_value) as hv
from a
  cross join xmltable(
    '//ROWSET/ROW'
    passing a.x
    columns
      table_name varchar2(30),
      partition_name varchar2(30),
      high_value varchar2(4000)
  ) t
order by hv desc

TABLE_NAME | PARTITION_NAME | HV       
:--------- | :------------- | :--------
T          | P20200601      | 01-JUN-20
T          | P20200301      | 01-MAR-20
T          | P20200101      | 01-JAN-20

db<>fiddle here