shell实现查询oracle数据库表,并写到本地txt文件

1、表结构

create table t_student(
    id number(10) primary key,
    name varchar2(50),
    birthday date
);

create sequence seq_t_student start with 1 increment by 1;

insert into t_student values(seq_t_student.nextval,'张三',sysdate);
insert into t_student values(seq_t_student.nextval,'李四',to_date('1990-01-01 13:13:13','yyyy-mm-dd hh24:mi:ss')); 
commit;
View Code

 

2、shell

#!/bin/bash
sqlplus -s centos/centos@win7orcl <<EOF >/ljxd/shell-demo/oracle/student.txt
set pages 0
set feed off
set heading off
set feedback off
set verify off
set linesize 1000
select t.id||'###'||t.name||'###'||to_char(t.birthday,'yyyy-mm-dd hh24:mi:ss') from t_student t;
EOF
View Code

 

3、分析

  centos/centos@win7orcl #数据库客户端配置请参考http://www.cnblogs.com/crazyMyWay/articles/4371984.html

  /ljxd/shell-demo/oracle/student.txt #为输出的文件

  set pages 0 #从txt文本第一行开始写入

  set feed off #...

  set heading off #去掉表头写入

  set feedback off #去掉最后一行空白行写入

  set verify off #...

  set linesize 1000 #每行只能1000个字符

 

4、测试命令

  

 

5、导出的结果文件如下:

  

posted @ 2015-03-27 17:02  crazyMyWay  阅读(10275)  评论(0编辑  收藏  举报