sqlplus example

最新推荐文章于 2025-08-20 20:33:15 发布

dingxy

最新推荐文章于 2025-08-20 20:33:15 发布

阅读量628

点赞数

CC 4.0 BY-SA版权

分类专栏： Linux/Shell/Perl/AWK... 文章标签： insert file sql

本文链接：https://blog.youkuaiyun.com/dingxy/article/details/5872909

Linux/Shell/Perl/AWK... 专栏收录该内容

110 篇文章

订阅专栏

本文介绍了一个使用SQL Plus编写的Shell脚本，该脚本能够创建并运行一个SQL日志文件，其中包括通过PL/SQL块生成的日志条目以及直接通过SQL命令插入的数据。此脚本演示了如何设置输出环境、定义文件名、使用spool命令来捕获输出以及如何使用prompt命令进行注释。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

File aa.sh :

-----------------------------------------------------------------------------------------------------------------------------------

sqlplus -s username/password@DBname <<!!!

set echo off
set verify off
set serveroutput on size 100000

column fname new_value fname noprint

SELECT 'mylog_' || to_char(sysdate, 'yymmdd_sssss') ||'.log' fname FROM dual;
spool &fname
begin
dbms_output.put_line('===========================================');
dbms_output.put_line('First insert: inserted &fname ');
dbms_output.put_line('===========================================');
end;
/
prompt ===========================================
prompt Second insert just using SQL
prompt ===========================================
spool off

----------------------------------------------------------------------------------------------------------------------------------

running as below :

-----------------------------------------------------------------------------------

[username@servername dxy]$ ./sqltest.sh

===========================================
First insert: inserted mylog_100909_41579.log
===========================================

PL/SQL procedure successfully completed.

===========================================
Second insert just using SQL
===========================================

--------------------------------------------------------------------------------------

NOTES:
1. column fname1 new_value fname2 noprint

fname1 : define a nuew column

fname2 : is the holder of fnam21

can use it like : & finame2 (as showed in the above &fname )

2.note the usage of spool and prompt

Reference:

http://www.orafaq.com/forum/t/37241/2/