곰곰히 생각해보면

데이터 누락건이 있어 누락된 데이터를 엑셀에 다운로드한 후 데이터를 입력하는 계기가 있었다.
이 때 날짜 데이터를 DB에 넣어야하는데... 자꾸 21/1/1이 44197과 같은 숫자로 표기되었다.

사실 입력 단계에서부터 시행착오는 있었는데...
INSERT INTO 후 데이터를 입력하려하면 '이 수식에 문제가 있습니다.'가 자꾸 뜨는것!

알아보니 텍스트와 실제Cell값을 함께 쓰기 위해서는 &구분자와 '" "'의 활용이 필요했다.

예시를 들자면, 아래와 같은 날짜가 있고 실제 날짜를 입력하기 위해서는

아래와 같이 입력하면 된다.

여기서 44197의 숫자가 문제였는데, TEXT(Cell위치,변환형식) 함수를 이용해서 해결이 가능했다.
나의 경우 여러개의 값을 입력하는 문제로 자체적으로 실제 문장에 쓸때 수식문제가 자주 발생하여,
별도의 셀에 TEXT변환을 입력 후 해당 값을 가져오는 형태로 했으나,
값이 별로 없는 경우 그냥 사용하면 될듯하다.

예시는 아래와 같다.

1. 실제 날짜

2. 변환 날짜

3. 응용

[Error 구문] SQL 오류: ORA-00984: 열을 사용할 수 없습니다
00984. 00000 -  "column not allowed here"

1. 상황 : 파라미터 개수에 맞춰 insert 도중 발생
2. 원인 : varchar2(문자열) 컬럼에 문자열을 입력하지 않음
ex) NVL(UPPER('rosebay'),admin)
=> NVL(UPPER('rosebay'),'admin') or (UPPER('rosebay'),'') 로 변경시 정상 동작
1) insert 문에서 문자열에 작은따옴표(')  누락(문자열 인식)
- 작은따옴표 대신에 큰따옴표를 사용할 경우
2) 오타(함수 이름 등)

--컬럼명 사용하는 테이블 찾기
SELECT * FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = '컬럼명'
-- DB에서 사용하는 Table, Procedure, Function 등에서 원하는 단어 위치 찾기
SELECT * FROM SYS.ALL_SOURCE
WHERE TYPE='PACKAGE'
AND TEXT LIKE '%소스에서 검색할 단어%';
-- DB 레퍼런스 확인
SELECT * FROM USER_DEPENDENCIES WHERE REFERENCED_NAME = 'P_IF_PCOFF';
SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = 'TB111';
-- 세션 lock, kill
select a.sid, a.serial# from v$session a, v$lock b, dba_objects c 
where a.sid = b.sid 
and b.id1=c.object_id 
and b.type='TM' 
and c.object_name='오브젝트네임';

alter system kill session '337, 59059';

--1. 오라클 데이타베이스명을 확인하는 방법
SELECT NAME, DB_UNIQUE_NAME FROM v$database;
--2. 오라클 SID를 확인하는 방법
SELECT instance FROM v$thread;

중복 체크 ----------------------------------------------------------------------
SELECT T1.컬럼명
           , COUNT(*)
  FROM 테이블명 T1
 GROUP BY T1.KIKB_DONG_NM
 HAVING COUNT(*) > 1 ;

1. 연도별 일자 구하기
SELECT TO_CHAR(TO_DATE(:yyyy||'1231', 'yyyymmdd'), 'ddd') dayChk FROM dual;
ex) 2018-365 / 2019--365 / 2020--366 / 2021--365

2. 시간 더하기
※ 기본형은 sysdate이지만 편의상 보기위해 to_char를 이용하여 표시함
1) 3시간 더하기 hour
select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') aa, 
        to_char(sysdate+ (interval '3' hour),'YYYY-MM-DD hh24:mi:ss') bb from dual;
- 결과 : 기본 2020-11-17 10:14:40=> 변경 2020-11-17 13:14:40
2) 3분 더하기 minute
select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') aa,
         to_char(sysdate+ (interval '3' minute),'YYYY-MM-DD hh24:mi:ss') bb from dual;
- 결과 : 기본 2020-11-17 10:16:35=> 변경 2020-11-17 10:19:35
3) 3초 더하기 second
select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') aa, 
         to_char(sysdate+ (interval '3' second),'YYYY-MM-DD hh24:mi:ss') bb from dual;

오랜만에 VIEW 생성 및 권한 처리가 필요하여 작업하며 기록에 남긴다.

