This article explains how to insert attachments using Oracle API’s from Back end. Working on attachments need some knowledge of how an attachment functions (Definitions, Entity, Document Category, Entities and Blocks, Primary Keys in attachments) in Oracle Apps. To understand the below API and what it actually does its advisable to read the Attachments Chapter in Developers Guide.

Primary Key information that uniquely identifies the product (such as the INVOICE_ID).

Below API takes single file from a shared drive and inserts into fnd_lobs. Then it’s attached to a particular Id (like check_id, invoice_id..) using the combination of pk1_value, entity name and category.

This sample shows how an attachment API works. You can use it to customize/enhance to requirement.

//Tested in R12.1.3

–Create a Server Directories in Oracle–This will be done by DBA mostly–Physical directory must be created inorder to save the files into server
CREATE OR REPLACE DIRECTORY SALE_INVOICE_PATH AS ‘/files/SALE_INVOICE/’;

This Document explains the steps of how we read an XML data file using PLSQL and write them to oracle tables.

Create a UTL Directory:

This involves the following steps. Creating a Logical directory and giving the Permissions to access this directory using an URL. Using the XML Dom Parser Procedures to read the Xml file , parse itand then load it into the respective columns.

1) Create a logical directoryCreate or replace directory TEST_LOB_DIR as ‘/data/tst1/attachment’It creates a directory with Owner SYS.Grant privileges on this directory to Apps.

2) Create a physical directory at the given location ‘/data/tst1/attachment’Permissions on this directory too.This directory has to be a UTL directory.

4) Bounce the Apache.Create a procedure to parse and retrieve the XML data and then insert it into appropriate columns in the database.Used two PLSQL packages for these –1. XMLPARSER2. XMLDOM

1. XMLParser – Proceduresi.)parse(p Parser, url VARCHAR2)Description – The parse procedure takes two parameters which are the parse object and the url of thexml file that has to parsed.ii.) setValidationMode(p Parser, yes BOOLEAN)Checks whether XML is Valid or not.iii.) setBaseDir(p Parser, dir VARCHAR2)Sets the base url or directory path.iv.) getDocument(p Parser)Get the Document which has to be parsed.