Java System.currentTimeMillis()를 오라클에서

 select systimestamp

, to_number(sysdate - to_date('01-01-1970','DD-MM-YYYY')) * (24 * 60 * 60 * 1000) milliseconds

from dual

JBoss EAP 6.0 (jBoss Application Server 7.0)에서 오라클 DataSource 설정하기

** 참고: https://community.jboss.org/thread/169104

1. 오라클 JDBC 드라이버(ojdbc6.jar) 다운로드
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

2. jBoss Oracle JDBC 모듈 추가
ojdbc6.jar 파일을 ${JBOSS_HOME}/modules/com/oracle/ojdbc6/main 디렉토리에 복사
위 디렉토리에 module.xml 파일을 생성하고 아래 내용으로 저장

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.1" name="com.oracle.ojdbc6">
    <resources>
        <resource-root path="ojdbc6.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api" />
        <module name="javax.transaction.api"/>
        <module name="javax.servlet.api" optional="true"/>
    </dependencies>
</module>



3. DataSource 설정
${JBOSS_HOME}/standalone/configuration/standalone.xml에 DataSource 정보 추가
            <datasources>
                ...
                <datasource jndi-name="java:/TestDS" pool-name="TestDS" enabled="true">
                    <connection-url>jdbc:oracle:thin:@192.168.0.1:ORCL</connection-url>
                    <driver>oracle</driver>
                    <security>
                        <user-name>scott</user-name>
                        <password>tiger</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
                        <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
                    </validation>
                </datasource>
                <drivers>
                    ...
                    <driver name="oracle" module="com.oracle.ojdbc6">
                        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
                    </driver>
                </drivers>
            </datasources>

[oracle] 컬럼정보 조회

SELECT

 A.TABLE_NAME, NVL(A.COMMENTS, '') AS T_CON,

 B.COLUMN_NAME,  B.DATA_PRECISION, B.DATA_SCALE,

 DECODE(B.NULLABLE, 'Y', 'NULL', 'N', 'NOT NULL') AS NOT_NULL,

 NVL(C.COMMENTS, '') AS COL_CON,

 B.DATA_TYPE, B.DATA_LENGTH, B.COLUMN_ID

 , (

     SELECT DECODE(UC.CONSTRAINT_TYPE, 'P',POSITION , '0') PRIMARY_KEY

     FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC

     WHERE UCC.TABLE_NAME = UC.TABLE_NAME

     AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME

     AND UC.CONSTRAINT_TYPE = 'P'

     AND UCC.TABLE_NAME = A.TABLE_NAME

     AND UCC.COLUMN_NAME = B.COLUMN_NAME

 ) PRIMARY_KEY

 FROM

 USER_TAB_COMMENTS A, USER_TAB_COLUMNS B, USER_COL_COMMENTS C

 WHERE

 A.TABLE_NAME = B.TABLE_NAME

 AND A.TABLE_NAME = C.TABLE_NAME

 AND B.COLUMN_NAME = C.COLUMN_NAME

 AND A.TABLE_NAME = 'EMP'

 ORDER BY  A.TABLE_NAME, B.COLUMN_ID

[oracle] 암호화 툴킷 사용

오라클 암호화 툴킷 사용하기

1. sys 계정으로 로그인

su - oracle
sqlplus /nolog
conn sys/manager as sysdba

2. dbms_obfuscation_toolkit 툴킷 설치

@$ORACLE_HOME/rdbms/admin/dbmsobtk.sql
@$ORACLE_HOME/rdbms/admin/prvtobtk.plb

grant execute on dbms_obfuscation_toolkit to public;

3. 패키지 생성 (오라클 일반 유저 계정으로)

CREATE OR REPLACE PACKAGE CryptIT AS
FUNCTION encrypt( Str VARCHAR2,
hash VARCHAR2 ) RETURN VARCHAR2;
FUNCTION decrypt( xCrypt VARCHAR2,
hash VARCHAR2 ) RETURN VARCHAR2;
END CryptIT;
/

CREATE OR REPLACE PACKAGE BODY CryptIT AS
crypted_string VARCHAR2(2000);

