엑셀로 데이터를 밀어넣다 보면 데이터가 간혹 잘못들어가는 경우가 있다. 의도치 않은 tab 문자열이라거나 공백문자열이라거나.

한두건이면 그냥 update query를 실행하면 되지만 여러건일 경우에는 답이 없다.


1
2
3
UPDATE
    table_a
SET column_name = REPLACE( column_name , ' ''')
cs


table_a 라는 테이블에서 column_name 컬럼의 데이터가 공백 문자열이 있을 경우, set 구문에 replace로 해당 컬럼을 치환한 후 update 해주면 된다.

아주 간단하고 심플함.

블로그 이미지

김생선

세상의 모든것을 어장관리

[Oracle 11g Enterprise] / [Mybatis 3.2.7] 기준


varchar2 데이터타입은 최대 사이즈를 4,000byte 까지 줄 수 있다. 그런데 4,000byte 이상의 데이터를 저장할 일이 있다. base64로 인코딩된 이미지라거나, xml 파싱된 영수증내용이라거나 아주 긴 블로그의 글이라거나. 그럴 때 oracle 에서 사용하는 데이터타입은 clob 이다.


CLOB은 XML 형태로 데이터를 지정하게 되는데, DB 툴에서는 select * from table로 간단하게 데이터 확인이 가능하지만, mybatis를 사용하게 되면 일반적인 select 쿼리로는 활용이 불가능하다. 그럴 때 다음과 같이 mybatis의 select 구절에 resultmap을 설정하면 된다.


1
2
3
4
5
6
7
8
9
10
11
12
<!-- resultMap 지정 -->
<resultMap id="QID_CLOB" type="hashMap" >
    <result property="ETC" column="etc" jdbcType="CLOB" javaType="java.lang.String"/>
</resultMap>
 
<!-- 조회쿼리 -->
<select id="QID_SELECT_INFO" parameterType="hashMap" resultMap="QID_CLOB" >
SELECT
    etc
FROM
    table
</select>
cs


사용법은 심플하다.

select 쿼리의 resultMap 파라미터는 별도로 선언할 resultMap의 ID를 지정해준다.

별도로 선언할 resultMap에서는 id를 맞춘 후, clob으로 가져올 컬럼명(column)을 지정하고, jdbcType을 clob으로 지정, 이를 어떠한 column name으로 내보낼 것인지 지정(property)해주면 된다.



1
2
3
4
5
6
SELECT
    a.etc AS a_etc
    b.etc AS b_etc
FROM
    table_1 a
    LEFT OUTER JOIN table_2 b ON a.no = b.no
cs

그런데 여기서 한가지 궁금한 점이 생기게 된다. 

각기 다른 테이블을 조인 후 가져올 때, 각 테이블의 같은 이름 clob은 어떻게 가져올까 하는 점이다.

테스트를 잠시 해보았는데 생각보다 매우 간단했다.


처음에는 같은 컬럼명이니까 위의 resultMap을 그냥 가져다 쓰면 ETC 컬럼을 ETC Property로 출력해주겠거니, 했지만 전혀 그렇지 않았다.(왜인지 Java의 Service 처럼 동작할거라 생각;)

그래서 두번째 방법을 사용해보았다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!-- resultMap 지정 -->
<resultMap id="QID_CLOB" type="hashMap" >
    <result property="A_ETC" column="a_etc" jdbcType="CLOB" javaType="java.lang.String"/>
    <result property="B_ETC" column="b_etc" jdbcType="CLOB" javaType="java.lang.String"/>
</resultMap>
 
<!-- 조회쿼리 -->
<select id="QID_SELECT_INFO" parameterType="hashMap" resultMap="QID_CLOB" >
SELECT
    a.etc AS a_etc
    b.etc AS b_etc
FROM
    table_1 a
    LEFT OUTER JOIN table_2 b ON a.no = b.no
</select>
cs


그랬더니 각기 다른 테이블에서 동일한 네이밍의 clob 컬럼을 정상적으로 가져올 수 있었다.

