'Products/Oracle DBMS' 카테고리의 글 목록

[OracleDB] 공통 변환함수 to_char, to_date, to_number

2012. 5. 3. 14:07

1. Oracle DB 에서 제공하는 변환형 함수(Conversion function)들

주로 아래 3가지 변환함수가 많이 사용됩니다.

Parameter	Explanation
to_char	숫자나 날짜를 문자열로 변환
to_date	문자를 날짜로 변환
to_number	문자를 숫자로 변환

기타 참조할 만한 Conversion function 으로는

- Bin_To_Num / NumToDSInterval / NumToYMInterval

- To_Single_Byte / To_Multi_Byte / To_Clob / To_NClob / To_Lob

- From_Tz / To_Timestamp / To_Timestamp_Tz / To_YMInterval / To_DSInterval

- HexToRaw / CharToRowid / RawToHex

등이 있으나, 이번에는 언급하지 않겠습니다.

가능한 DB 버전 : Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

- 아마 곧 GA release 될 Oracle 12c 에서도 지원되겠지요?

2. to_char function

Syntax : TO_CHAR(문자값,‘형식’) / to_char( value, [format_mask], [nls_language])

- value can either be a number or date that will be converted to a string.

- format_mask is optional. This is the format that will be used to convert value to a string.

- nls_language is optional. This is the nls language used to convert value to a string.

Example : 숫자를 문자열로 변환

SQL> select TO_CHAR(350000,'$999,999') from dual ;

