linux shell脚本连接oracle查询数据插入文件和日志文件中

时间:2021-05-08 06:37:47

#!/bin/sh

sqlplus "用户名/密码@数据库"<<EOF  或者只有一个库的 :sqlplus "用户名/密码"<<EOF

sqlplus "用户名/密码"<<EOF     显示连接详情,查询语句 ,查询结果;

sqlplus -S "用户名/密码" <<EOF  只显示sql语句查询结果;

sqlplus "用户名/密码"<<EOF>> xx.log  将信息增量添加到 xx.log 文件中;

#!/bin/sh
sqlplus -S "用户名/密码" <<EOF>> xss.log
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
select username from all_users;
exit;
EOF

第一种:

#!/bin/sh
sqlplus "用户名/密码"<<EOF>> xx.log
select username from all_users;
exit;
EOF

上诉语句产生一个xx.log 文件,文件内容如下:内容详尽

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 19 14:46:43 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> USERNAME ------------------------------

SCOTT

OWBSYS_AUDIT

OWBSYS

APEX_030200

APEX_PUBLIC_USER

FLOWS_FILES

MGMT_VIEW

SYSMAN

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

MDDATA

USERNAME ------------------------------

MDSYS

SI_INFORMTN_SCHEMA

ORDPLUGINS

ORDDATA

ORDSYS

OLAPSYS

ANONYMOUS

XDB

CTXSYS

EXFSYS

XS$NULL

USERNAME ------------------------------

WMSYS

APPQOSSYS

DBSNMP

ORACLE_OCM

DIP

OUTLN

SYSTEM

SYS

30 rows selected.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

第二种:

#!/bin/sh
sqlplus -S "用户名/密码" <<EOF
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
spool spool_file
select username from all_users;
spool off
exit;
EOF

上诉语句产生一个spool_file.lst文件,文件内容如下:文件只显示sql语句的查询结果

SCOTT                                                                          
OWBSYS_AUDIT                                                                   
OWBSYS                                                                         
APEX_030200                                                                    
APEX_PUBLIC_USER                                                               
FLOWS_FILES                                                                    
MGMT_VIEW                                                                      
SYSMAN                                                                         
SPATIAL_CSW_ADMIN_USR                                                          
SPATIAL_WFS_ADMIN_USR                                                          
MDDATA                                                                         
MDSYS                                                                          
SI_INFORMTN_SCHEMA                                                             
ORDPLUGINS                                                                     
ORDDATA                                                                        
ORDSYS                                                                         
OLAPSYS                                                                        
ANONYMOUS                                                                      
XDB                                                                            
CTXSYS                                                                         
EXFSYS                                                                         
XS$NULL                                                                        
WMSYS                                                                          
APPQOSSYS                                                                      
DBSNMP                                                                         
ORACLE_OCM                                                                     
DIP                                                                            
OUTLN                                                                          
SYSTEM                                                                         
SYS

上诉语句去掉 -S  输出到文件上的内容会增加2行,如下:

SQL> select username from all_users;
SCOTT                                                                          
OWBSYS_AUDIT                                                                   
OWBSYS                                                                         
APEX_030200                                                                    
APEX_PUBLIC_USER                                                               
FLOWS_FILES                                                                    
MGMT_VIEW                                                                      
SYSMAN                                                                         
SPATIAL_CSW_ADMIN_USR                                                          
SPATIAL_WFS_ADMIN_USR                                                          
MDDATA                                                                         
MDSYS                                                                          
SI_INFORMTN_SCHEMA                                                             
ORDPLUGINS                                                                     
ORDDATA                                                                        
ORDSYS                                                                         
OLAPSYS                                                                        
ANONYMOUS                                                                      
XDB                                                                            
CTXSYS                                                                         
EXFSYS                                                                         
XS$NULL                                                                        
WMSYS                                                                          
APPQOSSYS                                                                      
DBSNMP                                                                         
ORACLE_OCM                                                                     
DIP                                                                            
OUTLN                                                                          
SYSTEM                                                                         
SYS                                                                            
SQL> spool off

#!/bin/sh
sqlplus  "用户名/密码" <<EOF>> x.log
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
spool spool_file
select username from all_users;
spool off
exit;
EOF

上诉语句会产生一个x.log文件 一个spool_file.lst文件,多次运行会增量添加到x.log文件中,会重新生成spool_file.lst文件

x.log文件内容如下:

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 19 15:01:33 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SCOTT OWBSYS_AUDIT OWBSYS APEX_030200 APEX_PUBLIC_USER FLOWS_FILES MGMT_VIEW SYSMAN SPATIAL_CSW_ADMIN_USR SPATIAL_WFS_ADMIN_USR MDDATA MDSYS SI_INFORMTN_SCHEMA ORDPLUGINS ORDDATA ORDSYS OLAPSYS ANONYMOUS XDB CTXSYS EXFSYS XS$NULL WMSYS APPQOSSYS DBSNMP ORACLE_OCM DIP OUTLN SYSTEM SYS SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

spool_file.lst文件内容如下:

SQL> select username from all_users;
SCOTT                                                                          
OWBSYS_AUDIT                                                                   
OWBSYS                                                                         
APEX_030200                                                                    
APEX_PUBLIC_USER                                                               
FLOWS_FILES                                                                    
MGMT_VIEW                                                                      
SYSMAN                                                                         
SPATIAL_CSW_ADMIN_USR                                                          
SPATIAL_WFS_ADMIN_USR                                                          
MDDATA                                                                         
MDSYS                                                                          
SI_INFORMTN_SCHEMA                                                             
ORDPLUGINS                                                                     
ORDDATA                                                                        
ORDSYS                                                                         
OLAPSYS                                                                        
ANONYMOUS                                                                      
XDB                                                                            
CTXSYS                                                                         
EXFSYS                                                                         
XS$NULL                                                                        
WMSYS                                                                          
APPQOSSYS                                                                      
DBSNMP                                                                         
ORACLE_OCM                                                                     
DIP                                                                            
OUTLN                                                                          
SYSTEM                                                                         
SYS                                                                            
SQL> spool off