ORA-00932: inconsistent datatypes: expected CHAR got LONG

OS환경 : Oracle Linux 7.4 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

에러 : ORA-00932: inconsistent datatypes: expected CHAR got LONG

long type의 컬럼에 substr을 사용해 컬럼을 자르려고 할때 발생하는 에러

SQL>
set lines 200 pages 1000
col table_name for a15
col partition_name for a15
col high_value for a80
select table_name, partition_name, substr(high_value, 2, 6)
from dba_tab_partitions
where table_name = 'PTABLE';
 
select table_name, partition_name, substr(high_value, 2, 6)
                                          *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

 

 

high_value 컬럼 type 확인

SQL> desc dba_tab_partitions
 Name                              ?    Type
 -------------------------------------- -------------------------
 TABLE_OWNER                            VARCHAR2(128)
 TABLE_NAME                             VARCHAR2(128)
 COMPOSITE                              VARCHAR2(3)
 PARTITION_NAME                         VARCHAR2(128)
 SUBPARTITION_COUNT                     NUMBER
 HIGH_VALUE                             LONG
 HIGH_VALUE_LENGTH                      NUMBER
 PARTITION_POSITION                     NUMBER
 TABLESPACE_NAME                        VARCHAR2(30)
 PCT_FREE                               NUMBER
 PCT_USED                               NUMBER
 INI_TRANS                              NUMBER
 MAX_TRANS                              NUMBER
 INITIAL_EXTENT                         NUMBER
 NEXT_EXTENT                            NUMBER
 MIN_EXTENT                             NUMBER
 MAX_EXTENT                             NUMBER
 MAX_SIZE                               NUMBER
 PCT_INCREASE                           NUMBER
 FREELISTS                              NUMBER
 FREELIST_GROUPS                        NUMBER
 LOGGING                                VARCHAR2(7)
 COMPRESSION                            VARCHAR2(8)
 COMPRESS_FOR                           VARCHAR2(30)
 NUM_ROWS                               NUMBER
 BLOCKS                                 NUMBER
 EMPTY_BLOCKS                           NUMBER
 AVG_SPACE                              NUMBER
 CHAIN_CNT                              NUMBER
 AVG_ROW_LEN                            NUMBER
 SAMPLE_SIZE                            NUMBER
 LAST_ANALYZED                          DATE
 BUFFER_POOL                            VARCHAR2(7)
 FLASH_CACHE                            VARCHAR2(7)
 CELL_FLASH_CACHE                       VARCHAR2(7)
 GLOBAL_STATS                           VARCHAR2(3)
 USER_STATS                             VARCHAR2(3)
 IS_NESTED                              VARCHAR2(3)
 PARENT_TABLE_PARTITION                 VARCHAR2(128)
 INTERVAL                               VARCHAR2(3)
 SEGMENT_CREATED                        VARCHAR2(4)
 INDEXING                               VARCHAR2(4)
 READ_ONLY                              VARCHAR2(4)
 INMEMORY                               VARCHAR2(8)
 INMEMORY_PRIORITY                      VARCHAR2(8)
 INMEMORY_DISTRIBUTE                    VARCHAR2(15)
 INMEMORY_COMPRESSION                   VARCHAR2(17)
 INMEMORY_DUPLICATE                     VARCHAR2(13)
 CELLMEMORY                             VARCHAR2(24)
 INMEMORY_SERVICE                       VARCHAR2(12)
 INMEMORY_SERVICE_NAME                  VARCHAR2(1000)
 MEMOPTIMIZE_READ                       VARCHAR2(8)
 MEMOPTIMIZE_WRITE                      VARCHAR2(8)

long type임

 

 

해결 방법 : to_lob를 이용해 새 테이블 생성 후 조회 또는 plsql 이용

방법1. ctas 이용

아래와 같이 high_value 컬럼에 to_lob를 사용해 임시 테이블을 만들어서 substr을 이용할 수있음

임시 테이블 생성

SQL>
create table temp_high as 
select table_name, partition_name, high_value, to_lob(high_value) as hv_clob
from dba_tab_partitions
where table_name = 'PTABLE';

to_lob 함수를 사용하면 해당 컬럼이 clob 타입으로 저장됨

 

 

컬럼 타입 조회

SQL> desc temp_high
 Name                    Null?    Type
 ----------------------- -------- ---------------
 TABLE_NAME                       VARCHAR2(128)
 PARTITION_NAME                   VARCHAR2(128)
 HV_CLOB                          CLOB

clob 타입임

 

 

substr을 이용해 조회

SQL>
select table_name, partition_name, substr(hv_clob, 2, 6) as high_value
from temp_high;
 
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- --------------------------------------------------------------------------------
PTABLE          P202101         202102
PTABLE          P202102         202103
PTABLE          P202103         202104
PTABLE          P202104         202105
PTABLE          P202105         202106
PTABLE          P202106         202107
PTABLE          P202107         202108
PTABLE          P202108         202109
PTABLE          P202109         202110
PTABLE          P202110         202111
PTABLE          P202111         202112
PTABLE          P202112         202113
PTABLE          P_MAX           AXVALU
 
13 rows selected.

정상적으로 잘라서 볼수 있음

 

 

방법2. plsql 이용

원하는 컬럼을 plsql 소스에 각각 넣어서 수행하면 substr로 잘라서 볼수 있음

방법1보다는 번거롭지만 새로운 테이블을 만들지 않아도 되는 방식임

SQL>
set serveroutput on
declare
  cursor c_tab_partitions is
    select table_name, partition_name, high_value
    from dba_tab_partitions
    where table_name = 'PTABLE';
  lv_table_name varchar2(30);
  lv_partition_name varchar2(30);
  lv_high_value varchar2(10);
begin
  for rec in c_tab_partitions loop
    lv_table_name := rec.table_name;
    lv_partition_name := rec.partition_name;
    lv_high_value := substr(rec.high_value, 2, 6);
    -- 출력
    dbms_output.put_line(lv_table_name || ' ' || lv_partition_name || ' ' || lv_high_value);
  end loop;
end;
/
 
PTABLE P202101 202102
PTABLE P202102 202103
PTABLE P202103 202104
PTABLE P202104 202105
PTABLE P202105 202106
PTABLE P202106 202107
PTABLE P202107 202108
PTABLE P202108 202109
PTABLE P202109 202110
PTABLE P202110 202111
PTABLE P202111 202112
PTABLE P202112 202113
PTABLE P_MAX AXVALU
 
PL/SQL procedure successfully completed.

정상적으로 잘라서 볼수 있음

 

원인 : 일반적으로 LONG 타입은 가공하는것이 허용되지 않음

일반적으로 LONG 타입은 가공하는것이 허용되지 않음

요즘에는 long 타입 대신 lob 타입을 사용하는것을 권고함

 

 

참조 : https://positivemh.tistory.com/969

오라클 19c 파티션 테이블 high_value 컬럼 잘라서 보는 방법