본문 바로가기
728x90
반응형

Oracle24

모든 테이블에서 데이터 찾기 SELECT 'SELECT ''' || TABLE_NAME || ''', ''' || COLUMN_NAME || ''', COUNT(*) FROM ' || TABLE_NAME || ' WHERE ' || COLUMN_NAME || ' LIKE ''%찾고싶은값%'' UNION ' FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'VARCHAR2' 2024. 8. 23.
[Oracle] 윤년/평년 구하기 CREATE FUNCTION SF_GET_LEAPYEAR ( iYEAR VARCHAR2, -- 년도 ) RETURN VARCHAR2 IS RET_NAME BOOLEAN; BEGIN RET_NAME := ''; /* - 윤년 구하는 방법 - 특정연도를 4로 나누었을때 나누어 떨어지면 그 해는 일단, 윤년. 그러나 100으로 나누었을때 나누어 떨어진다면 4로 나누어 떨어진다고 해도 그 해는 평년. 그리고 마지막으로 400으로 나누어 떨어진다면 100으로 나누어 떨어진다고 해도 윤년. */ CASE WHEN (MOD(iYEAR, 4) = 0 AND MOD(iYEAR, 100) 0) OR (MOD(iYEAR, 400) = 0) THEN RET_NAME := TRUE; --'LEAP YEAR' ELSE RET_N.. 2024. 2. 23.
[Oracle] 테이블 중복값 중에 최신값 가져오는 방법 데이터를 추출하기 위해 여러 값중 최대값 OR 최소값을 구할 경우가 있다. 여러가지 방법으로 구할수도 있지만 아래와 같은 방법으로도 데이터를 추출 할 수 있으니 참고 하면 좋을것 같다. --Sample data /* [ 일자 ][ 이름 ][ 점수 ] 20240101 TEST 10 20240102 TEST 11 20240103 TEST 12 20240104 TEST 13 20240101 TTTT 5 20240102 TTTT 8 20240103 TTTT 1 20240109 TTTT 0 */ SELECT 이름 ,MAX(일자) AS 일자 ,SUBSTR(MAX(일자||'*'||점수),INSTR(MAX(일자||'*'||점수),'*')+1) AS 점수 FROM SAMPLE_TABLE GROUP BY 이름 --결과 [.. 2024. 2. 23.
한글 - 알파벳 - 숫자 순으로 정렬 ** ASCII 코드의 값 범위 45217 ~ 51454 -- 한글 48 ~ 57 -- 숫자 65 ~ 90 -- 대문자 97 ~ 122 -- 소문자 ASCII함수로 정렬하기 WITH TEST AS( SELECT '777' AS NAME FROM DUAL UNION ALL SELECT '567' AS NAME FROM DUAL UNION ALL SELECT '0' AS NAME FROM DUAL UNION ALL SELECT '가나다라마바사' AS NAME FROM DUAL UNION ALL SELECT 'A' AS NAME FROM DUAL UNION ALL SELECT 'B' AS NAME FROM DUAL UNION ALL SELECT 'a' AS NAME FROM DUAL UNION ALL SELE.. 2023. 8. 1.
ORA-06502: PL/SQL: 수치 또는 값 오류 ORA-06502: PL/SQL: 수치 또는 값 오류 UTL_SMTP으로 메일발송을 하기위해 html 의 값을 clob으로 넣어 발송시 오류가 발생 하였다. UTL_SMTP.write_raw_data 으로 clob 데이터 전송시 최대크기는 32K(32767byte)를 넘어서 발생 하여 아래와 같이 수정 하였다. V_HTML은 html 형식이 들어가 있다. ******생략 V_HTML CLOB; ClobLen PLS_INTEGER; amount BINARY_INTEGER := 8192; buffer VARCHAR2(16384); offset PLS_INTEGER := 1; ******생략 V_HTML := '' || LV_CRLF ClobLen := DBMS_LOB.GETLENGTH(V_HTML); LOO.. 2023. 7. 20.
오라클 문자열 자르기 컬럼 구분값 자르기 컬럼의 구분값으로 열로 변환(?) 하기 위해서 아래와 같이 정규식을 통해 문자열을 자를수 있습니다. 문자열 자르기 SELECT REGEXP_SUBSTR(FULL_PATH,'[^,]+', 1, 1) AS STR1 ,REGEXP_SUBSTR(FULL_PATH,'[^,]+', 1, 2) AS STR2 ,REGEXP_SUBSTR(FULL_PATH,'[^,]+', 1, 3) AS STR3 ,REGEXP_SUBSTR(FULL_PATH,'[^,]+', 1, 4) AS STR4 FROM( SELECT '메뉴,메뉴1,메뉴2,메뉴3' AS FULL_PATH ) 출력 STR1 STR2 STR3 STR4 메뉴 메뉴1 메뉴2 메뉴3 * 참고 https://gent.tistory.com/459 [Oracle] 오라클 문자열 구분.. 2023. 4. 24.
RDS 세션 Kill 하기 /*세션 조회*/ SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = '스키마' AND STATUS = 'ACTIVE'; /*AWS RDS 오라클 세션 Kill*/ begin rdsadmin.rdsadmin_util.kill( sid => '1288', serial => '3320'); end; 2023. 3. 20.
오라클 Instance Name 조회 하기 SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE; 2023. 3. 17.
오라클 GREATEST, LEAST 함수 - 최대값 최소값 https://gent.tistory.com/332 [Oracle] 오라클 GREATEST, LEAST 함수 사용법 및 주의사항 오라클에서 GREATEST 함수는 최대값, LEAST 함수는 최소값을 반환하는 함수이다. MAX, MIN 함수와 차이점은 서로다른 컬럼의 값을 비교한다는 것이다. 같은 행(Row)에서 서로다른 컬럼의 값을 비교하여 gent.tistory.com 2022. 11. 25.
RDS DB Import 중지 데이터 펌프 JOB 조회 SELECT * FROM DBA_DATAPUMP_JOBS; 데이터 펌프 JOB 중지 DECLARE h1 NUMBER; BEGIN h1:=DBMS_DATAPUMP.ATTACH(JOB_NAME => '"MIG_CHANGED_TABLE"', JOB_OWNER => 'RTIS_MIG'); DBMS_DATAPUMP.STOP_JOB(h1,1,0); END; 2022. 11. 2.
Oracle 특정컬럼 및 테이블 정보 조회하기 특정 컬럼 테이블 조회 /*전체 테이블에서 특정컬럼을 사용하는테이블 조회 하기*/ SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME='조회컬럼' ORDER BY TABLE_NAME; 테이블 Comment 조회 /* 테이블 Comment 조회 하기*/ SELECT * FROM all_tab_comments WHERE table_type = 'TABLE' AND table_name = '테이블명' 2022. 10. 28.
PL/SQL 정렬키 설정 Tools > Preferences > User Interface [ Key Configuration ] Edit / PL/SQL Beautifier [ 사용자가 원하는키 입력 해도되나 Shift + Ctrl + F ] 설정 2022. 10. 28.
시퀀스 조회 및 삭제 시퀀스 조회하기 SELECT SEQUENCE_OWNER,SEQUENCE_NAME,dbms_metadata.get_ddl('SEQUENCE',A.SEQUENCE_NAME,A.SEQUENCE_OWNER) FROM ALL_SEQUENCES A WHERE SEQUENCE_OWNER IN ('SCCOT'); 시퀀스 삭제하기 DROP SEQUENCE [시퀀스명] 2022. 10. 14.
Oracle SQL Developer를 이용함 Export/Import 방법 http://it-archives.com/221043275638/ SQLDeveloper DB Import/Export 방법 – 흑곰의 유익한 블로그 2호점 SQLDeveloper DB Import/Export 방법 Export는 데이터 추출이고 Import는 데이터 대입/삽입이다. Export는 생략하고 Import 에 대해서만 설명하겠다. 어차피 쓰는 메뉴가 비슷하다. 1. 보기 – DBA 를 선택해서 DBA it-archives.com * Import 시 DB폴더경로 확인방법 SELECT * FROM DBA_DIRECTORIES; 2022. 10. 7.
스키마별 인덱스 조회 스키마별 인덱스 조회 및 스크립트 추출 SELECT a.index_name, -- 인덱스 명 조회 DBMS_MEtadata.get_ddl('INDEX', A.index_name ,A.TABLE_OWNER) -- DLL활용하여 INDEX 스크립트조회 FROM all_ind_columns a WHERE a.TABLE_OWNER = 'SCOTT' GROUP BY a.index_name,A.TABLE_OWNER; 1. 인덱스 조회 샘플 https://gent.tistory.com/203 [오라클] 인덱스 조회 방법 (Index) 오라클(Oracle) 인덱스 조회 쿼리 오라클 쿼리를 작성하기 전에 필수적으로 인덱스(Index)를 확인해야 한다. 인덱스를 무시하고 쿼리를 작성하면 심각한 성능 이슈가 발생 할 수 있.. 2022. 10. 6.
오라클 DB 암복호화 하기 https://solbel.tistory.com/902 2022. 7. 26.
[ORACLE]사용자 및 권한 조회 하기 --1. 사용자에게 부여된 시스템 권한 확인 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '사용자명'; --2. 사용자에게 부여된 롤 확인(시스템 권한이 롤에 포함됨) SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '사용자명'; --3. 사용자에게 부여된 롤에 부여된 시스템 권한 확인 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '롤명'; --4. 타 사용자에게 부여한 객체(테이블등)권한 확인 SELECT * FROM DBA_TAB_PRIVS WHERE OWNER = '테이블소유자명'; SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '권한부여자명'; 권한 부여 --1. 테.. 2022. 7. 20.
Oracle XML 데이터 파싱 오라클에서 XML 데이터를 파싱 하여 입력 하는 예제 XML 형식 천리안 예제 테이블 생성 CREATE TABLE XML_TABLE( SEQ NUMBER(10), XML_DATE VARCHAR2(4000), PRIMARY KEY(SEQ) ) XML 데이터 입력 INSERT INTO XML_TABLE VALUES ( 1, '천리안' ) XML 파싱해서 조회 SELECT A.XML_DATA ,B.NAME ,B.VALUE FROM XML_TABLE A ,XMLTABLE('/tests/data' PASSING XMLTYPE(A.XML_DATA) COLUMNS NAME VARCHAR2(50) PATH '@name', VALUE NUMBER PATH '.' )B 2022. 7. 12.
오라클 LPAD, RPAD 함수 LPAD -LPAD함수는 지정한 길이 만큼 왼쪽부터 특정문자로 채울때 사용 LPAD("값" ,"총 문자길이", "채울 문자") 필자는 1,2,3 이 아닌 01,02,03 으로 채번을 하기위하여 사용 SELECT LPAD('1',2,0) FROM DUAL * 설명 : 1 이란 값이 들어오면 총 문자길이 2만큼 0 으로 채움 결과 : 01, 02 , 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13 RPAD -RPAD함수는 지정한 길이 만큼 오른쪽부터 특정문자로 채울때 사용 SELECT RPAD('1',3,0) FROM DUAL * 설명 : * 설명 : 1 이란 값이 들어오면 총 문자길이 3만큼 0 으로 채움 결과 : 100, 200, 300, 400 2022. 5. 4.
PL/SQL Developer 한글깨짐 PL/SQL 실행 시 아래 그림과 같이 한글이 깨질 경우 환경 변수에 설정값을 추가하면 됩니다. 첫번째. 시스템 > 고급 시스템 설정 두번째. 시스템 속성 > 고급 [탭] > 환경변수 세번째. NLS_LANG 시스템 변수 > 새로 만들기 변수이름 : NLS_LANG 변수값 : KOREAN_KOREA.KO16MSWIN949 결과 : 2022. 2. 16.
[오라클] 영문 날짜 표기 오라클 영문 날짜 조회 SELECT TO_DATE('20210101') , TO_CHAR(TO_DATE('20210101') ,'MonthDD YYYY','NLS_DATE_LANGUAGE=ENGLISH') FORMAT1 , TO_CHAR(TO_DATE('20210101') ,'MonthDD, YYYY','NLS_DATE_LANGUAGE=ENGLISH') FORMAT1_1 , TO_CHAR(TO_DATE('20210101'),'Month','NLS_DATE_LANGUAGE=ENGLISH') FORMAT2 , TO_CHAR(TO_DATE('20210101'),'MonthfmDD, YYYY','NLS_DATE_LANGUAGE=ENGLISH') FORMAT3 , TO_CHAR(TO_DATE('20210101').. 2021. 12. 8.
[ORACLE] DECODE 와 CASE활용 방법 DECODE DECODE는 IF문과 같습니다. SELECT DEPT, DECODE( DEPT, 'A팀' ,SUM(SAL), 'B팀' ,MAX(SAL), MIN(SAL)) FROM EMP GROUP BY DEPT ORDER BY DEPT 위와같은 방법으로 조회를 하게 되면, DEPT 'A팀'은 합계값, B팀은 MAX값 , 그외는 MIN값이 출력됩니다. CASE CASE문은 IF ~ TEHN ~ ELSE ~ END 의 프로그래밍 언언처럼 조건문을 사용할 수 있습니다. SELECT CASE WHEN DEPT= 'A팀' THEN SUM(SAL) WHEN DEPT = 'B팀' THEN MAX(SAL) ELSE MIN(SAL) END FROM EMP 2021. 11. 5.
오라클 오브젝트(object) 내용 조회하기 Function/Procedure 내용을 어떻게 하면 알 수 있을까? USER_OBJECTS는 DB에 존재하는 Object 들의 정보를 관리 하고 USER_SOURCE는 각 Object 들의 내용을 확인할 수 있습니다. 즉, Oracle 에서 Funtion/Procedure 의 내용을 확인하려면 SELECT A.OBJECT_ID, A.OBJECT_NAME, A.OBJECT_TYPE, B.TEXT FROM USER_OBJECTS A, USER_SOURCE B WHERE A.OBJECT_TYPE IN( 'FUNCTION','PROCEDURE') AND A.OBJECT_NAME = B.NAME AND B.TEXT LIKE '%찾을 내용%'; 을 수행하면 원하는 내용을 찾을 수 있습니다. 2021. 11. 4.
PL/SQL Developer 데이터 엑셀 업로드 PL/SQL Developer 프로그램에서 엑셀 업로드를 하기위해서는 아래 메뉴로 이동해야 합니다. 메뉴 : Tools > Text Importer 우선 데이터를 업로드 하기전에 Excel 에 내용을 csv 확장자로 변경해주어야 [ , ] 쉼표로 데이터를 구분 되도록해 오라클에서 처리를 해주게 됩니다. CSV로 만든데이터를 업로드 하게되면 아래와 같이 업로드를 확인할 수 있습니다. 업로드가 완료가되면 내용을 확인 하고 두번째 탭으로 넘어 가 Owner 및 테이블 각 필드별 컬럼등을 매칭 후 Import 하면 됩니다. 2021. 10. 28.
728x90
반응형

티스토리툴바