ORACLE 주차 계산 식, Previous, current and next values

ORACLE 주차 계산 식

 

SELECT TO_DATE('20131209','YYYYMMDD')

     , TO_CHAR(TO_DATE('20131212','YYYYMMDD'), 'D')
     , TO_DATE('20131209','YYYYMMDD') + ( 7 - TO_CHAR(TO_DATE('20131209','YYYYMMDD'), 'D') + 2)             AS AF_1WEEK_MON
     , TO_DATE('20131209','YYYYMMDD') + 112 + ( 7 - TO_CHAR(TO_DATE('20131209','YYYYMMDD') + 112, 'D') + 1) AS AF_16WEEK_SUN     
  FROM DUAL;

 

 

 

Previous, current and next values

The Oracle lag and lead functions can be used to retrieve values from a previous row (lag) or a next row (lead). Consider the following example. 
 
 
SQL> select deptno 
  2  ,      lag(deptno) over (order by deptno) as previous 
  3  ,      lead(deptno) over (order by deptno) as next 
  4  from   scott.dept 

 

오라클(Oracle) 내가 자주 사용하는 Query 모음

-- All User Names
SELECT USERNAME FROM ALL_USERS;

 

-- Schemas For DBA
SELECT DU.USERNAME FROM DBA_USERS DU JOIN USER_USERS UU ON DU.USERNAME = UU.USERNAME;

 

-- Schemas For Users
SELECT USERNAME FROM USER_USERS;

 

-- Tablesepaces
SELECT TS.TABLESPACE_NAME, TS.BLOCK_SIZE, TS.MIN_EXTLEN, TS.STATUS, TS.CONTENTS, TS.LOGGING
     , TS.FORCE_LOGGING, TS.SEGMENT_SPACE_MANAGEMENT, TS.DEF_TAB_COMPRESSION, TS.RETENTION
     , TS.BIGFILE, TS.INITIAL_EXTENT, TS.NEXT_EXTENT, TS.MIN_EXTENTS, TS.MAX_EXTENTS
     , TS.PCT_INCREASE, TS.EXTENT_MANAGEMENT, TS.ALLOCATION_TYPE 
FROM USER_TABLESPACES TS

 

-- Current Schema
SELECT US.USERNAME, US.DEFAULT_TABLESPACE, US.TEMPORARY_TABLESPACE FROM USER_USERS US

 

-- Tables Count
SELECT  COUNT(T.TABLE_NAME) FROM ALL_TABLES T WHERE T.STATUS='VALID' AND T.OWNER IN ('EEUMIT_DEV')

 

-- Tables
SELECT  T.TABLE_NAME, T.TABLESPACE_NAME, T.STATUS, T.CACHE, T.COMPRESSION, T.LOGGING
      , T.MONITORING, T.DEPENDENCIES, T.ROW_MOVEMENT, T.DEGREE, T.INSTANCES, T.PCT_FREE
      , T.INI_TRANS, T.MAX_TRANS, T.PCT_USED, T.BUFFER_POOL, T.FREELIST_GROUPS, T.FREELISTS
      , T.INITIAL_EXTENT, T.NEXT_EXTENT, T.MIN_EXTENTS, T.MAX_EXTENTS, T.PCT_INCREASE, M.COMMENTS 
  FROM ALL_TABLES T  LEFT JOIN  ALL_TAB_COMMENTS M 
                            ON T.TABLE_NAME = M.TABLE_NAME 
                           AND T.OWNER = M.OWNER 
 WHERE T.STATUS='VALID' 
    AND T.OWNER = 'EEUMIT_DEV'
ORDER BY T.TABLE_NAME

 

-- Columns
SELECT  C.COLUMN_NAME, C.NULLABLE, C.DATA_TYPE, C.DATA_LENGTH, C.DATA_PRECISION, C.DATA_SCALE, C.DATA_DEFAULT, C.CHAR_USED, M.COMMENTS 
  FROM ALL_TAB_COLUMNS C JOIN ALL_COL_COMMENTS M 
                           ON (C.TABLE_NAME = M.TABLE_NAME) 
                          AND (C.COLUMN_NAME = M.COLUMN_NAME) 
                          AND (C.OWNER = M.OWNER) 
 WHERE C.TABLE_NAME = 'TB_TEMP_ITE01'
   AND C.OWNER =  'EEUMIT_DEV'
 ORDER BY C.COLUMN_ID;
 
-- Pk Constraint 
SELECT CT.CONSTRAINT_NAME, CT.STATUS, CT.DEFERRABLE, CT.DEFERRED, CT.VALIDATED
      , CT.GENERATED, CT.INDEX_OWNER, CT.INDEX_NAME 
  FROM ALL_CONSTRAINTS CT 
 WHERE CT.CONSTRAINT_TYPE = 'P' 
    AND CT.OWNER='TB_DOF02001'
    AND CT.TABLE_NAME='TB_TEMP_ITE01';
    
-- Unique Constraint
SELECT CT.CONSTRAINT_NAME, CT.STATUS, CT.DEFERRABLE, CT.DEFERRED, CT.VALIDATED, CT.GENERATED, CT.INDEX_OWNER
     , CT.INDEX_NAME 
  FROM ALL_CONSTRAINTS CT 
 WHERE CT.CONSTRAINT_TYPE = 'U' 
   AND CT.OWNER='TB_DOF02001'
   AND CT.TABLE_NAME='TB_TEMP_KAE01';  
   
   
