====== Oracle Database ======


https://localhost:5500/em

  * [[oracle:dba|DBA]]
  * [[white:h5dbimport|H5 DB Import]]
  * [[oracle:user|Oracle User관리]]
  * [[oracle:install|Oracle설치]]
==== Oracle관련자료들 ====
  - [[oracle:mvn_oracle|Maven Oracle JDBC]]
  - [[oracle:schedule|Schedule]]
  - [[oracle:hint|Hint]]
  - [[oracle:merge|Merge Statement]]
  - [[oracle:trigger|Trigger]]
  - [[oracle:hashjoin|Hash Join]]
  - [[oracle:createTable|Create Table]]
  - [[oracle:rdsdblink|RDS DB Links]]
  - [[oracle:tablespace|Table Space]]
  - [[oracle:trunc|Trunc]]
  - [[oracle:user_source|user_source 사용]]
  - [[oracle:split_to_table|Split To Table]]
  - [[oracle:rollup|Rollup]]
  - [[oracle:wm_concat|WM_CONCAT is gone in 12c]]
  - [[oracle:sqlldr|SQLLDR loader]]
  - [[oracle:sha256|SHA256 Package]]
  - [[oracle:tnsnames|TNS Names]]
  - [[oracle:function|Functions]]
  - [[oracle:getDDL|테이블정보 얻기]]
  - [[oracle:comma|Use Comma]]
  - [[oracle:timestamp recovery|Timestampe를 이용한복구]]
  - [[oracle:xml sql|XML SQL]]
  - [[oracle:update_join|Update Join]]
  - [[oracle:find_source|프로시져 소스검색]]
==== TO_CHAR ====
<code sql>
       to_char(cnt_response / cnt_target * 100 ,'999,999,990.99')as rate_response
</code>
==== Alter Table ====
<code sql>
alter table supp_pfr add  grp_qty number(6,0);
comment ON COLUMN saled.reg_kind IS '세일등록구분';
</code>
==== Index ====
<code sql>
create UNIQUE INDEX AK_PEE_RATER_TARGET ON PEE_RATER_TARGET (APPR_ID, RATEE_EMP_NO)
</code>
==== ORA_ROWSCN ====
<code sql>
SELECT ORA_ROWSCN, last_name FROM employees WHERE employee_id = 188;

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), last_name FROM employees
   WHERE employee_id = 188;
</code>
==== Error Log Table ====
Data Bulk Processing 시 유용한 방법인듯 싶으니 참고하세요..

<code sql>
-- Error Log Table 생성
declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  DBMS_ERRLOG.CREATE_ERROR_LOG('ZZZ', 'ZZZ_ERRLOG');
end;



-- 적용 예
insert into zzz
  (nm)
SELECT '1' from dual
UNION ALL
SELECT '12345678901' from dual
LOG ERRORS INTO  zzz_errlog(select userenv('sessionid') from dual) REJECT LIMIT UNLIMITED;

</code>

==== RAISE_APPLICATION_ERROR ====
20000~20999까지의 에러 숫자는 비워져 있다. 이 에러 숫자에 사용자 정의 오류 메시지를 실행 시킬 수 있다. 

<code sql>
RAISE_APPLICATION_ERROR(-20001, 'asdf Error Message');
</code>
==== UTL_RAW ====
charset이 상이한 I/F에서 사용하여 한글 Encoding문제해결
**utl_row.cast_to_raw <=> utl_row.cast_to_varchar2**

<code sql>
UTL_RAW.CAST_FROM_BINARY_DOUBLE (n,endian) RETURN RAW;
UTL_RAW.CAST_FROM_BINARY_FLOAT (n, endian) RETURN RAW;
UTL_RAW.CAST_FROM_BINARY_INTEGER(n, endian) RETURN RAW;
UTL_RAW.CAST_FROM_NUMBER (n) RETURN RAW;
UTL_RAW.CAST_TO _BINARY_DOUBLE (n,endian) RETURN BINARY_DOUBLE;
UTL_RAW.CAST_TO _BINARY_FLOAT (n, endian) RETURN BINARY_FLOAT;
UTL_RAW.CAST_TO _BINARY_INTEGER(n, endian) RETURN BINARY_INTEGER;
UTL_RAW.CAST_TO _NUMBER (n) RETURN NUMBER;
UTL_RAW.CAST_TO_RAW (data) RETURN RAW;
UTL_RAW.CAST_TO_VARCHAR2 (r) RETURN VARCHAR;
</code>
==== Table Comments ====
<code sql>
SELECT A.TABLE_NAME, B.COMMENTS
  FROM ALL_TABLES A, ALL_TAB_COMMENTS B
 WHERE A.TABLE_NAME = B.TABLE_NAME
   AND A.TABLE_NAME LIKE 'LEM_COURSE%'
 ORDER BY A.TABLE_NAME;
</code>