1. 뷰 생성하기(테이블 및 컬럼은 예시 기재)
CREATE OR REPLACE FORCE VIEW "계정명"."V_뷰이름" 
("성명", "사번", "직위", "년차", "생년월일", "양력구분", "재직상태", "최종진급일", "전직급승진일") 
AS 
SELECT  NAME                        AS 성명
      , SABUN                          AS 사번
      , JIKWEE_CD                     AS 직위
      , F_COM_GET_JIKWEE_YEUNCHA(SABUN, TO_CHAR(SYSDATE, 'YYYYMMDD')) AS 년차
      , BIR_YMD                        AS 생년월일
      , DECODE(LUN_TYPE,1,'양',2,'음') AS 양력구분
      , STATUS_CD                    AS 재직상태 
      , CURR_JIKGUB_YMD          AS 최종진급일
      , NVL(ORD_YMD,CURR_JIKGUB_YMD) AS 전직급승진일
FROM (
                       SELECT A.NAME       AS NAME             -- 성명
                               , A.RES_NO     AS RES_NO           -- 주민번호
                               , A.BIR_YMD    AS BIR_YMD          -- 생년월일
                               , A.EMP_YMD    AS EMP_YMD       -- 입사일
                               , A.GEMP_YMD   AS GEMP_YMD    -- 그룹입사일
                               , A.LUN_TYPE   AS LUN_TYPE         -- 양음구분(1;양, 2:음)
                               , C.*
                              , F_COM_GET_CURR_JIKWEE_YMD(A.ENTER_CD,A.SABUN,TO_CHAR(SYSDATE,'YYYYMMDD'))
                                AS CURR_JIKGUB_YMD
                       FROM 인사마스터 A
                               , 인사기본 C
                       WHERE A.ENTER_CD = C.ENTER_CD
                          AND A.SABUN    = C.SABUN
                          AND A.RET_YMD IS NULL
                          AND C.STATUS_CD NOT IN('RA')
                          AND TO_CHAR(SYSDATE, 'yyyymmdd') BETWEEN C.SDATE AND NVL(C.EDATE, '99991231')
              )  
              WHERE SABUN NOT IN ('testinsa') -- 테스트계정                           
                 order by STATUS_CD, SABUN, NAME;

2. 권한주기 : 타 계정                            
grant select on ROSEBAY.V_INSA_INFO to INSA_INF;
                     계정명.뷰이름             to 권한 부여할 계정명
! 유저 계정 생성하기
create user testUser identified ROSEBAY
※  USER를 생성하기 위해서는 USER생성 권한이 있는 사용자로 접속해야 한다.

3. 타 계정(INSA_INF)에서 조회하기
select * from ROSEBAY.V_INSA_INFO;

4. 동의어(synonym) 부여하기
create synonym V_INSA_INF for ROSEBAY.V_INSA_INFO;
         동의어로 사용할 이름 for 원래 이름
select * from V_INSA_INF ; 
( = select * from ROSEBAY.V_INSA_INFO; )

참고자료
유저생성 : http://www.gurubee.net/lecture/1005

위와 같은 값이 출력된다고 할 때 110 CODE의 RESULT_MON컬럼과 RMON처럼 연산이 필요할 때라면?
아우터 조인을 활용해 보자.

SELECT
  A.SEQ, A.SABUN, A.CODE, A.RESULT_MON,
  DECODE(A.CODE,'110',(A.RESULT_MON-B.RESULT_MON), A.RESULT_MON) RMON
FROM TB_PAY A
LEFT JOIN TB_PAY B 
ON A.ENTER_CD = B.ENTER_CD --PK KEY1
AND A.SEQ= B.SEQ
AND A.SABUN = B.SABUN
AND B.CODE= '113'  
WHERE A.SEQ = '201907001'     
AND A.SABUN = 'rosebay'
AND A.RESULT_MON IS NOT NULL    
AND A.CODE IN ('110','113','230')
ORDER BY A.CODE

기본적이고 자주 쓰는 명령어들이지만 가끔 문법적으로 생각나지 않을 때 보기 위해 기록.

INSERT INTO 테이블 추가
INSERT INTO 테이블명 (AA,BB,CC) --컬럼명1,컬럼명2,컬럼명3
           VALUES(aa,bb,cc);

ALTER 컬럼 추가
ALTER TABLE 테이블 이름 ADD (컬럼명 타입);
ALTER 컬럼 구조 변경
ALTER TABLE 테이블 이름 MODIFY (컬럼명 타입);

CONSTRAINT 제약조건의 생성

CONSTRAINT 제약조건의 재생성
1) PK 키를 삭제 후 다시 생성 ! 스키마(접속시)명 생략 가능
- 삭제 : ALTER TABLE 스키마명.테이블명
   DROP CONSTRAINT SYS_C00626892; --기존PK KEY 명칭