-- Check Or Not Null Constraint
SELECT CT.SEARCH_CONDITION, CT.CONSTRAINT_NAME, CT.STATUS, CT.DEFERRABLE, CT.DEFERRED, CT.VALIDATED, CT.GENERATED 
  FROM ALL_CONSTRAINTS CT 
 WHERE CT.CONSTRAINT_TYPE = 'C' 
   AND CT.OWNER='TA_DEV'
   AND CT.TABLE_NAME= 'TB_DOF02001';  
   
-- Fk Constraint   
SELECT CT.CONSTRAINT_NAME, CT.STATUS, CT.DEFERRABLE, CT.DEFERRED, CT.VALIDATED, CT.GENERATED
     , CT.R_OWNER, RC.TABLE_NAME R_TABLE_NAME, CT.R_CONSTRAINT_NAME, RC.CONSTRAINT_TYPE R_CONSTRAINT_TYPE
     , CT.DELETE_RULE 
FROM ALL_CONSTRAINTS CT JOIN ALL_CONSTRAINTS RC 
                          ON CT.R_OWNER = RC.OWNER 
                         AND CT.R_CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
WHERE CT.CONSTRAINT_TYPE = 'R' 
  AND CT.OWNER='EEUMIT_DEV'
  AND CT.TABLE_NAME   
 

-- Data File
SELECT DF.FILE_NAME, DF.AUTOEXTENSIBLE, DF.BYTES, DF.MAXBYTES, DF.INCREMENT_BY 
  FROM DBA_DATA_FILES DF 
 WHERE  DF.TABLESPACE_NAME = '@tablespaceName'
 
 
-- Indexes 
SELECT IX.INDEX_NAME, IX.INDEX_TYPE, IX.TABLE_TYPE, IX.UNIQUENESS, IX.COMPRESSION, IX.TABLESPACE_NAME
      , IX.LOGGING, IX.PARTITIONED, IX.GENERATED, IX.ITYP_NAME, IX.ITYP_OWNER
      , IX.PARAMETERS, IX.DEGREE, IX.INSTANCES, IX.PCT_FREE, IX.INI_TRANS, IX.MAX_TRANS
      , IX.BUFFER_POOL, IX.FREELIST_GROUPS, IX.FREELISTS, IX.INITIAL_EXTENT, IX.NEXT_EXTENT
      , IX.MIN_EXTENTS, IX.MAX_EXTENTS, IX.PCT_INCREASE 
FROM  ALL_INDEXES IX 
WHERE IX.TABLE_OWNER = '&schemaName'
  AND IX.TABLE_NAME  = '&tableName'
     
  
-- Index Columns  
SELECT IC.COLUMN_NAME, IC.DESCEND 
  FROM ALL_IND_COLUMNS IC 
 WHERE IC.INDEX_OWNER = '&schemaName'
   AND IC.INDEX_NAME = '&indexName' 
ORDER BY IC.COLUMN_POSITION;

-- Index Column Expressions
SELECT IC.COLUMN_EXPRESSION 
  FROM ALL_IND_EXPRESSIONS IC 
 WHERE IC.INDEX_OWNER = '&schemaName'
   AND IC.INDEX_NAME  = '&indexName'
ORDER BY IC.COLUMN_POSITION;

 

 

오라클(Oracle) 컬럼 속성 조회

SELECT a.OWNER      "usrId"
     , A.TABLE_NAME     "tabNm"
     , A.COLUMN_NAME  "colNm"
     , B.COMMENTS       "description"
     , a.DATA_TYPE
     , DATA_LENGTH
     , a.NULLABLE
     , CASE WHEN C.Position IS NULL THEN '0' ELSE '1' END PK_YN
 FROM   ALL_TAB_COLUMNS  a
      , ALL_COL_COMMENTS b
      , ALL_CONS_COLUMNS c
WHERE  A.OWNER       = B.OWNER
  AND  A.TABLE_NAME  = B.TABLE_NAME
  AND  A.COLUMN_NAME = B.COLUMN_NAME
  AND  A.OWNER       = C.OWNER(+)
  AND  A.TABLE_NAME  = C.TABLE_NAME(+)
  AND  A.COLUMN_NAME = C.COLUMN_NAME(+)
  AND  A.OWNER       = :usrId
  AND  A.TABLE_NAME  = :tabNm
  AND  C.POSITION(+) > 0
ORDER BY c.Position, a.COLUMN_ID

 

 

SQL Injection

최근 개발 표준, 가이드 및 프레임웩에서 SQL Injection 공격에 대비해서 예전보다 안전해 졌습니다.

신입 사원 떄 SQL Injection에 대해서 잘 모르고 개발하다가 실제 공격당한 경험이 생각나서 정리 해 봅니다.

꼭 한번 보고 개발 할 떄 실수하지 않도록 하면 좋을 것 같습니다.

 

1.SQL Injection의 정의

 데이터베이스로 전달되는 SQL Query를 변경시키기 위해 Web Application에서 입력 받은 파라메터를 변조 후 삽입하여 

 비정상적인 데이터베이스 접근을 시도하거나 쿼리를 재구성하여 원하는 정보를 열람하는 해킹 기법

 

대량 삽입       데이터베이스에 악성코드를 대량으로 삽입

자동 스크립트 자동 삽입 스크립트를 사용하여 한 번에 악성코드를 대량 삽입

공격 로그       POST 나 HTTP Header(cookie, referrer 등)를 이용한 경우는 공격 로그를 찾기 어려움

