利用ODI加载,传输图象数据

先构建数据,把图片加载到Oracle:

 

localhost-> pwd
/tmp
localhost-> ls *.gif
iasicon24.gif  peop029.gif
localhost-> ls -l *.gif
-r-xr-xr-x  1 oracle oinstall 11902  2ÔÂ 10 16:49 iasicon24.gif
-r-xr-xr-x  1 oracle oinstall  5589  2ÔÂ 10 16:49 peop029.gif

 

SQL> select * from v$option where parameter ='Spatial';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
Spatial                                                          TRUE

SQL> create table test_spatial(name varchar2(10),content clob);

Table created.

SQL> conn / as sysdba
Connected

SQL> show user
USER is "SYS"

SQL> create or replace directory dir_spatial as '/tmp';

Directory created.

SQL> grant read,write on directory dir_spatial to scott;

Grant succeeded.

SQL> declare
  2          l_bfile bfile;
  3          l_clob  clob;
  4          l_str   varchar2(1000);
  5  begin
  6          insert into test_spatial (name,content)
  7          values ('iasicon24.gif',empty_clob())
  8          returning content into l_clob;
  9 
 10          l_bfile := bfilename('dir_spatial','iasicon24.gif');
 11 
 12          dbms_lob.fileopen(l_bfile);
 13          dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
 14          dbms_lob.fileclose(l_bfile);
 15          commit;
 16          exception when others then
 17          l_str:=sqlerrm(sqlcode);
 18          dbms_output.put_line(l_str);
 19  end;
 20  /

PL/SQL procedure successfully completed.

SQL> l
  1* select * from test_spatial
SQL> /

SQL> conn / as sysdba
Connected.
SQL> grant DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION to scott;

Grant succeeded.

创建存储过程做DEBUG：
create or replace
procedure spatial as
           l_bfile bfile;
          l_clob  clob;
          l_str   varchar2(1000);
  begin
          insert into test_spatial (name,content)
          values ('iasicon24.gif',empty_clob())
          returning content into l_clob;
 
           l_bfile := bfilename('DIR_SPATIAL','iasicon24.gif');
 
         dbms_lob.fileopen(l_bfile);
          dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
          dbms_lob.fileclose(l_bfile);
          commit;
          exception when others then
          l_str:=sqlerrm(sqlcode);
         dbms_output.put_line(l_str);
   end;

 

ORA-12899: 列 "SCOTT"."TEST_SPATIAL"."NAME" 的值太大 (实际值: 13, 最大值: 10)

SQL> conn scott/tiger
Connected.
SQL>
SQL> alter table test_spatial modify (name varchar2(30));

Table altered.

DEBUG:

ORA-22285: 对不存在的目录或文件进行 FILEOPEN 操作

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------
SYS                            DIR_SPATIAL                    /tmp
SYS                            IDR_DIR                        /u01/app/oracle/di
SYS                            SUBDIR                         /u01/app/oracle/pr
SYS                            XMLDIR                         /u01/app/oracle/pr
SYS                            LOG_FILE_DIR                   /u01/app/oracle/pr
SYS                            DATA_FILE_DIR                  /u01/app/oracle/pr
SYS                            MEDIA_DIR                      /u01/app/oracle/pr
SYS                            AUDIT_DIR                      /tmp/
SYS                            DATA_PUMP_DIR                  /u01/app/oracle/ad
SYS                            ORACLE_OCM_CONFIG_DIR          /u01/app/oracle/pr

10 rows selected.

再次修改以后

数据库是出来了，有乱码，郁闷

SQL> select * from test_spatial;

NAME
------------------------------
CONTENT
--------------------------------------------------------------------------------
iasicon24.gif
??????U???????????????????????????????????????????????????????????????????????

iasicon24.gif

创建另外的表

SQL> create table test_spatial2(name varchar2(30),content blob);

Table created.

create or replace
procedure spatial2 as
           l_bfile bfile;
          l_blob  blob;
          l_str   varchar2(1000);
  begin
          insert into test_spatial2 (name,content)
          values ('iasicon24.gif',empty_blob())
          returning content into l_blob;
 
           l_bfile := bfilename('DIR_SPATIAL','iasicon24.gif');
 
         dbms_lob.fileopen(l_bfile);
          dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
          dbms_lob.fileclose(l_bfile);
          commit;
          exception when others then
          l_str:=sqlerrm(sqlcode);
         dbms_output.put_line(l_str);
   end;

SQL> show user
USER is "SCOTT"
SQL> select * from test_spatial2;

NAME
------------------------------
CONTENT
------------------------------------------------------------------------------------------------------------------------
iasicon24.gif
474946383961A700B400E67F00DBDBDBABABABC2C2C2CCCCCCD3D4D3B3B3B3BDBDBD575757939393ABBDAD9B9B9B8C8C8C8383837A7A7AA49072D2DC
D3E2E3E2B2C3B4BBCABDFDFDFD74747424242561

SQL> create table georaster_table (type varchar2(40),GeoRaster MDSYS.SDO_GEORASTER);

Table created.

创建以后，字段类型长度不满足BLOB要求。

SQL> create table georaster_table (type varchar2(40),GeoRaster SDO_GEOMETRY);

Table created.

再次创建SDO_GEOMETRY类型，通过ODI操作也是不能导入的。

932 : 42000 : java.sql.SQLException: ORA-00932: 数据类型不一致: 应为 MDSYS.SDO_GEOMETRY, 但却获得 BLOB

java.sql.SQLException: ORA-00932: 数据类型不一致: 应为 MDSYS.SDO_GEOMETRY, 但却获得 BLOB

结论：
可以创建如下类型，把元数据和图像分离开来存储。
GeoRaster_Table表
RasterID Number
PyramIDLevel Number
BandBlockNumber Number
RowBlockNumber Number
ColumnBlockNumber Number
BlockMBR MDSYS.SDO_GEOMETRY
RasterBlock BLOB
 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/175005/viewspace-571478/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/175005/viewspace-571478/