Oracle Apps: Helpful Queries on FND Attachments tables and sample code to extract attachments

This post gives some helpful queries when working on FND Attachment tables. I have also given a sample program to extract these attachment into file system.

Attachment Types are stored in the fnd_document_datatypes table.

select * from fnd_document_datatypes;

Query to retrieve the 'Short Text' Attachments:

 select fad.document_id  
       ,fad.entity_name  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fds.short_text  
       ,(fad.entity_name || '_' || fad.document_id ||'_ST.txt') file_name  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_documents_short_text fds
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct  
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fds.media_id  
 and   fd.datatype_id  = fdd.datatype_id  
 and   fd.category_id  = fdct.category_id
 and   fdd.user_name   = 'Short Text'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'Long Text' Attachments:

 select fad.entity_name  
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fdl.long_text  
       ,(fad.entity_name || '_' || fad.document_id ||'_LT.txt') file_name  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_documents_long_text fdl
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fdl.media_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'Long Text'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'File' Attachments:

 select fad.entity_name   
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,fd.datatype_id  
       ,(fad.entity_name || '_' || fad.document_id ||'_' || fl.file_name) file_name  
       ,fl.file_data  
 from fnd_attached_documents fad  
     ,fnd_documents fd  
     ,fnd_lobs fl
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.media_id     = fl.file_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'File'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Query to retrieve the 'Web Page' Attachments:

 select fad.entity_name  
       ,fad.document_id  
       ,fad.pk1_value  
       ,fad.pk2_value
       ,fdct.user_name "Category"
       ,fdd.user_name "Type"  
       ,(fad.entity_name || '_' || fad.document_id ||'_URL.txt') file_name  
       ,fd.url  
 from fnd_attached_documents fad  
     ,fnd_documents fd
     ,fnd_document_datatypes fdd
     ,fnd_document_categories_tl fdct    
 where fad.document_id = fd.document_id  
 and   fd.datatype_id  = fdd.datatype_id
 and   fd.category_id  = fdct.category_id  
 and   fdd.user_name   = 'Web Page'  
 and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.  
 order by fad.pk1_value, pk2_value;  

Sample program to extract the attachments into file system.

declare  
  v_file     utl_file.file_type;   
  v_line     varchar2(1000);   
  v_blob_len number;  
  v_pos      number;  
  v_buffer   raw(32764);  
  v_amt      binary_integer := 32764;
  
  cursor cur_short_text_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fds.short_text   
          ,(fad.entity_name || '_' || fad.document_id ||'_ST.txt') file_name   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_documents_short_text fds  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and   fd.media_id     = fds.media_id   
    and   fd.datatype_id  = fdd.datatype_id   
    and   fdd.user_name   = 'Short Text'   
    and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
  cursor cur_long_text_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fdl.long_text   
          ,(fad.entity_name || '_' || fad.document_id ||'_LT.txt') file_name   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_documents_long_text fdl  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and  fd.media_id      = fdl.media_id   
    and  fd.datatype_id   = fdd.datatype_id   
    and  fdd.user_name    = 'Long Text'   
    and  fad.entity_name  = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
   
  cursor cur_files  
  is  
    select fad.entity_name    
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,fd.datatype_id   
          ,(fad.entity_name || '_' || fad.document_id ||'_' || fl.file_name) file_name   
          ,fl.file_data   
    from fnd_attached_documents fad   
        ,fnd_documents fd   
        ,fnd_lobs fl  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and   fd.media_id     = fl.file_id   
    and   fd.datatype_id  = fdd.datatype_id   
    and   fdd.user_name   = 'File'   
    and   fad.entity_name = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
  cursor cur_url_files  
  is  
    select fad.entity_name   
          ,fad.document_id   
          ,fad.pk1_value   
          ,fad.pk2_value   
          ,(fad.entity_name || '_' || fad.document_id ||'_URL.txt') file_name   
          ,fd.url   
    from fnd_attached_documents fad   
        ,fnd_documents fd  
        ,fnd_document_datatypes fdd   
    where fad.document_id = fd.document_id   
    and  fd.datatype_id   = fdd.datatype_id   
    and  fdd.user_name    = 'Web Page'   
    and  fad.entity_name  = :p_entity_name -- replace with the entity_name you want to extract.   
    order by fad.pk1_value, pk2_value;
  
begin
  
  -- Short Text Attachments   
  for c_file in cur_short_text_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.short_text);  
    utl_file.fclose(v_file);   
  end loop;
  
  -- Long Text Attachments   
  for c_file in cur_long_text_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.long_text);  
    utl_file.fclose(v_file);   
  end loop;
  
  -- File Attachments  
  for c_file in cur_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'wb', 32764);  
    v_blob_len := dbms_lob.getlength(c_file.file_data);  
    v_pos := 1;  
    while v_pos < v_blob_len  
    loop  
      dbms_lob.read(c_file.file_data,v_amt,v_pos,v_buffer);  
      utl_file.put_raw(v_file,v_buffer,true);  
      v_pos := v_pos + v_amt;  
    end loop;   
    utl_file.fclose(v_file);   
  end loop;
  
  --Web Page Attachments  
  for c_file in cur_url_files  
  loop  
    v_file := utl_file.fopen('XX_EXTRACT_DIR',c_file.file_name, 'W', 32764);  
    utl_file.put(v_file,c_file.url);  
    utl_file.fclose(v_file);   
  end loop;       
end;  

Note: The oracle directory object 'XX_EXTRACT_DIR' should be created before running the script. Refer the below blog to see how to use utl_file package.
Johny's Oracle Tips: PL/SQL: Sample code for UTL_FILE



Feel free to point out if anything is missing/wrong in this blog.