SUMMARY

• Over 7 years of experience as an Oracle PL/SQL developer with design, development, and testing of business applications such as OLTP, OLAP, Enterprise, web based, Client/Server architectures.
• Deep understanding of Software Development Life Cycle (SDLC) and implemented various models like Waterfall model, Agile model.
• Worked extensively on technologies like Oracle 9i/10g/11g/12c, SQL, PL/SQL.
• Expert in Oracle Import/Export utilities, Oracle SQL* Loader, SQL Navigator, SQL Developer, SQL*Plus, TOAD, PL/SQL Developer, ETL Process and Data Migration, SQL Server Integration Services (SSIS).
• Worked on export/import commands to move the structure and the data from development to production.
• Extensive experience in Design and Implementation of PL/SQL Stored Procedures, Functions, Packages, Views, Cursors, Ref Cursors, Collections, Records, Object Types, Database Triggers, Exception Handling.
• Assisted DBA in performing various DDL works like Creation of Schemas, Tables, Indexes, Materialized Views, Synonyms and Partitioning tables.
• Experience in designing ER diagrams, logical and physical Data Models, Use Case/Class/Sequence diagrams using tools like MS Visio, Power Designer and ERWIN.
• Proficiency in analysis, design, development, production and testing of applications in Oracle APEX.
• Experience in developing back - end PL/SQL packages, UNIX Shell Scripts for data migration and batch processing.
• Experience in Query Optimization, Debugging and Performance Tuning of the database, using the Oracle tools like Hints, Explain Plan, TKPROF, SQL TRACE, DBMS PROFILER, DBMS STATS.
• Expertise working with Data Warehouses,OLTPandOLAPimplementations with Analysis, requirements, data modeling, ETL Design, development, testing, Implementation and support.
• Experience working with Netezza database to implement data cleansing, performance tuning techniques.
• Extensively usedInformaticaclient tools Source Analyzer, Target designer, Mapping designer, Mapplet Designer, Transformations,InformaticaRepository Manager andInformaticaServer Manager.
• Expertise in development and support of Extract Transform load (ETL) processes using Informatica.
• Experience in BI reporting and visualizing tools such as: OBIEE, Cognos, MSBI (SSAS, SSRS).
• Excellent ability in writing specifications and gathering requirements from the user to develop reports using Oracle Forms and Oracle Reports as per the needs of the client and maintain documentation of the reports for future reference.
• Experience in Financial (AP, AR, FA, GL), Distribution (PO, OM, INV) modules in Oracle Applications R12.
• Experience in using various bug tracking tools like Jira, Bugzilla, IBM Rational ClearQuest and HP ALM/QC.
• Excellent experience in creating Test Cases, Test Plans, Technical Design Documents and Functional Design Documents.
• Experience in Coordinating with offshore team on daily basis for project execution and Planning.

TECHNICAL SKILLS

Languages: SQL, T-SQL, PL/SQL, Java, C#, .Net

Database: Oracle 9i/10g/11g/12c, MySQL, Microsoft SQL Server, DB2

Database Tools: Toad, PL SQL Developer, SQL Developer, SQL Navigator

Web Technologies: J2EE, JavaScript, JSP 2.0, JDBC, HTML, XML

Utilities: SQL*Loader, External Tables, EXP and IMP

Project Methodologies: Waterfall, Agile/Scrum

Version Control Tools: TFS, GIT, SVN, CVS

Operating Systems: Windows, LINUX, UNIX

Scripting Languages: UNIX Shell Scripts, Perl

BI Reporting Tools: SSIS, SSRS, Informatica, Cognos, Oracle Form Builder, Oracle Reports Builder, OBIEE

Other Tools: Erwin, Oracle APEX

PROFESSIONAL EXPERIENCE

Confidential, Rosemead, CA

Sr. Oracle PL/SQL Developer

Responsibilities:

• As a developer responsible for analysis, requirements gathering, documentation of functional/technical specifications, development, testing, and production support.
• Depending on the functionality, Created different database objects like Tables, Constraints, Stored Procedures, Functions and Packages.
• Created PL/SQL scripts to extract the data from the operational database into simple flat text files using UTL FILE package.
• CreatedPL/SQLobjects to implement business rules and load the data intoOracleDatabase using ETL andSQL*Loader.
• Developed algorithm and PL/SQL code for efficient retrieval and manipulation of complex data sets using PL/SQL packages.
• DevelopedInformaticamappings and workflows for extracts and feeds, worked with different relational databases such asOracleandSQLserver and on VSAM files.
• Worked extensively onInformaticapartitioning when dealing with huge volume of data and partitioned the tables using pass through, round robin and hash partitioning.
• Created complexInformaticamappings, mapplets and re-usable transformations and created mappings to load the data into various stages like landing, staging and target tables.
• Developed mappings usingInformaticaPower Center Designer to load data from various source systems to target database.
• Extensively usedInformaticaclient tools Source Analyzer, Warehouse designer, Mapping designer, Mapplet Designer, Transformations.
• Involved in accomplishing automateddataextraction from various RDBMS via scripts, ETL in database usage and especially indatawarehousing.
• Designed and implementedODIExtract, Transform and Load (ETL) processes, programs and scripts.
• Developed and documented theODIETL process like Creation and maintenance of the source to Client data mappings, ETL specifications and complex transformation rules and technical documentation.
• Created and manipulated stored procedures, functions, packages and triggers using TOAD.
• Created and modified several Unix Shell Scripts according to the changing needs of the project and client requirements and automated backend jobs, load data into the database.
• Implemented Data ingestion process using Unix Korn shell scripts resolved application issues in Unix production environment. Read and interpreted Unix logs.
• Extensively involved in performance tuning within packages, forms, and reports using dbms output, Forms debugger, Explain Plan, dbms profiler and optimized SQL queries, partitioned the fact tables, created materialized views for better performance.
• Created Indexes and used Bulk Collections for better performance and easy retrieval of data, by reducing context switching between SQL and PL/SQL engines.
• Wrote heavy stored procedures using dynamic SQL to populate data into temp tables from fact and dimensional tables for reporting purpose.
• Experience onDataWarehousing, developing strategies for Extraction, Transformation & Loading (ETL) mechanism using and Unix.
• Involved in requirement gathering, database design and implementation of star-schema, dimensional data warehouse using ERWIN and developed Master Detail reports using tabular and Group above reports.
• Taking existing Oracle BI Publisher/OBIEE Dashboard and other reports converting to SSRS using SQL Server.
• Analyzed, designed and developed Oracle forms queries, layout, report triggers, parameter form, data models etc.
• Responsible for design, develop and implementation of BI reporting solutions using SQL Server SSRS, SSIS.
• Used Git and Jira as version control and ticket submission tools.

Environment: Oracle 11g/12c, MS SQL Server, SQL * Plus, TOAD, OBIEE, SSIS, SSRS, Informatica 9.6.1, SQL*Loader, SQL Developer, Shell Scripts, UNIX Shell Scripting, Windows XP, Jira 6.4, Oracle Form Builder and Report Builder.

Confidential, Cincinnati, OH

Sr. Oracle PL/SQL Developer

Responsibilities:

• Analyzed and ensured efficient transition of all technical design documents and developed various SQL packages to provide support to all application developers.
• Developed conversion programs to bring Legacy details into Oracle table using SQL*Loader, PL/SQL Package and populated data into base tables.
• Involved into updating procedures, functions, triggers, and packages based on the change request.
• Wrote Stored Procedures for effective inserts and updates of the data to the tables aimed to reducing the network traffic and automated table creation and execute procedures using Unix shell scripts.
• Used Ref cursors and Collections for accessing complex data resulted from joining of large number of tables.
• Applying constraints and writing triggers to apply the business rules. Involved in error handling and transaction processing in the procedures.
• Partitioned Tables using Range Partitioning, List Partitioning and created local indexes to increase the performance and to make the database objects more manageable.
• Extensively involved in performance tuning using Explain Plan, DBMS PROFILER, Optimized SQL queries and created Materialized views for better performance.
• Creation of database objects like tables, views, Indexes on tables, synonyms, materialized views, stored procedures, packages using oracle tools like TOAD.
• Created many executable programs running onUNIXoperating system and wroteUnixShell Scripts for automating the process.
• Using the Crontab inUnixand scheduler in Windows for automatic Tasks (Analyzing tables, loading tables, Import/Export).
• DesignODImappings, procedures, and packages to perform ETL data transformations.
• Use theODIgraphical interface to define procedures, packages, and ETL jobs. Set up and maintain a secure, multi-userODIenvironment.
• Data loaded from legacy systems (ETL Operations) using PL/SQL and SQL*Loader.
• Involved in ETL in OLAP database to transfer data from source tables into the data warehouse tables using PL/SQL.
• Designed and Developed complex mappings, reusable Transformations for ETL usingInformatica Power Center
• Performance tuning of theInformaticamappings using various components like Parameter files, variables and dynamic Cache.
• UsedInformaticadesigner to create mappings using transformations like expression, filter, and aggregator, source qualifier to transform data and create extract files for downstream systems.
• Created and Configured Workflows, Worklets and Sessions to transport the data to target warehouse tables usingInformaticaWorkflow Manager.
• Involved in Unit and Integrating testing ofInformaticaSessions, Batches and the Target Data.
• Used DBMS SQLTUNE.REPORT SQL MONITOR package to generate SQL monitoring report and tune the queries.
• Developed custom Forms and Reports as per client requirements and making them web enabled using Oracle Reports builder and Oracle Forms builder respectively and developed Master Detail, Detail reports using tabular and Group above reports.
• Created and modified several UNIX Shell Scripts according to the changing needs of the project and client requirements and scheduled AutoSys jobs.
• Involved in UAT testing, Bug fixing and Production rollout.

