Sign in

Oracle PL/SQL,Informatica

United States
November 12, 2017

Contact this candidate


Ramesh M

Email : Mobile: 631-***-****

Professional Summary

** ***** ** ********* ********** in maintaining, delivering and managing complex IT solutions using, Oracle SQL, PL/SQL(8i/9i/10g/11g/12c), Informatica,Unix.

Experience in all phases of the SDLC (Software Development Life Cycle) of waterfall and agile methodologies from analysis, design, development, testing, implementation and maintenance with timely delivery against aggressive deadlines

Experience in working as Individual contributor

Experience in Working on multiple concurrent projects, prioritization the tasks and met the tough project deadlines.

Experience in Conceptual,Physical,Logical,Dimension data modeling .

Extensively worked on Data migration, Data cleansing, Data Staging of operational sources.

Experience in implementing etl logic using Oracle PL/SQL, SQL Loader.

Experience in identifying performance bottlenecks of SQL queries and improving the performance by using various performance technics including views, Explain plan, TKPROF,Indexing, Hints,table Partitioning.

Developed and Modified complex PL/SQL packages, functions, Procedures as per client

Experience in loading high volume of data using PL/SQL packages.

Identified several SQL and PL/SQL bottlenecks using DBMS_PROFILER, explain plan, auto trace.

Proficient in using Cursor variables, ref. cursors, varrays, Nested tables and Dynamic SQL in PL/SQL.

Experience in implementing complex business rules through Informatica transformations like Expression, Aggregator,Filter,Lookup,Router,Joiner,Update Strategy,Normalizer,Stored Procedure.

Expertise in Informatica Transformations involving Sources like Oracle, Oracle ERP, SQL server, Flat Files, XML Files and Targets like Oracle and Flat Files.

Experience in writing SQL Queries for data reconciliation process.

Experience in fixing performance issues in Informatica using session partitions.

Experience in Error Handling and debugging etl components using Informatica debugger.

Experience in scheduling etl jobs using AutoSys and Control-M schedulers.

Experience in Performance Tuning of sources, targets, mappings and sessions.

Experience in writing UNIX shell scripting for sftp, batch processing and automation of various Oracle Database jobs

Involved in Code reviews to ensure code meets client business standards

Maintained Object scripts versions using version controls CVS,PVCS,Clearcase,VSS and created label to them for production deployment

Involved in code deployment activities and sanity check in UAT and Production environment

Domain experience in Retail,Banking,Financial, Supply Chain Management, Logistics, Insurance.

Technical Skills

Programming Languages

Oracle(9i/10g/11g/12c) SQL,PL/SQL

DB IDE Tools

SQL Plus,Toad,PL/SQL Developer,SQL Navigator

DW Tools

Informatica 9.1/9.5/9.6,OWB 11g

Scripting Languages

UNIX Shell Scripting

Data Modeling Tools


Scheduler Tools


Version Controls


Domain Expertise






Project Details

1.Client: HCTRA,TX July2015 - till date

Description: HCTRA( Harris County Toll Road Authority) deals with toll operations.HCTRA’s RRS Solution provides solution for Customer services,Finance Department,Toll operations.It is integrated with RITE application of all Vehicles’s tags validation,Accounts maintaince,Payment,billing details etc.

Technology/Tools: Oracle (12c) SQL, PL/SQL,Toad,PL/SQL Developer,SQL Developer,Informatica 9.6,OWB 11g

Role: Senior Application Developer


Prepared technical design documents as per function specification.

Prepared DDL, DML scripts and executed them

Implemented ETL process using Oracle PL/SQL packages, procedures and functions to source data and loaded it into the RRS system.

Extensively used analytical functions and Materialized Views for reporting purposes.

SQL Query Optimization issues fixed using Explain Plans, Hints,Parallel process and PL/SQL performance issues fixed using Bulk collect, forall.

Created SQL queries to validate and reconcile data in tables.

Migrated OWB mapping logic into Informatica mappings to load data into Facts using various transformations like Expression, Lookups Joiner, Filter, Sorter, Router, Update strategy, Normalizer and Sequence generator from Sources Oracle,Flat files.

Created SQL Queries for data reconciliation between Source and DW.

Fixed performance issues by applying Session Partitioning, Incremental Aggregation, indexing lookup condition columns, optimizing SQL query, etc.

Deployed (Exported/Imported)mappings/workflows from Development to Test Repository and promoted to Production.

Created Script in UNIX for automation of Power Center sessions and workflows.