간단한 테스트 결과, resultMap의 동작구조는 SELECT가 우선적으로 실행된 결과를 가져온 뒤, 이를 resultMap에서 처리하는 구조로 여겨진다. 결과적으로 resultMap은 Alias로 잡힌 컬럼명을 인식한다는 말이다.

블로그 이미지

김생선

세상의 모든것을 어장관리

[Oracle 11g Enterprise] 기준


개발을 하다보면 산출물을 작성해야하고, 이 산출물에는 테이블 생성날짜 등도 필요한 경우가 있다. 몰아서 테이블들을 생성해두고 나중에 작성하려다보면 뭐 임의의 날짜로 작성해도 무방하나 혹여나 하는 1g의 불안감 때문에 확인하는 경우가 있다.


1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    owner
    , object_name
    , object_type
    , created
    , timestamp
FROM 
    all_objects
WHERE
    owner = 'USER_NAME'
    AND object_name ='TABLE_NAME'
 
cs


뭐 대충 이정도. 검색을 하다보면 all_objects 테이블이 아니라 user_objects라느니 뭐라느니 하는데 아마 버전마다 다른게 아닌가 싶을 정도.

여튼, 위의 컬럼은 다음과 같은 구성을 가지고 있다.


 owner

개체 소유계정. sys 계정이라면 해당 DB 내 모든 계정을 조회 가능하다.

 object_name

(간단히 말해) 개체명

개체명이라고 표현하는 이유는 테이블만이 보이는 것이 아니기 때문임.

자세한 부분은 object_type 참조

object_type

개체 타입. table인지 index인지 sequence 인지를 표현함. 

created 

생성날짜 

timestamp 

최근 access 날짜.

생성한 이후에 데이터를 insert 혹은 delete 할 때 마다 해당 날짜가 갱신된다.


뭐 대충 이정도. owner 에는 SYS 혹은 EXFSYS, SYSTEM 등 수많은 시스템용 owner가 있기에, 가장 기본적인 필터를 owner로 지정해두고 select 하는 것이 정신건강에 이로울 듯.

블로그 이미지

김생선

세상의 모든것을 어장관리

[Oracle 11g Enterprise 기준]


테이블의 데이터를 다른 테이블로 그대로 옮겨야 할 때가 있다.

Excel Export / Inport를 통한 방법이나 기타 여러 방법들이 있긴 한데 아무래도 귀찮은 방법이기도 하고. 그래서 Select Insert Query를 주로 사용한다.


1
2
INSERT INTO A_TABLE a
SELECT * FROM B_TABLE b WHERE b.no = '1'
cs


가장 기본적으로 위와 같은 구성으로 시작할 수 있다. A_TABLE에 B_TABLE의 데이터 중, no가 1인 항목만을 넣는 쿼리이다.


만약, 특정 컬럼에 대해서만 select insert를 한다면? 다음과 같다.


1
2
3
4
5
6
7
8
INSERT INTO A_TABLE a
    ( no , title , content , insert_date , insert_user )
SELECT 
    no , title , content , insert_date , insert_user
FROM
    B_TABLE
WHERE
    no = '1'
cs


위와 별개로, 다른 user에 할당된 테이블에 넣기 위해서는? 

회사에서는 하나의 DB에 user 단위로 운용/개발 DB가 분리되어있다. 당연하게도 dba 권한으로 아래의 쿼리를 이용해주면 된다.


1
2
3
4
5
6
7
8
INSERT INTO user_1.A_TABLE a
    ( no , title , content , insert_date , insert_user )
SELECT 
    no , title , content , insert_date , insert_user
FROM
    user_2.B_TABLE
WHERE
    no = '1'
cs


user_1은 user_2에 접근할 수 없기에 dba 권한으로 둘 다 접속이 가능한 상태에서 select / insert를 수행해주면 된다.


블로그 이미지

김생선

세상의 모든것을 어장관리