Environment: Oracle 11g, TOAD, APEX, SQL * Plus, PL/ SQL, SQL*Loader, Informatica Power Center, Oracle Reports Builder, Oracle Forms Builder, Unix Shell script, SQL Navigator, Bugzilla 5.0.

Confidential, Renton, WA

Oracle PL/SQL Developer

Responsibilities:

• Involved in gathering the requirements from the users and write the specs for the project.
• Developed PL/SQL Procedures, Functions, Packages and Triggers using Oracle Utilities like PL/SQL, SQL*Loader and handled Exceptions to handle key business logic.
• Created Views, Tables, and Constraints during the design phase of the application and used various types of triggers like row level, statement level.
• Loaded Data from different sources using PL/SQL routines, UTL FILE, External Tables to the oracle database tables.
• Wrote Shell scripts for calling SQL scripts to automate the loading and extraction process from legacy system.
• Developed scripts in Netezza to load the data from source to target and executed them by creating packages in SSIS and developed detailed Test cases for all Netezza procedures.
• Used Bulk Collect, Bulk Insert, for all statements as needed.
• Worked on User-defined and the System defined exceptions to handle different types of errors like NO DATA FOUND, ROWTYPE MISMATCH, and PRAGMAEXCEPTION INIT.
• Used SQLCODE and SQLERRM for Error handling.
• Created connection pools, physical tables, defined joins and implemented authorizations in the physical layer of the repository.
• Developed Metadata repository (. rpd), configured metadata the Business Model layer & Physical Data Model and Web Catalogs Objects as per the reporting requirements using OBIEE Admin and Web tool.
• Identified the aggregate levels for each source and created dimension sources for each level of aggregates.
• Developed different kinds of Reports (pivots, charts, tabular) using global and local Filters.
• Extensively used Dynamic SQL wherever necessary in the application.
• Created and Configured Workflows, Worklets and Sessions to transport the data to target warehouse tables usingInformaticaWorkflow Manager.
• Worked on Informatica Power Center tool - Source Analyzer, Data Warehousing designer, Mapping & Mapplet Designer and Transformation Designer usingBusinessobjects.
• CreatedAutoSysjobs for Informatica workflow to run at on time.
• Used Dbms job to schedule some procedures, which need to run in every night.
• Involved in performance tuning using Oracle Hints, Explain Plan for existing Views and Queries, TKPROF, dbms profiler. Dbms stats to gather stats as needed to improve performance.
• Created Materialized Views to reduce query run time for the end user and used External tables to load the data from excel files.
• Build several Unix Shell scripts for PLSQL programs to schedule them on Control-M.

Environment: Oracle 11g, OBIEE, Netezza, SSIS, RHEL 5, UNIX Shell Scripting, Informatica Power Center, SQL developer, SQL*Loader, Control-M, Bugzilla 4.4.

Confidential, North Chicago, IL

Pl/SQL Developer

Responsibilities:

