e800

专题： SaaS时代危机下的企业 JAVA语言百度竞价排名 Windows 7 刀片式服务器危机移动嵌入串串烧

更多精彩专题

数据库

首页 | 数据库新闻 | 数据库开发 | 数据库应用 | Oracle | DB2 | SQL Server | Mysql | 数据库教程

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

2008-05-12 11:24:14.0 推荐:0 收藏:0 评论:0 来源:e800数据库频道

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

请输入用户名: / 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'', ''<<ALL FILE>>'', ''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
<<ALL FILES>> execute) has not been granted to YUECHAOTIAN. The PL/SQL to grant
this is dbms_java.grant_permission( ''YUECHAOTIAN'', ''SYS:java.io.FilePermission'',
''<<ALL FILES>>'',
''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)