Oracle PL/SQL Developer

Ashish

ORACLE PL/SQL DEVELOPER

Ph: 412-***-****

E-Mail: *************@*****.***

Summary:

• Over 5+ years of IT experience in Analysis, Design, Development, Implementation and support Of RDBMS in Production, Development and Test environments using Oracle Tools in client server environments.

• Involved in entire life cycle of implementation (SDLC) from business analysis to final deployment and testing.

• Expertise in Software Development Life Cycle(SDLC) of client implementations including requirements gathering, business analysis, system configuration, design, development, testing, technical documentation and support

• Experience in using Oracle related tools like SQL * Loader, PLSQL Developer and SQL Developer • Strong technical background with approximately three years of production support experience in SQL, PL/SQL, Oracle, UNIX, and Shell Scripting

• Experienced in Development and Implementation of applications using MySQL and SQL Server databases

• Experience in Creating and Maintaining Database objects like Tables, Indexes, Views, Synonyms, Object types and Collections.

• Coded test packages and performed unit testing of PLSQL code using PLSQL framework and the SQL Developer unit testing package.

• Experience with Views, Materialized views, Cursors (Implicit and Explicit), Ref Cursors, and External tables, table partitioning, Index partitioning and nested loops, Advanced PLSQL.

• Extensive working knowledge on SQL Loader control programs, and PL/SQL validation scripts to transfer flat file data into oracle database tables

• Expertise in developing and customizing Oracle Forms and Reports using Oracle Forms10g/9i, Reports10g/9i, Discoverer 9i/10g, XML Publisher and developing concurrent programs using SQL, PL/SQL.

• Advanced skills in Oracle database programming using PL/SQL (Stored Procedures, Functions, Packages and Triggers), standard built-in Oracle packages

• Developed strategy for ETL and ELT process

• Developed SQL Server Integration Services (SSIS) packages for ETL process

• Strong technical expertise in UTL_FILE, DBMS_SQL, DBMS_OUPUT, DBMS_UTILITY, DBMS_JOBS packages.

• Experience in SQL or server level tuning using Explain Plan, TKPROF, STATSPACK Extensively used Cursors, Composite data type and Collections in writing the backend PL/SQL code

• Created dimensions and cubes for data warehouse using SQL Server Analysis Service (SSAS)

• Created various BI reports, dashboard using SQL Server Reporting Services (SSRS), PIVOT Tables, SAP BO, QlikView, Microstrategy, Tableau

• Knowledgeable in application and BI solutions development using Oracle APEX, Oracle Discover, SQL, PL/SQL, AWR, ADDM,,TOAD, SQL*LOADER, Informatica PowerCenter Data Integrator, OBIEE, OBIA, Oracle BI Publisher, Oracle JDeveloper

• Effectively using Table Functions, Indexes, Table Partitioning, Collections, Analytical functions, Materialized Views, Dynamic SQL, Records, PL/SQL Tables, Oracle supplied packages, Ref Cursor, External Tables, Query Rewrite and Transportable tablespaces in Oracle PL/SQL • System analysis, physical data model design and logical data model design using SAP PowerDesigner, Database administration and database tuning in Oracle RAC, Oracle EXADATA

• Design and develop BI solutions and application using Oracle PL/SQL, Oracle APEX, Oracle Forms, Oracle Reports, Crystal Reports, SSIS, SSRS, SSAS, OBIEE, OBIA, ODI, DAC, BI Publisher

• Experience in SQL performance tuning using cost-based optimization and knowledge of Query optimizer, Execution plans and Hints

• Strong experience in Data warehouse concepts, ETL

• Experience in ETL techniques and Analysis and Reporting including hands on experience with the Reporting tools such as Cognos.

• Parsing high-level design specification to simple ETL coding along with mapping standards.

• Requirement Study, high level design, low-level design, Coding, Testing, Quality assurance Documentation, Implementation and Project Planning.