• Experience in writing Technical design documents and developing unit test documents.
• Worked with utilities like SQL*Loader, External Tables, Import, Export to extract and load large volume of data.
• Wrote Packages to store the business rules and exceptions and wrote PL/SQL code to load data from the flat file to the tables.
• Involved in data migration from Production to test and created test data for analyzing problems, providing solution.
• Modified the queries by using less functions in the Where condition for reduction of time for Performance issues.
• Loading the Data into tables using SQL loader and using PLSQL Package UTL FILE (ETL).
• Wrote the Unix and Perl scripts for scheduling or running the scripts for day to day transactions.
• Strong knowledge of Extraction Transformation and Loading (ETL) processes using UNIX Shell scripting, SQL, PL/SQL and SQL Loader.
• Modifying the queries by using BULK COLLECT and FOR ALL to reduce the time for performance issues.
• Automated Quality Check tasks by creating PL/SQL procedures, cursors, functions and dynamic SQL, enhanced it with creating exceptions for Error handling.
• Extensively worked with third party database tools like TOAD & PL/SQL Developer.
• Hands on exposure on TFS Version Control. This includes splitting and merging of code branches in various Release and Version Management. Experience in Checking in and checking out the code in TFS (Team Foundation Server).
• Written complex Oracle SQL queries with complexity using Inner/Outer joins, Indexes, Sequences, Union All, Intersect & Minus set operators.
• Rich experience in writing SQL queries, Anonymous blocks, Views, Materialized views, PL/SQL procedures, functions, packages, triggers, cursors, collections, Ref cursor, cursor variables, System reference cursor, Dynamic SQL.
• Created materialized view on remote database and automated scheduler of refreshing of materialized view on source side.
• Verified the Cognos Report by extracting data from the Staging Database using PL/SQL queries.
• Experience in query optimization, performance and tuning (PL/SQL) using SQL Trace, TKPROF, Explain Plan, Indexing, Hints, Bulk Binds, Bulk Collect.
• Responsible for Unit, System and UAT testing the data and involved in Bug tracking using ClearQuest.

Environment: Oracle 10g, PL/SQL, SQL*Plus, PL/SQL Developer, Cognos, TOAD, UNIX, Perl, LINUX, IBM Rational ClearQuest, TFS.

Confidential, Cincinnati, OH

PL/SQL Developer

Responsibilities:

• Interacted with client to gather the requirements and implement the same in the project.
• Wrote and modified Oracle PL/SQL, SQL statements procedures and functions in Toad for Oracle.
• Created Stored Program Units like Functions, Triggers and procedures for implementing complex database intensive business rules.
• Used features like bulk collect to process large amount of data in data base.
• Improving SQL and PL/SQL flexibility, usability, and performance, using dynamic SQL and worked as a SQL developer to support business applications using Oracle SQL and PLSQL.
• Developed Unix Shell Scripts to automate backend jobs, loading data into the database using SQL* Loader.
• Involved in analysis, design, coding and Testing.
• Built and maintained SQL scripts, indexes, reports and queries for data analysis and extraction.
• Involved in creating Database Design (Physical and logical) as per the requirement given by business.
• Analyzed tables and indexes for Cost Based Optimization (CBO) to work efficiently.
• Extracted data from Flat files using SQL*LOADER and gathered Statistics and Analyzed Tables and Indexes for Performance tuning.
• Generated SQL and PL/SQL scripts to install create and drop database objects including tables, views, primary keys, indexes, constraints, packages, sequences, grants and synonyms.
• Executing and maintaining the data warehouse Extract Transform Load (ETL) process.
• Made use of performance tuning utilities like Explain plan, SQL Trace and DBMS PROFILER to reduce the run time of complicated queries on Oracle 10g.
• Wrote complex SQL queries using joins, sub queries and inline views to retrieve data from the database. Created Partitions on the tables to improve the performance.
• Created Cursors and Ref cursors as a part of the procedure to retrieve the selected data.
• Migrated data from MS Excel sheets to Flat files.

Environment: Oracle 10g, TOAD, Oracle Enterprise manager, SQL*Loader, Unix Shell Scripting, Jira 5.

Confidential, Bloomington, IL

PL/SQL Developer

Responsibilities:

