ORA-00997: illegal use of LONG datatype

DB: Oracle Database 19c 

 

 

---

 

메일을 발송하는 별도 스키마의 테이블에서 DB@LINK 를 사용하여 데이터를 가져와야했다.

이 때 LONG 타입 컬럼을 가공하여 사용해야했는데...

CREATE OR REPLACE FUNCTION GET_MAIL_APP_NO (P_CONTENT LONG) RETURN VARCHAR2 IS
	v_content VARCHAR2(4000);
	v_app_no  VARCHAR2(4000);
BEGIN
	v_content := P_CONTENT;
	v_app_no  := REGEXP_SUBSTR(v_content, '\d{9}');

	RETURN v_app_no;
    
EXCEPTION
	WHEN OTHERS THEN
		RETURN NULL; 
END;

 

처음에는 이런식으로 함수를 만들어서 해결하려고 했다.

SELECT
	GET_MAIL_APP_NO(A.CONTENT) AS APP_NO
FROM 테이블@db_link A

 

그러나...

[Error] Execution (25: 19): ORA-00997: LONG 데이터 유형은 사용할 수 없습니다

오류가 발생

 

그래서 두 번째 방법

JAVA단으로 가져온 후 문자열을 가공할까? 했는데

SELECT
	A.CONTENT AS RAW_CONTENT
FROM 테이블@db_link A

 

org.springframework.jdbc.UncategorizedSQLException: Error attempting to get column 'CONTENT' from result set. Cause: java.sql.SQLException: ORA-17004: 열 유형이 부적합합니다.: getCLOB not implemented for class oracle.jdbc.driver.T4CLongAccessor https://docs.oracle.com/error-help/db/ora-17004/ ;

 

JDBC는 LONG 데이터를 지원하지 않아 바로 오류를 뱉어낸다.

 

세 번째 방법

TO_LOB 메소드를 사용하여 형식을 바꾼다.

SELECT
	TO_LOB(A.CONTENT)
FROM 테이블@db_link A

오류 발생

ORA-00932: 일관성 없는 데이터 유형: -이(가) 필요하지만 LONG임

(어쨌든 LONG 타입은 못쓰는거...)

 

 

---

해결

 

매우 우회하는 방법으로 해결했다... 대용량 데이터는 이 방법이 비효율적일 것이다...

 

전역 임시테이블 생성

CREATE GLOBAL TEMPORARY TABLE 임시테이블
(
  MAILIDX  NUMBER(10),
  CONTENT  CLOB
)
ON COMMIT DELETE ROWS
NOCACHE;

 

전역 임시테이블이란...

세션(session) 또는 트랜잭션(transaction) 동안만 데이터를 저장하는 특수한 테이블이다.

데이터는 임시 테이블 스페이스에 저장됨

 

마지막 줄은

ON COMMIT DELETE ROWS -- 트랜잭션이 commit 시 테이블 데이터가 삭제

NOCACHE -- CLOB 데이터를 캐싱하지 않도록 설정

을 뜻한다

 

이제 프로시져에서 처리해주면 됨

-- 커밋을 때려주고... (데이터 초기화를 위함)
COMMIT; 

BEGIN
	-- MAILIDX 키 만큼 LOOP를 돌린다
    FOR i IN (SELECT MAILIDX, CONTENT
            FROM 테이블@db_link
              ) LOOP 
        INSERT INTO 임시테이블 (MAILIDX, CONTENT) 
        VALUES (i.MAILIDX, TO_CLOB(i.CONTENT)); 
        -- 데이터 차곡차곡 삽입
    END LOOP;
END;

 

이후 조회한다

SELECT
	-- 가공을 해야했기에 더 까다로웠다
	(SELECT NVL(REGEXP_SUBSTR(DBMS_LOB.SUBSTR(T.CONTENT), '\d{9}'), '-') FROM 임시테이블 T WHERE T.MAILIDX = A.MAILIDX) AS APP_NO
FROM 테이블@db_link A

 

완료