지난글, 2013/06/26 - [어장 프로그래밍/어장 DBA] - [MSSQL] MAX 함수 NULL 일 때 치환 에서는 MSSQL을 기준으로 글을 작성하였다.

현재 개발중인 플젝의 DB는 informix로 되어있고, 위와 같은 방식으로 max null 값을 구하려 했더니 informix 에서는 isnull 함수가 사용불가능하더라.


검색 해 보니 NVL 이란 함수가 있었고, 아래와 같은 방식으로 사용하였다.



SELECT NVL(MAX(testColumn), '0')+1 tempName FROM testTableName


testColumn의 MAX 값을 구하고, 이 값이 null 일 경우에는 0으로 치환한 후, 그 값에 +1을 하여 tempName 이라는 임시 컬럼명으로 Select 한다는 내용이다.(내가 써놓고 뭔가 어정쩡한 설명이란 생각이 든다.)


아무튼, 테스트를 해 보니 testColumn 값이 null 이 아니더라도 정상적으로 쿼리가 작동하기 때문에 만족하고 사용중. 뭐 이렇게 하면 된다.


블로그 이미지

김생선

세상의 모든것을 어장관리

where 조건을 줄 때, form 에서 영문자를 받을 일이 흔하다.

그리고 이 영문자로 where 조건을 주게 되는데, like 검색을 할 때에는 대소문자를 구분한다.


가령 컬럼에 KimFish 라고 값이 입력이 되어있고, form 에 입력된 조건이 kimfish 일 경우엔 조회가 안된다는 말.

따라서 like 쿼리에 대소문자 구분 없이 값을 입력해주는 function을 지정해줘야 하는데, 이것이 바로 upper function 되시겠다.


SELECT 

*

FROM

   테이블명 

WHERE 

upper(조회할 컬럼명) LIKE '%'||upper('입력받은 조건')||'%'


뭐 이런식.


내가 알기로는 대부분의 디비툴에서 사용 가능한 것으로 알고 있다.


** 추가 **

검색해본 적용 가능한 DBA

Oracle , MySql , MsSql, Informix

블로그 이미지

김생선

세상의 모든것을 어장관리

java.lang.OutOfMemoryError: Java heap space


2013:08:02 14:19:00



프로그램에서 서버 쿼리를 실행중에 위와 같은 에러가 발생하였다면 자신의 톰캣 서버 메모리를 의심해보아야 한다.


그리고 문제 해결 방법은 아래와 같다.


1. 이클립스 서버탭


이클립스 서버탭의 해당 톰캣 서버를 더블클릭한다.


2013:08:02 14:22:00

(이미지 설명 : 이 글에서는 Started 된 서버를 더블클릭 한다)



2. Server Overview


2013:08:02 14:24:39

(이미지 설명 : 톰캣 서버를 더블클릭하면 보이는 서버 오버뷰)



여기에서 파랑색으로 삐뚤삐둘 밑줄 쳐진, Open launch configuration 링크를 클릭한다.



3. Arguments


2013:08:02 14:28:45

(이미지 설명 : 사실 톰캣 서버는 여기에서 선택 가능하다)


선택한 서버가 실행중이라면 저러한 붉은 표시로 Server already running 이란 메시지를 띄우게 된다. 

해당 톰캣 서버를 멈추고, 밑줄쳐진 Arguments로 이동하자.



4. 설정 추가


2013:08:02 14:31:18


붉은 밑줄 부분이 아마도 추가가 안되어있을텐데, 현 시스템 상황에 고려하여 메모리 항목을 추가해주면 된다.

필자는 아래와 같은 형식으로 추가를 하였다.


 -XX:MaxPermSize=128m -Xms128m -Xms512m




그리고 적용시키면 방금 전 에러는 빠이염!

여러분, 즐프하십셔~



블로그 이미지

김생선

세상의 모든것을 어장관리

equal to 작업에서의 "Korean_Wansung_CS_AS"과(와) "Korean_Wansung_CI_AS" 간의 데이터 정렬 충돌을 해결할 수 없습니다.


