打个比方:表中有一字段是Date类型的。当初往数据库存数据是用oracle的sysdate取得值然后存放在该字段中的
(数据表中看见的格式是:2007-02-01 12:45:32)。
现在要用spool把表中的数据读取出来:(其中created字段是date类型的)
set colsep','
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 280
set numwidth 12
set termout off
set timing off
set trimout off
set trimspool on
set space 0
spool c:\test\MUSER.txt
select MUSER_ID || ',' || CREATED || ',' from MUSER;
spool off
EOF运行spool命令在muser.txt文件中看见该字段变成了“01-2月 -07”。那我在用sqlloader将数据倒回去时就出错了我要如何操作才能正确???谢谢各位!!!
(数据表中看见的格式是:2007-02-01 12:45:32)。
现在要用spool把表中的数据读取出来:(其中created字段是date类型的)
set colsep','
set echo off
set feedback off
set heading off
set pagesize 0
set linesize 280
set numwidth 12
set termout off
set timing off
set trimout off
set trimspool on
set space 0
spool c:\test\MUSER.txt
select MUSER_ID || ',' || CREATED || ',' from MUSER;
spool off
EOF运行spool命令在muser.txt文件中看见该字段变成了“01-2月 -07”。那我在用sqlloader将数据倒回去时就出错了我要如何操作才能正确???谢谢各位!!!
alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'现在输出是:122,2007-02-02 12:39:51那么我用sql loader时,该如何把2007-02-02 12:39:51从文件中输入????我用:
load data
infile 'C:\\testMUser.txt'
Append into table MUSER
FIELDS TERMINATED BY ","
(MUSER_ID,Created,ISACTIVE,NAME,BUREAU_ID,ORG_ID,PASSWORD,EMAIL ,TITLE)总是报错,说是时间created不对
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
'dd/mm/yyyy',
'dd/mm/yyyy hh24:mi:ss' );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;
if ( l_return is null )
then
l_return :=
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
p_string, 'GMT', 'EST' );
end if;
return l_return;
end;
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数
)