Oracle 데이터베이스 컬럼 조회 시 발생하는 ORA-00904 에러 해결하기

1. 문제 상황

기존 웹 사이트에는 9개의 서버와 DB가 연결되어 있고 해당 GW를 1개만 작업한 경우에 발생한 문제이다. 프로젝트에서 Oracle 데이터베이스의 테이블 컬럼을 조회하는 과정에서 ORA-00904: "TYPE": invalid identifier 에러가 발생했습니다. 이 에러는 주로 존재하지 않는 컬럼을 참조하거나, 컬럼의 권한 문제로 인해 발생합니다.

 

 

1.1 에러 로그

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "TYPE": invalid identifier
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
    ...

 

1.2 기존 코드의 문제점

private boolean checkIfTypeColumnExists(String dbName) {
    String checkSql = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TBL_TABLE' AND COLUMN_NAME = 'TYPE'";
    // ... 나머지 코드
}

1. 권한 제한 문제
   • USER_TAB_COLUMNS는 현재 로그인한 사용자의 테이블만 조회 가능
   • 다른 스키마의 테이블 정보를 조회할 수 없음
   • 제한된 권한을 가진 사용자는 조회 자체가 불가능
2. 스키마 관리 이슈
   • 특정 스키마를 지정하지 않아 잘못된 테이블을 조회할 위험
   • 동일한 테이블명이 여러 스키마에 존재할 경우 문제 발생
3. 예외 처리 미흡
   • 데이터베이스 조회 실패 시 적절한 에러 처리 부재
   • 운영 환경에서 문제 추적이 어려움

 

2. 해결 방안

2.1 개선된 코드

private boolean checkIfTypeColumnExists(String dbName) {
    String checkSql = "SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = ? AND OWNER = ? AND COLUMN_NAME = ?";
    int count = 0;

    try {
        JdbcTemplate currentTemplate = getJdbcTemplate(dbName);
        count = currentTemplate.queryForObject(
            checkSql, 
            Integer.class, 
            "TBL_TABLE", 
            dbName.toUpperCase(), 
            "TYPE"
        );
    } catch (DataAccessException e) {
        log.error("컬럼 존재 여부 확인 중 오류 발생: {}", e.getMessage());
        return false;
    }

    return count > 0;
}

private JdbcTemplate getJdbcTemplate(String dbName) {
    switch(dbSelect(dbName)) {
        case "3": return jdbcMultiTemplate;
        case "2": return jdbcSubTemplate;
        default: return jdbcTemplate;
    }
}

 

2.2 주요 개선 사항

1. 테이블 조회 뷰 변경
   • USER_TAB_COLUMNS → ALL_TAB_COLUMNS 변경
   • 접근 가능한 모든 테이블 정보 조회 가능
   • 권한에 따른 조회 제한 해결
2. 매개변수화된 쿼리
   • SQL Injection 방지
   • 재사용성 향상
   • 유지보수 용이성 증가
3. 예외 처리 강화
   • 데이터베이스 조회 실패 시 적절한 로깅
   • 운영 환경에서 문제 추적 용이
   • 안정적인 서비스 운영 가능
4. 데이터베이스 연결 관리 개선
   • 데이터베이스 선택 로직 분리
   • 코드 중복 제거
   • 확장성 향상

 

3. 적용 결과

3.1 성능 개선

• 불필요한 테이블 스캔 감소
• 정확한 스키마 지정으로 조회 속도 향상

3.2 안정성 향상

• 예외 상황에 대한 명확한 처리
• 운영 환경에서의 문제 추적 용이
• 데이터 정합성 보장

3.3 유지보수성 개선

• 코드 가독성 향상
• 재사용 가능한 컴포넌트 분리
• 향후 기능 확장 용이

 

4. 결론

이번 개선을 통해 데이터베이스 조회 관련 에러를 해결하고, 코드의 품질을 전반적으로 향상시켰습니다. 특히 다음과 같은 이점을 얻을 수 있었습니다:

1. 안정적인 데이터베이스 조회
2. 향상된 예외 처리
3. 더 나은 코드 구조
4. 용이한 유지보수

이러한 개선사항들은 향후 비슷한 문제가 발생했을 때 참고할 수 있는 좋은 사례가 될 것입니다.

 

5. 참고 자료

• Oracle Documentation: ALL_TAB_COLUMNS
• Spring Framework Documentation: JdbcTemplate