- 9+ years of experience as Oracle PL/SQL Developer in Oracle 12c, 11g, 10g, 9i & 8i versions and IT experience in Software Development/Maintenance & Production Support Projects.
- Technical areas of experience related to PL/SQL, Informatica, UNIX & Shell Scripting.
- Expertise in Client - Server application development using Oracle 11g/10g/9i/8i, PL/SQL, SQL *PLUS, TOAD and SQL*LOADER.
- Experience in Design, Development, and Enhancement and Maintaining applications in Oracle 9i/10g/11g/12c using SQL, PL/SQL.
- Experienced in complete life cycle Implementation of PL/SQL Development.
- Experienced in working in OLAP and OLTP Database environments.
- Proficient in writing complex SQL Queries, Stored Procedures, Normalization, Database Design, creating Indexes, Functions, Triggers, Sub Queries, Collections, Performance Tuning and Query Optimization.
- Experienced in importing data from Flat files from Legacy Applications to Oracle DB using SQL Loader.
- Creating Users, Roles and Profiles, Privilege management with role-based security.
- Expertise in Data Warehousing and ETL concepts.
- Good Exposure to Informatica Transformation concepts.
- Expertise in handling 24/7 Production Support projects.
- Good exposure to Development, Testing, Production Support, Debugging, Implementation and Documentation.
- Experienced in Requirements Gathering and Preparing Use Cases.
- Experience with Performance Tuning for Oracle RDBMS using Explain Plan and Hints.
- Comprehensive and in depth experience using the System Development Life Cycle(SDLC).
- Expertise in working with Projects in Agile Methodology.
- Created Shell Scripts for invoking SQL scripts and scheduled them using crontab.
- A self-motivated, responsible and reliable team player with a set of strong technical skills.
- Strong conceptual, business, analytical skills and innovative problem solving abilities.
- Good communication, interpersonal and problem solving skills.
- Experience in both Agile and Waterfall Software Development methodologies.
- Strong experience in Data warehouse concepts, ETL.
- Performance tuning and optimization using Explain Plan, SQL*Trace, TKPROF and Oracle Optimizer hints.
- Experienced in PL/SQL Performance Tuning using Bulk Collections and other optimization techniques,
- Expertise in Dynamic SQL and Exception Handling.
- Expertise in Data Integration of data from various data sources like SQL Server, Oracle, Flat Files, and XML files and Data Migration.
- Experienced in working with high availability and scalability databases.
- Worked with various performance tuning techniques using tools provided by Oracle like SQL*TRACE, TKPROF, HINTS, DBMS STATS etc.
- Good knowledge on logical and physical Data Modelling using normalizing Techniques.
- Created Tables, Views, Constraints, Index (B Tree, Bitmap and Function Based).
- Developed Complex database objects like Stored Procedures, Functions, Packages and Triggers using SQL and PL/SQL.
- Developed materialized views for data replication in distributed environments.
- Experience in Oracle supplied packages, Dynamic SQL, Records and PL/SQL Tables.
- Loaded Data into Oracle Tables using SQL Loader.
- Experience with Oracle Supplied Packages such as DBMS SQL, DBMS JOB and UTL FILE.
- Created Packages and Procedures to automatically drop table indexes and create indexes for the tables.
- Developed and Automated batch load process using SQL*Loader, Shell scripts and Crontab.
- Experience in SQL performance tuning using Cost-Based Optimization (CBO).
- Good knowledge of key Oracle performance related features such as Query Optimizer, Execution Plans and Indexes.
Databases: Oracle 8i/9i/10g/11g/12c, MySQL 5, MS SQL SERVER 2005/2008, DB2/UDB, SAP Tables and MS Access
Other Technologies: HP Project and Portfolio Management (PPM), HP ALM/QC, Oracle E-Business Suite 12, Oracle Recovery Manager (RMAN), Oracle Enterprise Manager (OEM), Oracle Applications Manager (OAM)
Programming Languages: SQL, PL/SQL, Unix shell scripts, SAS, C/C++/C#, Perl, XML, HTML, VB.NET
Star & Snow: Flake Schema Modeling, Erwin, Sybase Power Developer, SSIS, SSRS, OWB, ODI
Dev. Tools: Windows/Linux/Mac OS Environment, Eclipse, Git, XCode, Subversion (SVN), CVS, ClearCase.
Tools: TOAD, SQL*PLUS, SQL Developer, SQL*Loader, Crystal Reports, Forms 6i/9i/10g, Reports 6i/9i/10g, Work Flow Builder and XML Publisher.
Confidential, Denver, CO
- Preparing project-phase plans and implementation schedule, plan and conduct meeting and workshops relative to the project plans, and communicate day to day project status to all levels of management.
- Analyze, develop and implement custom application solutions using BMC Control-M scheduling tools.
- Analyze, processes, and test batch processes from requirements specifications, and ensures that the finished product meets requirements.
- Working with Application Developers to perform analysis and recommendations on new workflows identified for scheduling.
- Deployment of Workflows in development, QA and Production Control-M Environments and assist developers in performing functional and user acceptance tests.
- Created stored procedures, functions, packages, collections, triggers, object types to implement complex business functionality. Implemented data optimization, scrubbing and manipulation of staged data using PL/SQL packages.
- Encapsulated error handling and used autonomous transaction for logging. Used UTL FILE for exporting data and UTL MAIL to generate E-mails.
- Created shell scripts to fine tune the ETL flow of the Informatica workflows
- Extensively used PL/SQL features like nested table, V array and Bulk collect for high performance of stored procedures.
- Involved in writing complex SQL queries (cursors, ref cursors, sub queries, correlated sub queries). Provided support for multiple business groups and managed SDLC for multiple projects to satisfy business needs.
- Created various User defined EXCEPTIONS to control the process based on the business requirement.
Environment: Oracle 11g/10g, SQL, PL/SQL, SQL*PLUS, SQL*Loader, TOAD, Informatica, Control M
Confidential, Buffalo Grove, IL
Sr. Oracle PL/SQL Developer
- Gather requirements for change requests with the Business team and create design documents
- Provide User Access to Business End Users to the Front end tools after getting the required approvals
- Create PL/SQL packages for implementing Change Requests for adding new functionality to the application
- Created database objects like Tables, Views, Sequences, Synonyms, Stored Procedures, Functions, Packages, Cursor, Ref Cursor and Triggers.
- Wrote complex SQL Statements, Complex Joins, Co-related Sub-queries and SQL Statements with Analytical Functions
- Performed SQL and PL/SQL tuning using tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE
- Extensively used Oracle Hints to direct the optimizer to choose an optimum query Execution Plan
- Extensively used Bulk Collection in PL/SQL Objects for improving the performance
- Handled errors using Exception Handling extensively for debugging and maintainability.
- Automated Oracle and Informatica workflows using Unix Cron Utility in Unix Environment.
- Responsible for writing Unix Shell scripts for loading data using SQL*Loader. The Control Files for the tables were created and automated through UNIX shell scripts to perform data load in to Oracle tables. Used SQL Loader and PL/SQL scripts to load data into the system application.
- Performed Data Cleansing to weed out erroneous Orders from the DWH tables
- Extensively used Toad to develop Oracle PL/SQL Packages.
- Create Procedures and Functions in SQL Server to migrate data from Legacy servers to Oracle Datawarehouse tables
- Create Informatica worflows (7.0 for Flat Roll/9.0 for Tubular implementations) for implementing business logic
- Worked on Sessions & workflows for variety of loads starting from Source to Target
- Created Parameter Files to override session properties for source directory, Bad file Directory, Source, Target & created Mapplet parameters for Mapplets.
- Created Mappings using Designer, Extracted the data from the flat files and other RDBMS databases into staging area and loaded onto Data Warehouse
- Developed the Informatica Mappings by using Aggregator, SQL Overrides by using Lookups, source filter by using Source Qualifiers and Data Flow Management into multiple targets using Router Transformation
- Developed reusable Workflows, Worklets, Mappings and Mapplets using transformations including Router, Aggregator, Lookup, Filter, Expression, Sequence Generator, Update Strategy, Joiner and Union
- Create Shell scripts for automating the business logic
- Create ABPP (Agile Business Process Platform) workflows which sequences the execution of Oracle Packages, Informatica workflows, Shell scripts as per the business logic
- Deploy the code across the environments to main code consistency and update the same in Clear Case for version control
- Create Test Cases for verification and validation of the Change Requests
Environment: Oracle 10g / 11g/12c, SQL, PL/SQL, SQL*PLUS, Informatica, SQL*Loader, Oracle Forms 9i/10g, Reports 9i/10g, CVS, TOAD, UNIX, Shell scripting.
Confidential, Tulsa, OK
- Coordinated with the front-end design team to provide them with the necessary stored procedures and packages and the necessary insight into the data.
- Involved in migrating database from Oracle 9i to 10g database, developing screens and generating reports.
- Integrated with client to gather the requirements and implement the same in the project, along with many migrations of project’s application interfaces.
- Developed stored procedures and triggers to facilitate consistent data entry into the database. Written Stored Procedures using PL/SQL, functions, and procedures for common utilities.
- Participated in system analysis and data modeling, which included creating tables, views, indexes, synonyms, functions, procedures, cursors, and packages using TOAD and ERWIN.
- Created programming code using advanced concepts of Records, Collections, and Dynamic SQL.
- Used pipelined functions for faster data accessibility.
- Experienced in troubleshooting traditional export/import and data pump export/import.
- Handled Data Pump utility for the high speed infrastructure for data and metadata movement.
- Involved in working with IMPLICIT CURSORS, EXPLICIT CURSORS, and REF CURSORS.
- Created materialized views using different clauses like FAST, COMPLETE FORCE, and ON COMMIT.
- Used various Oracle PL/SQL collections such as VARRAYS, NESTED TABLE, and ASSOCIATIVE ARRAYS with INDEX BY VARCHAR2.
- Involved in creating various utility scripts to generate log files in UNIX using shell scripts.
- Optimizing performance tuning at source, target, mapping and session level.
Environment: Oracle 11g/10g, SQL, PL/SQL, SQL*Loader, SQL Developer, TOAD, ERWIN, UNIX/LINUX, Java/J2EE, Windows.
Confidential, Nashua, NH
- Gathered the requirements from the data analysts and architects and performed technical analysis to reach a viable solution.
- Responsible for Analysis, Design, Coding, Debugging and Deployment.
- Worked with the team to design, develop, test & implement system. Creation of database objects like tables, procedures using Oracle tools like PL/SQL, TOAD. Written Stored Procedures using PL/SQL.
- Worked in preparing functional document, user document, process flow diagram and flow chart.
- Developed PL/SQL Packages, Procedures and Functions accordance with Business Requirements.
- Created PLSQL procedure, used the DBMS SCHEDULER for automatic weakly sending emails to the users.
- Rich experience in writing SQL queries, Views, Materialized views, cursors, collections, Ref cursor, cursor variables, System reference cursor, Dynamic SQL.
- Written complex Oracle SQL queries with complexity using Inner/Outer joins, Indexes, Sequences, Union All, Intersect & Minus set operators.
- Trobuleshooted performance issues and bugs within anonymous blocks, Partitioning dbms output and Explain plan.
- Create various Data Mapping Repository documents as part of Metadata services (EMR)
- Short term assignment to assist Oracle in a data clean up and conversion / migration task using Oracle Warehouse Builder 10G Rel. 2, to migrate mainframe legacy to Oracle eBusiness Suite.
- Worked as an Informatica developer to design and develop Informatica mapping, to perform the Extraction, Transform and Load process by studying the business requirement from the users and the mapping documents.
- Customized Reports and Forms to meet user requirements mainly in financial modules (AP, GL, and AR).
- Developed Scripts for creation of database objects like tables, views, indexes etc.
- Provided Production Support for the application and business users.
- Handled Configuration Management Support for Release activities.
- Involved in deployment and post-deployment support.
- Created Indexes on the tables for faster retrieval of the data to enhance database performance.
- Involved in data loading using PL/SQL and SQL*Loader calling UNIX scripts to download and manipulate files.
- Created extensive documentation on the design, development, implementation, daily loads and process flow of the Informatica mappings
- Wrote PL/SQL Database triggers to implement the business rules in the application
- Analyze SQL statements, reorganize database objects and design indexes to improve response of queries.
- Developed reports using complex queries as per client request as a part of production support.
- Provided on call support, solved the user issues and made necessary changes.
Environment: Oracle 10g, PL/SQL, UNIX, TOAD, Oracle Forms 9i/10g, Reports 9i/10g, ERwin, IBM Web Sphere Data Stage, MKS Integrity, CVS, SUN Solaris, MS SQL Server
Confidential, Fort Worth, TX
- Involved in SDLC gathering requirements from end users. Participated in system analysis.
- Participated in code walkthroughs and review sessions with the development team.
- Developed stored procedures and triggers to facilitate consistent data entry into the database.
- Written Stored Procedures using functions and cursors for common utilities.
- Developed views to facilitate easy interface implementation and enforce security on critical customer information.
- Integrated packages and procedures to work with Web-Services.
- Extensively Used the DBMS XMLGEN and DBMS XMLSTORE package to generate and insert XML files in Oracle 11g.
- Performance Tuning for queries, functions and procedures for efficiency on Production environment.
- Analyzed the PROD data and developed Data Fix queries, including the cascade tables.
- Participated in system analysis and data modeling, which included creating tables, views, indexes, synonyms, triggers, functions, procedures, cursors and packages. Created programming code using advanced concepts of Records, Collections and Dynamic SQL.
- Developed Automated Batch Jobs to perform frequently running jobs.
- Monitored Indexes (XMLType: XMLIndex) and analyzed their status for performance tuning and query optimization.
- Designed/developed tables, views, materialized view, stored procedures, packages and functions.
- Used advanced Bulk techniques (FOR ALL, BULK COLLECT) to improve performance. Developed Installation scripts for all the deliverables.
- Generated Complex reports in Cognos 10.1 report studio including Drill Down reports from DMR modeled Frame work model.
- Automated scripts by scheduling control-M jobs, by using PowerShell scripts and windows scripting for improvement in performance of reporting.
- Designed AutoSys based solutions for communication of issues to technical teams.
- Involved in performance tuning on SQL using AUTO TRACE, EXPLAIN PLAN, TKPROF utilities.
- Performed SQL and PL/SQL tuning and Application tuning using various tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE.
- Written and edited shell scripts to automate the execution of the procedures and report the results through e-mail.
- Created Oracle forms components such as object groups, property classes and visual attributes
- Created and manipulated stored procedures, functions, packages and triggers using TOAD. Wrote heavy stored procedures using dynamic SQL to populate data into temp tables from fact and dimensional tables for reporting purpose.
- Created PL/SQL scripts to extract the data from the operational database into simple flat text files using UTL FILE package.
- Created materialized views using different clauses like FAST, COMPLETE, FORCE, ON COMMIT.
- Experience in working with IMPLICIT CURSORS, EXPLICIT CURSORS, and REF CURSORS.
- Worked on oracle PL/SQL collections ARRAYS, NESTED TABLE, and ASSOCIATIVE ARRAYS with INDEX BY VARCHAR2.
- Created new SSIS Package to extract date from legacy to SQL SERVER objects using BIDS and Visual studio 2010.
- Worked extensively on web forms and data binding controls like Grid View, Data List and drop down boxes and Mapping page fields to the database fields.
- Designed and created complex source to target mappings (STTM) using various transformations inclusive of but not limited to Aggregator, Look Up, Joiner, Source Forms, Source Qualifier, Expression, Sequence Generator, and Router Transformations.
- Created records, tables, collections (nested tables and arrays) for improving Query performance by reducing context switching.
- Performed and Supported unit testing, system testing and UAT testing. Dealt and resolved production issues.
- Track the development of the project and review the code to maintain good quality.
- Encapsulated error handling and use of autonomous transaction for logging. Used UTL FILE for exporting data and UTL MAIL to generate E-mails.
Environment: Oracle 11g, PL/SQL, SQL*Plus, Agile, SQL developer, Oracle Forms and Reports, TOAD, Unix Shell Scripting.