Sunitha Bobbala

SENIOR DEVELOPER – Oracle PLSQL

Summary:

9+ years of experience as an Oracle PLSQL Developer having hands on experience in database designing, development, production support, performance tuning, backup and recovery, migration, documentation.

Experience in Client-Server application development using Oracle 12c, 11g/10g/9i/8i, PL/SQL, SQL *PLUS, SQL*LOADER.

Domain Knowledge on Financial, Accounting as qualified with Chartered Account ICAI.

Good knowledge on logical and physical Data Modeling using normalizing Techniques and ER- Diagrams.

Good experience in writing complex SQL queries with advanced analytical functions, REGEX and Recursive

Experience in creating DDL scripts for Tables, Constraints, Indexes .Views, Materialized views, Indexes.

Proficient in creating PL/SQL standalone procedures, functions, packages, triggers.

Experience in query optimization, performance tuning using Indexing, Hints, partitioning after analyzing EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE.

Extreme Use of exception handling methods along with Pragma Exception_init in order to associate our own created exception names.

Developed Complex database packages using cursors, collections, Ref cursor, cursor variables, System reference cursor, Dynamic SQL.

Created records, PLSQL tables, collections (nested tables and arrays) for improving Query performance by reducing context switching.

Experience in using several built-in database packages such as DBMS_JOB, DBMS_OUTPUT, UTL_FILE, and DBMS_SQL to name a few.

Good experience in Oracle Application – web applications using HTP, HTF and OWA packages

Good knowledge in using PL/SQL Wrappers to protect the PL/SQL procedures and packages.

Experience in using DBMS_SCHEDULER to run jobs in parallel to improve time-constrained performance.

Expertise in loading data from legacy systems using SQL*Loader.

Creating External tables with Import and export data Dumps.

Experience with third party database tools like TOAD, SQL Developer, and PL/SQL Developer.

Strong knowledge of Extraction Transformation and Loading (ETL) processes using UNIX shell scripting, SQL, PL/SQL and SQL Loader, Informatics.

Involved in migration projects with SQL Server,Mongo DB

Good working experience with JAVA applications (JAVA APIS) as Database developer.

Experience in writing Shell Scripts for invoking SQL scripts and scheduled them using crontab.

Worked extensively in Banking, Finance, Manufacturing, Supply Chain domains.

Work experience with Agile, Waterfall Methodologies.

Worked extensively with version control tools like TFS, CVS and SVN.

Good in creating users and maintaining their roles and responsibilities.

Experience in writing Technical design documents and developing unit test documents.

Technical Skills:

Database

RDBMS, Oracle18c, 13c, 12c, 11g, 10g, 9i.

Languages

SQL, PL/SQL, UNIX Shell Scripting.

DB Management

TOAD, SQL Developer, PLSQL Developer, Rapid SQL, SQL Navigator

Tools/Utilities

SQL*Loader, Export/Import, TOAD, RMAN, Autosys, Job Scheduler, APEX, Data pump, Data guard, MS Access. Oracle Report Builder 9i,11g,IBM-TWS,BMC Remedy

Tuning Tools

TKPROF, EXPLAIN PLAN, STATSPACK

Data Modeling tools

CA Erwin7.x, 8.x and Toad Data Modeler.

Professional Experience:

Role: Senior PL/SQL Developer

Client: UPS-SCS-FGV 02/2017 –till date

Role: Senior PL/SQL Developer

Description:

UPS Flex Global View is an integrated global visibility, reporting and event management tool, improving the flow of information as goods progress through the supply chain. FGV has robust tracking (shipments, carriers, Vehicles), email messaging,proactive alerts for event managements and reporting capabilities that gives you an integrated view of supply chain which includes modules like Transportation,Customs,Supplier, Inventory and Reporting . It has capability to create customized queries

Roles and responsibilities:

Involved in full development cycle of Planning, Analysis, Design, Development, Testing and Implementation.

Attending Agile –Scrum meetings and to create user stories and backlog works in Sprint.

Interacted with end users and functional architects for mapping the legacy data to the Oracle tables.

Designed Data Modeling, Design Specifications and to analyze Dependencies .Implementing the scripts generated from Erwin to create schema for the Development, Production and Test Environments.

Wrote sequences for automatic generation of unique keys to support primary and foreign key constraints in data conversions.

