본문 바로가기

분류 전체보기

쿼리팁 세로데이터 가로로 조회 http://blog.naver.com/mjsolar/220151258163 SELECT MAX(DECODE(R, 1, VAL)) ,MAX(DECODE(R, 2, VAL))FROM (SELECT 1 R, 'A' VAL FROM DUALUNION ALL SELECT 2 R, 'B' VAL FROM DUAL) SELECT SUBSTR(XMAGG(XMLELEMENT(x,'/',VAL) ORDER BY level DESC).EXTRACT('//text()'), 2) AS CONCAT_VALFROM (SELECT ROWNUM-1 R, VAL, COUNT(*) OVER() TOTFROM tempTable)START WITH R < TOTCONNECT BY PRIOR R = TOT SELECT.. 더보기
계층쿼리 # 상위부서 구하기SELECT GROUP_IDFROM TB_C99_GROUPSTART WITH GROUP_ID = 106CONNECT BY PRIOR UP_DEPT_IDX = GROUP_ID # 하위부서 구하기 SELECT GROUP_IDFROM TB_C99_GROUPSTART WITH UP_DEPT_IDX = 105CONNECT BY PRIOR GROUP_ID = UP_DEPT_IDX WITH W_DEPT AS ( SELECT 'A00' CD, '회사' NM, '000' P_CD FROM DUAL UNION ALL SELECT 'A01' CD, '부서1' NM, 'A00' P_CD FROM DUAL UNION ALL SELECT 'A02' CD, '부서1_1' NM, 'A01' P_CD FROM DUA.. 더보기
sequence create sequencehistSeqincrement by 1start with 1maxvalue 9999cycle nocache; 더보기
트랜잭션 분산 오류 -트랜잭션 분산 오류SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT# FROM DBA_2PC_PENDING;commit force 'TOPSPLC.459304ef.6.36.2588179';rollback force '6.36.2588179'; exec dbms_transaction.purge_lost_db_entry('6.36.2588179'); delete from sys.pending_trans$ where local_tran_id in('6.36.2588179'); delete from pending_sessions$ where local_tran_id in('6.36.2588179'); delete from pending_sub_.. 더보기
TABLE SPACE SELECT A.TABLESPACE_NAME "테이블스페이스명", (A.BYTES - B.FREE) "사용공간", B.FREE "여유 공간", A.BYTES "총크기", TO_CHAR( (B.FREE / A.BYTES * 100), '999.99')||'%' "여유공간"FROM ( SELECT FILE_ID, TABLESPACE_NAME, SUBSTR(FILE_NAME,1,200) FILE_NM, SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY FILE_ID,TABLESPACE_NAME,SUBSTR(FILE_NAME,1,200)) A,( SELECT TABLESPACE_NAME, FILE_ID, SUM(NVL(BYTES,0)) FREE FROM DBA_FREE_SPACE.. 더보기
JOB BEGIN SYS.DBMS_JOB.REMOVE(342);COMMIT;END;/ DECLARE X NUMBER;BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'DECLARE P_SND_FLAG VARCHAR2(10); BEGIN P_SND_FLAG := ''TMC100''; SP_ST126_CRUD ( P_SND_FLAG ); COMMIT; END;' ,next_date => to_date('17/10/2015 12:00:00','dd/mm/yyyy hh24:mi:ss') ,interval => 'TRUNC(SYSDATE+1)+12/24' ,no_parse => FALSE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_cha.. 더보기
C# 오라클 BLOB 등록 http://dalki0126.blog.me/20149530848 더보기
EXCEPTION insertException EXCEPTION; SELECT COUNT(1) INTO v_COUNT FROM SYS_USER_INFO WHERE USER_GROUP_ID = IN_USER_GROUP_ID; IF v_COUNT = 0 THEN DELETE SYS_USER_GROUP WHERE USER_GROUP_ID = IN_USER_GROUP_ID; ELSE RAISE insertException; END IF; EXCEPTION WHEN insertException THEN OUT_ERRYN := 'Y'; OUT_ERRMSG := 'SP_MES_SYS_1040_USERGROUP_SET ERROR' || CHR(13) || CHR(10) || 'ERRCode : ' || TO_CHAR(SQLCODE); .. 더보기
PROCEDURE DECLARE p_code varchar2(10); t_code varchar2(10); BEGINprc_tree ('C1234', p_code, t_code); dbms_output.put_line(p_code || ':' ||t_code); END; CREATE OR REPLACE PROCEDURE LEADERS.SP_MES_SYS_xxxx_GRID_DEL( IN_CRUD IN VARCHAR2 , IN_PG_ID IN VARCHAR2 , IN_PG_SEQ IN VARCHAR2 , IN_COL_ORDER IN NUMBER , OUT_ERRYN OUT NOCOPY VARCHAR2 , OUT_ERRMSG OUT NOCOPY VARCHAR2)AUTHID DEFINER IS/*******************.. 더보기
cursor CURSOR CUR_WEIGHT IS SELECT ITEM_CD, EXAM_NO, USE_QTY FROM WRK_WEIGHING WHERE SITE_CD = IN_SITE_CD AND ORDER_NO = IN_ORDER_NO; OPEN CUR_WEIGHT; LOOP FETCH CUR_WEIGHT INTO vITEM_CD, vEXAM_NO, vQTY; EXIT WHEN CUR_WEIGHT%NOTFOUND; UPDATE INV_STOCK SET QTY = QTY + vQTY ,MOD_USER_ID = IN_USER_ID ,MOD_DATE = SYSDATE WHERE SITE_CD = IN_SITE_CD AND ITEM_CD = vITEM_CD AND EXAM_NO = vEXAM_NO AND WH_CD = '.. 더보기

티스토리툴바