SQL manipulation on dba_views column text is impossible because it’s LONG type

SELECT view_name, text 
FROM user_views 
WHERE text LIKE '%SEARCH%';
 
SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

So the column must be converted to CLOB. It can be done by using TO_LOB function which can be used in INSERT, CREATE TABLE as select, and UPDATE statements to convert.

You can’t use it for SELECT.

• LONG into a CLOB
• LONG RAW into a BLOB

CREATE TABLE convert_tbl
AS
SELECT view_name, TO_LOB(text) text
FROM user_views;

Now you can do your search

SELECT * 
FROM convert_tbl
WHERE text LIKE '%SOME SERCH%';

Another way is to create dedicated temporary table where you can store output for your INSERT

CREATE global temporary TABLE convert_glb
(view_name VARCHAR2(30),
 text clob);
 
INSERT INTO  convert_glb
SELECT view_name, TO_LOB(text) text
FROM user_views
 
SELECT * 
FROM convert_glb
WHERE high_value LIKE '%SOME SERCH%';

Another method is to use DBMS_XMLGEN package to transform first LONG into XML then XML into columns that can be used directly in sql. It’s very easy.

CREATE TABLE employees 
(
  employee_id NUMBER,
  employee_name VARCHAR2(100),
  department_id NUMBER,
  hire_date DATE
)
PARTITION BY LIST (department_id)
(
  PARTITION hr VALUES (10, 20),
  PARTITION it VALUES (30, 40),
  PARTITION sales VALUES (50, 60)
);

Now, let’s insert some sample data into the table:

INSERT INTO employees (employee_id, employee_name, department_id, hire_date) 
VALUES (1, 'John Doe', 10, TO_DATE('2020-01-01', 'YYYY-MM-DD')); 

INSERT INTO employees (employee_id, employee_name, department_id, hire_date) 
VALUES (2, 'Jane Smith', 20, TO_DATE('2019-05-15', 'YYYY-MM-DD')); 

INSERT INTO employees (employee_id, employee_name, department_id, hire_date) 
VALUES (3, 'Jim Brown', 30, TO_DATE('2021-06-30', 'YYYY-MM-DD')); 

INSERT INTO employees (employee_id, employee_name, department_id, hire_date) 
VALUES (4, 'Jessica White', 50, TO_DATE('2020-11-01', 'YYYY-MM-DD'));

Next, we want to query the partition data from the DBA_TAB_PARTITIONS view, generate the XML, and then use XMLTable to filter out specific partitions based on the HIGH_VALUE.

For example, let’s say we want to filter out partitions where the HIGH_VALUE is greater than a certain value (for instance, partitions with HIGH_VALUE above '40').