• Involved in preparing functional document, user document, process flow diagram and flowcharts.
• Worked on Erwin Data Modeler for mapping both user-defined and default data types.
• Developed PL/SQL packages, procedures, functions, materialized views, SQL Scripts and triggers to populate the historic data in the tables applying business logic.
• Wrote Package containing several Procedures and Functions in PL/SQL to validate before loading into Application database.
• Worked on sequences, functions, synonyms, indexes, triggers, packages, stored procedures, roles and granting privileges on OLTP server.
• Involved in Creation of tables, Partitioning tables, Join conditions, correlated subqueries, and nested queries, views for the business application development.
• Created temporary tables and developed SQL*Loader control files to load the legacy data into the temporary tables.
• Used the advanced features of PL/SQL like collections, nested table, array & Dynamic SQL.
• Merged data files coming from the customer into the existing data warehouse.
• Wrote stored PL/SQL Procedures for reports and involved in Developing and Generating Reports.
• Involved in Performance Tuning of SQL queries and Created indexes on tables and optimizing Stored Procedure queries.
• Extensively involved in tuning the queries and applying hints for faster and better performance of the SQL queries.
• Created the Shell Scripts to automate the execution of the PL/SQL subprograms and to move the data to store in historical folders.
• Used FTP to transfer the files into different servers as needed by the business users.
• Created the test scripts and complex queries to test the timesheet application and to quality assurance of the data.
• Involved in preparing test plans, unit testing, System integration testing, implementation and maintenance.

Environment: Oracle 10g, Forms 10, Reports 10g, Erwin, SQL*Plus, SQL*Loader, SQL, Oracle Application Framework(OAF), PL/SQL Developer, UNIX, Windows XP.

Confidential, St. Louis, MO

PL/SQL Developer

Responsibilities:

• Involved in full development cycle of Planning, Analysis, Design, Development, Testing and Implementation.
• Worked on gathering business requirements, and designed the process flow for the tool.
• Prepared the Technical Design Document based on the Business Requirement for various modules.
• Developed packages, procedures, triggers, functions, Indexes, analytical functions and Collections to implement Business Logic using SQL Navigator.
• Created PL/SQL, Shell Scripts for automation, data manipulation and validation.
• Involved in loading the data intoNetezzafrom legacy and flat files using Unix scripts. Worked on Performance Tuning ofNetezzaqueries with proper understanding of joins and Distribution
• Worked on Dynamic SQL by using Execute Immediate and DBMS SQL along with Bulk Collect.
• Designed logical and physical data models for Star and Snowflake schemas using Erwin.
• Wrote sequences for automatic generation of unique keys to support primary key and foreign key constraints in data conversions.
• Created and modified SQL, PL/SQL and SQL*Loader scripts for data conversions.
• Worked on XML along with PL/SQL and version control tool software CVS.
• Used Ref cursors, Records and Tables in PL/SQL programming.
• Designed Data Modeling, Design Specifications and to analyze Dependencies.
• Tested the application to ensure proper functionality, data accuracy, and modifications had no adverse impact on integrated system environment.
• Creating indexes on tables to improve the performance by eliminating the full table scans and views for hiding the actual tables and to eliminate the complexity of the large queries.
• Fine-tuned Procedures/SQL queries for maximum efficiency in various databases using Oracle Hints.

Environment: Oracle 10g, SQL*Plus, SQL*Loader, PL/SQL, Netezza, Erwin, CVS, SQL Navigator, XML, Windows.

Confidential

PL/SQL Developer

Responsibilities:

• Developed PL/SQL Stored Procedures, Materialized Views, Functions, Analytical functions, Triggers, packages and views.
• Loaded the data into database using the SQL*Loader from various sources.
• Used TOAD, PL/SQL developer tools for faster application design, developments Performance tuning and SQL tuning.
• Involved in Data warehouse design with Star schema.
• Involved in table redesigning with implementation of Partitions Table and Partition Indexes to make database faster and easier to maintain.
• Automating processes triggering events on scheduled days & times as defined by management using DBMS JOB.
• Written shell scripts and are scheduled using Cron jobs.
• Tuning of the SQL queries, which takes long time to process the request using Explain Plan, Hints to reduce the response time. worked on troubleshooting defects in timely manner and fixing bugs according to the software change requirement.
• Responsible for Unit testing, and documentation of the interfaces.

Environment: Oracle database 9i, PL/SQL developer, SQL Plus, SQL*Loader, SVN, UNIX, TOAD.