망할고양이의 도피처

Query 작성 중 다 외우기는 힘들어 (외우긴 했으나 사용법이...) 검색해야 할 때가 있어 정리합니다.

LOWER( column|expression ) 

괄호 안의 텍스트를 소문자로 전환합니다.

LOWER('ABCDEFG') --> abcdefg : 

UPPER( column|expression ) 

괄호 안의 텍스트를 대문자로 전환합니다.

UPPER('aabbCC') --> AABBCC

INITCAP( column|expression ) 

문자열의 첫 글자를 대문자로 전환 후 이후 텍스트는 소문자로 전환합니다. 
INITCAP('hello') --> Hello

CONCAT( column1|expression1 ,column2|expression2 ) 

문자열을 연결(붙여)준다. || 와 같은 효과를 함수로 사용할 때 사용한다.
CONCAT('AA','BBccc')  --> AABBcc

SUBSTR(column|expression, m [,n])   

substring 이랑 비슷한 기능을 수행한다. 차이첨은  m 번째부터 n 번째까지 인덱스 값으로 n 값이 m에 상대적이라는 것이 차이점이다.

시작 인덱스가 음수인경우 뒤에서부터 인덱스를 계산한다.
SUBSTR('A1B2C3',1,3) --> A1B

LENGTH( column|expression ) 

문자열의 길이를 리턴한다. 

LENGTH('ABCDEF') --> 6 : 

INSTR( column|expression, str) 

indexof와 동일한 기능을 제공한다. 즉, 문자열에 해당하는 문자가 있는지 확인하여 해당 문자의 인덱스를 리턴한다.

INSTR('AARCCC','R') --> 3 : 문자열에 'R'의 인덱스(몇 번째 위치)를 리턴한다. 

LPAD( column|expression,n,str ) : n 은 전체 길이  , str 대체 문자

주어진 문자열에 대해서 n 에대한 자릿수만큼 str의 문자(or 숫자)를 왼쪽에 체워 넣습니다. 
LPAD('ABC',10,'0') --> 0000000ABC

RPAD( column|expression,n,str ) : n 은 전체 길이  , str 대체 문자

주어진 문자열에 대해서 n 에 대한 자릿수만큼 str의 문자(or 숫자)를 오른쪽에 채워 넣습니다. 

RPAD('ABC',10,'0') --> ABC0000000

LTRIM( column|expression, str) 

문자열의 왼쪽 공백(여백) 문자를 제거한다. 또한 LTRIM은 왼쪽 반복적인 문자(str)나, 특정 문자열을 제거할 수 있다 제거할 문자가 동일하다면 옵션의 문자 개수는 무관하다

LTRIM('      test_Text') --> 'test_Text' 

RTRIM(column|expression)

문자열의 오른쪽 공백(여백)문자를 제거한다. 또한  오른쪽의 반복적인 문자를 제거할 수 있다. 제거할 문자가 동일하다면 옵션의 문자 개수는 무관하다

LTRIM('test_Text          ') --> 'test_Text' 

TRIM(leading/tailing/both, trim_character FROM target_source ) 

기본적으로 TRIM 함수는 문자열의 공백(스페이스바)을 제거합니다. (양쪽 모두). 문자와 문자 사이의 공백은 제거하지 못합니다. 

TRIM('     NEW ITEM     ') --> 'NEW ITEM'

특정 문자를 제거하는 목적으로 사용할 경우에는 아래와 같이 사용할 수 있다.
TRIM( 'S' FROM 'SSMITH') --> MITH 

REPLACE(source_text, target_str, conv_str)

source text에서 target_str에 해당하는 문자(or문자열)를 conv_str로 대체합니다.

REPLACE('last King', 'last', 'NEW') --> NEW King

Oracle 함수는 크게 단일행 함수와 복수행 함수로 구분할 수 있다.

Oracle에는 많은 함수가 존재한다. 함수는 Query가 쉬워지고 응용프로그램의 코딩을 줄여줄 수 있다

따라서 함수는 많이 알고 있으면 좋다.

가끔 쓰이는 몇가지 함수를 알아보도록 한다.

NVL2 (Column, Express1, Express2)

해당 컬럼이 NULL이면 express-1의 값을, NULL이 아니면 express-2의 값을 리턴해 줍니다. 