데이터 손실   악성코드 삽입 과정에서 데이터의 손실 또는 유실발생

 

2.SQL Injection의 공격 기법

 

2.1 인증 우회

 인증을 처리하는 모듈이 입력 값에 대해 적절히 검사하지 않았을 때 공격자는 비정상적인 SQL Query를 삽입할 수 있고 이를 이용해 데이터베이스에 영향을 줄 수 있습니다.

 주로 로그인 창에 적용되는 기법으로 이용자 아이디와 패스워드를 몰라도 로그인 할 수 있게 해줌

 ' or '1'='1

 

2.2 권한 상승

공격자가 DB의 시스템 권한을 획득한다면, SQL에서 기본적으로 제공하는 확장 프로시저를 이용하여 악성코드를 삽입하거나 DB를 변경하는 등의 여러 가지 시스템 명령어를 실행시켜 악용

xp_cmdshell : 임의의 명령 실행을 허가하는 내장된 저장 프로시저

 

기타 저장 프로시저 

※ xp_servicecontrol 프로시저는 사용자가 작동, 정지, 일시정지 그리고 연속 서비스하는 것을 허가

※ xp_dirtree 프로시저는 디렉토리 트리 획득을 허가

※ xp_makecab 프로시저는 사용자가 서버에 압축파일 만드는 것을 허가

 

2.3 시스템 에러 이용

 에러메시지를 통하여 정보 얻기

 조작된 URL을 요청하게 되면 홈페이지는 에러 메시지들을 발생

 에러 메시지는 공격자에게 유용한 정보를 제공하여 쉽게 DB 열람 및 시스템 명령어를 수행할 수 있음

 

2.4 데이터베이스 저장된 데이터의 열람 및 조작

Error-Based Injection, Blind SQL Injection 등의 기법을 통해 주요 데이터의 조회, 테이블 생성 등 데이터베이스에 대한 다양한 공격 가능

※ Error-Based Injection: 화면에 노출된 DB에러 메세지를 이용한 공격방식

※ Blind SQL Injection: 쿼리조건에 따른 결과화면의 차이를 이용한 공격방식

 

3.확인 방법

침입 확인 방법

 DB 확인

  - 임시 Table이나 이용자 계정으로 확인

  - HDSI Tool에 의한 침입: T-Jiaozhu, jiaozhu, comd_list 등 임시 Table 생성

  - D-SQL에 의한 침입: D99_Tmp라는 임시 Table 생성

 Web Log 확인

  - Table 관련한 Create, Select 구문 확인

  - 확장 저장 프로시저에 대한 로그 확인

  - 검색 대상 문자열 : XP_CMDSHELL, Net, user, update, insert, drop table 등

 

취약점 방법

 수동 확인 방법

  - GET 방식 SQL Injection 공격 탐색

  - POST 방식 SQL Injection 공격 탐색

 

  - SQL Injection공격에 취약한지 검사하는 방법으로 자신의 사이트가 SQL Injection 취약점에 노출되어 있는지 간단히 점검해 볼 수 있음

  - GET 방식 SQL 주입 공격 탐색

  - POST 방식 SQL 주입공격 탐색

  - 테스트 문자열

 

 

  자동 확인 방법

  - Paros Proxy를 이용한 자동 검색: SQL 취약점 점검 툴

  - nikto Web CGI Scanner: SQL 취약점 점검 툴

  - SQL Injector: SQL 취약점 점검 툴

 

- 빠른 시간 내에 SQL Injection 문제점들을 찾기 위해서는 자동화된 도구를 이용

- Paros Proxy를 이용한 자동 검색

: 서핑을 완료한 페이지에 대하여만 SQL Injection 취약점이 존재하는지 점검 가능

- nikto web CGI스캐너

: 기존에 잘 알려진 SQL Injection취약점에 대해서만 검색 가능하나 실제 서버의 응답결과를 확인하지 않으므로 오탐의 소지가 높음

- SQL Injector

: 점검하고자 하는 사이트의 시작페이지를 지정한 후 “SQL 주입 취약점 Scan” 버튼을 누르면 사이트의 모든 페이지 및 매개변수에 대하여 SQL Injection 취약점이 존재하는지 점검

 

 

Oracle functions 정리

