SELECT TRIM(REGEXP_REPLACE(‘(010)-1!@1 1&%1-22**2 2’, ‘[^[:alnum:]+]’, ”))
FROM DUAL;

오라클 V$SQL에 관련해서 잘 설명한 블로그가 있어서 링크해놓는다.
http://doraeul.tistory.com/80 – 도래울님 블로그

select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(1) c from ‘||owner||’.’||table_name)),’/ROWSET/ROW/C’)) as count
from all_tables
where table_name like ‘%M40%’
;

테이블리스트를 가지고 테이블컬럼수를 가져오는 쿼리

SELECT
BB
,COUNT(1) AS COUNT_NORMAL
,COUNT(1) OVER(PARTITION BY CC) AS COUNT_OVER
,COUNT(CASE WHEN BB = CC THEN 1 ELSE NULL END) AS COUNT_CASE
FROM TABLE
GROUP BY BB

[출처] 오라클 컬럼추가삭제수정, 행제거, 치환, 데이터입력, 다중테이블 다중행입력, pivoting, 데이터수정|작성자 josua21
http://blog.naver.com/PostView.nhn?blogId=kg00jh&logNo=220608266261

COMMENT ON TABLE tableuser.tablename IS ‘this is tablecomment’;
COMMENT ON COLUMN tableuser.tablename.columnname IS ‘this is columncomment’;

alter table em04 add (email varchar2(10)) ;
alter table em04 modify(email varchar2(50)) ;
alter table em04 drop column email ;
alter table em04 set unused (comm) ; — comm 사용하지 못하도록 설정
alter table em04 drop unused column ; –사용되지 않는 컬럼을 삭제하라

WITH ALL_TAB_PRIMARY_KEY AS (
SELECT — 테이블의 PK컬럼
CONS.OWNER, COLS.TABLE_NAME, COLS.COLUMN_NAME, COLS.POSITION, CONS.STATUS, ‘P’ AS PRIMARY_KEY
FROM ALL_CONSTRAINTS CONS, ALL_CONS_COLUMNS COLS
WHERE CONS.CONSTRAINT_TYPE = ‘P’
AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
–AND CONS.TABLE_NAME = ‘(YOUR_TALBE_NAME)’
)
SELECT A.OWNER,A.TABLE_NAME,B.COMMENTS AS TABLE_DESC
,A.COLUMN_NAME,C.COMMENTS AS COLUMN_DESC,D.PRIMARY_KEY
,A.COLUMN_ID,A.DATA_TYPE,A.DATA_LENGTH,A.DATA_SCALE,A.NULLABLE
FROM ALL_TAB_COLUMNS A
,ALL_TAB_COMMENTS B
,ALL_COL_COMMENTS C
,ALL_TAB_PRIMARY_KEY D
WHERE A.OWNER = ‘(OWNER_NAME)’
AND A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME
AND A.OWNER = C.OWNER(+) AND A.TABLE_NAME = C.TABLE_NAME(+) AND A.COLUMN_NAME = C.COLUMN_NAME(+)
AND A.OWNER = D.OWNER(+) AND A.TABLE_NAME = D.TABLE_NAME(+) AND A.COLUMN_NAME = D.COLUMN_NAME(+)
AND A.TABLE_NAME IN ( ‘(YOUR_TALBE_NAME)’ )

LISTAGG를 사용해서 JOHN,LEE,SMITH,LEE,PARK,LEE 형식으로 데이터를 만들수 있다.
중간에 LEE 값을 중복제거를 하려면 REGEXP_REPLACE(CONTENTS,'([^,]+)(,\1)+’, ‘\1’) 를 사용해서 중복제거를 뽑아내자.

SELECT
DEPTNO
,LISTAGG(EMPNO, ‘,’) WITHIN GROUP (ORDER BY ROWNUM) AS ORIGINAL_DATA
,REGEXP_REPLACE(
LISTAGG(EMPNO, ‘,’) WITHIN GROUP (ORDER BY ROWNUM)
,'([^,]+)(,\1)+’, ‘\1′) AS REMOVE_DUPLICATE_DATA
— REGEXP_REPLACE(CONTENTS,'([^,]+)(,\1)+’, ‘\1’) — REMOVE THE DUPLICATE
FROM EMP_DATA
GROUP BY DEPTNO

만약에 WM_CONCAT을 사용할 수 있다면 간단하게 아래와 같이 DISTINCT를 집어넣으면 된다.

SELECT
DEPTNO
,wm_concat(name) names
,wm_concat(DISTINCT name) REMOVE_DUPLICATE_names
FROM EMP_DATA
GROUP BY DEPTNO

전문전송시 베트남어를 처리할 때 문제가 있음

,rpad(NVL(EMPLOYEE_NUMBER,’ ‘),10,’ ‘) AS EMPLOYEE
,SUBSTRB(RPAD(NVL(TO_MULTI_BYTE(EMPLOYEE_NAME),’ ‘),30 ,’ ‘) || RPAD(‘ ‘,30,’ ‘),1,30) AS EMPLOYEE_NAME
,SUBSTRB(RPAD(NVL(TO_MULTI_BYTE(EMPLOYEE_ENGLISH_NAME),’ ‘),150 ,’ ‘) || RPAD(‘ ‘,30,’ ‘),1,30) AS EMPLOYEE_ENGLISH_NAME

참고.
TO_MULTI_BYTE(), RPAD() 전각, 전문 채우기(http://dobioi.com/453)
PL/SQL 문자열 함수 정리 (http://deviant86.tistory.com/m/post/456)

oracle 10g
SELECT A2.TABLE_NAME, WM_CONCAT(A2.COLUMN_NAME) AS “COLUMN_NAME”
FROM ALL_CONSTRAINTS A1, ALL_CONS_COLUMNS A2
WHERE UPPER(A1.TABLE_NAME) LIKE ‘TB_M30%’
AND UPPER(A1.OWNER) = ‘MESUSER’
AND A1.TABLE_NAME = A2.TABLE_NAME
AND A1.OWNER = A2.OWNER
AND A1.CONSTRAINT_NAME = A2.CONSTRAINT_NAME
AND A1.CONSTRAINT_TYPE = ‘P’
GROUP BY A2.TABLE_NAME

———————————————————————-

oracle 11g
SELECT A2.TABLE_NAME, LISTAGG(A2.COLUMN_NAME, ‘,’) WITHIN GROUP (ORDER BY A2.POSITION) “COLUMN_NAME”
FROM ALL_CONSTRAINTS A1, ALL_CONS_COLUMNS A2
WHERE UPPER(A1.TABLE_NAME) LIKE ‘TB_M30%’
AND UPPER(A1.OWNER) = ‘MESUSER’
AND A1.TABLE_NAME = A2.TABLE_NAME
AND A1.OWNER = A2.OWNER
AND A1.CONSTRAINT_NAME = A2.CONSTRAINT_NAME
AND A1.CONSTRAINT_TYPE = ‘P’
GROUP BY A2.TABLE_NAME

오렌지에서는 정상적으로 실행되는데 서버올려놓고 실행하면 발생하는 문제
java.sql.SQLException: ORA-01861: literal does not match format string

to_char(add_months(pln_horizon_str_dt,1),’YYYY’)

to_char, to_date를 사용할 때, 두번 째 인자값을 명시하지 않으면
DBMS의 NLS_DATE_FORMAT 값에 따라 위의 에러가 갑툭튀 한다고 한다.

그래서 쿼리를 아래와 같이 변경했더니 제대로 돌아간다.
to_char(add_months(to_date(pln_horizon_str_dt,’yyyymmdd’),1),’YYYY’)