- Over 7+ years of experience in analysis, design, development, testing and implementation of application software as Oracle PL/SQL Developer.
- Expertise in SQL and PL/SQL programming and strong knowledge on Oracle architecture and database design
- Expertise in loading data from flat files to the Database Tables using SQL*Loader, External Tables, UTL Utilities
- Strong experience in designing and developing SQL Queries, PL/SQL packages, Functions, Stored Procedures, Views, Triggers and Cursors, Oracle objects Views, Materialized Views, Sequences, Synonyms, Database Links, and table - spaces
- Experience in advanced PL/SQL programming using Records, Collections and Bulk collect, Nested tables, Types, V-arrays involving in Landscape of Database
- Experience in client-server application development using Oracle … Toad, SQL* Plus, SQL *LOADER and PL/SQL developer
- Extensively worked on Triggers for implementing complex data integrity checks and for auditing tables
- Experienced in Unit testing and Integration Testing with excellent debugging skills
- Experience in query optimization, performance and tuning (PL/SQL) using SQL Trace, TKPROF, Explain Plan, Indexing, Hints, Bulk Binds, Bulk Collect, Creation of global temporary tables and table partitioning
- Experience in developing SQL Loader control programs and PL/SQL validation scripts for validating the data load from staging area to production.
- Sound knowledge in UNIX Shell scripting
- An expert with Excel/VBA and well aware of the close relationship Excel and Oracle have
- Extensive work experience in ETL processes consisting of data sourcing, data transformation, mapping and loading of data from multiple source systems into Data Warehouse
- Experience on the implementation of ETL Tools by using Oracle Warehouse Builder (OWB)
- Designed the Data Warehouse/ ETL processes using Informatica Power Center 9.6 to extract, transform and load data from multiple input sources
- Experience on Data Warehousing, developing strategies for Extraction, Transformation & Loading (ETL) mechanism-using Teradata and UNIX
- Developed UNIX Shell scripts to automate table creation, execute procedures using dyn amic SQL statements
- Integrated Excel spreadsheets into Oracle using SQL*Loader
- Exposure to Waterfall, AGILE software development methodologies
- Sound knowledge in PHP, Python and programming proficiency in .NET
- Expertise in Java, Algorithms, data structures and problem solving skills
- Experience with Software development life cycle (SDLC) process - performing detailed analysis, collecting requirements, documenting requirements, coding and unit testing, integration
- Developed normalized Logical and physical database models to design OLTP system
- Good understanding in the areas of Users, Roles, Privileges, Schema and Object management & Session monitoring
- Worked on production support, troubleshooting the application and resolving bugs
- Excellent communication skills and leadership qualities with analytical problem solving abilities including playing vital role in coordinating multiple business meetings
- Facilitated user training to build skills in the advanced use of Microsoft Excel
RDBMS: Oracle 12c/ 11g/10g/9i
Languages: SQL, PL/SQL,C#, Shell Scripting, Perl, Power Shell Scripting
Operating Systems: UNIX(AIX), Sun Solaris and Windows NT
Oracle Tools: SQL*Loader, TOAD, Forms Builder 10g/9i/6i, Reports Builder 10g/9i/6i
GUI: Developer/2000 Forms 10g/9i/6i, Reports 10g/9i/6i
Other tools: Sub Version (Microsoft), GIT,SVN, Putty
Confidential, Charlotte, NC
- Participated in day to day agile processes like scrums and sprints.
- Design and Develop Oracle Packages, Functions, and Procedure and Postgres functions.
- Participated in the Analysis phase, preparing the technical specification, Development and Testing documents.
- Ongoing proactive database-level performance tuning, with a good working knowledge of Oracle internals.
- Involved in User Acceptance testing and Post production support. Ensured that all defects are resolved by team within time.
- Build and maintain Oracle PL/SQL packages and stored procedures (using TOAD and PL/SQL developer), Workflows, and SQR reports as requested by client, for Processing and Analysis.
- Developed UNIX shell scripts and scheduled batch jobs
- Extensively involved in Performing database, SQL's, API's tuning and optimization.
- Coordinated with front-end Developers for implementing logic in stored procedures and functions for faster retrieval of data.
- Undertook Code Optimization and Performance Tuning of the application using Explain Plan, hints, indexing and Partitioning of tables.
- Designed and developed the data migration Scripts and Programs to upgrade the Development, QA, UAT and Production DB schemas upgrade to Oracle 12c from Oracle 11g in Development and Production Environments.
- Developed test cases that were used in conjunction with the Quality Assurance team to validate code.
- Interacted with Business Users in creating Unit and System Test cases for enhancements in the existing application.
- Developed Oracle partitions, and performance tuning/query tuning.
- Created ETL transformations and jobs using Oracle using dynamic packages and procedures using oracle code.
- Used Git and Jira as our versions control and ticket submission tools.
- Created Technical Design for the Back End PL/SQL based on Business Requirement Documents and the Functional System Design.
- Creation of database objects like tables, views, synonyms, materialized views, stored procedures, packages using oracle tools like PL/SQL Developer.
- 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 into updating procedures, functions, triggers, and packages based on the change request.
- Used Oracle JDeveloper to support JA Built complex queries using SQL and wrote stored procedures using PL/SQL in Various API's like Java, .Net and Hierarchical databases like Oracle and Access.
- Built Complex queries using SQL and wrote stored procedures using PL/SQL.
- Used Ref cursors and Collections for accessing complex data resulted from joining of large number of tables.
- Involved in moving the data from flat files to staging area tables using SQL *Loader.
- Extensively used for all and bulk collect to fetch large volumes of data from table.
- Performed SQL and PL/SQL tuning and Application tuning using various tools like TKPROF, AUTOTRACE.
- Provide support to Front End and fix defects in various stages of Testing i.e IST, ET and UAT.
- Perform Technical Validation on production Release day and send a Success/ failure report accordingly.
- Worked closely with Production Support team to Troubleshoot and fix any production issues.
- Review data transformation rules and provide technical suggestions in data transformation logic and pseudo code (PL/SQL) program asynchronously.
- Wrote SQL Loader control file for flat file data load and scheduled them in UNIX to load every night in Production Environment.
- Used Crontab to schedule the shellscripts to run at particular time on a particular day.
- Worked closely with users in gathering and analyzing requirements and providing solutions that meet their requirements.
- Worked on Oracle XML/BI Publisher Reports to show the Data from Custom tables as requested by the business.
Environment: Oracle 11g, PL/SQL, SQL Developer, TOAD, Unix Sun Solaris 2.6,Oracle Applications R11i/R12, WINDOWS NT Workstation.
Sr. Data Warehouse PL/SQL Developer
- logical data modeling using ERWIN and transforming them into physical design
- System analysis, gathering and analyzing user requirements, transforming them into functional design specifications and reviews
- Reverse Engineer the GM EPC4 (General Motors - Electronics Parts Catalog) to understand the working of EPC4, the queries involved and database objects used
- Created procedures to validate the data in the RAW tables, process them and load them into Baseline.
- Generated reports for inactive and active clients, inactive and active accounts and various transactions for different client types using crystal reports
- Used set based approach to process the ERAPA data in the BL and load them into CDR
- Created views, functions, triggers and procedures to process the data and make the data available in Chapter, page and filter item CDR tables
- Used nested tables, bulk collect, cursors, ref cursors and Exception handlers as a part of my procedures to implement the business rules
- Extensive use of analytical functions and agreegate functions
- Good knowledge of partitions and partition pruning
- Redesign/ restructure and recommended changes in PL/SQL code flow that resulted in reduction of publishing time
- Tuning the system for optimal performance, generating test scenarios, debugging, implementation, enhancement and bug fixes
- Unix shell scripting to Annotate the Images, capture callout, convert them and SQL Load the Images into the staging table
- Created indexes, plugged in hints and made use of partition pruning as necessary to reduce the execution time of the queries
- Used Explain Plan extensively to understand the execution plans made by the Oracle optimizer
- Created Unit Test Cases (UTC) to test the working of the publishing process
Sr. Oracle Database Developer
- Used Excel Macros to process the data in the Spreadsheet and load them into the staging tables
- Downloaded data from AS400 server with IBM iSeries into a .csv file
- Loaded the data from .csv into the staging tables through SQL Loader
- Created procedures, packages and functions to find the accuracy of the data in the Global Data warehouse (GDW)
- Created procedures to validate the data in the staging table, process them and load them into the main tables
- Created procedures to give the users a reporting functionality to query the data by parameters
- Used Procedures, Packages and Functions to move data from CDW (Client Data Warehouse) into the attribution environment
- Created procedures for the following requirements like move attribution commission across roles, clone the employee’s coverage across months, Bulk Employee change, Save Coverage
- Created Oracle procedures as a part of the End of the Day script to copy over the data in CDW to other products
- Used records, cursors, %TYPE and ref cursors in the procedures created
- Made use of bulk collect to process the data retrieved from cursor/ ref cursor
- Created indexes on necessary attributes for faster data retrieval
- Made use of explain plan to find the access path taken when the query is executed
- Made use of analytical functions like rank and dense rank to assign is primary to the employee having maximum portion
- Population of data in to the input tables using SQL*Loader
- Data transfer from Input tables to the Output tables using procedures, packages and functions
- Created Cursors and Ref cursors as a part of the procedure to retrieve the selected data
- Made use of Bulk Collect feature to process the data from cursor/ Ref Cursors
- Made use of Subquery-Factoring (WITH clause) to make complex SQL statements simple and make them more efficient
- Used composite data types like %ROWTYPE and %TYPE
- Using set operators in PL/SQL like Union, Union all, Intersect and Minus
- Used a wide variety of exception handlers in the procedures to handle the exceptionsDocumentation of