ASCII	The ASCII function returns the decimal representation in the database character set of the first character of char. Example: ASCII('b') =98
CHR	The CHR function returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set. Example: CHR(10)||CHR(13) = carriage return plus line feed.
COALESCE	The COALESCE function returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null. Example: select COALESCE(col1, col2, col3) FROM emp;
CONCAT	The CONCAT function returns the concatenation of 2 strings. You can also use the || command for this. Example: CONCAT('abc','def') = 'abcdef'
CONVERT	The CONVERT function converts a string from one characterset to another. The datatype of the returned value is VARCHAR2. Example: CONVERT('This is an example','UTF-8','WE8ISO8859P1') SELECT CONVERT('占� 占� 占� 占� 占� A B C D E ', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL; = A E I ? ? A B C D E ?
DUMP	The DUMP function returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.
INSTR	Returns the position of a String within a String. For more information see Oracle instr function
INITCAP	Transform String to init cap Example: INITCAP('ORADEV') = 'Oradev'
INSTRB	Returns the position of a String within a String, expressed in bytes.
INSTRC	Returns the position of a String within a String, expressed in Unicode complete characters
INSTR2	Returns the position of a String within a String, expressed in UCS2 code points
INSTR4	Returns the position of a String within a String, expressed in UCS4 code points
LENGTH	The LENGTH functions returns the length of char. LENGTH calculates length using characters as defined by the input character set. Example: length('oradev.com') = 10
LENGTHB	Returns the length of a string, expressed in bytes.
LOWER	The LOWER function returns a string with all lower case characters. Example: LOWER('ORADEV') = 'oradev'
LPAD	Add characters to the left of a string until a fixed number is reached. Example: lpad('abc',8,'x') = 'xxxxxabc'. If the last parameter is not specified, spaces are added to the left.
LTRIM	LTRIM removed characters from the left of a string if they are equal to the specified string. Example: ltrim('aaaaaabc','a') = 'bc' If the last parameter is not specified, spaces are removed from the left side.
REPLACE	The replace function replaces every occurrence of a search_string with a new string. If no new string is specified, all occurrences of the search_string are removed. Example: replace('a1a1a1','a','2') = '212121'.
REVERSE	Reverses the characters of a String. Example: REVERSE('oradev.com') = 'moc.vedaro'
RPAD	Add characters to the right of a string until a fixed number is reached. Example: rpad('abc',8,'x') = 'abcxxxxx'. If the last parameter is not specified, spaces are added to the right.
RTRIM	RTRIM removed characters from the right of a string if they are equal to the specified string. Example: rtrim('bcaaaaaa','a') = 'bc' If the last parameter is not specified, spaces are removed from the right side.
SOUNDEX	SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English. Example: select * from emp where lastname SOUNDEX('SMITH');
SUBSTR	Returns a substring. For more information see Oracle substring
SUBSTRB	Returns a substring expressed in bytes instead of characters.
SUBSTRC	Returns a substring expressed in Unicode code points instead of characters.
SUBSTR2	Returns a substring using USC2 code points.
SUBSTR4	Returns a substring using USC4 code points.
TRANSLATE	TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. Example: SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL; = 'SQL_Plus_Users_Guide'
TRIM	The TRIM function trims specified characters from the left and/or right. If no characters are specified, the left and right spaces are left out. Example: trim(' Oradev dot com ') = 'Oradev dot com'.
|| (pipes)	With pipes you can concattenate strings. Example 'Oradev'||'.com' = 'Oradev.com'.
UPPER	Transform a string to all upper case characters. Example: UPPER('oradev') = 'ORADEV'
VSIZE	The VSIZE function returns the byte size of a String.

 

 

instr(string, substring [,position [,occurrence]])
with: 
string: the string that is searched.
substring: the substring which we are looking for in the string
position: The position from which we start the search (an integer value). If position is negative, then Oracle counts and searches backward from the end of string. If omitted, this defaults to 1.
occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive. If this is omitted, this defaults to 1.
Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype
If no value is found, the instr will return the value 0.

Examples

INSTR('CORPORATE FLOOR','OR', 3, 2) = 14
INSTR('CORPORATE FLOOR','OR', -3, 2) = 2
INSTR('ab ab ab','ab') = 1
INSTR('ab ab ab','ab',1,2) = 4
INSTR('ab ab ab','ab',2,2) = 7
INSTR('abcabcabcdef','de') = 7

 

 

 

 

Oracle number format

You can use a number format in Oracle in :
1. The TO_CHAR function to format a number datatype.
i.e. TO_CHAR(value,'90.99')
2. The TO_NUMBER function to convert a CHAR or VARCHAR2 value to a NUMBER datatype.
i.e. TO_CHAR('24.33','99.99')

All number format models cause the number to be rounded to the specified number of significant digits. If a value has more significant digits to the left of the decimal place than are specified in the format, then pound signs (#) replace the value. If a positive value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~).

ElementExampleDescription

, (comma)	9,999	Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions: A comma element cannot begin a number format model. A comma cannot appear to the right of a decimal character or period in a number format model.
. (period)	99.99	Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a number format model.
$	$9999	Returns value with a leading dollar sign.
0	0999 9990	Returns leading zeros. Returns trailing zeros.
9	9999	Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B	B9999	Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of "0"s in the format model).
C	C999	Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).
D	99D99	Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.). Restriction: You can specify only one decimal character in a number format model.
EEEE	9.9EEEE	Returns a value using in scientific notation.
FM	FM90.9	Returns a value with no leading or trailing blanks.
G	9G999	Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model. Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
L	L999	Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter).
MI	9999MI	Returns negative value with a trailing minus sign (-). Returns positive value with a trailing blank. Restriction: The MI format element can appear only in the last position of a number format model.
PR	9999PR	Returns negative value in <angle brackets>. Returns positive value with a leading and trailing blank. Restriction: The PR format element can appear only in the last position of a number format model.
RN rn	RN rn	Returns a value as Roman numerals in uppercase. Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.
S	S9999 9999S	Returns negative value with a leading minus sign (-). Returns positive value with a leading plus sign (+). Returns negative value with a trailing minus sign (-). Returns positive value with a trailing plus sign (+). Restriction: The S format element can appear only in the first or last position of a number format model.
TM	TM	"Text minimum". Returns (in decimal output) the smallest number of characters possible. This element is case-insensitive. The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If output exceeds 64 characters, then Oracle automatically returns the number in scientific notation. Restrictions: You cannot precede this element with any other element. You can follow this element only with 9 or E (only one) or e (only one).
U	U9999	Returns in the specified position the "Euro" (or other) dual currency symbol (the current value of the NLS_DUAL_CURRENCY parameter).
V	999V99	Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the "V".
X	XXXX xxxx	Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle rounds it to an integer. Restrictions: This element accepts only positive values or 0. Negative values return an error. You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has 1 leading blank.

 

 