Created DDLS for Tables, Views, Constraints, Index (B Tree, Bitmap and Function Based).

Creating sequences, synonyms as per applications requirements.

Responsible for creating and modifying the PL/SQL stored procedures, function, packages, Cursor, Ref Cursor and Triggers according to the business requirement.

Developed Materialized views for data replication in distributed environments.

Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances

Used advanced Bulk techniques FOR ALL, BULK COLLECT to improve performance.

Extensively used package like DBMS_STATS, UTL_FILE, DBMS_SCHEDULER and UTL_SMTP.

Using Explain Plan, Trace and TKPROF for analyzing performance for database objects, tuning and optimizing complex Queries and PL/SQL code.

Generated server side validations through PLSQL objects.

Used the advanced features of PL/SQL like Records, Tables, Object types and Dynamic SQL.

Migrated PL/SQL Code from Oracle 10g to 11g and implemented Object types.

Used SQL* Loader and export/import mechanism for data migration for bulk upload.

Implemented PL/SQL to perform application security and batch job scheduling. Debugging the PLSQL units to resolve batch issues.

Used advance features of Oracle 11g for PL/SQL programming like Records and Collections, PLSQL tables, Bulk Bind, Ref. Cursors, Nested tables and Dynamic SQL in PLSQL objects for better performance.

Creating UNIX Shell scripts to automate various DB tasks and scheduling the backend jobs.

Fine-tuned procedures/SQL queries for maximum efficiency in various databases using Oracle Hints, for Rule based optimization.

Perform database backup and recovery in support of business continuity through multiple methods (DataPump).

Designed and developed various custom packages triggers and forms.

Tuning and balancing I/O by monitoring file system and tablespace I/O statistics.

Created some Custom reports for the end users to check the details and errors. Writing complex queries for the customized reports and representing.

Created LLD, HLD, and Unit Testing, UAT and Implementation plan documents.

Experienced in documentation, provided documentation for database design, analysis, modifications, creating schemas etc.

Environment: Oracle 11g, 12C, CA Erwin7.X, UNIX shell scripting, PL/SQL Developer, SQL Developer, Toad, APEX, SQL*Plus, SQL Navigator, Oracle Forms 11g, Reports 11g, Core java,JavaScript, Informatica 9.6.1, KMSites (KEDB, KMDB).

Role: Senior PL/SQL Developer

Client: Cigna 05/2016 –02/2017

Role: Senior PL/SQL Developer

Description:

SQR -Hyperion Migration of Exodus for Cigna Dental

Roles and responsibilities:

Involved in full development cycle of Planning, Analysis, Design, Development, Testing and Implementation.

Responsible for systems analysis, development, testing and production support of Oracle SQR to SQL migration.

Involved in migrating database from Oracle SQR to Oracle 12c.

Studying current application and preparing the case study document for migration parts.

Created COM+ module for accessing different DBs.

Developed SQL and PL/ SQL packages, procedures, functions, triggers for migration of data between databases.

Writing SQL scripts for SQR reports that has to be developed as a basis in the product.

Load the data from External Tables and Flat files, Excel to oracle database.

Created Oracle Tables, Views, Constrains, Synonyms, Sequences, created Oracle Materialized Views.

Used the inner join, outer join, cross-join while writing the complex SQL Queries.

Used Collection objects, ref cursor, and bulk collect, aggregate functions while Writing SQL Queries.

Used Analytic functions, Decode, case statement while writing complex SQL queries.

Managed performance and tuning of SQL queries and fixed the slow running queries in production with utilities like Explain, Trace, and Stored Outlines.

Exported the database and imported the same into development and test environment to test run report and job executions.

Data loads are been performed by using the SQL loader, Imports & UTL files based on file formats.

Generating files for downstream in different forms (.lis,.dat )

CLOB to file data writing for the systems to create large data files. Extensively used package like DBMS_STATS, UTL_FILE, DBMS_SCHEDULER and UTL_SMTP.

Created lots of UNIX shell script to automate the process.

Experience in writing shell scripting for various ETL needs.

Using Job schedulers and export and import scripts to migrate jobs and chain related actions.

Created parameterized reports and customized existing reports. Developed various sub-reports, running totals per period.

Creating table partitions and rebuilding indexes and table space migration.

Run the Reports from Business Object Info View.

Created PLSQL packages to use as utilities and libraries in ETL processing.

