How to append system time to the file name in oracle?

Question

Hi ,

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.

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?

Thanks, Priya.R

Answer 1

This is how you can get a dynamic filename in 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)

To place the output in a known directory amend the SPOOL command to something like...

SPOOL c:\output_dir\&dynamic_filename

To get an SQL Plus script to exit after an error then include this command...

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK

Answer 2

Thanks for your reply. It would be helpful if you could explain me in some detailed way.

Answer 3

I am not able to see the file that gets created also. Please help in finding the file and also to create the file under specified directory (OS directory and the oracle has the directpry defined to it).

Thanks in advance.

Answer 4

It seems you want to look into the UTL_FILE Oracle supplied package, as you're trying to use PL/SQL to create the 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:

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:

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;