先构建数据,把图片加载到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