NULLIF (Express1, Express2)
Express-1과 express-2의 값을 비교하여 그 값이 같으면 NULL을 리턴 하고 다르면 express-1의 값을 리턴해 줍니다.

COALESCE(Express1, , , , ExpressN)
Express-1이 NULL이 아니면 express-1을 리턴하고, NULL이면 express-2 를, express-1과 express-2 모두 NULL이면 express-n을 리턴해 줍니다.

TRIM ( LEADING [문자열] FROM col1 ) ;

LEADING은 왼쪽 문자열을 절삭 시켜 줍니다.

TRIM ( TRAILING [문자열] FROM col1 ) ;

 TRAILING은 오른쪽 문자열을 절삭 시켜 줍니다. 

TRIM ( BOTH [문자열] FROM col1 ) ;

BOTH는 양쪽 문자열을 동시에 절삭 시켜 줍니다.

SELECT 그룹함수([DISTINCT]/all]),
{column [Alias],...}
FROM 테이블명
[WHERE Query 조건(들)]
[GROUP BY 컬럼1, 컬럼2, …n]
[HAVING Group-Conditiona]
[ORDER BY 컬럼1, 컬럼2, …..[ASC/DESC]];

GROUP BY : 결과값을 지정한 컬럼을 기준으로 그룹화
HAVING : GROUP BY에 의한 결과에 대한 조건 절
ORDER BY : 결과값을 분류(Sorting)할 때

Oracle 설치 후 계정을 생성한 뒤 시스템을 사용하는데 갑자기 시스템 로그인이 안될 때가 있다

시스템 로그에 ORA-28001: the password has expired 가 있다면 쉽게 해결 할 수 있다.

아래 Query를 이용하여 프로필 정보를 확인하면 

아래와 같이 붉은 글자 항목이 180으로 설정되어있을 것이다. 

이 값을 UNLIMITED로 변경해주면 해결이 가능하다.

ORA-12631 : 이름 검색에 실패

TNS-12631 Username retrieval failed

어느 날 갑자기 혹은 PC를 교체하여 똑같은 환경을 구성했음에도... 위와 같은 오류가 발생하는 경우에는 아래와 같이 조치하면 해결할 수 있습니다. 

sqlnet.ora 파일을 찾아서 내용을 보면 아래와 같습니다. 

해당 파일의 내용중 

윗부분을 주석처리 하고 아래와 같이 변경합니다.

www.oracle.com 에 접속을 한뒤 아래 그림과 같이 메뉴를 따라가서 다운로드 페이지까지 이동합니다.

아래와 같은 페이지가 나오면 아래 붉은색 박스를 클릭하여 상세 화면으로 이동합니다. 

아래 라이센스 동의를 Accept를 선택하시고 붉은색 박스를 선택하면 로그인 과정을 거친후 다운로드 받을 수 있습니다. 

Tablespace 확장 요청이 발생할 때마다 찾기가 귀찮아서 포스트를 작성합니다. 

# 테이블 스페이스 용량별로 조회하기 (단위 : BYTE)

# DataFile 추가를 통한 Tablespace 확장

ORA-01847 : day of month must be between 1 and last day of month

왜 DB 관리툴로 돌리면 잘 수행 되는 쿼리가 서버에서 실행되면 오류가 발생할까?..

혹은

이와 반대의 경우를 경험할 때가 있다.

일반적으로 이 오류는 예를 들어 2018-12-32이라는 문자열을 날짜로 변환할 때 발생한다.

즉, 달력에 없는 날짜를 문자열을 이용하여 Date 타입으로 변환할 때 발생한다는 이야기이다. 

별도의 타입에 대한 변환이 명시되지 않으면 알아서(?) 타입을 변환해주는 오토케스팅이 낳은 좋지 않은 케이스라 할 수 있다. 

의외로 잘 깜박하는 예시를 들어보록 하자.

컬럼 Date_A 가 있다. 이 컬럼은 Date Type이며 Null 일 수 있다.

이 컬럼을 이용하여 아래와 같이 작성하면 ORA-01847을 만날 수 있다. 

위의 쿼리는 다음과 같이 작성되는것이 오류를 예방하고 혼란에 빠지지 않을 수 있다.