Shulou(Shulou.com)06/01 Report--

First, the problem description received a task today, is to require every day through the SQL script to generate excel files, and automatically sent to the relevant personnel mailbox. I really haven't done this requirement. I've only done it before to generate HTML web page files through SQL scripts. As a result, I started the learning process again, and in today's Internet era, solving the problem is easy, and I quickly found eygle's blog (using SQL*PLUS to build the perfect excel or html output http://www.eygle.com/archives/2005/04/eoasqlplusieaae.html). The following is the experimental part of the learning process.

Second, experiment 1. Set up a test table

SAM@dzwj > create table test1 (id int,name varchar2 (10), loc varchar2 (30), hire_date date,email varchar2 (20), department varchar2 (20)

Table created.

SAM@dzwj > insert into test1 values (1, recording sam1, writing, etc., etc.)

1 row created.

SAM@dzwj > insert into test1 values. (2) SysdateRecord, sam2, oracle.compose, and it.

1 row created.

SAM@dzwj > insert into test1 values (3PhoneSystem.sam3andoracle.composit`)

1 row created.

SAM@dzwj > insert into test1 values (4 recordssam4 recording, recording beijingwriting, sysdatewriting, sam4writing, oracle.composing, recording, recording, writing, etc.)

1 row created.

SAM@dzwj > insert into test1 values (5 recordssysdatereachoracle.composit`)

1 row 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

two。 Edit main.sql

[oracle@testdb ~] $cat main.sql

Set linesize 200 pagesize 10000

Set term off verify off feedback off

Set markup html on entmap on 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. Edit get_tables.sql

[oracle@testdb ~] $cat get_tables.sql

Select * from test1

4. Edit the execution file 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. Give collect.sh executive permission

[oracle@testdb] $chmod upright x collect.sh

6. Execution

[oracle@testdb] $. / collect.sh

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 11:00:19 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

7. Verify that you send the file back to your local machine and open it to get the desired excel file

Third, it is a happy thing to sum up living in the Internet era, this task is over, but there are still some small problems, such as no modification of the time NLS_DATE_FORMAT variable at the beginning, and there is no time to export the time type data, only the year, month and day. In a word, it is right to study more and practice more. Thank you to God eygle. Where there is a will, there is a way.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.