-
SAM@dzwj> create table test1(id int,namevarchar2(10),locvarchar2(30),hire_datedate,emailvarchar2(20),departmentvarchar2(20));
-
Table created.
-
-
SAM@dzwj> insert into test1values (1,'sam1','beijing',sysdate,'sam1@oracle.com','it');
-
1row created.
-
SAM@dzwj> insert into test1values (2,'sam2','beijing',sysdate,'sam2@oracle.com','it');
-
1row created.
-
SAM@dzwj> insert into test1values (3,'sam3','beijing',sysdate,'sam3@oracle.com','it');
-
1row created.
-
SAM@dzwj> insert into test1values (4,'sam4','beijing',sysdate,'sam4@oracle.com','it');
-
1row created.
-
SAM@dzwj> insert into test1values (5,'sam5','beijing',sysdate,'sam5@oracle.com','it');
-
1row created.
-
-
SAM@dzwj> SAM@dzwj> commit;
-
Commit complete.
-
-
SAM@dzwj> select * from test1;
-
-
ID NAME LOC HIRE_DATE EMAIL DEPARTMENT
-
---------- ---------- ------------------------------ ------------------ -------------------- --------------------
-
1 sam1 beijing 28-NOV-17 sam1@oracle.com it
-
2 sam2 beijing 28-NOV-17 sam2@oracle.com it
-
3 sam3 beijing 28-NOV-17 sam3@oracle.com it
-
4 sam4 beijing 28-NOV-17 sam4@oracle.com it
-
5 sam5 beijing 28-NOV-17 sam5@oracle.com it
2.编辑 main.sql
-
[oracle@testdb~]$ cat main.sql
-
set linesize 200pagesize 10000
-
set termoff verifyoff feedbackoff
-
set markup htmlon entmapon spool on preformat off
-
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
-
spool /home/oracle/test1.xls
-
@/home/oracle/get_tables.sql
-
spool off
-
exit
3.编辑 get_tables.sql
-
[oracle@testdb~]$ cat get_tables.sql
-
select * from test1;
4.编辑执行文件 collect.sh
-
[oracle@testdb~]$ cat collect.sh
-
#!/bin/bash
-
. /home/oracle/.bash_profile
-
DATE=`date +%Y%m%d`
-
sqlplus sam/oracle@dzwj@/home/oracle/main
-
mv/home/oracle/test1.xls/home/oracle/test1_${DATE}.xls
5.给collect.sh 执行权限
-
[oracle@testdb~]$ chmod u+x collect.sh
6.执行
-
[oracle@testdb~]$ ./collect.sh
-
-
SQL*Plus: Release 11.2.0.4.0 Productionon Wed Nov 29 11:00:19 2017
-
-
Copyright(c) 1982, 2013, Oracle. All rights reserved.
-
-
-
Connectedto:
-
OracleDatabase 11g Enterprise EditionRelease 11.2.0.4.0- 64bit Production
-
With the Partitioning, OLAP, Data Miningand Real Application Testing options
-
-
Disconnectedfrom OracleDatabase 11g Enterprise EditionRelease 11.2.0.4.0- 64bit Production
-
With the Partitioning, OLAP, Data Miningand Real Application Testing options
7.验证
将文件传回到本地机器打开，得到想要的excel文件
三、总结
生活在Internet时代真是件幸福的事，此次任务算是告一段落，但是当中还是碰到一些小problems，比如一开始没有加时间NLS_DATE_FORMAT变量的修改，导出的时间类型数据时没有时间，只有年月日。总而言之，多学习，多实践，没错的。向eygle大神致谢。 Where there is a will, there is a way.
