Thursday, May 21, 2009
Leitura de Arquivos pelo PL/SQL e Java
Dúvida:
Marcio,
Bom dia!
Visitei seu BLOG e encontrei um exemplo pra inserir arquivo num campo BLOB numa tabela de banco de dados do ORACLE.
Gerar Arquivo de Campo BLOB
Funcionou, beleza, porem a minha necessidade seria ,inserir todos os arquivos de um determindo diretorio em uma tabela.
Voce teria alguma dica, ou algum exemplo que poderia me auxiliar ?
Desde já agradeco pela ajuda.
Em minha resposta imediata, informei ao colega que era necessário usar uma procedure java para ler os arquivos do diretório no nível do sistema operacional e, desse modo, usar a coleção da forma que lhe convier. Não sou desenvolvedor java, então fui obrigado a pesquisar, acabei encontrando um ótimo código que irei "emprestar" para meu exemplo. O site onde o encontrei é a asktom, o fonte não pertence ao Tom Kyte e sim ao Anthony Wilson da Austrália. Na mesma thread é possível encontrar outros exemplos assim como excelentes respostas do Tom sobre o assunto.
Eu achei interessante a solução do Anthony na medida em que a informação devolvida é completa. Ele usou um type com informação do tipo: se arquivo; se legível; se gravável; se escondido; tamanho do arquivo em bytes; e última modificação.
ops$marcio:DB11G> set lines 80
ops$marcio:DB11G> desc DIR_ENTRY
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_TYPE VARCHAR2(1)
READABLE VARCHAR2(1)
WRITEABLE VARCHAR2(1)
HIDDEN VARCHAR2(1)
FILE_SIZE NUMBER
MODIFIED DATE
NAME VARCHAR2(4000)
Meu exemplo dar-se-á em um Oracle 11g com o java (JVM) rodando sobre Linux EL5. Caso haja interesse em reproduzir o exemplo, por favor, cheque suas configurações e verifique se estão parecidas.
[marcio@pc scripts]$ uname -a
Linux pc.mportes.local 2.6.18-8.el5PAE #1 SMP Tue Jun 5 23:39:57 EDT 2007 i686 i686 i386 GNU/Linux
[marcio@pc scripts]$ cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5 (Carthage)
[marcio@pc scripts]$ sqlplus /
SQL*Plus: Release 11.1.0.7.0 - Production on Thu May 21 00:30:24 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ops$marcio:DB11G> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
5 rows selected.
ops$marcio:DB11G> select COMP_NAME, VERSION, STATUS from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle Application Express 3.2.0.00.27 VALID
OWB 11.1.0.6.0 VALID
Oracle Enterprise Manager 11.1.0.7.0 VALID
Oracle Ultra Search 11.1.0.7.0 VALID
OLAP Catalog 11.1.0.7.0 VALID
Spatial 11.1.0.7.0 VALID
Oracle Multimedia 11.1.0.7.0 VALID
Oracle XML Database 11.1.0.7.0 VALID
Oracle Text 11.1.0.7.0 VALID
Oracle Expression Filter 11.1.0.7.0 VALID
Oracle Rules Manager 11.1.0.7.0 VALID
Oracle Workspace Manager 11.1.0.7.0 VALID
Oracle Database Catalog Views 11.1.0.7.0 VALID
Oracle Database Packages and Types 11.1.0.7.0 VALID
JServer JAVA Virtual Machine 11.1.0.7.0 VALID
Oracle XDK 11.1.0.7.0 VALID
Oracle Database Java Packages 11.1.0.7.0 VALID
OLAP Analytic Workspace 11.1.0.7.0 VALID
Oracle OLAP API 11.1.0.7.0 VALID
Em resumo, após criar a procedure java para fazer a leitura dos arquivos no diretório, vou salvá-los em uma tabela. Note que é possível usar a cláusula WHERE para selecionar os arquivos de acordo com os campos do type DIR_ENTRY, exemplo: todos os arquivos a partir de uma data; trazer somente os arquivos visíveis, etc. Neste caso, vou subir todos meus archives '/oracle/arch'.
ops$marcio:DB11G> !ls -l /oracle/arch
total 2377612
-rw-r----- 1 oracle dba 100942848 May 20 02:23 1_100_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 02:23 1_101_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 15:33 1_102_683430221.dbf
-rw-r----- 1 oracle dba 18439168 May 20 21:20 1_103_683430221.dbf
-rw-r----- 1 oracle dba 57431552 Apr 27 22:15 1_17_683430221.dbf
-rw-r----- 1 oracle dba 90300416 May 17 19:56 1_90_683430221.dbf
-rw-r----- 1 oracle dba 73134080 May 17 21:35 1_91_683430221.dbf
-rw-r----- 1 oracle dba 9502208 May 19 00:03 1_92_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 19 00:39 1_93_683430221.dbf
-rw-r----- 1 oracle dba 42433536 May 19 14:20 1_94_683430221.dbf
-rw-r----- 1 oracle dba 19104768 May 19 20:17 1_95_683430221.dbf
-rw-r----- 1 oracle dba 100943872 May 20 00:48 1_96_683430221.dbf
-rw-r----- 1 oracle dba 100937216 May 20 02:22 1_97_683430221.dbf
-rw-r----- 1 oracle dba 100942336 May 20 02:22 1_98_683430221.dbf
-rw-r----- 1 oracle dba 100940288 May 20 02:22 1_99_683430221.dbf
ops$marcio:DB11G> create or replace type
2 dir_entry as object (
3 file_type varchar2(1),
4 readable varchar2(1),
5 writeable varchar2(1),
6 hidden varchar2(1),
7 file_size number,
8 modified date,
9 name varchar2(4000)
10 );
11 /
Type created.
ops$marcio:DB11G>
ops$marcio:DB11G> create or replace type dir_array as table of dir_entry;
2 /
Type created.
ops$marcio:DB11G>
ops$marcio:DB11G> create or replace and compile java source named "Util" as
2
3 import java.io.File;
4 import java.io.FilenameFilter;
5 import java.io.IOException;
6
7 import java.sql.Connection;
8 import java.sql.SQLException;
9 import java.sql.ResultSet;
10 import java.sql.PreparedStatement;
11 import java.sql.Timestamp;
12
13 import oracle.sql.ARRAY;
14 import oracle.sql.STRUCT;
15 import oracle.sql.ArrayDescriptor;
16 import oracle.sql.StructDescriptor;
17
18 import oracle.jdbc.driver.OracleDriver;
19
20 public class Util {
21
22 private static Connection conn;
23
24 static {
25 try {
26 conn = (new OracleDriver()).defaultConnection();
27 } catch (SQLException e) {
28 System.out.println(e);
29 }
30 }
31
32
33 /**
34 * List the files in the directory represented by the given Oracle DIRECTORY
35 * object.
36 *
37 * @param dirname The name of the DIRECTORY object for which we want to list
38 * the files (case sensitive).
39 * @throws IOException
40 * @throws SQLException
41 */
42 public static ARRAY listFiles(String dirname)
43 throws IOException, SQLException {
44
45 String dirpath = getDirectoryPath(dirname);
46 File directory = getDirectory(dirpath);
47
48 STRUCT[] ret = fileList(directory);
49
50 // Create an array descriptor and return it.
51 ArrayDescriptor desc = ArrayDescriptor.createDescriptor (
52 "DIR_ARRAY", conn);
53
54 return new ARRAY(desc, conn, ret);
55 }
56
57 /**
58 * Create a File object with the abstract pathname given by the parameter.
59 *
60 * @param dirpath The filesystem path of the directory
61 * @throws IOException If the directory represented by this pathname does
62 * not exist, or if it is a file.
63 */
64 private static File getDirectory(String dirpath) throws IOException {
65
66 File directory = new File(dirpath);
67
68 if(!directory.exists()) {
69 throw new IOException("Directory: "+dirpath+" does not exist.");
70 }
71 if(!directory.isDirectory()) {
72 throw new IOException("Path: "+dirpath+" is not a directory.");
73 }
74
75 return directory;
76 }
77
78 /**
79 * Get the filesystem path for the Oracle DIRECTORY object given by the
80 * input parameter.
81 *
82 * @param dir The name of the DIRECTORY object for which we want the path.
83 * @throws IOException If there is no DIRECTORY object with the given name.
84 */
85 private static String getDirectoryPath(String dir)
86 throws SQLException, IOException {
87 String sql = "select directory_path from all_directories where " +
88 "directory_name = ?";
89
90 PreparedStatement s = conn.prepareStatement(sql);
91 s.setString(1, dir);
92 ResultSet rs = s.executeQuery();
93
94 // There should be one row and one only.
95 if(rs.next()) {
96 return rs.getString(1);
97 } else {
98 throw new IOException("Directory object "+dir+" does not exist.");
99 }
100
101 }
102
103 /**
104 * Create an array of STRUCT objects representing the files in the given
105 * directory.
106 *
107 * @param directory The File object representing the directory.
108 * @throws SQLException
109 */
110 private static STRUCT[] fileList(File directory) throws SQLException {
111
112 // Create the array of files to add.
113 File[] files = directory.listFiles (
114 new FilenameFilter() {
115 // Accept all files
116 public boolean accept(File dirpath, String name) {
117 return true;
118 }
119 }
120 );
121
122 // No files in directory
123 if(files == null) {
124 return null;
125 }
126
127 STRUCT[] ret = new STRUCT[files.length];
128
129 // Create the struct entry for each file.
130 for(int i=0; i<files.length; i++) {
131 File f = files[i];
132 StructDescriptor sd = StructDescriptor.createDescriptor (
133 "DIR_ENTRY", conn);
134 Object[] attributes = {
135 f.isDirectory() ? "D" : (f.isFile() ? "F" : "U"),
136 f.canRead() ? "Y" : "N",
137 f.canWrite() ? "Y" : "N",
138 f.isHidden() ? "Y" : "N",
139 new Long(f.length()),
140 new Timestamp(f.lastModified()),
141 f.getName()
142 };
143 STRUCT s = new STRUCT(sd, conn, attributes);
144 ret[i] = s;
145 }
146
147 return ret;
148 }
149
150 }
151 /
Java created.
ops$marcio:DB11G> create or replace package util
2 as
3 function ls(p_dirname in varchar2) return dir_array;
4 end;
5 /
Package created.
ops$marcio:DB11G>
ops$marcio:DB11G> create or replace package body util
2 as
3 function ls(p_dirname in varchar2) return dir_array
4 is
5 language java name 'Util.listFiles(java.lang.String) return oracle.sql.ARRAY';
6 end;
7 /
Package body created.
ops$marcio:DB11G>
ops$marcio:DB11G> drop table t purge;
Table dropped.
ops$marcio:DB11G>
ops$marcio:DB11G> create table t ( n varchar2(40), x blob );
Table created.
ops$marcio:DB11G>
ops$marcio:DB11G> create or replace directory test_dir as '/oracle/arch';
Directory created.
ops$marcio:DB11G>
ops$marcio:DB11G> declare
2 l_blob blob;
3 l_bfile bfile;
4 l_dir varchar2(30) default 'TEST_DIR';
5 begin
6 for x in ( select name from table(util.ls(l_dir)))
7 loop
8 insert into t( n, x ) values ( x.name, empty_blob() ) returning x into l_blob;
9 l_bfile := bfilename( l_dir, x.name );
10 dbms_lob.fileopen( l_bfile );
11 dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
12 dbms_lob.fileclose( l_bfile );
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
ops$marcio:DB11G>
ops$marcio:DB11G> select n, dbms_lob.getlength(x) bytes from t;
N BYTES
---------------------------------------- -------------
1_17_683430221.dbf 57431552
1_97_683430221.dbf 100937216
1_101_683430221.dbf 100943872
1_92_683430221.dbf 9502208
1_98_683430221.dbf 100942336
1_103_683430221.dbf 18439168
1_94_683430221.dbf 42433536
1_102_683430221.dbf 100943872
1_100_683430221.dbf 100942848
1_96_683430221.dbf 100943872
1_95_683430221.dbf 19104768
1_90_683430221.dbf 90300416
1_99_683430221.dbf 100940288
1_91_683430221.dbf 73134080
1_93_683430221.dbf 100943872
15 rows selected.
É bom lembrar que se for a primeira execução, o exemplo possivelmente vai falhar. Propositalmente, deixei esta explicação para o final a fim de advertir que é necessário dar privilégio de gravação no diretório onde a procedure java fará a leitura. Esse tipo de situação deve ser observada já que toda a permissão, especialmente nesse nível, precisa ser inventariada e controlada. Portanto, cuidado quando sugerir esse tipo de implementação em produção. Seguindo o exemplo, vou modificar o caminho do DIRECTORY para provocar o erro e demonstrar como dar o acesso.
ops$marcio:DB11G> create or replace directory test_dir as '/oracle';
Directory created.
ops$marcio:DB11G> select name from table(util.ls('TEST_DIR'));
select name from table(util.ls('TEST_DIR'))
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
(java.io.FilePermission /oracle/admin write) has not been granted to OPS$MARCIO. The PL/SQL to grant this is
dbms_java.grant_permission( 'OPS$MARCIO', 'SYS:java.io.FilePermission', '/oracle/admin', 'write' )
ops$marcio:DB11G> exec dbms_java.grant_permission( 'OPS$MARCIO', 'SYS:java.io.FilePermission', '/oracle/*', 'write')
PL/SQL procedure successfully completed.
ops$marcio:DB11G> select name from table(util.ls('TEST_DIR'));
NAME
-----------------------------------------------
admin
lost+found
apex
arch
cfgtoollogs
oradata
oraInventory
11.1.0
diag
ewallet.p12
sqladm
oraInventory_10g
12 rows selected.
ops$marcio:DB11G> col name format a30
ops$marcio:DB11G> select * from table(util.ls('TEST_DIR'));
F R W H FILE_SIZE MODIFIED NAME
- - - - ------------- ------------------- ------------------------------
D Y N N 4096 25/07/2008 01:04:16 admin
D Y Y N 16384 07/05/2007 09:22:07 lost+found
D Y N N 4096 06/04/2009 01:04:34 apex
D Y N N 4096 21/05/2009 00:04:04 arch
D Y N N 4096 07/11/2008 19:53:34 cfgtoollogs
D Y N N 4096 05/11/2008 00:52:45 oradata
D Y Y N 4096 07/11/2008 19:48:13 oraInventory
D Y Y N 4096 19/06/2008 22:33:54 11.1.0
D Y Y N 4096 26/06/2008 01:45:37 diag
F N N N 9973 21/08/2008 00:51:39 ewallet.p12
D Y Y N 4096 27/09/2008 15:34:40 sqladm
D Y N N 4096 30/06/2008 22:16:28 oraInventory_10g
12 rows selected.
