본문 바로가기

oracle30

[오라클] Oracle의 rollup 사용시 최종 총계(Total)만 보고 싶다면? 총계, 소계를 보고 싶을 때 rollup 명령어를 자주 사용하죠.rollup에 컬럼을 하나면 넣으면 총계만 나오지만, 컬럼이 여러개일 경우에는 소계와 총계가 섞여서 나옵니다. 위 그림처럼 Total이 여러개 나오는데중간중간에 보이는 Total이 소계이고,맨 마지막 Total이 모든 데이터의 값을 더한 총계가 됩니다. 때에 따라서는 소계는 필요없고 총계만 필요할 때가 있는데 어떻게 하면 될까요? 방법은 아주 간단합니다.Group by ROLLUP(COLUMN1, COLUMN2)=>Group by ROLLUP((COLUMN1, COLUMN2)) ROLLUP( ... ) => ROLLUP(( ... ))이렇게 괄호를 한번 더 감싸주면 총계만 볼 수 있답니다^^ 2017. 11. 20.
오라클(Oracle) CLOB 데이터형식 저장(Insert/Update) 프로시저 샘플 CLOB은 저장하는 방식이 달라서 insert/update 할 때 좀 쿼리가 복잡합니다. 기본적으로...insert할 때, Clob인 컬럼은 빈값(empty_clob)을 넣은 후에 다시 업데이트하는 형식으로 쿼리를 작성해야 하기 때문에 번거롭죠. 그래서, 저는 Clob이 들어간 테이블에 insert/update 할 때는 Insert/Update 프로시저를 만들어서 사용합니다.아래는 Insert 프로시저 예제입니다. 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980CREATE OR REP.. 2017. 10. 17.
[Orange] 오렌지에서 오라클(Oracle) CLOB 글자 다 보는 법 오라클(Oracle)의 VARCHAR/VARCHAR2가 4000 Byte여서 종종 CLOB을 사용하게 되는데요.문제는... 오렌지에서 이걸 다 확인이 안되고 글자가 잘리는 현상이 나옵니다. 위 그림에서 PARAM2라는 컬럼이 CLOB으로 돼 있는데,안의 내용을 확인해보면 끝의 글자가 잘려서 보입니다.안의 내용이 아무리 많더라도 다 확인하려면 어떻게 해야할까요? Toad에서는 CLOB도 다 잘 보이는데 오렌지는 왜 안되냐고 고민하셨던 분들...1분만 투자해서 아래처럼 설정을 조금만 변경하시면 앞으로 CLOB 때문에 머리 아플 일은 없을 겁니다. 메뉴에서 [Option > Options]를 클릭합니다. Database > Long Size => 200000으로 설정합니다.원래 기본값은 20으로 돼 있습니다... 2017. 9. 30.
[오라클] DB Link 생성하기 오라클에서 DB Link 생성하는 스크립트입니다.tnsnames.ora 파일에 DB정보를 저장하지 않고 바로 생성할 수 있는 소스랍니다. 12345678910CREATE DATABASE LINK TESTDBCONNECT TO SUSERIDENTIFIED BY "1234"USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 100.211.111.107)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SEP) ))';Colored by Color Scriptercs DB Link명 : TESTDB => @TESTDB 이렇게 사용하시면 됩니다.USER명 : SUSER비밀번호 : 1234US.. 2017. 8. 17.
[Orange] 오렌지에서 오라클(Oracle) 다른 유저로 DB 가져오기(import) 서로 다른 사용자의 데이터를 import하려면 DBA 권한이 있어야만 가능합니다.YOWMSDEV라는 유저의 데이터를 YOCWMS라는 유저로 import 해봤습니다.저는 system으로 로그인을 했답니다. 오렌지의 상단메뉴에서 [Tools > Import]를 선택합니다. System으로 로그인을 했구요.(SysDBA로 로그인하셔도 됩니다.) From User에 YOWMSDEV를 입력하시구요. To User에 YOCWMS 유저를 선택합니다. 하단의 [Import Now] 버튼을 클릭하시면 Import가 실행됩니다.완료되면 테이블 등이 YOCWMS 유저에서 잘 보이는지 확인하시면 끝~!!! 2017/05/17 - [프로그래밍 언어/쿼리문/Oracle/MySQL] - [Orange] 오렌지에서 오라클(Oracl.. 2017. 7. 27.
[오라클] Oracle에서 컬럼의 값 만큼 행(Row)을 늘리는 쿼리 1234567SELECT ASNDKY, WAREKY, YQTYASN_IFROM ASNDHWHERE 1=1AND DOCDAT = '20170531';cs 이 쿼리를 실행하면 결과가 이렇게 나온다. YQTYASN_I라는 컬럼의 값의 수만큼 row를 뿌려줘야 하는 경우가 생겼는데 어떻게 해야 할까?오라클에서는 CONNECT BY를 사용하면 해결할 수 있다. 12345678910SELECT H.ASNDKY, H.WAREKY, H.YQTYASN_IFROM ASNDH H, (SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL 2017. 6. 29.
[오라클] 테이블정의서 자동으로 만들어주는 Oracle 쿼리 (Primary Key 여부 포함) 123456789101112131415161718192021222324252627282930313233343536373839404142WITH LIST AS( SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH, A.NULLABLE, B.COMMENTS FROM dba_tab_columns A, all_col_comments B WHERE A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND A.OWNER = 'LOTOSDEV' -- DB명),PKLIST AS( SELECT C.TABLE_NAME, C.COLUMN_NAME, C.POSITIO.. 2017. 6. 26.
[오라클] 동적쿼리를 실행해서 원하는 값을 가져오는 Oracle 함수 예제 보통 쿼리에 컬럼이나 테이블명 등은 변수로 받을 수가 없는데요.원하는대로 쿼리를 만들어서 결과값을 가져오는 오라클 함수 샘플입니다. 123456789101112131415161718192021222324252627282930CREATE OR REPLACE FUNCTION ZBF_GET_DELIVERY_NM ( p_ptnrky in varchar2 , p_wareky in varchar2) RETURN VARCHAR IS v_sql VARCHAR2(1000); v_returnValue VARCHAR2(180);/******************************************************************************/BEGIN BEGIN v_sql := ' SELECT B.. 2017. 6. 23.
[Oracle] 오라클 락(lock) 걸렸을 때 사용할 유용한 쿼리들 오라클 락 걸렸을 때 유용하게 사용할 쿼리들입니다. * 락걸린 테이블 확인123SELECT DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER, VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE FROM V$LOCKED_OBJECT VO, DBA_OBJECTS DOWHERE VO.OBJECT_ID = DO.OBJECT_IDColored by Color Scriptercs * 해당 테이블에 LOCK 이 걸렸는지 확인1234SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAMEFROM V$SESSION A, V$LOCK B, DBA_OBJECTS CWHERE A.SID = B.SID AND B.ID1 = C.OBJEC.. 2017. 6. 8.
[Orange] 오렌지에서 플랜(plan) 실행 안될 때(Plan table ORANGE.ORANGE_PLAN_TABLE does not exist) 해결 방법 오라클 툴인 오렌지(Orange)에서 plan이 실행이 안될 때가 있습니다.아마도 처음 설치하면 기본적으로 plan 실행이 안될 겁니다.PLAN 단축키 : Ctrl + E Plan table ORANGE.ORANGE_PLAN_TABLE does not exist. Please execute 'Orange configuration Admin' and set Plan Table to 'orange.orange_plan_table' or create your private Plan Table. Plan Table can be created or specified on [General] page with menu [Option] -> [Options...] in Plan Tool 이런 메시지가 뜨면... PLA.. 2017. 6. 2.
[Oracle] 날마다 시퀀스가 초기화되는 오라클 날짜 시퀀스(Sequence) 만들기 날짜를 포함한 시퀀스가 필요해서 만들어 봤습니다.날마다 시퀀스가 1로 초기화 돼야 해서 생각보다는 복잡합니다.1. 시퀀스 생성 2. 시퀀스를 1로 초기화하는 프로시저 생성 3. 프로시저를 오라클 job에 등록 4. 날짜를 포함한 시퀀스 값을 가져오는 함수를 생성 1. 시퀀스 생성 - 저는 3자리 시퀀스가 필요해서 MAX 999까지만 설정했습니다.123456789/* 배치키 시퀀스 */CREATE SEQUENCE SEQ_BATCHKEYSTART WITH 1MAXVALUE 999MINVALUE 1CYCLECACHE 10NOORDER;cs 2. 시퀀스를 1로 초기화하는 프로시저 생성한다.123456789101112131415/* 시퀀스 초기화 Procedure */CREATE OR REPLACE PROCEDU.. 2017. 5. 30.
[Oracle] 오라클 함수(Function)에서 DML(Insert/update/delete)문 사용하기 오라클 함수(Function)에서는 기본적으로 DML(Insert/update/delete)문을 사용할 수 없습니다.하지만, 기본적으로 그렇다는 것이지 사용할 방법이 없다는 건 아니랍니다. 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253CREATE OR REPLACE FUNCTION ZBF_GET_BARNUM ( p_bartyp in NUMBER, p_bardat in varchar2, p_gubun in varchar2 -- CUR/NEW) RETURN VARCHAR IS v_returnValue VARCHAR2(60); v_curnum NUMBER(10); v_cnt NUMB.. 2017. 5. 26.
[ERWin] Reverse Engineer 기능으로 오라클(Oracle)에 연결해서 자동으로 ERD 생성하기 현재 진행중인 프로젝트의 솔루션이 ERD를 제공하고는 있지만 파워포인트로 워낙 간략하게 돼 있어서 제대로 된 ERD를 만들어 달라는 요구를 받았습니다.솔루션을 설치하면 기본적으로 만들어지는 테이블이 200개가 넘는데 이걸 언제 그리나요? 그래서, 찾아보니 ERWin의 Reverse Engineer를 사용하면 오라클에 연결해서 자동으로 ERD를 그려주더라구요.이걸 다 그렸으면 적어도 2~3일은 걸렸을 것 같은데,,, 10분도 안되는 시간에 뚝딱 ERD를 만들어 버렸네요^^ 이렇게 테이블의 컬럼과 comment를 가지고 자동으로 ERD를 만들어 준답니다.그럼, 시작해 볼까요? [Tools > Reverse Engineer...]를 클릭합니다. [Logical/Physical]을 클릭하시고, 오라클 버전을 선.. 2017. 5. 22.
[오라클] Oracle에서 모든 테이블 목록 가져오는 쿼리 현재 진행하는 프로젝트는 솔루션을 가지고 작업을 하는데요.테이블이 엄청나게 많더라구요. 테이블 수를 보고 깜짝 놀랐는데...도대체 테이블 수가 몇 개나 되는지 궁금한데 어떻게 알 수 있을까요? select object_name from user_objects where object_type = 'TABLE'; -- 테이블 목록 select object_name from user_objects where object_type = ‘SEQUENCE’; -- 시퀀스 목록 이 쿼리를 사용하면 테이블 목록을 가져올 수 있답니다. 테이블 개수가 224개나 된다니... 정말 엄청나네요~ ㄷㄷㄷ 2017. 5. 22.
[오라클] PK(Primary Key)만 가져오는 Oracle 쿼리 1234567SELECT C.POSITION ,C.COLUMN_NAMEFROM USER_CONS_COLUMNS C INNER JOIN USER_CONSTRAINTS S ON C.CONSTRAINT_NAME = S.CONSTRAINT_NAME AND S.CONSTRAINT_TYPE = 'P'WHERE C.TABLE_NAME = 'SKUWC'ORDER BY 1Colored by Color Scriptercs 해당 테이블의 primary key(프라이머리 키)만 가져오는 쿼리랍니다.이 쿼리를 응용해서 DB 상의 모든 테이블의 Primary Key만 뽑을수도 있겠죠. 1234567891011121314151617181920212223242526272829303132333435363738394041WITH LIS.. 2017. 5. 12.

티스토리툴바