SELECT * 
FROM XMLTable
( 
  '/ROWSET/ROW' PASSING 
  (SELECT XMLType(DBMS_XMLGEN.getXML('SELECT table_owner, table_name, partition_name, high_value 
FROM dba_tab_partitions
 WHERE table_name = ''EMPLOYEES'' ')) FROM dual) 
COLUMNS 
  table_owner VARCHAR2(128) PATH 'TABLE_OWNER', 
  table_name VARCHAR2(128) PATH 'TABLE_NAME', 
  partition_name VARCHAR2(128) PATH 'PARTITION_NAME', 
  high_value CLOB PATH 'HIGH_VALUE' 
) 
WHERE TO_NUMBER(high_value) > 40; -- Filter HIGH_VALUE > 40

Hope it helps 🙂

Tomasz

I would like to show how to improve your queries by using deterministic functions. Deterministic functions has got “memory”. It remembers result returned by called function in its “memory”. So next time the function is called with the same parameters it returns immediately result from “memory” instead of running function.

“Memory” persists only for time of execution of a SQL. It’s not persistent on session or instance level.

Let’s create simple deterministic function

CREATE OR REPLACE FUNCTION test_det(id NUMBER)
RETURN NUMBER
deterministic
AS
BEGIN
  DBMS_LOCK.sleep(1);
  RETURN id;
END;
/

Let’s prepare test data

DROP TABLE test;
 
CREATE TABLE test(id NUMBER);
 
BEGIN
  FOR i IN 1..10
  LOOP
    FOR j IN 1..5
    LOOP
      INSERT INTO test VALUES(j);
    END LOOP;
  END LOOP;
  COMMIT;
END;
/

We have 5 groups with 10 records each

SELECT id, COUNT(*) FROM test
GROUP BY id
ORDER BY id;
 
ID COUNT(*)
-- --------
 1       10 
 2       10 
 3       10 
 4       10 
 5       10

Let’s run test for deterministic function

SET timing ON
SELECT id, test_det(id) FROM test WHERE id=1
 
ID TEST_DET(ID)
-- ------------
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 1            1 
 
 10 rows selected 
 
Elapsed: 00:00:01.011

Function was called 10 times but executed only once, so overall time is close to 1 sec.

SET timing ON
SELECT test_det(id), COUNT(*) 
FROM test
GROUP BY test_det(id);
 
TEST_DET(ID) COUNT(*)
------------ --------
           1       10 
           2       10 
           4       10 
           5       10 
           3       10 
 
Elapsed: 00:00:05.018

Deterministic function was called 50 times but executed only 5 times. For another 45 cases results was taken from function “memory”.

If we run this query again in our session we have to wait again 5 seconds because function “memory” is not preserved on session, instance level.

SET timing ON
SELECT test_det(id), COUNT(*) 
FROM test
GROUP BY test_det(id);
 
TEST_DET(ID) COUNT(*)
------------ --------
           1       10 
           2       10 
           4       10 
           5       10 
           3       10 
 
Elapsed: 00:00:05.010

I hope you like it 🙂

Tomasz

This article presents how to convert HIGH_VALUE to (sub)partition name. HIGH_VALUE is column which you can find in USER/ALL/DBA_TAB_PARTITIONS or USER/ALL/DBA_TAB_SUBPARTITIONS. Additional it shows nice extension keyword PARTITION FOR which can simplify your operations on (sub)partitions.

Test data

Let’s prepare some test data. One table is created TEST_TBL with simple composite partitioning with six partitions. Column HIGH_VALUE shows border between (sub)partitions within the table. All examples were executed in my own schema “TOMASZ”.

show user
USER is TOMASZ

create table test_tbl
(
 area     number,
 district varchar2(1),
 rec_num  number
)
   partition by range (area, district)
subpartition by range (rec_num)
subpartition template
( 
  subpartition s1 values less than(10),
  subpartition s2 values less than(20)
)
(
 partition "P1"  values less than (10, 'B'),
 partition "P2"  values less than (10, 'C'),
 partition "P3"  values less than (10, 'D'),
 partition "P4"  values less than (20, 'B'),
 partition "P5"  values less than (20, 'C'), 
 partition "P6"  values less than (20, 'D')
);

select partition_name, high_value
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

PARTITION_NAME  HIGH_VALUE
--------------- -------------
P1              10, 'B'
P2              10, 'C'
P3              10, 'D'
P4              20, 'B'
P5              20, 'C'
P6              20, 'D'

select partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name='TEST_TBL'
order by partition_name, subpartition_position;

PARTITION_NAME   SUBPARTITION_NAME    HIGH_VALUE   
---------------- -------------------- -------------
P1               P1_S1                10           
P1               P1_S2                20           
P2               P2_S1                10           
P2               P2_S2                20           
P3               P3_S1                10           
P3               P3_S2                20           
P4               P4_S1                10           
P4               P4_S2                20           
P5               P5_S1                10           
P5               P5_S2                20           
P6               P6_S1                10           
P6               P6_S2                20

Now it’s time to insert data into TEST_TBL. Each record is stored in separate subpartition and 2 records per partition.

insert into test_tbl values(10, 'A', 5);
insert into test_tbl values(10, 'B', 5);
insert into test_tbl values(10, 'C', 5);
insert into test_tbl values(10, 'A', 15);
insert into test_tbl values(10, 'B', 15);
insert into test_tbl values(10, 'C', 15);
insert into test_tbl values(20, 'A', 5);
insert into test_tbl values(20, 'B', 5);
insert into test_tbl values(20, 'C', 5);
insert into test_tbl values(20, 'A', 15);
insert into test_tbl values(20, 'B', 15);
insert into test_tbl values(20, 'C', 15);
commit;

select 'P1' part_name, a.*  from test_tbl partition (p1) a
union all
select 'P2', a.* from test_tbl partition (p2) a 
union all
select 'P3', a.* from test_tbl partition (p3) a
union all
select 'P4', a.* from test_tbl partition (p4) a
union all
select 'P5', a.* from test_tbl partition (p5) a
union all
select 'P6', a.* from test_tbl partition (p6) a;

PART_NAME       AREA DISTRICT    REC_NUM
--------- ---------- -------- ----------
P1                10 A                 5 
P1                10 A                15 
P2                10 B                 5 
P2                10 B                15 
P3                10 C                 5 
P3                10 C                15 
P4                20 A                 5 
P4                20 A                15 
P5                20 B                 5 
P5                20 B                15 
P6                20 C                 5 
P6                20 C                15

select 'P1_S1' part_name, a.*  from test_tbl subpartition (p1_s1) a
union all
select 'P1_S2', a.* from test_tbl subpartition (p1_s2) a 
union all
select 'P2_S1', a.* from test_tbl subpartition (p2_s1) a
union all
select 'P2_S2', a.* from test_tbl subpartition (p2_s2) a
union all
select 'P3_S1', a.* from test_tbl subpartition (p3_s1) a
union all
select 'P3_S2', a.* from test_tbl subpartition (p3_s2) a
union all
select 'P4_S1', a.* from test_tbl subpartition (p4_s1) a
union all
select 'P4_S2', a.* from test_tbl subpartition (p4_s2) a 
union all
select 'P5_S1', a.* from test_tbl subpartition (p5_s1) a
union all
select 'P5_S2', a.* from test_tbl subpartition (p5_s2) a
union all
select 'P6_S1', a.* from test_tbl subpartition (p6_s1) a
union all
select 'P6_S2', a.* from test_tbl subpartition (p6_s2) a;

PART_NAME       AREA DISTRICT    REC_NUM
--------- ---------- -------- ----------
P1_S1             10 A                 5 
P1_S2             10 A                15 
P2_S1             10 B                 5 
P2_S2             10 B                15 
P3_S1             10 C                 5 
P3_S2             10 C                15 
P4_S1             20 A                 5 
P4_S2             20 A                15 
P5_S1             20 B                 5 
P5_S2             20 B                15 
P6_S1             20 C                 5 
P6_S2             20 C                15

Function TBL$OR$IDX$PART$NUM

In Oracle you can find very interesting undocumented function TBL$OR$IDX$PART$NUM with following syntax.

TBL$OR$IDX$PART$NUM("PARTITIONED_TABLE_NAME", 0, d#, p#, "HIGH_VALUE")

The function returns (sub)partition position within partitioned table based on HIGH_VALUE parameter.

Examples

This example returns partition position within table TEST_TBL based on HIGH_VALUE defined manually – 5, ‘A’

select 
  TBL$OR$IDX$PART$NUM
   ("TOMASZ"."TEST_TBL", 0, 1, 0, 5, 'A') pos
from dual;

       POS
----------
         1

Following example returns partition position based on HIGH_VALUE created dynamically from two partition key columns AREA, DISTRICT. Each records will tell you to which partition it belongs.

SELECT a.*, 
  TBL$OR$IDX$PART$NUM("TOMASZ"."TEST_TBL", 0, 1, 0, 
                      a."AREA", a."DISTRICT") part
FROM tomasz.test_tbl a;

      AREA DISTRICT    REC_NUM       PART
---------- -------- ---------- ----------
        10 A                 5          1 
        10 A                15          1 
        10 B                 5          2 
        10 B                15          2 
        10 C                 5          3 
        10 C                15          3 
        20 A                 5          4 
        20 A                15          4 
        20 B                 5          5 
        20 B                15          5 
        20 C                 5          6 
        20 C                15          6

It’s very useful command to find a partitions name which you want to split.

For example new AREA can appear with value 15 and you want to create new partitions for it. So question is which partition should be split for HIGH_VALUE 15, ‘B’. With the function TBL$OR$IDX$PART$NUM it’s piece of cake.

SELECT table_name, partition_name, high_value 
FROM user_tab_partitions
where table_name='TEST_TBL'
  and partition_position=TBL$OR$IDX$PART$NUM
  ("TOMASZ"."TEST_TBL", 0, 1, 0, 15, 'B');

TABLE_NAME    PARTITION_NAME    HIGH_VALUE
------------- ----------------- -----------
TEST_TBL      P4                20, 'B'

Finding subpartition is more complicated. You need to first find data_object_id for subpartition segment

SELECT a.*, 
  TBL$OR$IDX$PART$NUM("TOMASZ"."TEST_TBL", 0, 3, 0, 
             a."AREA", a."DISTRICT", a."REC_NUM") data_object_id
FROM tomasz.test_tbl a;

      AREA DISTRICT    REC_NUM DATA_OBJECT_ID
---------- -------- ---------- --------------
        10 A                 5       76274464 
        10 A                15       76274465 
        10 B                 5       76274466 
        10 B                15       76274467 
        10 C                 5       76274468 
        10 C                15       76274469 
        20 A                 5       76274470 
        20 A                15       76274471 
        20 B                 5       76274472 
        20 B                15       76274473 
        20 C                 5       76274474 
        20 C                15       76274475

then you can join it with USER_OBJECTS to get subpartition_name

SELECT 
  a.*, 
  b.object_name, b.subobject_name, b.object_type
FROM test_tbl a,
     user_objects b
where
  TBL$OR$IDX$PART$NUM("TOMASZ"."TEST_TBL", 0, 3, 0, 
               a."AREA", a."DISTRICT", a."REC_NUM")=b.data_object_id
  and b.object_name='TEST_TBL';

AREA DISTRICT REC_NUM OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE       
---- -------- ------- ----------- -------------- ------------------
  20 C             15 TEST_TBL    P6_S2          TABLE SUBPARTITION
  20 C              5 TEST_TBL    P6_S1          TABLE SUBPARTITION
  20 B             15 TEST_TBL    P5_S2          TABLE SUBPARTITION
  20 B              5 TEST_TBL    P5_S1          TABLE SUBPARTITION
  20 A             15 TEST_TBL    P4_S2          TABLE SUBPARTITION
  20 A              5 TEST_TBL    P4_S1          TABLE SUBPARTITION
  10 C             15 TEST_TBL    P3_S2          TABLE SUBPARTITION
  10 C              5 TEST_TBL    P3_S1          TABLE SUBPARTITION
  10 B             15 TEST_TBL    P2_S2          TABLE SUBPARTITION
  10 B              5 TEST_TBL    P2_S1          TABLE SUBPARTITION
  10 A             15 TEST_TBL    P1_S2          TABLE SUBPARTITION
  10 A              5 TEST_TBL    P1_S1          TABLE SUBPARTITION

Keyword PARTITION FOR

Oracle 11g introduces very nice keyword “PARTITION FOR” which helps to manipulate partitions based on HIGH_VALUE. It’s silently converted into partition name by Oracle.

PARTITION FOR(HIGH_VALUE)

The syntax is supported in many operations on Oracle partitions.

Examples

SELECT from a partition based on HIGH_VALUE

select * from test_tbl PARTITION FOR(7, null);

      AREA DISTRICT    REC_NUM
---------- -------- ----------
        10 A                 1

SPLIT a partition based on HIGH_VALUE

alter table test_tbl
split PARTITION FOR(7, null) at(7, maxvalue)
into
(
  partition p0,
  partition p1
);
select partition_name, high_value 
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

PARTITION_NAME  HIGH_VALUE
--------------- ----------------
P0              7, MAXVALUE
P1              10, 'B'
P2              10, 'C'
P3              10, 'D'
P4              20, 'B'
P5              20, 'C'
P6              20, 'D'

DROP a partition based on HIGH_VALUE

alter table test_tbl drop PARTITION FOR (1, null);

select partition_name, high_value 
from user_tab_partitions
where table_name='TEST_TBL'
order by partition_position;

PARTITION_NAME  HIGH_VALUE 
--------------- ----------------
P1              10, 'B'
P2              10, 'C'
P3              10, 'D'
P4              20, 'B'
P5              20, 'C'
P6              20, 'D'

Have a fun 🙂

  Tomasz