存储过程直接读取EXCEL插入表中

显示全部楼层 · 2010-10-8 09:28:52

create or replace and compile java source named readexcel as
import jxl.Sheet;
import jxl.Cell;
import java.io.InputStream;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import jxl.Workbook;
public class ReadExcel
{

public static String read() {
String str = "";

String elementrq = null, elementecbm = null, elementch = null;

jxl.Workbook rwb = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver&quot

;

InputStream is = new FileInputStream("C:/tmp/abc.xls&quot

;

rwb = Workbook.getWorkbook(is);

Sheet rs = rwb.getSheet(0);

int rsColumns = rs.getColumns();

int rsRows = rs.getRows();

Connection con = DriverManager.getConnection(

"jdbc

racle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger&quot

;

Statement s = con.createStatement();

for (int i = 1; iExecute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.io.FilePermission',
'>',
'execute');
SQL> execute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.lang.RuntimePermission',
'writeFileDescriptor',
'*' );
SQL> execute dbms_java.grant_permission( 'YOUR_DATABASE_USER',
'SYS:java.lang.RuntimePermission',
'readFileDescriptor',
'*' );
SQL> Commit;

4. Use it

Set Serverout On
Declare
x Varchar2(2000);
Begin
x := OSCommand_Run('/usr/bin/who > /home/test');
DBMS_OUTPUT.Put_Line(x);
End;
/

千问 · 2010-10-8 09:28:52

另外，可以得到默认的数据库连接：
Connection conn = DriverManager.getConnection("jdbc:default:connection:");