Posted in 2017

Writing data to textfiles – UTL_FILE

DECLARE
 dir_exists NUMBER;
 dir_name VARCHAR2(50);
 file_name VARCHAR2(50);
 v_exists BOOLEAN;
 v_file_length NUMBER;
 v_blk_size BINARY_INTEGER;
 file_h UTL_FILE.file_type;
BEGIN
 dir_name := 'DATA_PUMP_DIR';
 -- get file_name
 select dbms_random.string('A',5)||'_'|| sys_context('userenv','db_name') || '.txt'
 into file_name
 from dual;

 -- Check if file exists and drop if it is
 UTL_FILE.FGETATTR (dir_name, file_name, v_exists, v_file_length, v_blk_size);
 IF v_exists THEN
   DBMS_OUTPUT.PUT_LINE('PURGING FILE: '||file_name) ;
   UTL_FILE.fremove(dir_name, file_name);
 END IF;

 -- open the file
 file_h := UTL_FILE.fopen(dir_name, file_name, 'W', 32767);

 -- get and write the data
FOR rec in (select username||';'||password as data from dba_users ) LOOP
 UTL_FILE.put_line (file_h, rec.data);
END LOOP;
 UTL_FILE.fclose (file_h);

END;
/
Advertisements

Author:

Database administrator who loves to work with Oracle software. (Sometimes not)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s