Developed complex reports – multi-functioned, leveled and grouped, summary functionality in main reports providing detailed drilldown through On Demand Sub-reports, dynamic link labeling, complex functions, cross-tabs, charts, shared variables, imported parameters and record sets from stored procedures, conditionally suppressed sections, error handling/messaging, clean and attractive designs, user friendly with instructional ToolTips.

DataMart redesign identified Fact, Transaction Tables.

Environment: Oracle 11g, 12C, CA Erwin7.X, UNIX shell scripting, PL/SQL Developer, SQL Developer, Toad, APEX, SQL*Plus, SQL Navigator, Oracle Forms 11g, Reports 11g, Core java,JavaScript, Informatica 9.6.1, KMSites (KEDB, KMDB).

Wipro Technologies LTD

Project: Nissan Inc. 04/2014 – 02/2016

Role: Senior Oracle Developer

Description:

Nissan Vehicle Incentive Management System (VIMS): Nissan VIMS is an incentive management program for Nissan Canada and USA. The top management and business team will use the VIMS application to create ad-hoc and yearly/monthly incentive programs for its dealers in different geographic regions. Nissan Dealers use the VIMS application to get the incentive information for particular vehicles.

Roles and responsibilities:

Responsible for design and development of DB objects for different modules as per Nissan Canada requirements Payment Module, Geo module and Qualification Module.

Worked with the Application Development team to implement data strategies, build data flows and develop conceptual data models.

Analyze data-related system integration challenges and propose appropriate solutions.

Perform non-production database refreshes from production databases via exp/imp, RMAN backup/restore.

Exporting/Importing the 11G Databases through export/import data pumps.

Develops, produces and maintains structural design of databases based upon logical data models and business requirements.

Coordinates and aligns with application team and ETL team.

Create, edit and delete schema objects like Tables, Indexes, Sequences, Views, and Materialized Views.

Using SQL loader to load data from flat files to databases, export/import utilities for data transfer between schemas/databases.

Implement and maintain databases with respect to access methods, access time, batch processes, device allocation, validation checks, organization, as well as protection and security.

Created scripts to create new tables, views, queries for new enhancement in the application using TOAD

Create, Edit and debug PL/SQL objects Standalone procedures, Functions, Packages, Triggers Cursors, User defined data types).

Handled errors using Exception Handling (Bulk Exceptions) extensively for the ease of debugging and displaying the error messages in the application.

Done root cause analysis to identify and designing problems specific SQL queries.

For performance, tuning rewrote application queries using hints to direct the optimizer to choose an optimum query execution plan.

Develops data migration/conversion techniques for upgrades. Evaluates database integration, integrity, and security.

Created PL/SQL scripts to extract the data from the operational database into simple flat text files using UTL_FILE package.

Local Database Administration. TNS Entries Job schedulers.

Created indexes on the tables for faster retrieval of the data to enhance database performance.

Performed capacity planning, provided backup strategy for remote production database.

Created Master scripts to load data into Master Tables.

Developed Master Detail reports in Reports 9i to meet the requirements of end users within the system.

Management of Schemas, Objects & Partitioning the tables & indexes.

Performed database recovery in various scenarios ensuring least downtime. Performed health checks on the database at periodic intervals.

Implement and maintain databases with respect to access methods, access time, batch processes, device allocation, validation checks, organization, as well as protection and security.

Produce and maintain program code, documentation i.e. preparing, and submitting Logical Data Modeling (LDM) Physical Data Modeling (PDM) Documents to the Client.

Environment: Red Hat Linux 5.0, Oracle 10g,11g and 12c, TOAD,CA Erwin, Oracle Reports Builder 11g, Informatica Power Center, APEX, SQL* Plus, SQL* Loader,Java, JavaScript, CSS, HTML.

Wipro Technologies LTD

Project: Wellington Management Company 11/2012 – 03/2014

Role: Oracle Developer

Description:

Wellington Management Company is a private, independent investment company. The company invests in the public equity, fixed income and alternative markets all around the world. Equity Enterprise reporting project is basically a reporting solution to the managers and analyst in the client organization. The objective is that the client can benefit from reduced number of reports itself and the effort required to develop these reports by reusable build elements in project work items like Functional Specification Document and Packages.

As a Production Support DB –Interacting with Business Development groups for maintenance and enhancement of the database as per the Service Level Agreement.