Preparing implementation plan for moving the code to production such as pre implementation, post implementation and back out plan.

Involved in DB environment set up for QA testing.

Created test plans and executed during development phase to ensure the code meet business requirements.

Interacted with testers to resolve any issues or defects during testing and verification.

Worked on Integration testing and User Acceptance Testing (UAT).

Involved in root cause analysis of defects and fixing those defects.

Checked-in DB Objects latest versions in ClearCase.

Involved in code deployment activities and sanity check in UAT and Production environment.

Involved in 24/7 production support

2.Client: Estee Lauder,NY August2012-July 2015

RDS Application is Estee Lauder ‘s C2M Portfolio application. Estee Lauder’s entire research and development are maintained by this application. RDS maintains all research formulation data.This project was developed on Agile scrum methodology.

Technology/Tools: Oracle(11g) SQL, PL/SQL,Unix,SQL Navigator,Erwin,Informatica 9.5,Autosys

Role: Team Lead


Involved in Design of ER Diagrams for new and existing change requests.

Prepared Technical Design documents based on functional documents provided by business team.

Created PL/SQL Procedures, Functions, Packages,Views, Materialized View,Triggers.

Improved the application performance significantly by tuning SQL Queries and PL/SQL program units using explain plan, TKPROF,Indexing, Hints,table Partitioning,DBMS_PROFILER.

Analyzed the application enhancement requests came from client and provided suggestions to the team.

Extracted data from source systems like Oracle and flat files as per the requirements and loaded it to Oracle DW Database.

Designed Informatica mappings by using various basic transformations like filters, routers, source qualifiers, lookups,joiner,update strategy,aggregators, XML source qualifiers, sorters etc.

Building reusable components include reusable expression, mapplets, sessions and flows to facilitate flexible portfolios.

Scheduled Informatica workflows using AutoSys.

Used session logs, Informatica Debugger and Performance logs for error handling when workflows and session fails.

Involved in code reviews to ensure applications meet coding standards.

Created scripts in Unix environment for daily and monthly audit reports of the daily transactions and monetary adjustments.

Created Unix scripts for file transfers using sftp.

Worked with off-shore team for development and Testing activities.

Checked-in DB Objects latest versions in VSS.

Involved in code deployment activities and sanity check in UAT and Production environment.

Followed ITIL-V3 process for production support activities.

Conducted daily status meeting with team and reported the status to client.

3.Client: Emirates Airline,Dubai April2011-July 2012

‘HR Direct’ was an Oracle based web intranet application integrated with oracle HRMS system designed to manage all types of employee i.e. permanent, contingent, temporary etc across all countries and to handle employee attendance, visa processing, leave management, salary details,promotions, warnings etc. The application was accessible to all employees depending on their grades.

Technology: Oracle(11g) SQL, PL/SQL, SQLServer, Informatica 9.1, Control-M

Role: Senior Application Developer


Collected requirements from business users and prepared functional and technical documents.

Designing and development of complex PL/SQL packages, procedures, functions and SQL scripts as per business requirements.

SQL Query Optimization issues fixed using Explain Plans, Hints,Parallel process and PL/SQL performance issues fixed using Bulk collect, forall.

Debugging the issues and implemented bug fixes and enhancements within tight deadlines.

Involved in Unit Testing and Integrated Testing with multiple Components.

Designing and development of ETL from Source SQL server, Flat files to Oracle using Informatica mappings and workflows.

Experience in implementing update strategies, incremental loads and Change Data Capture (CDC) using SCD Type-1 and SCD Type-2

Debugging the mapping issues and implemented bug fixes and enhancements within tight deadlines

Involved in Unit Testing and Integrated Testing with multiple Components.

Checked-in DB Objects latest versions in CVS.

Participated in change control reviews for the code changes that go into production

Involved in code deployment activities and sanity check in UAT and Production environment

Provided production support and 24/7 support for resolving the critical production issues.

4.Client: Verizon,TX March2009- August2011

‘Eazy Status’ is for tracking and obtaining the status of orders placed by customers in Verizon which provides a one stop shop for Implementation Managers-IM to track their customer’s order progress from order submission to installation. This application provides the latest data for pending orders and allows the Implementation Managers to take a proactive approach for tracking their orders and reviewing milestones. This project follows the Agile& Scrum methodology.

Technology/Tools: Oracle(10g) SQL, PL/SQL,Toad.

Role: Senior Software Engineer


Involved in requirement analysis,Datamodel, preparing high level, low level technical documents.

