SUMMARY:

• 6 + years of experience as an Oracle PL/ SQL Developer, including System Analysis, Design, Development & Support of Oracle Database 12c/11g in Production, Staging, Development, and Testing Environments
• Extensive knowledge of Relational Database Management Systems (RDBMS) as well as Data Warehousing including Star Schema and Snowflake Schema, and Data Marts used in relational and dimensional modeling
• Experience in drawing various data models, such as E - R Diagram, Data Flow Diagrams/Process Diagrams using ER WIN, Oracle data modeler and MS VISIO
• Expert experience in writing SQL queries and PL/ SQL Development, Stored Procedures / Functions/ Packages / Database Triggers, Exception Handlers, Cursors, PL/ SQL records, and Pipeline Function
• Expert working experience in Oracle PL/SQL Development using various Oracle key components such as Stored Procedures, Functions, Packages, DB Triggers, Views, Materialized Views, DBlinks, Exception handling, Oracle Supplied Packages, Collections, PL/SQL Types, External Tables, Merge Statements, Autonomous transactions, Global Temporary Tables (GTT), Bulk Load, Cursor, Ref Cursors, Partitioned Tables, Dynamic SQL, SQL*Loader, Data Pump, UTL FILE, Database Links etc.
• Expertise in Creating and Maintaining Database objects like Tables, Views, Indexes, Constraints, Materialized Views, Synonyms, and Sequences.
• Proficient in writing and tuning Complex SQL statements, complex joins co-related sub-queries and SQL statements with Analytical Functions (ROW NUMBER, RANK, DENSE RANK, Lag, Lead, Connect by level etc).
• Experience in writing Dynamic SQL Statements using EXECUTE IMMEDIATE and DBMS SQL.
• Worked in extensively on Query Level Performance tuning using Explain Plan, SQL Trace and TKPROF utilities to pinpoint time consuming SQL's and tuned them by creating indexes and forcing specific plans.
• Working knowledge on using new partitioning techniques like Interval, Reference and Extended Composite (List-List, List-Range) Partitioning.
• Expertise in loading data from XML/FLAT files into Oracle Database Tables using SQL*LOADER and External Tables.
• Experience in Unit Testing, writing test plans and test cases
• Strong Experience in Writing PERL, UNIX Bash and Korn Shell scripts to automate various Processes
• Proficient in creating Shell Scripts for invoking SQL scripts and scheduling them using Crontab
• Experience in releasing the enhancements and code fixes into various environments through the process of Release Deployment
• Good Experience in creation of Quality related Documents (Function specification, design document (DB design architecture) and installation qualification (for implementation of the code) and Unit Test Case, Operational qualification and procedural qualification documents)
• Extensive experience with IDEs such as TOAD, SQL*PLUS, and PL/ SQL Developer
• Experience in maintaining different versions of code using various Version Control tools, such as CVS, SVN
• Hands on experience in using different Statistical tools such as SAS, R and data visualization tools such as Tableau
• Excellent communication, interpersonal and presentation skills
• Excellent team player with the ability to work independently and interact with people at all levels

PROFESSIONAL EXPERIENCE:

Oracle PL/SQL Developer

Confidential - Queens, NY

Responsibilities:

• Involved in all phrases of a project, including gathering requirements, creating specifications, development of database objects, developing code and validating programs and production support
• Worked on Agile Scrum Methodology to meet timelines with quality deliverables
• Developed various database objects like object types, trigger, procedures, functions and packages
• Extensively used features like autonomous transactions, bulk load methods, optimizer Hints, Cursor variables and returning of multi record set from procedures and functions
• Worked extensively on Exception handling for proper display of error message to the user and debugging
• Created UNIX shell scripts to automate data loading, extraction and to perform regular updates to database tables to keep in sync with the incoming data from other sources.
• Worked on SQL*loader to load data into database, which is called CDM, from files received from Business Tech Team and manipulate the data
• Used techniques like Direct Path Load, for loading the data faster and created filters in Control files to pre-process data, as part of performance tuning
• Created and used Table Partitions and Sub partitions to further improve performance while loading daily feeds of sizes 10 - 14 GB (80 million to 120 million data set) into tables
• Implemented Sub partitions, CTAS (Create Table as Select), Exchange partition to load data into target tables
• Extensively worked on Schema Migration, Database Program Migration, Data Migration from Sybase to Oracle by using Oracle SQL Developer.
• Worked on SQL Tuning by using various tools like EXPLAINPLAN, DBMS PROFILER, Hints etc.
• Worked on Dynamic SQL by using Execute Immediate and DBMS SQL along with Bulk Collect.
• Extensively used PL/ SQL advanced features like forward declaration, Procedure Overloading in the PL/ SQL Packages.
• Used UTL FILE PACKAGES for writing DBMS OUTPUT into the files.
• Extensively used Oracle 11g features like RESULT CACHE, Compound Triggers etc.
• Worked on SQL*Loader to load data from flat files obtained from various facilities every day
• Worked on Table partitioning and Index creation to improve the performance of the query.
• Worked with DBA's regarding the data checks during migration, loading, creation and management of tables, indexes, table spaces etc.
• Developed PL/ SQL triggers and master tables for automatic creation of primary keys.
• Involved in Requirement Gathering, High Level Design, Low Level Design, Development, Unit testing & Integration Testing of Data Migration process using ETL Methodology.
• Used Informatica to extract data and modify it depending on the specific business logic
• Report the project status and generate milestone reports.
• Creation of database objects like tables, views, materialized views, procedures, and packages using oracle tools like Toad, PL/ SQL Developer, and SQL* plus.

Environment: Oracle 11g, UNIX shell, SQL Developer, Toad & Windows 7

Oracle/PL SQL Developer

Confidential - Baltimore, MD

Responsibilities:

• Involved in complete system development life cycle
• Involved in the team for documenting functional and technical specifications, including specifications for others to build
• Involved in modifying existing PL/ SQL Procedures, Functions and developing new ones
• Wrote SQL, PL/ SQL, views and Bulk Collect for implementing business rules and data transformations
• Developed procedures to run the SQL * LOADER to load data from various sources in the form of Excel, CSV or flat files into the target database tables
• Developed back end interfaces using PL/ SQL packages, stored procedures, functions, Object Types, Pipelined functions, Oracle queues and used Collections, Bulk Collects to improve performance by minimizing the number of context switches between the PL/ SQL and SQL engines.
• Wrote complex SQL queries using Joins, sub queries and inline views to retrieve data from the database.
• Created Cursors, Collections, and database triggers for maintaining complex integrity constraints and implementing the complex business rules.
• Participated in analyzing the requirements for the logical and physical design of the database.
• Knowledge of Normalization, Data Warehousing, and data transfer, documentation, preventive maintenance, code review, automation, store procedures and triggers etc. Implemented purging techniques on the data which is not required by the clients.
• Extensive use of Unix Shell Scripts and Autosys to automate process.
• Optimized SQL statements and PL/SQL blocks by using Oracle Hints, explain plans, profiling, collecting statistics and Trace sessions for Cost based Optimization
• Developed Error Logging and Data logging to better understand the performance of the ETL procedures and to associate the errors to the procedures
• Run batch jobs for loading database tables from Flat Files using SQL Loader
• Proactively tuned SQL queries and performed refinement of the database design leading to significant improvement in system response time and efficiency
• Prepared program specifications for PL/ SQL Procedures and Functions to do the conversion
• Provide timely Production Support by quickly analyzing the root cause and by providing alternatives to resolve issues.
• Created tables using Toad as well as created and managed roles for different users to access database objects.
• Written various Triggers, Stored Procedures to perform or automate many of the tasks.
• Optimized SQL queries used in Reports and Stored Programs (Procedures, Functions and Packages) to improve performance drastically. improved the performance of the application by rewriting the SQL queries.
• Wrote packages to fetch complex data from different tables in remote databases using joins, sub queries.
• Resolved Priority Production Support Issues and Defect Fixes.
• Supported operations during the build/deployment of a new release to the Integration, QA and Production environments
• Wrote PL/ SQL programs to read from files, upload data, and mass updates/inserts of data based on certain business policies
• Developed oracle Reports, PL/ SQL packages to perform Certain Specialized functions
• Extensively used various data transformation functions of PL/ SQL
• Responsible for writing Install Scripts for migration of code through various environments and implementing
• Extensively used tools like TOAD, SQL developer
• Involved in Unit testing, Integration testing, System testing, Regression and UAT testing along with other team members

Environment: Oracle 9i/10g (SQL, PL/ SQL), SQL Loader, Windows, Toad, Unix

Jr. Oracle Developer

Confidential - Boston, MA

Responsibilities:

• Involved in Production Support and Operations teams to resolve production issues in a timely and efficient manner.
• Troubleshoot the existing PL/SQL procedures, functions, triggers
• Stored Procedures and Triggers were developed for Business rules.
• Involved in writing procedural scripts using PL/SQL.
• Analyzing tables and indexes for performance tuning
• Involved in coding and testing of the system.
• Effectively resolved QA Trackers.
• Performed SQL and PL/SQL tuning and Application tuning using various tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE.
• Transferred SQL and Korn shell programs into ODI and created data extracts. Involved in Importing and exporting the Tables, Users, and Schemas.
• Worked on UNIX and Perl scripts for scheduling Autosys jobs in UAT/SIT/PFIX/PROD Environments. Experience with custom development of data warehouse, operational data store, and OLTP systems.
• Handled errors using Exception Handling extensively for the ease of debugging and displaying the error messages in the application.
• Used PL/SQL Tables, Ref Cursors to process huge volumes of data and used bulk collect and bulk bind for mass update as performance improvement process.
• Used Ref cursors and Collections for accessing complex data resulted from joining of large number of tables.
• Involved in SQL Tuning by creation of indexes, rebuilding Indexes using Explain Plan, SQL Trace and TKPROF Tools.
• Involved in creating Pseudo code documents for writing stored procedures.
• Worked in Production Support Environment as well as QA/TEST environments for projects, work orders, maintenance requests, bug fixes, enhancements, data changes, etc.
• Improved query performance by query optimization - tracing the query execution plan (explain plan).
• Created indexes for better performance.
• Developed scenarios for Unit, Integration testing to ensure that all the components work correctly when integrated.
• Involved in PL/SQL code review and modification for the development of new requirements.

Environment: Oracle 10g / 11g/12c, SQL, PL/SQL, SQL*PLUS, Informatica, SQL*Loader, CVS, TOAD, UNIX, Shell scripting.

TECHNICAL SKILLS:

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

ETL Tools: IBM Info Sphere Data Stage, Informatica

Data Warehousing: 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, Workflow Builder and XML Publisher.