Analyzed the Functional Specification provided by the Architect and created Technical Specs documents for all the mappings

Analyzed and fixed the data discrepancies between various systems and identified bad data caused due the data migration from various legacy systems.

Created PL/SQL Stored Procedures, Functions, Packages, and Triggers, extensively used Cursors, REF cursors, User-defined object types, Records, in PL/SQL programming

Developed several stored procedures and functions using advanced Oracle concepts such as Bulk Binds, Bulk Collects to improve performance.

Experience with Oracle 10g and/or 11g Developer's Suite (Oracle Forms and Oracle Reports).

Exporting/Importing the 11G Databases through export/import data pumps.

Handled PL/SQL compile-time, run-time errors, debugging of Stored Procedures and packages for business logic modification, and responding to system events through triggers.

Effectively used Triggers to track the user profile updates and to send the messages to downstream systems.

Extensively used Materialized views and DB links for remote instances to retrieve data from other databases.

Coordinate with offshore team and client, update daily status reporting to client.

Monitored 24x7 production instances; supported over 10 development / QA instances.

Involved in fine-tuning the existing packages for better performance and providing on-going support to existing applications and troubleshooting serious errors when occurred.

Managed user accounts and ensured security of databases using roles and privileges.

Responsible for monitoring the scheduled JOBS and fixing the issues encountered.

Supporting EQSYS server which used to generate the Excel Reports for users.

Handling report Failures during the Batch Run and fixing it.

Interacted with front-end developers and worked accordingly to facilitate report requirements.

As per user requirements generated reports from the data base and maintained customer viewpoint.

Created new form triggers (like when-validate-trigger) to handle customer input validations base on one or several combination of conditions.

Developed automated scripts to execute PL/SQL packages and procedures.

Created different Fact and Dimension tables to increase the performance of complex SQL queries that are used in reporting.

Environment: UNIX, Oracle 11g, 10g, TOAD 9.7.2, RMAN, SQL*Plus, PL/SQL, SQL*Loader, SAP BI/BO, Oracle Forms and Reports Builder 10g, PL/SQL Developer, Lotus Notes 8.5, Share Net.

Wipro Technologies Ltd, Bangalore 04/2011 – 10/2012

Project: Nokia Siemens Networks

Role: Oracle Developer

Description:

Nokia Siemens Networks is a multinational data networking and telecommunications equipment company wholly owned by Nokia Corporation. NSN -Modular Logistics System covers mostly on the critical areas of the Enterprise Resource planning via: Sales Order Processing, Contracts, Purchase Order Processing, Registers, Hardware Repair Service, Inventory Management, Delivery Database Management, Invoicing, and Delivery & Forwarding. The Application exchanges Financial and other critical data with SAP system.

Developed Oracle Application using the System Development Life Cycle (SDLC) stages and AGILE methodology.

Analyzed and designed solutions based on stakeholder requirements.

Designed Conceptual and Physical Database Model (CDM - PDM):

Created scripts to create new tables, views, queries for new enhancement in the application using TOAD.

Created indexes on the tables for faster retrieval of the data to enhance database performance Used merge statement and parallel hints to update table with complex and nested queries

Fine-tuned large complicated SQL statements queries to optimize the application performance.

Scripts/Automation of daily support/maintenance/monitoring activities using Shell, SQL and PL/SQL scripting.

Wrote Unix Shell Scripts to process the files on daily basis like renaming the file, extracting date from the file, unzipping the file and remove the junk characters from the file before loading them into the base tables.

Created database links between two databases to access the table data from one local database to remote database using distributed database feature. Created snapshots for some most queried tables to get the most refreshed data. Planned and installed replication of database also.

Responsible for Maintaining the application availability, performance and fixing the database issues

Done root cause analysis to identify the designing problems specific to SQL queries

Resolved issues with critical MLS batch jobs like GR confirmation, Inventory reports, Ware house reservations, Invoices and shipments.

Performing Unit testing at various levels for Pre-PROD and Production environment.

Ensuring the change implementation in all pre-production and production environments.

Updating code check in to the code repository on time.

Environment: Oracle 10g/9i, Oracle Forms 9i, Reports 9i, Linux, SQL, PL/SQL, SQL Plus, Unix, Erwin Data Modeler, SQL Developer, PLSQL Developer.

Contact this candidate