Oracle date format

With the functions to_char and to_date, a date format can be used. Example:
select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
will return something like: 24/03/2006 14:36:43

Here is a list of all the formats that can be used:

Format maskDescription

CC	Century
SCC	Century BC prefixed with -
YYYY	Year with 4 numbers
SYYY	Year BC prefixed with -
IYYY	ISO Year with 4 numbers
YY	Year with 2 numbers
RR	Year with 2 numbers with Y2k compatibility
YEAR	Year in characters
SYEAR	Year in characters, BC prefixed with -
BC	BC/AD Indicator *
Q	Quarter in numbers (1,2,3,4)
MM	Month of year 01, 02...12
MONTH	Month in characters (i.e. January)
MON	JAN, FEB
WW	Weeknumber (i.e. 1)
W	Weeknumber of the month (i.e. 5)
IW	Weeknumber of the year in ISO standard.
DDD	Day of year in numbers (i.e. 365)
DD	Day of the month in numbers (i.e. 28)
D	Day of week in numbers(i.e. 7)
DAY	Day of the week in characters (i.e. Monday)
FMDAY	Day of the week in characters (i.e. Monday)
DY	Day of the week in short character description (i.e. SUN)
J	Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH	Hournumber of the day (1-12)
HH12	Hournumber of the day (1-12)
HH24	Hournumber of the day with 24Hours notation (0-23)
AM	AM or PM
PM	AM or PM
MI	Number of minutes (i.e. 59)
SS	Number of seconds (i.e. 59)
SSSSS	Number of seconds this day.
DS	Short date format. Depends on NLS-settings. Use only with timestamp.
DL	Long date format. Depends on NLS-settings. Use only with timestamp.
E	Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE	The full era name
FF	The fractional seconds. Use with timestamp.
FF1..FF9	The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM	Fill Mode: suppresses blianks in output from conversion
FX	Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I	the last 3,2,1 digits of the ISO standard year. Output only
RM	The Roman numeral representation of the month (I .. XII)
RR	The last 2 digits of the year.
RRRR	The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC	Century. BC dates are prefixed with a minus.
CC	Century
SP	Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH	Spelled and ordinal format; 1 results in first.
TH	Converts a number to it's ordinal format. For example 1 becoms 1st.
TS	Short time format. Depends on NLS-settings. Use only with timestamp.
TZD	Abbreviated time zone name. ie PST.
TZH	Time zone hour displacement.
TZM	Time zone minute displacement.
TZR	Time zone region
X	Local radix character. In america this is a period (.)

 

 

Killing a session in Oracle

Sessions in oracle can be killed with the command:

SQL> alter system kill session 'sid,serial#';

The value for sid and serial# can be query'd using the following query:

select * from v$session or select sid, serial#, osuser, program from v$session;

After this command, the indicated session is marked for kill. When it's possible the session will be killed. Sometimes this can take a while (for example when a lot of rollback is needed). 
To kill a session faster you can use the keyword IMMEDIATE like this:

SQL> alter system kill session 'sid,serial#' immediate;

Oracle to_date function

The oracle to_date function converts a string in a specified format to an Oracle date format.

Syntax

to_date('formatted string'); (returns a date using the default oracle date format)
to_date('formatted string','format string'); (returns a date using the format string specified)
to_date('formatted string','format string','nls description');(returns a date using the format string specified and using the specified NLS settings)

Example:

to_date('01-JAN-2006');
to_date('01-01-2004','DD-MM-YYYY');
to_date('31-12-2006 23:34:59','DD-MM-YYYY HH24:MI:SS'); 
to_date('01-JAN-99''DD-MON-YY,'nls_date_language = American');

 

The Oracle substr function

The substr function is a function that returns a substring from a string.

syntax

substr([input],[start],[length]) or 
substr([input],[start]) or 
With input the String to take a substring from,
start is the starting position where 1 is the first character. (if you pass 0, this will be substituted by 1) and the optional length parameter is the number of characters in the substring. If length is left out, then substr will return the substring from position start till the end of the input-string.

Sample code:

select substr('1234567890',3,2) from dual; 
will return: '34'.

select substr('1234567890',7) from dual; 
will return: '7890'.

 

Oracle date functions

Oracle has a number of functions that apply to a date

Sysdate	Returns the current date/time
ADD_MONTHS	Function to add a number of months to a date. For example: add_months(SYSDATE,3) returns 3 months after sysdate. This could be rounded to below is the resulting month has fewer days than the month this function is applied to.
+,- (plus/minus)	In Oracle you can add or substract a number of days from a date. Example: sysdate+5 means systemdate/time plus 5 days
GREATEST	With the greatest function you can select the date/time that is the highest in a range of date/times. Example: greatest (sysdate+4,sysdate,sysdate-5) = sysdate+4.
LEAST	With the least function you can select the earliest date/time in a range of date/times. Example: least(sysdate+4,sysdate,sysdate-5) = sysdate-5.
LAST_DAY	Returns the last_day of a month based on the month the passed date is in. Example: last_day(sysdate) returns the last day of this month.
MONTHS_BETWEEN	Returns the number of months between two dates. The number is not rounded. Example: months_between(sysdate, to_date('01-01-2007','dd-mm-yyyy')) returns the number of months since jan 1, 2007.
NEXT_DAY	Date of next specified date following a date NEXT_DAY(, ) Options are SUN, MON, TUE, WED, THU, FRI, and SAT SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual; NOTE: This can be dependend on NLS_SETTINGS!
ROUND	Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day ROUND(, ) SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR FROM dual;
TRUNC	Convert a date to the date without time (0:00h) Example: TRUNC(sysdate) returns today without time.
First day of the month.	trunc(example_date,'MM') Example: select trunc(TO_DATE('31-JAN-2007'),'MM') FROM dual;
TO_CHAR(date,format_mask)	Converts a date to a string using a format mask. Format masks are explained

 

Oracle pl/sql trim function

The trim function removed characters from beginning and/or end of a string in Oracle. Oracle has 3 functions for this:

TRIM

The TRIM function trims specified characters from the left and/or right.
If no characters are specified, the left and right spaces are left out.
Example: trim(' Oradev dot com ') = 'Oradev dot com'.
Another option is:
trim(trailing 'a' from 'aaaabbaaaa') which results in 'aaaabb' or
trim(leading 'a' from 'aaaabbaaaa') which results in 'bbaaaa' or
trim(both 'a' from 'aaaabbaaaa') which results in 'bb'.

LTRIM

LTRIM removes characters from the left of a string if they are equal to the specified string. Example: ltrim('aaaaaabc','a') = 'bc' If the last parameter is not specified, spaces are removed from the left side.

RTRIM

RTRIM removes characters from the right of a string if they are equal to the specified string. Example: rtrim('bcaaaaaa','a') = 'bc' If the last parameter is not specified, spaces are removed from the right side.

 

 

Oracle pl/sql

declare string_to_parse varchar2(2000) := 'abc,def,ghi,klmno,pqrst'; l_count number; l_value varchar2(2000); begin string_to_parse := string_to_parse||','; l_count := length(string_to_parse) - length(replace(string_to_parse,',','')); -- In oracle 11g use regexp_count to determine l_count for i in 1 .. l_count loop select regexp_substr(string_to_parse,'[^,]+',1,i) into l_value from dual; dbms_output.put_line(l_value); end loop; end;

XML Functions

Oracle provides XML functions to operate on or return XML documents or fragments.
Here is a list of all the XML functions (in version 10.2):

FunctionUsageDescription

APPENDCHILDXML	APPENDCHILDXML(XMLTYPE_instance,XPath_string,value_expr) or APPENDCHILDXML(XMLTYPE_instance,XPath_string,value_expr,namespace_string)
APPENDCHILDXML appends a user-supplied value onto the target XML as the child of the node indicated by an XPath expression. XMLType_instance is an instance of XMLType. The XPath_string is an Xpath expression indicating one or more nodes onto which one or more child nodes are to be appended. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node. The value_expr specifies one or more nodes of XMLType. It must resolve to a string. The optional namespace_string provides namespace information for the XPath_string. This parameter must be of type VARCHAR2.
DELETEXML	DELETEXML(XMLTYPE_instance, XPath_string) or DELETEXML(XMLTYPE_instance, XPath_string, namespace_string)
DELETEXML deletes the node or nodes matched by the XPath expression in the target XML. XMLType_instance is an instance of XMLType. The XPath_string is an Xpath expression indicating one or more nodes that are to be deleted. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node. Any child nodes of the nodes specified by XPath_string are also deleted. The optional namespace_string provides namespace information for the XPath_string. This parameter must be of type VARCHAR2.
DEPTH	DEPTH(correlation_integer)
DEPTH is an ancillary function used only with the UNDER_PATH and EQUALS_PATH conditions. It returns the number of levels in the path specified by the UNDER_PATH condition with the same correlation variable. The correlation_integer can be any NUMBER integer. Use it to correlate this ancillary function with its primary condition if the statement contains multiple primary conditions. Values less than 1 are treated as 1.
EXTRACT (XML)	EXTRACT(XMLTYPE_instance,XPath_string) or EXTRACT(XMLTYPE_instance,XPath_string, namespace_string)
EXTRACT (XML) is similar to the EXISTSNODE function. It applies a VARCHAR2 XPath string and returns an XMLType instance containing an XML fragment. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node. The optional namespace_string must resolve to a VARCHAR2 value that specifies a default mapping or namespace mapping for prefixes, which Oracle Database uses when evaluating the XPath expression(s).
EXISTSNODE	EXISTSNODE(XMLTYPE_instance,XPath_string) or EXISTSNODE(XMLTYPE_instance,XPath_string, namespace_string)
EXISTSNODE determines whether traversal of an XML document using a specified path results in any nodes. It takes as arguments the XMLType instance containing an XML document and a VARCHAR2 XPath string designating a path. The optional namespace_string must resolve to a VARCHAR2 value that specifies a default mapping or namespace mapping for prefixes, which Oracle Database uses when evaluating the XPath expression(s).  The namespace_string argument defaults to the namespace of the root element. If you refer to any subelement in Xpath_string, then you must specify namespace_string, and you must specify the "who" prefix in both of these arguments.
EXTRACTVALUE	EXTRACTVALUE(XMLTYPE_instance,XPath_string) or EXTRACTVALUE(XMLTYPE_instance,XPath_string, namespace_string)
The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node. The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.
INSERTCHILDXML	INSERTCHILDXML(XMLTYPE_instance,XPath_string, child_expr, value_expr) or  INSERTCHILDXML(XMLTYPE_instance,XPath_string, child_expr, value_expr, namespace_string)
INSERTCHILDXML inserts a user-supplied value into the target XML at the node indicated by the XPath expression. Compare this function with INSERTXMLBEFORE. XMLType_instance is an instance of XMLType.  The XPath_string is an Xpath expression indicating one or more nodes into which the one or more child nodes are to be inserted. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.  The child_expr specifies the one or more element or attribute nodes to be inserted.  The value_expr is an fragment of XMLType that specifies one or more notes being inserted. It must resolve to a string.  The optional namespace_string provides namespace information for the XPath_string. This parameter must be of type VARCHAR2.
INSERTXMLBEFORE	INSERTXMLBEFORE(XMLTYPE_instance,XPath_string, value_expr) or INSERTXMLBEFORE(XMLTYPE_instance,XPath_string, value_expr, namespace_string)
INSERTXMLBEFORE inserts a user-supplied value into the target XML before the node indicated by the XPath expression. Compare this function with INSERTCHILDXML.  XMLType_instance is an instance of XMLType.  The XPath_string is an Xpath expression indicating one or more nodes into which one or more child nodes are to be inserted. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.  The value_expr is a fragment of XMLType that defines one or more nodes being inserted and their position within the parent node. It must resolve to a string.  The optional namespace_string provides namespace information for the XPath_string. This parameter must be of type VARCHAR2.
PATH	PATH(correlation_integer)
PATH is an ancillary function used only with the UNDER_PATH and EQUALS_PATH conditions. It returns the relative path that leads to the resource specified in the parent condition. The correlation_integer can be any NUMBER integer and is used to correlate this ancillary function with its primary condition. Values less than 1 are treated as 1.
SYS_DBURIGEN	SYS_DBURIGEN(column,text)
SYS_DBURIGen takes as its argument one or more columns or attributes, and optionally a rowid, and generates a URL of datatype DBURIType to a particular column or row object. You can then use the URL to retrieve an XML document from the database.
SYS_XMLAGG	SYS_XMLAGG(expr) or SYS_XMLAGG(expr,fmt)
SYS_XMLAgg aggregates all of the XML documents or fragments represented by expr and produces a single XML document. It adds a new enclosing element with a default name ROWSET. If you want to format the XML document differently, then specify fmt, which is an instance of the XMLFormat object.
SYS_XMLGEN	SYS_XMLGEN(expr) or SYS_XMLGEN(expr,fmt)
SYS_XMLGen takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType containing an XML document. The expr can be a scalar value, a user-defined type, or an XMLType instance.  If expr is a scalar value, then the function returns an XML element containing the scalar value.  If expr is a type, then the function maps the user-defined type attributes to XML elements.  If expr is an XMLType instance, then the function encloses the document in an XML element whose default tag name is ROW.  By default the elements of the XML document match the elements of expr. For example, if expr resolves to a column name, then the enclosing XML element will be the same column name. If you want to format the XML document differently, then specify fmt, which is an instance of the XMLFormat object.
UPDATEXML	UPDATEXML(XMLTYPE_instance,XPath_string, value_expr) or UPDATEXML(XMLTYPE_instance,XPath_string, value_expr, namespace_string)
UPDATEXML takes as arguments an XMLType instance and an XPath-value pair and returns an XMLType instance with the updated value. If XPath_string is an XML element, then the corresponding value_expr must be an XMLType instance. If XPath_string is an attribute or text node, then the value_expr can be any scalar datatype. You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node. The datatypes of the target of each XPath_string and its corresponding value_expr must match. The optional namespace_string must resolve to a VARCHAR2 value that specifies a default mapping or namespace mapping for prefixes, which Oracle Database uses when evaluating the XPath expression(s).
XMLAGG	XMLAGG(XMLTYPE_instance) or  XMLAGG(XMLTYPE_instance, order_by_clause)
XMLAgg is an aggregate function. It takes a collection of XML fragments and returns an aggregated XML document. Any arguments that return null are dropped from the result.  XMLAgg is similar to SYS_XMLAgg except that XMLAgg returns a collection of nodes but it does not accept formatting using the XMLFormat object. Also, XMLAgg does not enclose the output in an element tag as does SYS_XMLAgg.  Within the order_by_clause, Oracle Database does not interpret number literals as column positions, as it does in other uses of this clause, but simply as number literals.
XMLCDATA	XMLCDATA(value_expr)
XMLCData generates a CDATA section by evaluating value_expr. The value_expr must resolve to a string. The value returned by the function takes the following form:  If the resulting value is not a valid XML CDATA section, then the function returns an error.The following conditions apply to XMLCData:  The value_expr cannot contain the substring ]]>.  If value_expr evaluates to null, then the function returns null.
XMLCOLATTVAL	XMLCOLATTVAL(value_expr AS c_alias) or XMLCOLATTVAL(value_expr AS c_alias, value_expr AS c_alias,..)
XMLColAttVal creates an XML fragment and then expands the resulting XML so that each XML fragment has the name column with the attribute name. You can use the AS c_alias clause to change the value of the name attribute to something other than the column name. You must specify a value for value_expr. If value_expr is null, then no element is returned. Restriction on XMLColAttVal: You cannot specify an object type column for value_expr.
XMLCOMMENT	XMLCOMMENT(value_expr)
XMLComment generates an XML comment using an evaluated result of value_expr. The value_expr must resolve to a string. It cannot contain two consecutive dashes (hyphens). The value returned by the function takes the following form:  <!--string-->  If value_expr resolves to null, then the function returns null.
XMLCONCAT	XMLCONCAT(XMLTYPE_instance) or XMLCONCAT(XMLTYPE_instance, XMLTYPE_instance, ...)
XMLConcat takes as input a series of XMLType instances, concatenates the series of elements for each row, and returns the concatenated series. XMLConcat is the inverse of XMLSequence.  Null expressions are dropped from the result. If all the value expressions are null, then the function returns null.
XMLFOREST	XMLFOREST(value_expr AS c_alias) or XMLFOREST(value_expr AS c_alias, value_expr AS c_alias,..)
XMLForest converts each of its argument parameters to XML, and then returns an XML fragment that is the concatenation of these converted arguments.  If value_expr is a scalar expression, then you can omit the AS clause, and Oracle Database uses the column name as the element name.  If value_expr is an object type or collection, then the AS clause is mandatory, and Oracle uses the specified c_alias as the enclosing tag. The c_alias can be up to 4000 characters.  If value_expr is null, then no element is created for that value_expr.
XMLPARSE	XMLPARSE(DOCUMENT, value_expr) or XMLPARSE(DOCUMENT, value_expr WELLFORMED) or XMLPARSE(CONTENT, value_expr) or XMLPARSE(CONTENT, value_expr WELLFORMED)
XMLParse parses and generates an XML instance from the evaluated result of value_expr. The value_expr must resolve to a string. If value_expr resolves to null, then the function returns null.  If you specify DOCUMENT, then value_expr must resolve to a singly rooted XML document.  If you specify CONTENT, then value_expr must resolve to a valid XML value.  When you specify WELLFORMED, you are guaranteeing that value_expr resolves to a well-formed XML document, so the database does not perform validity checks to ensure that the input is well formed.
XMLPI	XMLPI(identifier) or XMLPI(NAME identifier) or XMLPI(NAME identifier , value_expr)
XMLPI generates an XML processing instruction using identifier and optionally the evaluated result of value_expr. A processing instruction is commonly used to provide to an application information that is associated with all or part of an XML document. The application uses the processing instruction to determine how best to process the XML document.
XMLQUERY	XMLQUERY(XQuery_string RETURNING CONTENT) or XMLQUERY(XQuery_string XML_passing_clause RETURNING CONTENT)
XMLQUERY lets you query XML data in SQL statements. It takes an XQuery expression as a string literal, an optional context item, and other bind variables and returns the result of evaluating the XQuery expression using these input values.  XQuery_string is a complete XQuery expression, including prolog.  The expr in the XML_passing_clause is an expression returning an XMLType that is used as the context for evaluating the XQuery expression. You can specify only one expr in the PASSING clause without an identifier. The result of evaluating each expr is bound to the corresponding identifier in the XQuery_string. If any expr that is not followed by an AS clause, then the result of evaluating that expression is used as the context item for evaluating the XQuery_string.  RETURNING CONTENT indicates that the result from the XQuery evaluation is either an XML 1.0 document or a document fragment conforming to the XML 1.0 semantics.
XMLROOT	XMLROOT(...)
XMLROOT lets you create a new XML value by providing version and standalone properties in the XML root information (prolog) of an existing XML value. If the value_expr already has a prolog, then the database returns an error. If the input is null, then the function returns null.
XMLSEQUENCE	XMLSEQUENCE(XMLTYPE_instance) or XMLSEQUENCE(sys_refcursor_instance) or XMLSEQUENCE(sys_refcursor_instance, fmt)
XMLSequence has two forms:  The first form takes as input an XMLType instance and returns a varray of the top-level nodes in the XMLType. This form is effectively superseded by the SQL/XML standard function XMLTable, which provides for more readable SQL code. Prior to Oracle Database 10g Release 2, XMLSequence was used with SQL function TABLE to do some of what can now be done better with the XMLTable function.  The second form takes as input a REFCURSOR instance, with an optional instance of the XMLFormat object, and returns as an XMLSequence type an XML document for each row of the cursor.  Because XMLSequence returns a collection of XMLType, you can use this function in a TABLE clause to unnest the collection values into multiple rows, which can in turn be further processed in the SQL query.
XMLSERIALIZE	XMLSERIALIZE(DOCUMENT, value_expr) or XMLSERIALIZE(DOCUMENT, value_expr AS datatype) or XMLSERIALIZE(CONTENT, value_expr) or XMLSERIALIZE(CONTENT, value_expr AS datatype)
XMLSerialize creates a string or LOB containing the contents of value_expr.  If you specify DOCUMENT, then the value_expr must be a valid XML document.  If you specify CONTENT, then the value_expr need not be a singly rooted XML document. However it must be valid XML content.  The datatype specified can be a string type (VARCHAR2 or VARCHAR, but not NVARCHAR or NVARCHAR2) or CLOB . The default is CLOB.
XMLTABLE	XMLTABLE(XML_namespaces_clause, XQuery_string XMLTABLE_options) XMLTABLE(XQuery_string XMLTABLE_options)
XMLTable maps the result of an XQuery evaluation into relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL.
XMLTRANSFORM	XMLTRANSFORM(XMLTYPE_instance,XMLTYPE_instance)
XMLTransform takes as arguments an XMLType instance and an XSL style sheet, which is itself a form of XMLType instance. It applies the style sheet to the instance and returns an XMLType.  This function is useful for organizing data according to a style sheet as you are retrieving it from the database.