5) iRecruitment Data Migration - How To Migrate Resume

Oracle iRecruitment integrates with Oracle HRMS to manage the entire workforce life-cycle.It automates every phase of the recruitment process.

Most of the companies have their Recruitment solution.Now in most of the iRecruitment implementation we need to do the migration of Resume files from already existing system to newly configured (may be from iRecruitment perspective) oracle iRecruitment system.Here we will only discuss about how we can migrate resume from existing system to our oracle system.

Here we have considered that reader has a basic knowledge on Oracle iRecruitment functionality and aware of terminology(candidate,applicant,vacancy etc..).

Basic Requirement

Our basic requirement is to migrate Resume files and make it available through iRecruitment.

Pre-Requisite

Following are the pre-requisites for Resume migration

1) iRecruitment system is configured

2) Candidate and/or Applicant are already migrated and present in the system.

3) Resume files are already transfered from older system to our oracle database server(say database server path:- /usr/tmp ).The path may differ in your case.

4) The candidate/applicant resume mapping file(file that contains the candidate/applicant necessary details(any unique identifier) and corresponding file name. The mapping file is loaded in our migration stagging table.

Solution Approach

Before we start with the solution, we must familiar with some of the following oracle seeded pl/sql functions and their usages

1) bfilename(Directory,File_Name)

BFILENAME returns a BFILE locator that is associated with a physical LOB binary file on the server file system.Its a BFILE locator of the location where actually file is located(i.e in our case it will be directory where Resume files are present).

Directory:- Database object that serves as an alias for a full path name on the server file system where the files are actually located.We need to create a database directory object that represent the database server directory where file is present(in our case it will be /usr/tmp)

File_Name:- Actual Name of the file.

2) dbms_lob.fileopen(File_Loc IN OUT NOCOPY BFILE,Open_Mode IN BINARY_INTEGER)

It will open the file in read-only mode, to enable us to read the file present in database server.

File_Loc :- Its a BFILE locator of the original file to be opened.

Open_Mode:-Indicates that file access will be read-only. This parameter can be omitted from calls to FILEOPEN because the program assigns a default value of

FILE_READONLY.

3) dbms_lob.getlength(File_Loc)

This function gets the length of the specified LOB. The length in bytes or characters is returned.The length returned for a BFILE includes the EOF, if it exists.

File_loc:- The file locator for the LOB whose length is to be returned

4) dbms_lob.loadfromfile(Dest_Lob IN OUT NOCOPY BLOB,

Src_File IN BFILE,amount IN INTEGER,

Amount IN INTEGER

Dest_Offset IN INTEGER := 1,

Src_Offset IN INTEGER := 1

);

It will help us to read the life from the source file and write it to the destination.

Dest_Lob:- LOB locator of the target for the load.In Our case we are loading resume and all the resumes are stored in binary_doc column of

irc_documents table.Hence it will be the lob locator of that column.

Src_File:- BFILE locator of the source for the load.Our case it will be BFILE locator of the file that we want to load.

Amount:- Number of bytes to load from the BFILE.In our case it should be whole file

Dest_Offset:- Offset in bytes or characters in the destination LOB (origin: 1) for the start of the load.

Src_Offset:-Offset in bytes in the source BFILE (origin: 1) for the start of the read.

5) MIME Type

Multipurpose Internet Mail Extensions (MIME) is an Internet Standard that extends the format of e-mail to support:

- Text in character sets other than ASCII
- Non-text attachments
- Message bodies with multiple parts
- Header information in non-ASCII character sets

The MIME types mainly depends on the file extension. Some of the common MIME types are given below(File extensions are case insensitive,i.e, MIME type will be same for pdf or PDF)

Now lets start our pl/sql program design step by step (Here we will write a program to upload a single file)

a) First we have to create a row in irc_document table with blank binary_doc column. This can be achieved by calling oracle provided API

irc_document_api.create_document. Also commit the changes.

irc_document_api.create_document(p_validate => FALSE

,p_effective_date => SYSDATE

,p_type => 'RESUME'

,p_person_id => p_person_id

,p_mime_type => p_mime_type

,p_assignment_id => NULL

,p_file_name => p_file_name

,p_description => 'Resume'

,p_document_id => p_document_id--Out

,p_object_version_number => l_object_version_number--out

);

once we create the create document we need to call another oracle API irc_document_api.process_document.

This procedure performs the conversion from character data stored in IRC_DOCUMENTS.BINARY_DOC into character data stored in IRC_DOCUMENTS.CHARACTER_DOC. In addition it also performs fast / full synchronization of the data in CHARACTER_DOC to allow it to be searched.

b) Now we have to create database directory object that serves as an alias for a full path name on the server file system. here database directory name 'RESUME_IN'

EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY RESUME_IN AS '''||p_file_path||'''');

c) We have to get the BFILE locator of the resume file(file name:- ABC.rtf)

l_file_on_os:=bfilename('RESUME_IN',p_file_name);

d)Open the file in readonly mode

dbms_lob.fileopen(l_file_on_os,dbms_lob.file_readonly);

e) Now we have to create the Lob_locator, so that we can read the file from source and store it in the destination.This can be done by opening the particular row (identified by API out parameter Document_id) in for update mode and selecting the column where we will store the resume (here it should be binary_doc column)

into BLOB datatype variable.

SELECT binary_doc

INTO l_temp_blob

FROM irc_documents

WHERE document_id = p_document_id FOR UPDATE;

f) Now we are ready to read the file and store it in the destination lob_locator.

dbms_lob.loadfromfile(l_temp_blob,l_file_on_os,dbms_lob.getlength(l_file_on_os));

g) Once the reading is complete ensured that we are closing the open file.

dbms_lob.fileclose(l_file_on_os);

h) Commit the transaction.

i) Again we have to call the irc_document_api.process_document

This procedure MUST be called after every call to CREATE_DOCUMENT or UPDATE_DOCUMENT.

and then commit.

Note:- 1) To avoid exceeding the SESSION_MAX_OPEN_FILES limit, include a matching call to the FILECLOSE procedure for each BFILE that is opened.

When an exception occurs after opening a file, it is possible that execution continues without closing the file (i.e., the matching call to FILECLOSE is

not executed due to abnormal termination). In this case, the file remains open, and we run the risk of exceeding the SESSION_MAX_OPEN_FILES limit.

It is good practice to include a call to the FILECLOSEALL procedure within an exception handler whenever FILEOPEN is used.

2) Before you migrate your solution to any controlled environment(UAT/PROD) test the code properly in Test environment.

3) The directory argument is case sensitive. That is, we must ensure that we specify the directory object name exactly as it exists in the data

dictionary.

For example, if an "Resume_In" directory object was created using mixed case and a quoted identifier in the CREATE DIRECTORY statement, then

when using the BFILENAME function we must refer to the directory object as 'Resume_In'.

Disclaimer:- This is a knowledge sharing site. This topic talks about a custom solution. Oracle may not provide you a support for any data

corruption or any other problem in your custom code/problem arises because of the custom code. The author is not responsible for any

kind of system/data problem appears because of usages of this code.Reader/implementer must do it on his/her own risk/responsibility.