TECHNICAL SKILLS :

Programming

PL/SQL, C/C++, Java, ASP.NET, HTML, XML, JavaScript, Shell Scripting, JSON,Angular, Bootstrap

Databases

Oracle, SQL Server, MS Access, MYSQL, DB2, Exadata, Teradata, Oracle 12c,11g, 10g, 9i. SQL Server Management Studio, T-SQL

Tools

SQL*PLUS, SQL *Navigator, SQL worksheet, iSQL*PLUS, TOAD, PLSQL Developer, SQL Developer, SQL Navigator, Visual Studio, Talend, Toad, Putty, forms Builder,Jira,Exadata, Reports Builder, Procedure Builder, Graphics Builder, Import &Export utility, ERWIN 3.5/4.0, ER Studio, SQL Developer Data Modeler, Explain Plan, TKPROF, Microsoft Excel, Microsoft PowerPoint

Source Control

Visual Source Safe, Clear Case, Win CVS, TFS, SFTP

Reporting Tools

Crystal Reports 9.0, Cognos Reports, Report 6i and 9i, Oracle Reports, Oracle Forms

Other

Informatica, XML spy, Microsoft Office, Toad, SQL* Loader, Workflow, WinRunner, SSIS, SSRS, Microsoft Visio, VI Editor, FTP, SFTP, HTML.5, ASP.NET

Operating Systems

UNIX (Solaris/HP-UX), AIX, Linux, Windows Server, Windows, Mac

WORK EXPERIENCE:

Client: S&P GLOBAL - New Jersey November 16 – Present

Role: ORACLE PL/SQL DEVELOPER

Description: Applications each pertaining to various Business Areas like Human Resource (HR Data Mart), E-Learning (TIS - Training Integration System) and Finance Service Distributed data residing in heterogeneous data sources are consolidated into target enterprise data warehouses. This Data Warehouse is used to deliver reports and information to sales and marketing management

Responsibilities:

• Experienced in interacting with Business Users, Business Analyst, Project Managers, and developers to gather requirements and translating requirements to technical design specifications and source to target mappings.

• Designed and Developed database objects like tables, views, indexes, procedure, functions and packages.

• Involved in Performance Tuning, which included creating Indexes, Providing Hints, Modifying Tables, Analyze Tables and Estimate Stats using Explain plan utility, also in changing the existing static report generating Procedures and Packages to Dynamic Procedures to support from multiple schemas.

• Created/modified Oracle procedures, packages and functions to move the change data across multiple environments using Database links.

• Interacted with front end developer and worked accordingly to facilitate report requirements.

• Worked on different tasks in Workflows like sessions, events raise, event wait, decision, e-mail, command, worklets, Assignment, Timer and scheduling of the workflow.

• Worked on database connections, SQL joins, cardinalities, loops, aliases, views, aggregate conditions, parsing of objects and hierarchies.

• Created sessions, configured workflows to extract data from various sources, transformed data, and loading into data warehouse.

• Used Debugger to test the mappings and fixed the bugs.

• Used various transformations like Filter, Expression, Sequence Generator, Source Qualifier, Lookup, Router, Rank, Update Strategy, Joiner, Stored Procedure and Union to develop robust mappings in the Informatica Designer.

• Done analysis of Source, Requirements, existing OLTP system and identification of required dimensions and facts from the Database.

• Expert knowledge of Oracle Forms and Oracle Forms, Oracle Reports

• Developed various complex applications using Oracle Forms and Oracle Reports

• Developed various mapping by using reusable transformations in Data marts.

• Involved in full life cycle development including Design, ETL strategy, troubleshooting Reporting, and Identifying facts and dimensions.

• Created dimensions and cubes for data warehouse using SQL Server Analysis Service (SSAS)

• Created various BI reports, dashboard using SQL Server Reporting Services (SSRS), PIVOT Tables, SAP BO, QlikView, Microstrategy, Tableau

• Designed the ETL processes using Informatica to load data from Oracle, Flat Files (Fixed Width and Delimited) to staging database and from staging to the target Warehouse database.

• Responsible for monitoring all the sessions that are running, scheduled, completed and failed. If the session fails debug the Mapping.

• Involved in testing Unit and integration Testing of Informatica Sessions, Batches, fixing invalid Mappings

.• Built the Informatica workflows to load table as part of data load.

• Wrote Queries, Procedures and functions that are used as part of different application modules.

Environment: Windows, Oracle 12c, Informatica Power center, Workflow Monitor, TOAD, Erwin, Oracle SQL, PL/SQL, T-SQL, SQL Developer, MS Vision

Client: Wellington Management - Boston,MA September 2015 to October 2016

Role: Oracle PL/SQL Developer

Description: Workforce Management Application Design outlined in this document builds upon the discussions and notes captured during the company management team meetings about streamline the company processes and procedures. The project scope is to develop the following features in Workforce Management System. Employee registration and administration, Timesheet management, Employee / Company document management, Company Dashboard, Job posting / notifications, Hiring process / procedure, Client Invoices and Payments, Company events calendar.

Responsibilities:

• Actively involved in interacting with front end users and gathered User Requirement and Online System Specification. • Writing Procedures, Functions, Triggers and Packages.

• Written Stored Procedures using PL/SQL and functions and procedure for common utilities using Oracle 11g/12c.

• Created number of database objects like tables, views, indexes, synonyms and materialized

views using SQL Loader.

• Involved in creating procedures, functions, packages and triggers to facilitate functionality in different modules.

• Created data mapping files for the data coming from different Web Services.

• Created audit tables and implemented complex business logic using triggers.

statement level trigger and multiple triggers.

• Worked on XML along with PL/SQL to develop and modify web forms.

• Utilized SSIS to populate data from various data sources and also in the creation of packages for different data loading operations for applications and to export data from other sources to SQL databases.

• Developed complex stored procedure using T-SQL to generate reports using SSRS.

• Writing database objects such as functions, stored procedures, packages and triggers to meet the functional requirements using Oracle PL/SQL and T-SQL

• Create Crystal Reports with multiple drilldown and sub-reports.

• Loaded the data into the tables using TOAD and SQL*plus.

• Involved in preparing test plans, unit testing, System integration testing, implementation and maintenance.

• Modified in Scripts due to database changes using SQL and Oracle PL/SQL

• Developed ETL sessions for initial full loading and incremental loading.

• Extensively used the advanced features of PL/SQL like Records, Tables, Object types and

Dynamic SQL.

• Monitoring nightly ETL batch flow and resolving the issues and directing it to concerned person.

• Handled errors using Exception Handling extensively for the ease of debugging and displaying the error messages in the application.

• Worked on requirement analysis, coding and testing various modules in a software

development life cycle.

• Developed Unix Shell Scripts to Schedule jobs to automate tasks of loading data into Tables.

Environment: Oracle 10g,11g,12c PL/SQL, ETL, SSIS, SSRS, SQL*Plus, SQL*Loader, SQL Developer, TOAD, Crystal reports, Agile.UNIX, UNIX Shell Scripts

Client: Wellcare - Tampa, FL May 2014 to July 2015

Role: Oracle PL/SQL Developer

Description: WellCare Provides Health Plans and has developed a full complement of expertise in three major areas of government-sponsored health care: Medicaid, Medicare Advantage and Medicare Prescription Drug Plans. My role towards the project is developed ETL procedures to ensure conformity and understanding the business requirements in designing the data warehouse.

Responsibilities:

• Created custom PL/SQL procedures to read data from flat files to dump to Oracle database

using SQL * Loader.

• Developed PL/SQL Procedures and database triggers for the validation of input data and to implement business rules.

• Created database objects like packages, procedures, and functions according to the client

requirement.

• Extracting the data from simple flat text files into operational database.

• Used SSIS to create ETL packages dtsx files) to validate, extract, transform and load data to data warehouse databases, data mart databases, and process SSAS cubes to store data to

OLAP databases.

• Created the PL/SQL packages, procedures, functions applying the business logic to load the data to relevant tables.

• Extensively worked in tuning the queries and applying hints for faster and better performance of the SQL Queries and Developed SSIS project for transferring the data.

• Developed views to facilitate easy interface implementation and enforce security on critical

customer information.

• Perform T-SQL tuning and optimizing queries for Reports and SSIS packages.

• Involved in Extraction, Transformation and Loading (ETL) of the data in the table for Oracle

database and Converted different source system data into oracle format T-SQL.

• Created Reports using SQL Server Reporting Services (SSRS)

• Created records, tables, collections for improving performance by reducing context switching.

• Created number of database triggers according to business rules using PL/SQL.

• Develop, test and maintain ETL procedures employing both ETL tools and custom PL/SQL

• Wrote complex SQL Queries including inline queries and sub queries for faster data retrieval from multiple tables.

• 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.

• Responsible to tune ETL mappings to optimize load and query Performance

• Developed Oracle Forms for form end user using oracle form builder 10g.

• Created data mapping files for the data coming from different Web Services

• Extensively used the advanced features of PL/SQL like Records, Tables, Object types and

Dynamic SQL.

• Advising requesters and implementation using SQL and PL/SQL scripts to target the best

audience for the marketing/sales campaign/event scheduled.

• Tune ETL procedures and STAR schemas to optimize load and query Performance.

• Involved in gathering and documenting the requirements that were critical to the business process and using those requirements to design application software using a Use Case approach

Environment: Oracle 11g, T-SQL, SQL*Plus, SQL*Loader, PL/SQL Developer, Web Services, SSIS, SSRS, TOAD, Agile

Client: IRIS DIGITAL TECHNOLOGIES Pvt. Ltd, India April 2013 – April 2014

Role: PL/SQL Developer

Description: The project aimed in developing reports and dashboards that visualize the timely status reports, measuring performance and recognizing backlogs in completion of orders for team centric from real-time OLTP system. Xcelsius Dashboards visualizations were also offered for Historical reports to track order provisioning process and to gauge order provisioning performance and helped upper management to make critical decisions to improve customer experience and expectations.

Responsibilities:

•Interacting with the business users to understand the functionality and their requirements.

•Designed and developed custom packages, procedures, Triggers using PL/SQL.

•Loaded data into Oracle using PL/SQL, Dynamic SQL UTL_FILE, SQL* Loader and extensive use of External Tables

•Designed the packages and procedures for getting the data from Source systems to ESA (Enterprise Staging Area) and from ESA to EDW(Enterprise Data Warehouse)

•Extensively worked on Oracle Packages, procedures and functions using PL/SQL, SQL, SQL Navigator and PL/SQL Developer.

•Performed unit testing on the developed code to ensure that its bug free and handled Production issues and manage the same in issue log.

•Interacted with Business Analysts and Users in requirement gathering, built complex queries using SQL and wrote stored procedures using PL/SQL.

•Involved in the database design for better understanding the relations, associations and dependencies within the database.

•Involved in creating peoplesoft Interactive Charts and Navigation links using Java Script

•Designed Technical design document based on the business requirements.

•Worked on SQL*Loader to perform bulk data loads into database tables from external data files.

•Designed and developed Oracle PL/SQL Procedures, Functions, and Database Triggers and involved in creating and updating Packages to meet business requirements.

•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.

Environment: Oracle 10g, PL/SQL, Report Builder 10.1.2.0.2, Jasper Server 3.1.1/1.3.0, Jasper ETL, IReport 3.1.1/1.3.0, Toad 7.5.2.0, UNIX.

Contact this candidate