カタカタブログ

SIerで働くITエンジニアがカタカタした記録を残す技術ブログ。Java, Oracle Database, Linuxが中心です。たまに数学やデータ分析なども。

PL/SQLからOSコマンド・シェルスクリプトを実行する

Oracle DB SQL Linux

PL/SQLでアプリを書いていると、たまにOSコマンドやシェルスクリプトを実行したくなる。
まあOracle DB上からJavaを実行することはできるので、どうにかできるかなあとは思っていると、
すでにPLSQL_OSCOMMANDなるものがあるらしい。

Oracle PL/SQL executing OS Commands
http://sourceforge.net/projects/plsqlexecoscomm/

しかもパブリック・ドメインライセンス。さっそく入れてみる。
環境はOracle DB11gR2 on CentOS6.5。

PLSQL_OSCOMMANDをインストールする

以下からPLSQL_OSCOMMAND_1.0.zipをダウンロードする
http://sourceforge.net/projects/plsqlexecoscomm/
ダウンロードしたファイルをDBサーバに配置し、unzipする

[oracle@localhost PLSQL_OSCOMMAND]$ pwd
/home/oracle/installer/PLSQL_OSCOMMAND
[oracle@localhost PLSQL_OSCOMMAND]$ ls -l
合計 216
-rw-r--r--. 1 oracle oinstall 219244 3月 20 15:03 2014 PLSQL_OSCOMMAND_1.0.zip
[oracle@localhost PLSQL_OSCOMMAND]$ unzip PLSQL_OSCOMMAND_1.0.zip

srcディレクトリに移動

[oracle@localhost PLSQL_OSCOMMAND]$ ls
PLSQL_OSCOMMAND_1.0.zip apidoc.zip changelog.txt readme.txt samples sqldeveloper src
[oracle@localhost PLSQL_OSCOMMAND]$ cd src

使用しているDBバージョンに応じたディレクトリに移動

[oracle@localhost src]$ ls
10g 11g 12c
[oracle@localhost src]$ cd 11g/
[oracle@localhost 11g]$ ls
01-java-source.sql 02-plsql-spec.sql 03-plsql-body.sql 04-grant-public.sql install.sql uninstall.sql

sysユーザでDBにログイン

[oracle@localhost 11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 20 15:31:54 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

インストールスクリプトを実行し、エラーなく終了することを確認

SQL> @install.sql
*
* ** **
* **** ****
* ** ** ******
* ** ** ** **
* ** ** *******
* ** ** **
* ****** ****** *******
*
*************************************************
** 1. Installing Java Code ...
**
... java source FILE_TYPE
No errors.
... compiling java FILE_TYPE
No errors.
... java source OS_COMMAND
No errors.
... compiling java OS_COMMAND
No errors.
*************************************************
** 2. PL/SQL Package Specs
**
... type spec FILE_TYPE
No errors.
... type spec FILE_LIST_TYPE
No errors.
... package spec FILE_PKG
No errors.
... package spec OS_COMMAND
No errors.
... package spec LOB_WRITER_PLSQL (deprecated)
No errors.
... package spec FILE_SECURITY
No errors.
... package spec FILE_PKG_VERSION
No errors.
*************************************************
** 3. PL/SQL Package Bodys
**
... type body FILE_TYPE
No errors.
... package body FILE_PKG
No errors.
... package body OS_COMMAND
No errors.
... package body LOB_WRITER_PLSQL (deprecated) ...
No errors.
... package body FILE_SECURITY
No errors.
... package body FILE_PKG_VERSION
No errors.
SQL>

以下のようにOSコマンドが実行できる

SQL> select os_command.exec_clob('pwd') cmd from dual;
CMD
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/dbs

普通にコマンドが打てた。結構使いやすそうなかんじ。

シェルスクリプトを実行する

続いてシェルスクリプトをたたいてみる。
まずhello, worldと出力するだけのシェル作成
hello.sh

#!/bin/bash
echo "hello,world"

実行結果は標準出力に"hello,world"するだけ。

[oracle@localhost ~]$ /home/oracle/hello.sh
hello,world

exec_clobで実行し標準出力を受け取る

SQL> select os_command.exec_clob('/home/oracle/hello.sh') ret from dual;
RET
--------------------------------------------------------------------------------
hello,world

execで実行しリターンコードを受け取る

SQL> select os_command.exec('/home/oracle/hello.sh') ret from dual;
 RET
----------
 0

シェルスクリプト実行もOK！
ただ、ソースを読む限りリターンコードと標準出力の両方を同時には受け取れないっぽい。

一般ユーザからも実行できるよう権限を付与

このままだとsysユーザしか使えないのでscottユーザでもやってみる。

[oracle@localhost ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 20 17:15:40 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select os_command.exec_clob('pwd') cmd from dual
       *
ERROR at line 1:
ORA-00904: "OS_COMMAND"."EXEC_CLOB": invalid identifier

scottユーザだと実行権限がないのでエラーになってしまう
標準で用意されている権限付与SQLをsysユーザで実行する

SQL> @04-grant-public.sql

Grant succeeded.
(・・・中略)
Synonym created.

再度scottユーザで実行

SQL> conn scott/tiger
Connected.
SQL> select os_command.exec_clob('pwd') cmd from dual;
ERROR:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
<<ALL FILES>> execute) has not been granted to SCOTT. The PL/SQL to grant this
is dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '<<ALL
FILES>>', 'execute' )
ORA-06512: at "SYS.OS_COMMAND", line 56



no rows selected

エラー！！
エラーメッセージを読む限り、ファイルパーミッションがないよう。dbms_java.grant_permissionを打てば付与できると言っているようにみえる。
sysユーザでエラーメッセージ中で指示されたdbms_java.grant_permissionプロシージャを実行

SQL> conn / as sysdba
Connected.
SQL> call dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '<<ALLFILES>>', 'execute' );