- 추가1) : 시스템 랜덤이름으로 추가
ALTER TABLE 스키마명.테이블명
-- ADD CONSTRAINT PK명 PRIMARY KEY
   ADD ( PRIMARY KEY
  (ENTER_CD, YYYY, HALF_CD, BRANCH_CD)); --컬럼명1,컬럼명2,컬럼명3,컬럼명4
- 추가2) : 본인이 정한 PK별칭으로 추가
ALTER TABLE 테이블명
ADD CONSTRAINT 별칭_PK PRIMARY KEY (HOUSE_CODE, CONT_SEQ, CONT_COUNT) ;
2) NOT NULL 컬럼 추가
- ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

회사에서 연말정산 패치로 패치파일을 계속 넘겨주는데,
SQL문에 툴에서 '&'가 자꾸 걸린다.

이 때 사용하는 명령어를 남겨본다.

SET DEFINE OFF

SQL Developer 등에서 오류가 날 때(&으로 변수를 찾을 때 이용해 보자.)

참고
구루비 : http://www.gurubee.net/lecture/1148

DB에서 제일 어려운 게 계층형 구조이다. 몇 년이 지나도 아직도 넘나 어려운 것...

오늘도 위기의 오류는 ORA-01436: CONNECT BY의 루프가 발생되었습니다.

이 문제는 오류문구에 답이 있는데 루프를 도는데 문제가 발생했다는 것으로
1) 데이터엔 문제가 없는데 쿼리에 문제가 있다.
: Connect by 절에 조인 조건 누락
2) 쿼리엔 문제가 없는데 데이터에 문제가 있다. 
: 순환되는 자료 오류

2)의 케이스인 경우 간단한 해결 방법으로는 Connect By 뒤에 NOCYCLE을 추가하는 것이다.
말 뜻 그대로 루프 사이클을 돌리지 않겠다는 건데...

제대로 고치려면 1) 쿼리 자체의 수정 혹은 2)Data의 수정이 필요하다.

특히나, 오류가 발생되는 지점을 확인하기 위해서는 CONNECT_BY_ISCYCLE을
조회하는 컬럼에 추가하여 오류 지점이면 1, 아니면 0으로 구분할 수 있으며,
WHERE CONNECT_BY_ISCYCLE = 1을 추가하여 찾아 고쳐보도록 한다.

Ex)
Select LEVEL AS _Lev
        ,Col1
        ,Col2
        ,Col3
        ,Col4
        ,CONNECT_BY_ISCYCLE
FROM (
          Select A.ColSub1
                  ,A.ColSub2
                  ,A.ColSub3
                  ,A.ColSub4
           From Ttest1 A, Ttest2 B
           Where A.ColSub1 = B.ColSub1
            ...
           Group By ColSub1, ColSub2, ColSub3, ColSub4
           ) A
Start With A.Col1 IS NULL
Connet By NOCYCLE PRIOR A.Col2 = A.Col3 --Join 조건

필자의 경우 A.Col2 = A.Col3 데이터가 동일하여 계층 구조를 만드는데 오류가 발생되었음

주저리주저리... 부모를 의미하는 Col3와 Col2 데이터가 동일 = 자기자신을 가르킨 형태..
운영 데이터랑 테스트 데이터가 일치하지 않는데다가 오류 메시지도 데이터 세팅 모두 끝낸 상태에서 쿼리를 조회하니 그때서야 오류가 나와서 하아...

참고
구루비 : http://www.gurubee.net/article/60617 오류 케이스 찾는 방법 설명

계층쿼리 실행 순서
Start With => Connect By => Where

반복적으로 숫자 증가시켜 입력(INSERT)하기
: 동일한 내용을 특정 숫자만 추가해서 입력하여야 할 경우 사용한다.

예시) 1~20까지 숫자 증가시켜 입력시키기
DECLARE
  P_SQLCODE VARCHAR2(1000);
  P_SQLERRM VARCHAR2(1000);  
  i BINARY_INTEGER := 0;
BEGIN
-- for문 loop
  FOR i in 1..20 LOOP
  BEGIN  
    INSERT INTO TB_TEST001 
    values
    ('ABC','19G02',i,'P14',1,sysdate,'rosebay')
    ;  
  EXCEPTION
  WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( 'cnt='||i );
        P_SQLERRM := 'cnt='||i||'의 TB_TEST001 UPDATE시 => ' || SQLERRM;
        P_COM_SET_LOG_NOCOMMIT('ABC','PAP','AAA','10','cnt='||i ||SQLERRM , 'ADMIN');
        RETURN;
  END;
  END Loop;
END;