Tuesday, March 23, 2010

SQLでOSの資源を操作したい時 - Java Stored Procedureの活用

SQLとPL/SQLの多様な機能に慣れてみればOracleがインストールされているOSシステムの資源をSQLやPL/SQLで制御したくなります。SQLとPL/SQLの活用を極大化したい自然な願いだと思います。Java Stored Procedureがこのような要求事項を解決するに一番強力なツールと言えます。


実際に私が開発しているトラブルシューチングパックというライブラリーも内部的にJava Stored Procedureを幅広く活用しています。


簡単な例を通じて説明してみます。次のような要求事項があります。


  • オラクルが設置されたシステムの特定のフォルダーのファイルリストをSQL分を通じて得たいです。

次のように解決できます。

UKJA@ukja1106> connect sys/oracle@ukja1106 as sysdba
Connected.

Session altered.

Elapsed: 00:00:00.00

SID SERIAL# PID
---------- ---------- ----------
129 12732 12376

Elapsed: 00:00:00.00
SYS@ukja1106>
SYS@ukja1106> exec dbms_java.grant_permission('UKJA', 'SYS:java.io.FilePermission', -
> 'c:\temp', 'read ,write, execute, delete');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SYS@ukja1106>
SYS@ukja1106> connect ukja/ukja@ukja1106
Connected.

Session altered.

Elapsed: 00:00:00.00

SID SERIAL# PID
---------- ---------- ----------
129 12734 10400

Elapsed: 00:00:00.00
UKJA@ukja1106>
UKJA@ukja1106> create or replace and compile java source named FileList
2 as
3 import java.io.*;
4 import java.util.*;
5 import java.sql.*;
6 import oracle.sql.*;
7
8 public class FileList {
9
10 public static oracle.sql.ARRAY getFileList() throws Exception {
11
12 File f = new File("c:\\temp");
13 String[] flist = f.list();
14
15 Connection conn = DriverManager.getConnection("jdbc:default:connection:");
16
17 ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARCHAR2_ARRAY", conn);
18 ARRAY array = new ARRAY(desc, conn, flist);
19 return array;
20 }
21
22 }
23 ;
24 /

Java created.

Elapsed: 00:00:00.18
UKJA@ukja1106>
UKJA@ukja1106> create or replace type varchar2_array as table of varchar2(1000);
2 /

Type created.

Elapsed: 00:00:00.01
UKJA@ukja1106>
UKJA@ukja1106>
UKJA@ukja1106> create or replace function get_file_list
2 return varchar2_array
3 as language java
4 name 'FileList.getFileList() return oracle.sql.ARRAY';
5 /

Function created.

Elapsed: 00:00:00.00
UKJA@ukja1106>
UKJA@ukja1106> select * from table(get_file_list);

COLUMN_VALUE
--------------------------------------------------------------------------------
1269319491031.sql
1269319491031.txt
20100323.log
343225818.out
343225818.trc

Elapsed: 00:00:00.03
UKJA@ukja1106>

本当に簡単で強いせす。活用によって多くの作業を自動化できるはずです。

3 comments:

  1. こんにちは。
    日本在住のシステムエンジニアでZingBayと申します。

    記事の内容、興味を覚えました。
    これからじっくり読みたいと思います。

    さて、記事の内容にも興味を覚えたのですが
    それに加えてSQLPLUSの画面に興味があります。
    行単位のコントラストとか、どうやって着けている
    のですか?
    何か特別なツールをお使いとか?

    もし宜しければご教授下さい。

    ReplyDelete
  2. 次のURLを参照して下さい。

    http://alexgorbatchev.com/wiki/SyntaxHighlighter:Hosting

    SyntaxHighlighterというオープンソースライブラリーで、Hostingバージョンを使えばBlogで適用できます。

    ReplyDelete
  3. blogで使う時に加工されているのですね。
    てっきり、あのような表示ができるSqlPlusがリリース
    されているのかと思いました。
    有難うございます

    ReplyDelete