전진

ora-01007 variable not in select list

프로시져나 함수를 이용할때 발생하는 에러.

TYPE Gv_DATA_LIST IS TABLE OF TABLE_NAME %ROWTYPE;

이런식으로 테이블을 타입으로 지정해줄때 발생한다.

타입으로 지정한 테이블의 컬럼명이나 컬럼의 타입이 프로시져내에서 실행하는 쿼리와 동일 하다면 괜찮지만..

간혹가다 전혀 다른 테이블의 데이터를 가져와서 넣어야 하는 경우가 생기는데..

이럴때 타입으로 지정한 테이블의 타입으로 컬럼명과 컬럼의 타입을 맞춰주면 에러는 발생하지 않는다.

컬럼의 갯수와 순서까지 다 맞춰주는것이 좋다.

값이 없는 경우 빈값으로 채워주면 된다.

서브 쿼리로 결과 값을 테이블로 만들어 놓고 그 테이블의 데이터로 다시 쿼리를 해야 하는데(예를 들어 Case When등을 쓰는경우) 서브쿼리의 결과 값이 없을 경우 제대로 된 값을 뽑아내지 못할때가 있다.

그럴땐 결과 값이 있는것처럼 만들어야 하는 경우가 발생하는데..

이럴땐 Max나 Min을 쓰도록 하자.

select 
case when (a.ncd + a.lld < sysdate) then 'true' 
else 'false' end as result
from
(
  select 
    nvl(change_day, 90) as ncd
    ,(case 
    when login_date is null then to_date('20160401','yyyymmdd')
    when login_date > to_date('20160401','yyyymmdd') then login_date
    END) as lld
  from (
    select min(login_date) as login_date, min(change_day) as change_day
    from FT_PWD_CHANGE
    where site_id='aaaa'
    AND login_id='bbbb'
  )
) a

서브쿼리 컬럼 값에 min을 쓴것을 볼수 있을것이다.

min을 안썼을 경우에는 결과값이 아예 나오지 않지만 min을 쓰면 결과값이 정상적으로 출력 된다.

오라클을 새로운 서버에 설치를 하고 SQLDeveloper에서 이전과 달라진건 아이피 밖에 없겠지 하고 아이피를 바꾼뒤 테스트 버튼을 눌렀더니 나오는 메세지..

문제는 하나다 SID와 서비스이름을 헷갈려서 생기는 에러..?

SID를 사용하는지 서비스이름을 사용하는지 정확하게 확인하고 정확하게 선택한뒤 사용하도록 하자.

언제 였는지 기억은 나지 않지만 오라클 스키마를 생성하고 권한 부여 하는걸 포스팅 한적이 있다.

이번은 스키마 생성과 함께 테이블 스페이스를 지정하는 법..

데이터를 저장 할 테이블 스페이스를 생성하는 SQL문..

인덱스를 저장 할 테이블 스페이스를 생성하는 SQL문..

스키마를 생성하고 테이블 스페이스를 지정..

생성한 스키마에 인덱스도 지정..

만들어진 스키마에 권한을 지정..

소문자로 쓰여진 부분만 본인에 맞게 고쳐서 사용하면 된다.

INDEX에 쓰여질 테이블 스페이스도 생성을 했는데 그게 아니라면 그부분은 생략해도 된다.

오라클에 설정된 디렉터리 목록을 보는 쿼리.

위의 쿼리를 실행 하면 현재 오라클의 DBA에 설정된 디렉터리들의 목록을 확인 할 수 있다.

예를 들어 DATA_PUMP_DIR, DATA_FILE_DIR, LOG_FILE_DIR등의 디렉터리 정보를 볼 수 있다.

이걸 하는 이유는 데이터를 임포트 한뒤 어디에 임포트가 되는지 확인하기 위함이고..

다시 이 데이터를 다른 데이터베이스에 임포트 시킬때 파일의 위치를 확인하기 위함이다.

참조할 항목은 DATA_PUMP_DIR..

오라클 설치 후 모든 설정을 끝낸 뒤에 SQL Developer로 접속을 해봤더니 다음과 같은 상태 메세지가 출력 되었다.

The Network Adapter could not establish the connection(TNS-17002)

뭔가 네트워크 상에 문제가 발생되어 안된다는 메세지인것은 분명함..

그래서 저 내용으로 검색을 해보니 최상단에 나타나는 검색 결과는 오라클에서 사용중인 포트가 방화벽에서 열려있는가를 확인해 보라는 답변들이 다수였다. 하지만 문제 해결을 위해서 iptables에 오라클에서 사용중인 포트를 등록해 놓은 상태였다.

혹시나 telnet명령어로 확인해 보니..

Trying xxx.xxx.xx.xxx...

telnet: connect to address xxx.xxx.xx.xxx: Connection refused

telnet: Unable to connect to remote host

위와 같은 결과를 출력했다. 이는 포트가 열려 있지 않은 상태.

지금은 프로젝트 진행이 더 중요한 상태라 일단은 방화벽을 사용하지 않는 명령어로 방화벽을 꺼놓은 상태..

[root@oracle ~]# service ipchains stop

ipchains: 인식되지 않은 서비스

[root@oracle ~]# service iptables stop

iptables: 방화벽 규칙을 지웁니다:                          [  OK  ]

iptables: 체인을 ACCEPT 규칙으로 설정 중:  filter          [  OK  ]