FUNCTION encrypt( Str VARCHAR2,
hash VARCHAR2 ) RETURN VARCHAR2 AS
pieces_of_eight INTEGER := ((FLOOR(LENGTH(Str)/8 + .9)) * 8);

BEGIN

dbms_obfuscation_toolkit.DESEncrypt(
input_string => RPAD( Str, pieces_of_eight ),
key_string => RPAD(hash,8,'#'),
encrypted_string => crypted_string );
RETURN crypted_string;
END;

FUNCTION decrypt( xCrypt VARCHAR2,
hash VARCHAR2 ) RETURN VARCHAR2 AS
BEGIN
dbms_obfuscation_toolkit.DESDecrypt(
input_string => xCrypt,
key_string => RPAD(hash,8,'#'),
decrypted_string => crypted_string );
RETURN trim(crypted_string);
END;
END CryptIT;
/

[oracle] exp/imp

테이블 스페이스 만들기

CREATE TABLESPACE PMS
DATAFILE  'C:\oracle\oradata\orcl\pms.dbf' SIZE 50M
DEFAULT STORAGE (INITIAL 512K   NEXT 1M   MINEXTENTS 1   MAXEXTENTS 2147483645   PCTINCREASE 0);

유저생성

create user pms identified by pms123
default tablespace pms;
grant connect, resource to pms;

export

exp userid=system/manager@nice file='D:\backup\exp.dmp'
exp userid=system/manager@nonghyup file='D:\backup\nong.dmp'

import

imp userid=system/dhfkdhfk file='D:\backup\projects\pdms\nong.dmp' fromuser=pms touser=pms

Copy 명령어를 이용한 복사 (SQL Plus 상에서만 지원됨)

COPY FROM pms/pms123@nonghyup insert TB_LO_SERVER_RESOURCE USING select * FROM TB_LO_SERVER_RESOURCE;

오라클 빨리 죽이기

sqlplus /nolog
conn sys/manager@oracle as sysdba
shutdown immediate

sys 계정으로 로그인

su - oracle
sqlplus /nolog
conn sys/manager as sysdba

backup.bat

1. @echo off
2. SET TNSNAME=mytns
3. SET ID=scott
4. SET PASSWORD=tiger
5. SET DATE1=%date:~0,4%%date:~5,2%%date:~8,2%
6. SET TIME1=%time:~0,2%%time:~3,2%%time:~6,2%
7. SET backup_filename=%TNSNAME%-%DATE1%%TIME1%.dmp
8. 
   
9. echo %backup_filename%
10. 
    
11. exp userid=%TNSNAME%/%ID%@%PASSWORD% file='D:\backup\kddms\%backup_filename%'

[oracle] lag, lead, first_value, last_value

SELECT
MM
, LAG(MM) OVER (ORDER BY MM) AS PREV_MM
, LEAD(MM) OVER (ORDER BY MM) AS NEXT_MM
, FIRST_VALUE(MM) OVER (ORDER BY MM) AS FIRST_MM
, LAST_VALUE(MM) OVER (ORDER BY MM) AS LAST_MM
FROM (
    SELECT LPAD(LEVEL, 2, '0') AS MM FROM DUAL CONNECT BY LEVEL <= 12
    UNION ALL SELECT NULL FROM DUAL
    UNION ALL SELECT NULL FROM DUAL
)

[oracle] DDL 조회하기

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, '<USER_ID>') FROM ALL_TABLES
WHERE TABLE_NAME = '<TABLE_NAME>'

[oracle] CONNECT BY 연산자 사용방법

1부터 5까지 출력쿼리

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= 5;

출력
 1
 2
 3
 4
 5

 10부터 15까지 출력쿼리
SELECT LEVEL+9 FROM DUAL CONNECT BY LEVEL+9 < 16;
SELECT ROWNUM+9 FROM DUAL CONNECT BY LEVEL+9 < 16;

출력
 10
11
12
13
14
15

 20060101일부터 20060105일까지 출력쿼리
 SELECT TO_DATE('20060101', 'YYYYMMDD') + LEVEL - 1 FROM DUAL
 CONNECT BY LEVEL <= 10;

출력
 2006-01-01
2006-01-02
2006-01-03
2006-01-04
2006-01-05

 2003년부터 2007년까지 출력쿼리
 SELECT TO_CHAR(TO_CHAR(sysdate,'yyyy') -5 + ROWNUM) yyyy FROM dual
 CONNECT BY LEVEL <= 5;

출력
 2003
2004
2005
2006
2007

 ※ MySQL에서는 Connect By와 같은 효과를 낼수 없다.(꼬진 DBMS)

10개의 로우가 있는 테이블에 컬럼(NAME)을 추가하고 1부터 10을 넣는 방법
UPDATE 테이블 SET NAME = (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10);

1000~9999까지의 숫자중에 4개의 숫자합이 7이 되는 수는 몇개인지 체크하는 쿼리
 

select count(1) from (
 select * from
   (
   select
       num,
       trunc(num/1000) as first,
       trunc(mod(num,1000)/100) as second,
       trunc(mod(num,100)/10) as third,
       mod(num,10) as forth
   from (
     select 999+rownum as num from dual connect by level+999<10000
   )
 ) where first+second+third+forth = 7
 or first+second+third+forth in (07, 16, 25, 34, 43, 52, 61, 70)
 -- 4자리 수의 총합이 두자리인 경우는 다시 더한다고 가정
 );

 테이블에 테스트 데이타 쉽게 넣기
 
select level,dbms_random.string('A',20)
 from dual
 connect by level < 1000; <- 1000이라는 수치만 변경하면 됨
 
또는

 INSERT INTO test (a,b)
 SELECT NVL(a, 0) + level as a, 'aaa' || to_char(NVL(a, 0) + level) as b
 FROM (SELECT MAX(a) as a FROM test)
 CONNECT BY LEVEL <= 10; <- 10이라는 수치만 변경하면 됨

[oracle] DatabaseMetaData 를 이용해 테이블/필드 정보 조회

오라클에 접속해서 DatabaseMetaData 를 이용해 테이블/필드 정보를 가져올 경우 테이블/컬럼 코멘트 가져오도록 설정하기

((oracle.jdbc.driver.OracleConnection)connection).setRemarksReporting(true);

오라클에서 프로시저 호출

package org.javaya.test;

import java.sql.*;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

public class RefCursor {

public static void main(String[] args) {
RefCursor vTest = new RefCursor();
vTest.prepareCall();
}

void prepareCall() {

Connection conn = null;
CallableStatement cstmt = null;
OracleCallableStatement ocstmt = null;

try {
String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORA";
String user = "scott";
String password = "tiger";

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url, user, password);

// Stored Procedure 를 호출하기 위해 JDBC Callable Statement를 사용 합니다
String query = null;

query = "{call PROCUDURE_NAME(?, ?,?)}";

cstmt = conn.prepareCall(query);

// 프로시져의 In Parameter로 SELECT문장을 넘깁니다.
int x = 1;
cstmt.setString(x++, "001");
cstmt.setString(x++, "2008");

// CallableStatement를 위한 REF CURSOR OUTPUT PARAMETER를
// OracleTypes.CURSOR로 등록합니다.
int outIndex = x++;
cstmt.registerOutParameter(outIndex, OracleTypes.CURSOR);

// CallableStatement를 실행합니다.
cstmt.execute();

// getCursor() method를 사용하기 위해 CallableStatement를
// OracleCallableStatement object로 바꿉니다.
ocstmt = (OracleCallableStatement) cstmt;

// OracleCallableStatement 의 getCursor() method를 사용해서 REF CURSOR를
// JDBC ResultSet variable 에 저장합니다.
ResultSet cursor = ocstmt.getCursor(outIndex);
//ResultSet cursor = ocstmt.executeQuery();

// 쿼리결과 empno, ename 출력
ResultSetMetaData rsmd = cursor.getMetaData();
int columnCount = rsmd.getColumnCount();

for (int i=0; i<columnCount; i++) {
System.out.print(rsmd.getColumnName(i+1) + "\t");
}
System.out.println("");

int count = 0;
while (cursor.next()) {
System.out.print(count++ + "==>\t");
for (int i=0; i<columnCount; i++) {
System.out.print(cursor.getString(i+1) + "\t");
}
System.out.println("");
}

System.out.println("done!");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ocstmt.close();
cstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}