Created new DB Objects (tables,views,sequences,synonyms,dblinks etc) and PL/SQL procedures, functions, packages,triggers as per business requirements.

Daily status update in scrum meeting.

Created indexes on table columns to improve SQL query performances.

Identified high resource consumed long running SQL queries and re-wrote them to use less resource consumption with quick query response.

Created shell scripts for batch process and automated these scripts using Cron.

Defined indexes on various tables to improve the performance of the queries for reporting purpose.

Wrote complex packages for batch processes.

Checked-in DB Objects latest versions in CVS and labeled scripts for production deployment.

Involved in code deployment activities and sanity check in UAT and Production environments.

Client: Cisco,CA Aug 2007 – Mar 2009

TAXPRO an inter-company tax calculations of Cisco which includes PBT, PAT. The Inter-company tax calculations are done in Entities level (GL Entity, Corp Tax Entity) .The Company has subsidiary companies in the world and for each company a unique account number is assigned. The amounts are divided into two categories i.e payables and receivable with respect to parent company. Each amount is converted into parent currency and the net amount is calculated based on Sets of Books id. After the conversions the net amount is considered as the 956 payable taxable amounts.

Technology/Tools: Oracle(9i) SQL, PL/SQL,Toad

Role: Senior Software Engineer


Analyzed the business requirements and converted these into technical design document.

Created Design, Technical, Installation and Requirement specification verification documents for both new and existing projects.

Created PL/SQL packages,Procedures,Functions,Triggers, collections, ref-cursors to pass the data between Java application and the Oracle database.

Loaded data from flat files into Tables using SQL*Loader utility.

Performed Unit testing, integration and System testing to ensure the application meets business requirement.

Deployed DB objects into all environments in all phases (DEV, QA and PROD) of project.

Proactively tuned SQL queries and performed refinement of the database design leading to significant improvement of system response time and efficiency.

Involved in SQL tuning, PL/SQL tuning and Application tuning using tools like EXPLAIN PLAN etc.

Checked-in DB Objects latest versions in PVCS and labeled scripts for production deployment using Quintana.

Client: Trizetto,CA Jan 2007 – July 2007

FACETS is an Insurance System which handles information about policies,payments,insurer .Health insurance usually provides either direct payment or reimbursement for expenses associated with illnesses and injuries. The cost and range of protection provided by your health insurance will depend on your insurance provider and the particular policy you purchase. If your employer does not offer a health insurance plan, you may wish to purchase health insurance on your own.

Technology/Tools: Oracle(9i) SQL, PL/SQL,Toad

Role: Software Engineer


Involved in requirement analysis, preparing high level, low level technical documents.

Implemented business logic using Oracle PL/SQL Procedures, functions, packages.

Application tuning by analyzing table, index statistics.

Involved SQL,PL/SQL tuning using Indexes, hints,forall bulkcollect.

Performed Unit testing, integration and System testing to ensure the application meets business requirement.

Interacted with subject matter experts and business analysts to resolve any issues related to the requirement.

Checked-in DB objects in VSS

Involved in production deployment activities and performed sanity check.

Client: Gati,Hyderabad Sep 2004 – Sep 2006

WHMS is a warehousing and logistics system which deals with the all shipping operations of Gati ltd which takes care of the field operations and including total accounting processes/operations and warehouse operations. The shipment data can be accessed from all the Gati offices in the world. All data can be accessed and manipulated depending on the user's privileges.

Technology/Tools: Oracle(9i) SQL, PL/SQL,Forms,Reports,Toad

Role: Software Engineer


Worked closely with functional team to understand business needs and documented the requirement.

Developed new oracle forms as per the business requirement.

Involved in implementation of new Oracle PL/SQL Procedures,functions,packages.

Created new forms (open_form,new_form,call_form) as per business requirement.

Created new menus,LOVs,Radio groups,Text boxes with validations

Developed new oracle reports and modified existing reports as per business requirement.

Generated reports for management to analyze the business and make decisions.

Wrote many adhoc SQL queries for data analysis and debugging, fixing issues.

Identifying and debugging issues, fixed, and implemented bug fixes and enhancements within tight deadlines

Checked-in DB Objects latest versions in VSS.

Involved in production support activities.

Academic Qualification

Master of Science in Mathematics from Osmania University, India

Bachelor of Technology in Agricultural Engineering from ANGRAU,India.

Post-Graduation Diploma in Banking and Financial Services from Acharya Nagarjuna University, India

Contact this candidate