iptables: 모듈을 언로드하는 중:                            [  OK  ]

[root@oracle ~]# chkconfig ipchains off

ipchains 서비스의 정보를 읽는 도중 오류가 발생했습니다: 그런 파일이나 디렉터리가 없습니다

[root@oracle ~]# chkconfig iptables off

[root@oracle ~]# reboot

명령어 실행과 실행 결과를 모두 적어놨는데..

ipchains가 실행되지 않는 부분은 신경쓰지 않아도 된다.

방화벽 설정을 off시키고 리눅스를 재부팅 하는 과정.

다시 telnet으로 포트확인을 해보니 아래와 같이 결과가 나오면서 정상적으로 접속 되는것을 볼수 있다.

Connected to xxx.xxx.xx.xxx

Escape character is '^]'.

일단은 임시방편이지만 iptables에 포트를 추가해도 방화벽때문에 포트가 막힌 문제에 대해서 알아봐야 하고..

알아본 다음 방화벽을 다시 설정해 정상적으로 돌아가도록 해야한다.

이거때문에 반나절도 더 시간낭비를..

좀전에 다시 안 사실을 정리해보면..방화벽 설정을 그동안 잘못해준것 같다.

iptables에 직접 포트를 열어주는 부분을 적어 넣고 적용을 해줬었는데..

적용 하는 순서를 잘못해준건지 아니면 포트를 여는 부분 외에 추가로 더 해줘야 할 부분이 있었던건지..

iptables INPUT -p tcp -m tcp --dport 1521 -j ACCEPT

iptables OUTPUT -p tcp -m tcp --dport 1521 -j ACCEPT

이 두 명령어로 해결 되었다.

기존에는 iptables수정하면서 한줄만 추가를 해줬는데 이건 INPUT과 OUTPUT을 모두 명령어로 추가 해주는 방법 같은데..

아무튼 나중에 다시 같은 일이 반복 되면 확인하고 시도해 볼 방법은 많을 것 같다.

ORA-01031: insufficient privileges

패스워드 인증 방식으로 접속이 불가능한 계정으로 SQL Developer같은 클라이언트 프로그램으로 접속 할 경우 이와 같은 에러가 발생한다.

SELECT * FROM v$pwfile_users;

패스워드 인증 방식으로 접속 가능한 계정이 어떤것이 있는지 확인 하는 쿼리.

USERNAME       SYSDB SYSOP SYSAS

------------------------------ ----- ----- -----

SYS       TRUE  TRUE  FALSE

현재 관리가 계정인 SYS만이 패스워드 인증방식으로 접속 가능하것을 확인 할 수 있다.

관리자 계정으로 접속 한뒤 클라이언트 프로그램에서 접속할 계정의 권한을 바꿔주면 된다.

GRANT sysdba TO 권한변경ID;

USERNAME       SYSDB SYSOP SYSAS

------------------------------ ----- ----- -----

SYS                   TRUE  TRUE  FALSE

권한변경ID        TRUE  FALSE FALSE

권한 변경 후 다시 목록을 확인하면 추가 됨을 확인 할 수 있다.

이후 SQL Developer로 확인 하면 제대로 접속됨을 확인 할 수 있다.

Oracle의 컬럼 추가와 수정..

컬럼 추가 : 

alter table t_show_content add(add_column_name varchar2(20));

컬럼명 수정 : 

alter table table_name rename column before_column to after_column;

아주 기본적인 명령.

현재 하고 있는 프로젝트를 빠르게 진행하다 보니 시작은 MySQL로 시작했다가 중간에는 Oracle로 바꿨다.

JDBC드라이버와 URL등을 다 바꾸고 쿼리 부분도 오라클에 맞게 수정을 해준 다음 해보면 에러가 발생 하였다.

Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected

위와 같은 에러.

에러 내용을 보면 알겠지만 FROM절 뒤의 키워드가 잘못됐다? 못찾겠다?

나는 분명 쿼리에 문제가 없고 그 쿼리를 SQL Developer에 넣어 돌려보면 정상적인 값이 나온다.

분명 쿼리에는 문제가 없다.

모든 방법을 다 동원해서 해봤지만 나오는 결과는 똑같았다.

검색을 해보면 쿼리가 잘못 됐다거나 오라클 방화벽 문제거나 리스너가 구동 되지 않은 상태에서 하면 저런 결과가 나온다는 말뿐.

그러다 검색된 결과가..

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
            p:driverClassName="${jdbc.driverClassName}" p:url="${jdbc.url}" p:username="${jdbc.username}"
            p:password="${jdbc.password}"
            p:validationQuery="SELECT 1 FROM DUAL"
            p:testOnBorrow="true"
            p:testWhileIdle="true"
            p:timeBetweenEvictionRunsMillis="6000"
            p:removeAbandoned="true"
            p:removeAbandonedTimeout="60"/>

database-context.xml의 내용이다.

p:validationQuery="SELECT 1 FROM DUAL" 이게 문제였다.

Oracle이외의 DataBase는 SELECT 1을 쓴다고 한다.

Oracle로 변경을 하면서 저것을 바꿔주지 않으니 났던 에러였다.

SELECT 1이었던 값을 SELECT 1 FROM DUAL로 바꿔주니 아주 잘 된다.

이것 때문에 대체 얼마나 많은 시간을 소비한건지..-_-;;