TO_CHAR(3

---------

$350,000

SQL>

Example : 날짜를 문자열로 변환

SQL> select to_char(sysdate, 'yyyy/mm/dd HH24:MI:SS') from dual ;

TO_CHAR(SYSDATE,'YY

-------------------

2012/05/03 13:24:25

SQL> select to_char(sysdate - 31, 'yyyymmdd') from dual ;

TO_CHAR(

--------

20120402

SQL> select to_char(sysdate, 'yyyy/mm/dd') from dual ;

TO_CHAR(SY

----------

2012/05/03

SQL> select to_char(sysdate, 'Month DD, YYYY') from dual ;

TO_CHAR(SYSDATE,'MONTHDD,YYYY')

---------------------------------------------

May 03, 2012

SQL> select to_char(sysdate, 'FMMonth DD, YYYY') from dual ;

TO_CHAR(SYSDATE,'FMMONTHDD,YYYY')

---------------------------------------------

May 3, 2012

SQL> select to_char(sysdate, 'MON DDth, YYYY') from dual ;

TO_CHAR(SYSDATE,'MONDDT

-----------------------

MAY 03RD, 2012

SQL> select to_char(sysdate, 'FMMON DDth, YYYY') from dual ;

TO_CHAR(SYSDATE,'FMMOND

-----------------------

MAY 3RD, 2012

SQL> select to_char(sysdate, 'FMMon ddth, YYYY') from dual ;

TO_CHAR(SYSDATE,'FMMOND

-----------------------

May 3rd, 2012

SQL>

Frequently Asked Questions

Question:

Why doesn't this sort the days of the week in order?

select ename, hiredate, to_char((hiredate),'fmDay') "Day"

from emp

order by "Day";

Answer:

In the above SQL, the fmDay format mask used in the to_char function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmD format mask as follows:

select ename, hiredate, to_char((hiredate),'fmD') "Day"

from emp

order by "Day";

3. to_date function

Syntax : TO_DATE(문자값, ‘형식’) / to_date(string1, [format_mask], [nls_language])

- string1 is the string that will be converted to a date.

- format_mask is optional. This is the format that will be used to convert string1 to a date.

- nls_language is optional. This is the nls language used to convert string1 to a date.

Example : 문자를 날짜로 변환

SQL> select TO_DATE('May 3 2012','MONTH DD YYYY') from dual ;

TO_DATE('

---------

03-MAY-12

SQL> select sysdate - to_date('20120101', 'yyyymmdd') from dual ;

SYSDATE-TO_DATE('20120101','YYYYMMDD')

--------------------------------------

123.577465

SQL> select to_date('2012/05/03', 'yyyy/mm/dd') from dual ;

TO_DATE('

---------

03-MAY-12

SQL> select to_date('050312', 'MMDDYY') from dual ;

TO_DATE('

---------

03-MAY-12

SQL> select to_date('20120503', 'yyyymmdd') from dual ;

TO_DATE('

---------

03-MAY-12

SQL>

4. to_number function

Syntax : TO_NUMBER(문자값) / to_number( string1, [format_mask], [nls_language] )

- string1 is the string that will be converted to a number.

- format_mask is optional. This is the format that will be used to convert string1 to a number.

- nls_language is optional. This is the nls language used to convert string1 to a number.

Example : 문자를 숫자로 변환

SQL> select TO_NUMBER('1234567') from dual ;

TO_NUMBER('1234567')

--------------------

1234567

SQL> select to_number('1210.73', '9999.99') from dual ;

TO_NUMBER('1210.73','9999.99')

------------------------------

1210.73

SQL> select to_number('546', '999') from dual ;

TO_NUMBER('546','999')

----------------------

546

SQL> select to_number('23', '99') from dual ;

TO_NUMBER('23','99')

--------------------

23

SQL>

5. Syntax 의 형식에 대해서

형식 : 숫자를 문자로 변환시에 형식에 사용되는 요소

Parameter	Explanation
9	일반적인 숫자를 나타냄
0	앞의 빈자리를 0으로 채움
$	dollar를 표시함
L	지역 통화 단위
.	소숫점을 표시함
,	천단위를 표시함

형식 : 날짜를 문자로 변환시에 형식에 사용되는 요소

Parameter	Explanation
SCC	세기를 표시 S는 기원전(BC)
YEAR	연도를 알파벳으로 spelling
YYYY	4자리 연도로 표시
YYY / YY / Y	끝의 3 / 2 / 1 자리 연도로 표시
Q	Quarter(분기) of year (1, 2, 3, 4; JAN-MAR = 1).
MONTH	월을 알파벳으로 spelling
MON	월의 알파벳 약어
MM	월을 2자리 숫자로 표시
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
DAY	일에 해당하는 요일, Name of day.
DY	일에 해당하는 요일의 약어
DDD	연도,월,일 중의 날짜를 숫자로 표시, Day of year (1-366).
DD	Day of month (1-31).
D	Day of week (1-7).
HH	시간을 표시, Hour of day (1-12).
HH12	시간을 표시, Hour of day (1-12).
HH24	시간을 표시, Hour of day (0-23).
MI	분을 표시, Minute (0-59).
SS	초를 표시, Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM(A.M.),PM(P.M.)	오전인지 오후인지를 표시
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

원문 : http://datacloud.tistory.com/5

Reference : http://www.techonthenet.com/oracle/functions/index.php

End of dicument.

저작자표시 동일조건

'Products > Oracle DBMS' 카테고리의 다른 글

[OracleDB] 공통 Invalid Object Compile 방법 (0)	2012.05.02
[OracleDB] 10g 를 Windows 7 에 설치 Tip (0)	2012.04.30
[OracleDB] 11g User Password 유효기간, 부정확한 Password 허용횟수 변경 (0)	2012.03.26

Tracbacks (+view to the desc.)

[OracleDB] 공통 Invalid Object Compile 방법

2012. 5. 2. 10:59

포동푸우 Products/Oracle DBMS View Comments

// Subject : [OracleDB] 공통 Invalid Object Compile 방법

// Tag : Oracle DB Invalid Object Compile Manually Recompile script

// 2012.05.02. wschoi@penta.co.kr Penta Systems technology

1. Oracle DB 에서 왜 주기적인 Invalid Object ReCompile 이 필요한가 ?

아래의 여러 가지 사유들로, Object 들이 Invalid 상태로 빠지게 됩니다.

- Oracle DB 에 대한 Major, Minor Upgrade 수행 후

- Function 이 사용하는 Table 의 Column 추가/삭제/변경이 발생되는 경우

보통은 Object 들을 CALL/USE 하게 되면, 다시 Valid 상태로 자동 Compile 이 되나,

때로는 아래의 문제들을 발생시키기도 합니다. DB 장애의 원인이 되기도 합니다.

- ORA-4020 : Deadlock 이 감지된 상태에서의 library object 에 대한 lock을 획득 실패, <Note:130409.1> 참조

- ORA-00911 : User miss 로 (Descriptive Flexfield 등을 신규생성/수정 시, Segment name 의 첫 CHARACTER 로 숫자나 알파벳 혹은 한글이 아닌 특수문자(ex> "[]")를 사용) Object 가 Compile 불가 상태로 변경된 경우

따라서, DBA 들은, 주기적으로 Invalid Object 들을 조사해서, 사전에 Recompile 을 통해, 관련 Object 들의 문제를 조치하고, 예기치 못한 Issue 발생에 대비하고 있습니다.

2. Invalid Object 를 Re compile 하는 방법

기본적으로 Oracle DB 에는 Invalid Object 들을 Compile 하는 5가지 방법이 존재합니다.

- DBMS_DDL

- DBMS_UTILITY

- UTL_RECOMP

- UTLRP.SQL

- Manually Recompile

저는 간단한 Script 를 이용해서, Manual recompile 하기를 좋아 하는데, text mode 의 telnet 창에 접속하여, 다수의 서버들에서 logging 하면서 동시 작업하기가 편하기 때문입니다. 각각의 방법들은 장단점들이 있지만, 여기서는 언급하지 않고, 수행 방법들만 기술하도록 하겠습니다.

2-A. DBMS_DDL.ALTER_COMPILE

Definition : ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

Syntax : Exec dbms_ddl.alter_compile ( type , schema, name);

- Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.

Example

SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

PL/SQL procedure successfully completed.

SQL>

2-B. DBMS_UTILITY.COMPILE_SCHEMA

Definition : This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax : Exec dbms_utility.compile_schema ( schema,compile all)

- Compile All : Object type ( procedure, function, packages,trigger)

Example

SQL> exec dbms_utility.compile_schema('SCOTT');

PL/SQL procedure successfully completed.

SQL>

2-C. UTL_RECOMP

Definition : This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.

Syntax : Exec UTL_RECOMP.RECOMP_SERIAL ();

- Note: Required SYS user to run this package.

Example

SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();

PL/SQL procedure successfully completed.

SQL>

2-D. UTLRP.SQL scripts

Definition : Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

Syntax : Located: $ORACLE_HOME/rdbms/admin

- Note: Required SYS user to run this script.

- Recommended: After upgrade or migrate database.

Example

SQL> @ c:\oracle\product\10.2.0\db_1\rdbms\admin\UTLRP.SQL

TIMESTAMP

-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2012-02-03 10:40:22

PL/SQL procedure successfully completed.

COMP_TIMESTAMP UTLRP_END 2012-02-03 10:40:29

PL/SQL procedure successfully completed.

SQL>

2-E. Manually recompiling : Best Approach to Recompile all Invalid Objects

Syntax : ALTER OBJECT_TYPE.OBJECT_NAME COMPILE ;

- Note: VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY

Example : Object Type 에 따라, 몇 개의 Script 로 나눌 수 있습니다.

SQL> Spool recompile.sql

SQL> Select ‘alter ‘object_type’ ’object_name’ compile ; ’ From user_objects where status <> ‘VALID’ and object_type IN (‘VIEW’, ’SYNONYM’, ‘PROCEDURE’, ’FUNCTION’, ‘PACKAGE’, ’TRIGGER’) ;

SQL> spool off

SQL> @ recompile.sql

- Note : VIEW, SYNONYM, PROCEDURE, PACKAGE, FUNCTION, TRIGGER

SQL> Spool pkg_body.sql

SQL> Select ‘alter package ’object_name’ compile body ; ’ from user_objects where status <> ‘VALID’ and object_type = ‘PACKAGE BODY’ ;

SQL> spool off

SQL> @ pkg_body.sql

- Note : PACKAGE BODY

SQL> Spool undefined.sql

SQL> select ‘alter materizlized view ’object_name’ compile ; ’ from user_objects where status <> ‘VALID’ and object_type =‘UNDEFINED’ ;

SQL> spool off

SQL> @ undefined.sql

- Note : UNDEFINED

SQL> Spool javaclass.sql

SQL> Select ‘alter java class ’object_name’ resolve ; ’ from user_objects where status <> ‘VALID’ and object_type =‘JAVA CLASS’ ;

SQL> spool off

SQL> @ javaclass.sql

- Note : JAVA CLASS’

SQL> Spool typebody.sql

SQL> Select ‘alter type ‘object_name’ compile body ; ’ from user_objects where status <> ‘VALID’ and object_type =‘TYPE BODY’ ;

SQL> spool off

SQL> @ typebody.sql

- Note : TYPE BODY

SQL> Spool public_synonym.sql

SQL> Select ‘alter public synonym ‘object_name’ compile ; ’ from user_objects where status <> ‘VALID’ and owner = ‘PUBLIC’ and object_type = ‘SYNONYM’ ;

SQL> spool off

SQL> @ public_synonym.sql

- Note : PUBLIC SYNONYM

원문 : http://datacloud.tistory.com/4

End of document.

저작자표시 동일조건

'Products > Oracle DBMS' 카테고리의 다른 글

[OracleDB] 공통 변환함수 to_char, to_date, to_number (0)	2012.05.03
[OracleDB] 10g 를 Windows 7 에 설치 Tip (0)	2012.04.30
[OracleDB] 11g User Password 유효기간, 부정확한 Password 허용횟수 변경 (0)	2012.03.26

Tracbacks (+view to the desc.)

[OracleDB] 10g 를 Windows 7 에 설치 Tip

2012. 4. 30. 17:40

포동푸우 Products/Oracle DBMS View Comments

1.1.1 Windows 7 에서 Oracle 10g 설치시 Error 발생

A. 개요

Oracle Database 12c(Cloud version)이 곧 GA release를 앞두고 있으나, 아직도 많은 고객들이 Oracle 8.0.x, 8i, 9i, 10g, 11g를 사용하고 있고, 이를 최신 Windows 인 7 (혹은 Vista)에 설치하는 경우가 있다. 설치할 DB의 version 은 정책적 결정 사항이다.

현재 가장 많이 사용되고 있는 Oracle DB 10g 버전을 Windows 7 환경에서 설치하기 위해 Tip이 필요한 이유는, 10g 출시 때에는 windows 7 등이 아직 출시되지 않았기 때문이다. 이미 많은 사례들을 통해, Support 가 확인되었으므로, 아래의 Tip을 통해, 설치가 가능하다

아래의 Tip 은 10g DBMS 혹은 Client 설치 모두에 적용 가능합니다.

B. Windows 7/Vista/2008R2 에서, Oracle 10g DBMS/Client 설치 시, Error 발생

발생 Error Message 생략

특정한 화일을 찾을 수 없다거나, 현제 버전을 지원할 수 없다는 메세지가 출력된다.

1.1.2 W/A(Support List 추가) 조치 후 Install 가능

A. 설치 Binary 선택

Windows 7 64 bit 인 경우 10204_vista_w2k8_x64_production_db.zip 을 사용

B. 설치 Binary 수정

설치 환경화일 중에, 아래 3개 file 에 Windows 7 에 해당하는 Code 6.1 을 넣은 후, 고급설치를 선택해 설치를 진행하면 됩니다. OS, DB 세부 version 에 따라 유사 이름의 다른 폴더에 화일이 존재하거나, 동일 폴더에 유사한 이름의 화일이 존재하기도 합니다.

위치 : \10204_vista_w2k8_x64_production_db\database\install\oraparam.ini

수정사항 : Windows 7 을 의미하는 6.1을 추가

[Certified Versions]

#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE

Windows=5.0,5.1,5.2,6.0,6.1

위치 : \10204_vista_w2k8_x64_production_db\database\stage\prereq\db\refhost.xml

수정사항 : Windows 7 을 의미하는 6.1라인들을 추가

<CERTIFIED_SYSTEMS>

<OPERATING_SYSTEM>

<SERVICE_PACK VALUE="1"/>

</OPERATING_SYSTEM>

<OPERATING_SYSTEM>

<SERVICE_PACK VALUE="1"/>

</OPERATING_SYSTEM>

<OPERATING_SYSTEM>

</OPERATING_SYSTEM>

<OPERATING_SYSTEM>

</OPERATING_SYSTEM>

<OPERATING_SYSTEM>

<VERSION VALUE="6.1"/>

</OPERATING_SYSTEM>

</CERTIFIED_SYSTEMS>

위치 : \10204_vista_w2k8_x64_production_db\database\stage\prereq\db_prereqs\db\refhost.xml

수정사항 : Windows 7 을 의미하는 6.1라인들을 추가

<CERTIFIED_SYSTEMS>

<OPERATING_SYSTEM>

<SERVICE_PACK VALUE="1"/>

</OPERATING_SYSTEM>

<OPERATING_SYSTEM>

<SERVICE_PACK VALUE="1"/>

</OPERATING_SYSTEM>

<OPERATING_SYSTEM>

</OPERATING_SYSTEM>

<OPERATING_SYSTEM>

</OPERATING_SYSTEM>

<OPERATING_SYSTEM>

<VERSION VALUE="6.1"/>

</OPERATING_SYSTEM>

</CERTIFIED_SYSTEMS>

C. Installer 에서 고급설치를 선택

기본 설치로 진행 할 경우 오류가 발생합니다. “고급 설치”로 진행합니다.

원본 : http://datacloud.tistory.com/3

End of Documents.

저작자표시 동일조건

'Products > Oracle DBMS' 카테고리의 다른 글

[OracleDB] 공통 변환함수 to_char, to_date, to_number (0)	2012.05.03
[OracleDB] 공통 Invalid Object Compile 방법 (0)	2012.05.02
[OracleDB] 11g User Password 유효기간, 부정확한 Password 허용횟수 변경 (0)	2012.03.26

Tracbacks (+view to the desc.)

[OracleDB] 11g User Password 유효기간, 부정확한 Password 허용횟수 변경

2012. 3. 26. 10:10

포동푸우 Products/Oracle DBMS View Comments

1.1.1 PASSWORD_LIFE_TIME을 통한 password 유효기간 변경

A. 개요

Oracle 11g 부터 user password 의 유효기간 기본 값이180(일)로 변경되었다. (기존 값 UNLIMITED), 따라서 180일이 지나면 passwd 가 expire 된다

B. 설정변경

-- Default Profile 을 변경하여 일괄 적용하는 방법

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;

-- 새로운 Profile 을 생성하여, 특정 user 에 제한적으로 적용하는 방법

#] connect /as sysdba

SQL> create profile profile_new01 LIMIT PASSWORD_LIFE_TIME UNLIMITED ;

SQL> alter user <username> profile profile_new01 ;

1.1.2 FAILED_LOGIN_ATTEMPTS을 통한 부정확한 password 허용횟수 변경

A. 개요

Oracle 11g 부터 user password 를 부정확하게 입력하는 경우, 접속 시도를 허용하는 횟수 기본값이 10(회)로 변경되었다. (기존 값은 UNLIMITED), 따라서 부정확한 패스워드를 10회 입력하게 되면, 계정이 잠기게 된다. 10gR2 부터는 password 에 한해서 영문 대문자, 소문자를 구분하므로, 특히 (통상 대/소문자를 구분하지 않는) Windows 로 부터의 자동접속 경로에 의해, user account 가 lock 되는 경우가 다수 발생된다.

B. 설정변경

-- Default Profile 을 변경하여 일괄 적용하는 방법

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED ;

-- 새로운 Profile 을 생성하여, 특정 user 에 제한적으로 적용하는 방법

#] connect /as sysdba

SQL> create profile profile_new01 LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED ;

SQL> alter user <username> profile profile_new01 ;

C. 잠긴 계정 (locked account)을 접속 가능하도록 풀어 주는 Query

SQL> connect /as sysdba

SQL> alter user <username> account unlock ;

D. 관련 정보 조회 (현재 설정값)

SQL> connect /as sysdba

SQL> select * from dba_profiles where resource_name in ('FAILED_LOGIN_ATTEMPTS',

'PASSWORD_LIFE_TIME');

PROFILE RESOURCE_NAME RESOURCE LIMIT

---------------------------- ------------------------------- ---------------- --------------

MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

SQL>

End of documents

저작자표시 비영리 변경금지

'Products > Oracle DBMS' 카테고리의 다른 글

[OracleDB] 공통 변환함수 to_char, to_date, to_number (0)	2012.05.03
[OracleDB] 공통 Invalid Object Compile 방법 (0)	2012.05.02
[OracleDB] 10g 를 Windows 7 에 설치 Tip (0)	2012.04.30

MW GURU

'Products/Oracle DBMS'에 해당되는 글 4건

[OracleDB] 공통 변환함수 to_char, to_date, to_number

'Products > Oracle DBMS' 카테고리의 다른 글

Comments (+add yours?)

Tracbacks (+view to the desc.)

[OracleDB] 공통 Invalid Object Compile 방법

'Products > Oracle DBMS' 카테고리의 다른 글

Comments (+add yours?)

Tracbacks (+view to the desc.)

[OracleDB] 10g 를 Windows 7 에 설치 Tip

1.1.1 Windows 7 에서 Oracle 10g 설치시 Error 발생

1.1.2 W/A(Support List 추가) 조치 후 Install 가능

'Products > Oracle DBMS' 카테고리의 다른 글

Comments (+add yours?)

Tracbacks (+view to the desc.)

[OracleDB] 11g User Password 유효기간, 부정확한 Password 허용횟수 변경

1.1.1 PASSWORD_LIFE_TIME을 통한 password 유효기간 변경

1.1.2 FAILED_LOGIN_ATTEMPTS을 통한 부정확한 password 허용횟수 변경

'Products > Oracle DBMS' 카테고리의 다른 글

Comments (+add yours?)

Tracbacks (+view to the desc.)

Pages

Recent Posts

Recent Comments

Recent Trackbacks

Tag List

Book mark

티스토리툴바