이 문제의 경우는 join에서 발생한다. 조인하려는 두 테이블의 각 컬럼 속성을 잘 보면 데이터 정렬란이 있는데 이 부분이 Korean_Wansung_CS_AS 이거나 CI_AS인데(물론 이 이외에도 많다) 각 컬럼이 다르기 때문에 발생하는 에러사항이다.


해결방법은 


A.컬럼명 collate Korean_Wansung_CI_AS = B.컬럼명 collate Korean_Wansung_CI_AS


와 같은 예시로 각 컬럼명을 대입해주는 방식으로 해결할 수 있으나, 나의 경우엔 실패했고...


귀찮아서 디비의 속성을 까보고 각 컬럼의 데이터 정렬 속성이 다르기에 확 그냥 바꿔주는 식으로 해결했다.


두번째 방법은 디비 정렬 속성을 바꾸는 것도 있겠다.

블로그 이미지

김생선

세상의 모든것을 어장관리

인포믹스(informix) 에서 현재날짜를 조건으로하여 쿼리할 일이 생겼다. 상황은 이러하다.


DB 컬럼에 글 쓴 날짜(컬럼명 : reg_dt | 속성 varchar)와 게시마감 날짜(컬럼명 : lim_dt | 속성 varchar)가 존재. 

가령 reg_dt에 2013년 6월 30일 00시 00분이 입력되고 lim_dt에 2014년 6월 30일 00시 00분이 입력되었다고 치자.

게시판 조건은 lim_dt가 현재날짜보다 과거일 경우 쿼리하지 않아야 한다는 조건이다.


그렇다면 사실 reg_dt는 신경쓰지 않아도 되며 lim_dt가 현재보다 과거인지만 체크를 하면 되는데 어떻게 해야하는지 informix는 잘 모르는 상황.

검색을 해보니 아래와 같은 방식으로 쿼리에 성공했다.


SELECT * FROM 테이블명 WHERE lim_dt >= to_char(current, "%Y%m%d%H%M%S") 


to_char(current, "%Y%m%d%H%M%S")란, 현재 시스템 시간을 포맷, YYYYMMDDHHMMSS(년월일시분초)로 가져오며, 이를 to_char 형식으로 변환하고 lim_dt와 비교를 한다는 것이다. 


내가 비교하려는 컬럼이 varchar 형식이기 때문에 to_char로 받은 듯 하다. 아직 data 형식의 컬럼과는 비교하지 않았다.


주의할 점은, 포맷변환을 할 때 %Y%m%d%H%M%S 에서와 같이 년/시/분/초 는 대문자로 쓰여지고 월/일은 소문자로 쓰여졌는데, 이거 대소문자 구분 안하면 월이 분으로 표시가 되는 불상사가 생기니 주의하시기 바란다.




블로그 이미지

김생선

세상의 모든것을 어장관리

MSSQL 에서 MAX 함수를 써서 값을 가져올 일이 생겼다.

게시판 댓글을 달 때 두 명 이상의 유저가 달라붙어서 동시다발적으로 댓글을 달 경우에 댓글의 MAX 값을 가져오고 이를 SELECT KEY로 이용하여 본래의 쿼리에 INSERT 하는 용도로 사용한다.

그런데 댓글이 아무것도 없을 때에는 MAX 값으로 가져온 댓글 번호가 0이 아닌 NULL로 가져오게 되는데, 이렇게 되면 쿼리 에러가 나타나게 된다.


이럴 때 MAX 값이 0이거나 NULL 일 때 1 로 치환해주는 것을 찾았다.

max ( reply_no ) reply_no


본래에서 이렇게 사용하였다면

isnull ( max ( reply_no ) , 0 )

와 같이 사용해주면 null이 0으로 변환이 되며

isnull ( nullif ( max ( reply_no ) , 0 ) , 1 )


와 같이 사용해주면 null이 1로 변환이 된다.

블로그 이미지

김생선

세상의 모든것을 어장관리

티스토리 툴바