Call completed.

再度scottユーザで実行

SQL> conn scott/tiger
Connected.
SQL> select os_command.exec_clob('pwd') cmd from dual;
ERROR:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
<<ALL FILES>> execute) has not been granted to SCOTT. The PL/SQL to grant this
is dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '<<ALL
FILES>>', 'execute' )
ORA-06512: at "SYS.OS_COMMAND", line 56



no rows selected

変わらない。。。
READMEを読むと、任意のOSコマンド実行するためにはJAVASYSPRIV権限がいるとのこと！なので付与する。

SQL> conn / as sysdba
Connected.
SQL> grant javasyspriv to scott;

Grant succeeded.

再度scottユーザで実行

SQL> conn scott/tiger
Connected.
SQL> select os_command.exec_clob('pwd') cmd from dual;

CMD
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/dbs

今度こそ正しくscottユーザで実行できた！

その他の使い方

あとは、ファイル操作系の便利そうなパッケージも入っているので、いくつか基本的なところを試してみた。

  • 指定ディレクトリのファイル一覧を取得する
select * from table(file_pkg.get_file_list(file_pkg.get_file('/home/oracle/installer/PLSQL_OSCOMMAND'))) l

|FILE_PATH                                                     |FILE_NAME              |FILE_SIZE|LAST_MODIFIED|IS_DIR|IS_WRITEABLE|IS_READABLE|FILE_EXISTS|
------------------------------------------------------------------------------------------------------------------------------------------------------------
|/home/oracle/installer/PLSQL_OSCOMMAND/readme.txt             |readme.txt             |     1987|2013-12-10   |N     |Y           |Y          |Y          |
|/home/oracle/installer/PLSQL_OSCOMMAND/src                    |src                    |     4096|2013-07-15   |Y     |Y           |Y          |Y          |
|/home/oracle/installer/PLSQL_OSCOMMAND/sqldeveloper           |sqldeveloper           |     4096|2013-12-10   |Y     |Y           |Y          |Y          |
|/home/oracle/installer/PLSQL_OSCOMMAND/changelog.txt          |changelog.txt          |     2922|2013-12-10   |N     |Y           |Y          |Y          |
|/home/oracle/installer/PLSQL_OSCOMMAND/apidoc.zip             |apidoc.zip             |    38099|2013-12-11   |N     |Y           |Y          |Y          |
|/home/oracle/installer/PLSQL_OSCOMMAND/PLSQL_OSCOMMAND_1.0.zip|PLSQL_OSCOMMAND_1.0.zip|   219244|2014-03-20   |N     |Y           |Y          |Y          |
|/home/oracle/installer/PLSQL_OSCOMMAND/samples                |samples                |     4096|2013-12-10   |Y     |Y           |Y          |Y          |
※見やすさのためSQL実行結果を整形して表示している
  • ファイルを読み込む
SQL> select file_pkg.get_file('/home/oracle/hello.sh').get_content_as_clob('iso-8859-1') contents from dual;
CONTENTS
--------------------------------------------------------------------------------
#!/bin/bash
echo "hello,world"