3:对于oracle date类型打印到shell之后变“年月日”处理
SQL查询结果:
SQL> select ''''||clgjid||'''', jgsj,rksj,';' from sa.zxx_test where rownum <3;
''''||CLGJID||'''' JGSJ RKSJ ';'
------------------------------------------ ----------- ----------- ---
'100001228857696' 2015/3/31 0 2015/3/30 2 ;
'100001228857725' 2015/3/31 0 2015/3/30 2 ;
[oracle@rhel6 zxx_shell]$ cat 6-oracle.sh
#!/bin/bash
SOURCE_DB="zxx/zxx@orclone"
SOURCE_DATA=
source_sql_page="select ''''||clgjid||'''', jgsj,rksj,';' from sa.zxx_test where rownum <3;"
function getSourceData()
{
old_data=`sqlplus -s $SOURCE_DB<
set pagesize 0 heading off echo off termout off feedback off linesize 1200 colsep "," trimspool on trimout on
$source_sql_page
quit;
EOF`
#delete last char
SOURCE_DATA=${old_data%?}
}
getSourceData
echo $SOURCE_DATA
[oracle@rhel6 zxx_shell]$ ./6-oracle.sh
'100001228857696' ,31-MAR-15 ,30-MAR-15 ,; '100001228857725' ,31-MAR-15 ,30-MAR-15 ,
解决方法:使用to_char转换成字符串类型
SQL> select ''''||clgjid||'''', to_char(jgsj,'yyyy-mm-dd hh24:mi:ss'),to_char(rksj,'yyyy-mm-dd hh24:mi:ss'),';' from sa.zxx_test where rownum <3;
''''||CLGJID||'''' TO_CHAR(JGSJ,'YYYY-MM-DDHH24:M TO_CHAR(RKSJ,'YYYY-MM-DDHH24:M ';'
------------------------------------------ ------------------------------ ------------------------------ ---
'100001228857696' 2015-03-31 00:01:18 2015-03-30 23:39:17 ;
'100001228857725' 2015-03-31 00:01:32 2015-03-30 23:39:30 ;
将上面的source_sql_page变成:
source_sql_page="select ''''||clgjid||'''', to_char(jgsj,'yyyy-mm-dd hh24:mi:ss'),to_char(rksj,'yyyy-mm-dd hh24:mi:ss'),';' from sa.zxx_test where rownum <3;"