SQL로 OS 자원을 조작하고 싶을 때 - Java Stored Procedure의 활용
출처: http://ukja.tistory.com/309SQL과 PL/SQL의 다양한 기능에 익숙해지다보면 Oracle이 설치되어 있는 OS 시스템의 자원을 SQL이나 PL/SQL로 제어하고 싶은 욕구가 생깁니다. SQL과 PL/SQL의 활용을 극대화하고 싶은 자연스러운 욕구입니다. Java Stored Procedure가 이런 요구사항을 해결하는데 가장 강력한 도구입니다.
실제로 제가 개발하고 있는 트러블슈팅팩이라는 라이브러리도 내부적으로 Java Stored Procedure를 폭넓게 활용하고 있습니다.
간단한 예를 들어 설명해보겠습니다. 다음과 같은 요구사항이 있습니다.
- Oracle이 설치된 시스템의 특정 폴더의 파일 리스트를 SQL을 이용해서 얻고 싶습니다.
01 |
UKJA@ukja1106> connect sys/oracle@ukja1106 as sysdba |
02 |
Connected. |
03 |
|
04 |
Session altered. |
05 |
|
06 |
Elapsed: 00:00:00.00 |
07 |
|
08 |
SID SERIAL# PID |
09 |
---------- ---------- ---------- |
10 |
129 12732 12376 |
11 |
|
12 |
Elapsed: 00:00:00.00 |
13 |
SYS@ukja1106> |
14 |
SYS@ukja1106> exec dbms_java.grant_permission('UKJA', 'SYS:java.io.FilePermission', - |
15 |
> 'c:\temp', 'read ,write, execute, delete'); |
16 |
|
17 |
PL/SQL procedure successfully completed. |
18 |
|
19 |
Elapsed: 00:00:00.03 |
20 |
SYS@ukja1106> |
21 |
SYS@ukja1106> connect ukja/ukja@ukja1106 |
22 |
Connected. |
23 |
|
24 |
Session altered. |
25 |
|
26 |
Elapsed: 00:00:00.00 |
27 |
|
28 |
SID SERIAL# PID |
29 |
---------- ---------- ---------- |
30 |
129 12734 10400 |
31 |
|
32 |
Elapsed: 00:00:00.00 |
33 |
UKJA@ukja1106> |
34 |
UKJA@ukja1106> create or replace and compile java source named FileList |
35 |
2 as |
36 |
3 import java.io.*; |
37 |
4 import java.util.*; |
38 |
5 import java.sql.*; |
39 |
6 import oracle.sql.*; |
40 |
7 |
41 |
8 public class FileList { |
42 |
9 |
43 |
10 public static oracle.sql.ARRAY getFileList() throws Exception { |
44 |
11 |
45 |
12 File f = new File("c:\\temp"); |
46 |
13 String[] flist = f.list(); |
47 |
14 |
48 |
15 Connection conn = DriverManager.getConnection("jdbc:default:connection:"); |
49 |
16 |
50 |
17 ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", conn); |
51 |
18 ARRAY array = new ARRAY(desc, conn, flist); |
52 |
19 return array; |
53 |
20 } |
54 |
21 |
55 |
22 } |
56 |
23 ; |
57 |
24 / |
58 |
|
59 |
Java created. |
60 |
|
61 |
Elapsed: 00:00:00.18 |
62 |
UKJA@ukja1106> |
63 |
UKJA@ukja1106> create or replace type varchar2_array as table of varchar2(1000); |
64 |
2 / |
65 |
|
66 |
Type created. |
67 |
|
68 |
Elapsed: 00:00:00.01 |
69 |
UKJA@ukja1106> |
70 |
UKJA@ukja1106> |
71 |
UKJA@ukja1106> create or replace function get_file_list |
72 |
2 return varchar2_array |
73 |
3 as language java |
74 |
4 name 'FileList.getFileList() return oracle.sql.ARRAY'; |
75 |
5 / |
76 |
|
77 |
Function created. |
78 |
|
79 |
Elapsed: 00:00:00.00 |
80 |
UKJA@ukja1106> |
81 |
UKJA@ukja1106> select * from table(get_file_list); |
82 |
|
83 |
COLUMN_VALUE |
84 |
-------------------------------------------------------------------------------- |
85 |
1269319491031.sql |
86 |
1269319491031.txt |
87 |
20100323.log |
88 |
343225818.out |
89 |
343225818.trc |
90 |
|
91 |
Elapsed: 00:00:00.03 |
92 |
UKJA@ukja1106> |