PL/SQL 调用操作系统命令一例——导入导出

1. 创建用户并授权

 

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

 

C:\Documents and Settings\IBM>set oracle_sid=orasid

 

C:\Documents and Settings\IBM>sqlplus

 

SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 5月 9 11:19:04 2008

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

请输入用户名:  / as sysdba

 

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

 

SQL> create user yuechaotian identified by test default tablespace users;

 

用户已创建

 

SQL> grant dba to yuechaotian;

 

授权成功。

 

SQL> begin

  2    Dbms_Java.Grant_Permission('YUECHAOTIAN', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');
  3    Dbms_Java.Grant_Permission('YUECHAOTIAN', 'java.io.FilePermission', 'D:\oracle\ora92\bin\exp.exe', 'read ,write, execute, delete');
  4    dbms_java.grant_permission('YUECHAOTIAN', 'java.lang.RuntimePermission',   '*',    'writeFileDescriptor' );
  5  end;
  6  /

 

PL/SQL 过程已成功完成。

 

2. 在新用户下创建java过程

 

SQL> conn yuechaotian/test
已连接。

SQL> create or replace and compile java source named "Util"
  2  as
  3    import java.io.*;
  4    import java.lang.*;
  5    public class Util extends Object
  6    {
  7      public static int RunThis(String args)
  8      {
  9               Runtime rt = Runtime.getRuntime();
 10               int        rc = -1;
 11               try
 12               {
 13                       Process p = rt.exec(args);
 14                       int bufSize = 4096;
 15                       BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
 16                       int len;
 17                       byte buffer[] = new byte[bufSize];
 18                       while ((len = bis.read(buffer, 0, bufSize)) != -1)
 19                         System.out.write(buffer, 0, len);
 20                       rc = p.waitFor();
 21               }
 22               catch (Exception e)
 23               {
 24                 e.printStackTrace();
 25                 rc = -1;
 26               }
 27               finally
 28               {
 29                 return rc;
 30               }
 31      }
 32    }
 33  /

 

Java 已创建。

 

SQL> create or replace
  2  function RUN_CMD(p_cmd in varchar2) return number
  3     as
  4    language java
  5     name 'Util.RunThis(java.lang.String) return integer';
  6  /

 

函数已创建。

 

SQL> create or replace procedure RC(p_cmd in varchar2)
  2    as
  3      x number;
  4    begin
  5     x := run_cmd(p_cmd);
  6    end;
  7  /

 

过程已创建。

 

3. 测试一下导入导出的调用

3.1 创建测试表

 

SQL> create table test_tianyc(a) as select rownum from dba_objects where rownum < 6;

 

表已创建。

 

SQL> select * from test_tianyc;

 

         A
----------
         1
         2
         3
         4
         5

 

3.2 导出数据

 

SQL> set serveroutput on
SQL> exec dbms_java.set_output(100000);

 

PL/SQL 过程已成功完成。

 

SQL> exec rc('exp userid=yuechaotian/test@s9i tables=(test_tianyc) file=c:\mygirl.dmp');
java.security.AccessControlException: the Permission (java.io.FilePermission
<> execute) has not been granted to YUECHAOTIAN. The PL/SQL to grant
this is dbms_java.grant_permission( 'YUECHAOTIAN', 'SYS:java.io.FilePermission',
'<>',
'execute' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java)
at java.lang.SecurityManager.checkPermission(SecurityManager.java)
at
oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java
)
at java.lang.SecurityManager.checkExec(SecurityManager.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at java.lang.Runtime.exec(Runtime.java)
at Util.RunThis(Util.java:11)

 

PL/SQL 过程已成功完成。

 

SQL> conn / as sysdba
已连接。
SQL> exec dbms_java.grant_permission( 'YUECHAOTIAN', 'SYS:java.io.FilePermission','<>','execute' );

 

PL/SQL 过程已成功完成。

 

SQL> conn yuechaotian/test@s9i
已连接。
SQL> exec rc('exp userid=yuechaotian/test@s9i tables=(test_tianyc) file=c:\mygirl.dmp');

 

PL/SQL 过程已成功完成。

 

3.3 删除该表，执行导入。

 

SQL> drop table test_tianyc;

 

表已丢弃。

 

SQL> exec rc('imp userid=yuechaotian/test@s9i tables=(test_tianyc) fromuser=yuechaotian touser=yuechaotian file=c:\mygirl.dmp');

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_tianyc;

 

         A
----------
         1
         2
         3
         4
         5