SqlLoader怎么使用
nbsp; 在unix上,行结束标记是n即CHR(10),而windows nt平台的行结束标记是rn即CHR(13)||CHR(10); select * from dept; 3)在infile指令在、上使用VAR属性,加载一个变宽文件,在该文件使用的格式中,每一行前几个字节指定了这一行的长度 --控制文件 load data infile demo.dat "var 3" --表明了前三个字节用于记录每一行的字节数 into table dept replace fields terminated by ',' trailing nullcols (deptno, dname "upper(:dname)", loc "upper(:loc)", comments ) --数据文件 05410,Sales,Virginia,this is the sales office in Virginia 注:在unix上换行符只算一个字节,在windows nt上算两个字节 select * from dept; 4)在infile指令上使用STR属性,加载一个变宽文件,其中用某个字符序列来表示行结束符,而不是用换行符表示 select utl_raw.cast_to_raw('|'||chr(10)) from dual;--可见在unix上为x'7C0A' nbsp;在windows上用nbsp;nbsp; select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;--为x'7C0D0A' --控制文件 load data infile demo.dat "str x'7C0D0A'" into table dept replace fields terminated by ',' trailing nullcols (deptno, dname "upper(:dname)", loc "upper(:loc)", comments ) --数据文件 10,Sales,Virginia,this is the sales office in Virginia| select * from dept; 6、加载lob数据 1)加载内联的lob数据。这些lob数据通常内嵌有换行符和其他特殊字符 --修改表dept truncate table dept; alter table dept drop column comments; alter table dept add comments clob; --数据文件 10,Sales,Virginia,this is the sales office in Virginia| 20,Accounting,Virginia,this is the Accounting office in Virginia| 30,Consuling,Virginia,this is the Consuling office in Virginia| 40,Finance,Virginia,"this is the Finance office in Virginia,it has embedded commas and is much longer than the other comments filed.If you feel the need to add double quotes text in here like this:""you will need to double up those quotes!""to preserve them in the string. This field keeps going for up to 1000000 bytes (because of the control file definition I used) or until we hit the magic and of record marker, the | followed by an end of line - it is right here -gt;"| --控制文件 load data infile demo.dat "str x'7C0D0A'" into table dept replace fields terminated by ',' optionally enclosed by '"' trailing nullcols (deptno, dname "upper(:dname)", loc "upper(:loc)", comments char(1000000) --sqlldr默认输入的字段都是char(255)。char(1000000)表示允许输入多达1000000个字符 ) select * from dept; 2)加载外联的lob数据。 nbsp;nbsp;nbsp; 需要把包含有一些文件名的数据文件加载在lob中,而不是让lob数据与结构化数据混在一起。这样就不必使用上述的4种方法之一来避开输入数据中 nbsp;nbsp;nbsp; lobfile数据采用以下某种格式: nbsp;nbsp;nbsp; 定长字段(从lobfile加载字节100到10000); --加载数据的表 create table lob_demo (owner varchar2(255), time_stamp date, filename varchar2(255), data blob) --假设有一目录,其中包含想要加载到数据库中的文件。以下为想要加载文件的owner,time_stamp,文件名及文件本身 load data infile * replace into table lob_demo (owner position(17:25), time_stamp position(44:55) date "Mon DD HH24:MI", filename position(57:100), data lobfile(filename) terminated by EOF ) begindata -rw-r--r-- 1 tkyte tkyte 1220342 jun 17 15:26 classes12.zip select owner,time_stamp,filename,dbms_lob.getlength(data) from lob_demo; 3)将lob数据加载到对象列 一般用于加载图像 create table image_load( id number, name varchar2(255), image ordsys.ordimage) --首先要了解ordsys.ordimage类型 加载这种数据的控制文件如下所示: load data infile * into table image_load replace fields terminated by ',' (id, name, file_name filler, image column object ( source column object ( localdata lobfile(file_name) terminated by EOF nullif file_name='none' ) ) ) begindata 1,icons,icons.gif 注:column object告诉sqlldr这不是一个列名,而是列名的一部分。 (编辑:武汉站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |