UTL File Concept

When we are going to work with UTL Files, we have to follow four steps,

1. Declare a File Variable
2. Open a File using File Variable
3. Write the data into the file using File Variable
4. Close the file using File Variable.

Note: To create a file in the server the file directory path should be defined or registered.  To check the defined file directory path below query can be used,
        select value from v$parameter where name like 'utl_file_dir'
If the above select statement doesn't return any records, that means there is no defined file directory path.  This will be taken care by DBA's.  Else if you want to create a directroy path follow the below steps

• Connect as SYSDBA
• CREATE OR REPLACE DIRECTORY AS '/PATH'
• GRANT READ, WRITE  ON DIRECTORY TO SCOTT;
• ALTER SYSTEM SET UTL_FILE_DIR = '/PATH' SCOPE=SPFILE 

UTL File Steps:

• Declare a File Variable:

         Syn:   file_var_name UTL_FILE.FILE_TYPE;
           Ex:  fv UTL_FILE.FILE_TYPE;

• Open a File using File Variable

            Syn: file_var_name  := UTL_FILE.FOPEN(,,);
             Ex:  fv := UTL_FILE.FOPEN('C:/TEMP','SAM.txt','W');

• Write the data into the File

             Syn: UTL_FILE.PUT_LINE(file_var_name,'Message' / variable);
               Ex: UTL_FILE.PUT_LINE(fv,'Hello');

• Close the File

              Syn: UTL_FILE.FCLOSE('file_var_name);
                Ex: UTL_FILE.FCLOSE(fv);

Sample piece of UTL File Code,

create or replace procedure utl_sample
is
  l_file_var utl_file.file_type;
begin
  l_file_var := utl_file.fopen('C;/temp','SAM.txt','W');
  utl_file.put_line(l_file_var,'This is a sample prog');
  utl_file.fclose(l_file_var);
end;

Custom Form with DFF

• Register the Table
• Register the DFF
• Define the Segments
• Normal Form Development
• Change the properties of Attribute1,2,3.. columns Visible to No
• Change the properties of Attribute_Category,
• Sub Class to TEXT_ITEM_DESC_FLEX
• LOV to ENABLE_LIST_LAMP
• Validate from List to No
• Database Item to No
• In Program Unit create Package Spec and Body
• Package Spec:
• procedure xxdff_proc(event varchar2);
• Package Body:
• procedure xxdff_proc(event varchar2)

                                   IS
                                   begin
                                       if event = 'WHEN-NEW-FORM-INSTANCE' then
                                       FND_DESCR_FLEX.DEFINE(BLOCK => 'XXDFF,
                                                                                         FIELD => 'Attribute_category',
                                                                                         appl_short_name=>'FND',
                                                                                         DESC_FLEX_NAME='XXDFF');    
                                      end if;
                                  end;

• Triggers,
• WHEN-NEW-FORM-INSTANCE
• PACKAGE_NAME.XXDFF_PROC('WHEN-NEW-FORM-INSTANCE');
• WHEN-NEW-ITEM-INSTANCE
• FND_FLEX.EVENT('WHEN-NEW-ITEM-INSTANCE');