1、1：shell连接oracle数据库shell连接数据库有两种方式，一种是传入参数和接收返回值SQL查询结果：SQL> select default_tablespace,temporary_tablespace ,';'from dba_users where username in ('ZXX','SA');DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ';'------------------------------ ------------------------------ ---REMOTE320DB TEMP ;REMOTE320DB TEMP ;shell连接oracle数据：[oracle@rhel6 zxx_shell]$ cat 6-oracle.sh#!/bin/bashSOURCE_DB="zxx/zxx@orclone" SOURCE_DATA= #全局变量source_sql_page="select default_tablespace,temporary_tablespace,';' from dba_users where username in ('ZXX','SA');" #SQL语句,每行之前用";"隔开function getSourceData(){ old_data=`sqlplus -s $SOURCE_DB<<EOF set pagesize 0 heading off echo off termout off feedback off linesize 1200 colsep "," trimspool on trimout on #列之间用,号隔开，linesize很重要 $source_sql_page quit;EOF` #必须顶格写 #delete last char SOURCE_DATA=${old_data%?} #去除字符串最后一个字符}getSourceDataecho $SOURCE_DATA[oracle@rhel6 zxx_shell]$ ./6-oracle.shREMOTE320DB ,TEMP ,; REMOTE320DB ,TEMP ,[oracle@rhel6 zxx_shell]$注意点：1：每列之间隔开符在 set中使用 setcolsep ","2：每行数据之间隔开需要在SQL中使用，';' from，分隔符必须是shell非转义字符和表数据中没有的字符，否则到时候无法分割3：打印长度在set中使用linesize 1200，建议越大越好，否则表字段多了之后截取字符时会导致列变短4：每行数据使用分隔符之后，获取数据之后需要去除最后一个字符5：如果需要连接数据库，EOF必须顶格写，否则报错。
2、2：对于oracle number长数值打印到shell中之后变科学计数法处理SQL查询结果：SQL> select clgjid,FWBH ,';' from sa.zxx_test where rownum <3; CLGJID FWBH ';'---------------- -------- ---100001228857696 10008 ;100001228857725 10008 ;[oracle@rhel6 zxx_shell]$ cat 6-oracle.sh#!/bin/bashSOURCE_DB="zxx/zxx@orclone"SOURCE_DATA=source_sql_page="select clgjid,FWBH ,';' from sa.zxx_test where rownum <3;"function getSourceData(){ old_data=`sqlplus -s $SOURCE_DB<<EOF 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%?}}getSourceDataecho $SOURCE_DATA[oracle@rhel6 zxx_shell]$ ./6-oracle.sh1.0000E+14,10008 ,; 1.0000E+14,10008 ,对于oracle中number类型长数值，打印到shell中会变成科学计数法解决方法：使用字符串拼接SQL> select ''''||clgjid||'''',''''||FWBH||'''',';' from sa.zxx_test where rownum <3;''''||CLGJID||'''' ''''||FWBH||'''' ';'------------------------------------------ ---------------- ---'100001228857696' '10008 ' ;'100001228857725' '10008 ' ;将number类型变成字符串类型就可以，使用单引号(也可以使用其他符合拼接)注意：oracle中字符单引号，表示方法就是两个单引号将上面的source_sql_page变成：source_sql_page="select ''''||clgjid||'''',''''||FWBH||'''',';' from sa.zxx_test where rownum < 3;"
3、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/bashSOURCE_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<<EOF 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%?}}getSourceDataecho $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;"
4、4：对于oracle 中文字符打印到shell之后乱码问题处理乱码问题：服务端和客户端字符集不一致导致SQL 查询结果：SQL> select hphm from sa.zxx_test where rownum <3;HPHM--------苏F12345苏F12345[oracle@rhel6 zxx_shell]$ cat 6-oracle.sh#!/bin/bashSOURCE_DB="zxx/zxx@orclone"SOURCE_DATA=source_sql_page="select hphm from sa.zxx_test where rownum <3;"function getSourceData(){ old_data=`sqlplus -s $SOURCE_DB<<EOF 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%?}}getSourceDataecho $SOURCE_DATA[oracle@rhel6 zxx_shell]$ ./6-oracle.sh ?F12345 ?F1234 [oracle@rhel6 zxx_shell]$服务端：oracle数据库，它的字符集SQL> select userenv('language') from dual;USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.ZHS16GBK客户端字符集：Linux，它的字符集[root@rhel6 ~]# su - oracle[oracle@rhel6 ~]$ echo $NLS_LANG[oracle@rhel6 ~]$需要编辑/home/oracle/.bash_profile文件，添加：export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
5、5：对于oracle “\"打印到shell之后转义问题处理SQL> select ''''||clgjid||'''',txml from zxx_test where rownum < 3;'100001228857696' jjhikx\2015\03\30\23\3206811188\'100001228857725' jjhikx\2015\03\30\23\3206811188\解决办法：采用字符串拼接方法
6、6：对shell变量接收有限返回值问题处理进行批量处理，比如分页处理
7、7：对于oracle中表中列空字符处理采用nvl2或者nvl函数