oracle 如何在oracle中将系统时间附加到文件名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1524498/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to append system time to the file name in oracle?
提问by PaulJ
I am very new to oracle.I need to create a file with the system timestamp from oracle. Please let me know how do i do that.
我对 oracle 很陌生。我需要从 oracle 创建一个带有系统时间戳的文件。请让我知道我该怎么做。
More over I need to write any exceptions or errors thrown by my pl/sql code to a file and exit after an error. How do i do this?
更重要的是，我需要将我的 pl/sql 代码抛出的任何异常或错误写入文件并在出现错误后退出。我该怎么做呢？
Thanks, Priya.R
谢谢，Priya.R
回答by DCookie
It seems you want to look into the UTL_FILEOracle supplied package, as you're trying to use PL/SQL to create the file.
当您尝试使用 PL/SQL 创建文件时，您似乎想查看Oracle 提供的UTL_FILE包。
You can generate the file this way:
您可以通过以下方式生成文件：
(You'll need to create an Oracle DIRECTORY first, pointing to the OS location of the file:
（您需要先创建一个 Oracle DIRECTORY，指向文件的操作系统位置：
CREATE OR REPLACE DIRECTORY DIR AS 'your OS directory';
Note that the name 'DIR' is used in the sample code that follows. You will also require the CREATE DIRECTORY privilege, and then grant read and write permissions on the directory to the user who will use it:
请注意，后面的示例代码中使用了名称“DIR”。您还需要 CREATE DIRECTORY 权限，然后向将使用它的用户授予对该目录的读写权限：
GRANT READ,WRITE ON DIR TO user1;
)
)
DECLARE
v_logfile VARCHAR2(100);
v_FH UTL_FILE.FILE_TYPE;
BEGIN
v_logfile := TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS')||'_process.log';
v_FH := UTL_FILE.FOPEN(DIR, v_logfile, 'w');
UTL_FILE.PUTLINE(v_FH, 'Some text on a new line');
UTL_FILE.FCLOSE(v_FH);
END;
回答by PaulJ
This is how you can get a dynamic filename in SQL Plus
这是在 SQL Plus 中获取动态文件名的方法
SET TERMOUT OFF
DEFINE dynamic_filename = idle
COLUMN which_dynamic NEW_VALUE dynamic_filename
SELECT 'prefix_'
||TO_CHAR( SYSDATE, 'YYYYMMDD' )
||'_'
||TO_CHAR( SYSDATE, 'HH24MISS' )
||'.log' which_dynamic
FROM dual;
SET TERMOUT ON
SPOOL &dynamic_filename
SELECT * FROM dual;
SPOOL OFF
The file gets created in the default directory for SQL Plus (on windows this is the "Start In:" property of the shortcut)
该文件在 SQL Plus 的默认目录中创建（在 Windows 上，这是快捷方式的“开始于：”属性）
To place the output in a known directory amend the SPOOL command to something like...
要将输出放在已知目录中，请将 SPOOL 命令修改为...
SPOOL c:\output_dir\&dynamic_filename
To get an SQL Plus script to exit after an error then include this command...
要让 SQL Plus 脚本在出错后退出，请包含此命令...
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
