Home

Prabhagar - Oracle Developer

[email protected]

Location: Pittsburgh, Pennsylvania, USA

Relocation: Yes

Visa: H1B

Experience Summary Overall 17+ years of experience in IT industry, 15+ years of IT experience as Oracle Developer/DBA for System Analysis, Designing, Testing Development & Support of Oracle 8i, 9i, 10g, 11g & 12c in Production, Staging, Development environments. Professional Summary Highly skilled and experienced PL SQL Developer/DBA with wide knowledge of a variety of programs and procedures. Adept at all types of data storage and extraction. Able to work well independently or as part of a team. Core Qualifications Excellent expertise in Shell and Unix programming Experience in financial, retail and telecom domain. Expert in creating SQL Queries, PL/SQL package, function, stored procedure, triggers, and cursors. Created database objects like tables, views, sequences, synonyms, indexes using Oracle tools like SQL*Plus , SQL Developer and Toad. Enforced data integrity using integrity constraint and database triggers. Proficient in advance features of Oracle 11g for PL/SQL programming like Using Records and Collections, Bulk Bind, Ref. Cursors, Nested tables and Dynamic SQL. Experience in SQL and PL/SQL tuning and query optimization tools like SQL Trace, Explain Plan, and DBMS_PROFILER. Strong knowledge in Oracle utilities like SQL*Loader, Export/Import and data pump utilities like EXPDP/IMPDP. Extensively used package like DBMS_STATS, UTL_FILE, DBMS_SCHEDULER and UTL_SMTP. Strong knowledge with PL/SQL Wrapper to protect the PL/SQL procedures or packages. Having good exposure with versioning tools SVN, GIT. Extensively used Cubes & Rollups for making cross tabular reports. Strong knowledge of Star Schema, Snow Flake Schema, Dimension and Fact table. Expertise in handling errors using Exceptions. Making structural changes to the database objects. Transferred data using data transfer tools like FTP (File Transfer Protocol) and SFTP (Smart FTP) Exposure & knowledge of designing logical & physical Data Model in Relational Database Management System (RDBMS). Experience in working high-transacted OLTP systems. Extensive experience with all phases of the Software Development Life Cycle including analysis, Resource Planning, code development, testing, implementing and maintenance. Documentation of projects for Functional Requirement Spec (FRS), Use case Spec, ER-Diagram, Test Plan, and Test Script & Test Cases. Have worked in fast paced AGILE environment & attended daily scrum meeting. Assisted QA Teams for bug testing, User Acceptance Testing (UAT) & System Integration Testing. Highly motivated, excellent team player, enthusiastic, strong initiative and quick learner of new technologies. Strong interpersonal and communication skills in dealing with customers. Technical Proficiency Databases : Oracle 7.x/8i/9i/10g/11g/19c (SQL, PL/SQL, Stored Procedures, Triggers), MS SQL SERVER 2000/2005/2008, Postgres,Terra Data and MS Access. ETL Tools : SQL*Loader, ODI Reporting Tools : Crystal Reports, Oracle Reports 2.5 Operating Systems : UNIX(Sun Solaris, LINUX, HP UNIX, AIX), Windows NT/98/95/2000& Windows XP. Languages/Utilities : SQL, PL/SQL, Unix shell scripts, Java, XML, C and Cobol. Tools : Toad, SQL Developer DevOps Tools : Docker, Jenkins. Grunts and Jira Education M.C.A from Bharathidhasan University, Trichirappalli, India B.Sc., Computer Science Bharathidhasan University, Trichirappalli, India Projects Details Employer DVI Technologies Inc (Mar 2019 Till Date) Duration Jan 2023 Till Date Client Elixir Solutions/Medimpact Technology Oracle 12c/Teradata/Unix/ Postgres,/Python Role Senior Developer, Elixir Avengers Project: Pharmacy Claims Settlement Project Summary: Working closely with the Avengers team to handle claims processing and setting up new clients. This involved improving the existing procedures, creating setup files, and automating processes using scripts. Additionally, I managed data extraction from SFTP file system and file creation, ensuring smooth data handling. Roles and responsibilities are: Worked on the performance improvement of existing PLSQL Stored Procedure. Created configuration file (.Json) for all the set-up on which claims will be settled by running the Stored Procedure. Created bash script to trigger the Stored Procedure and get the configuration from .Json file. Created bash script which will delete the profile for terminated subscribers and not to proceed the claims. Making data warehouse extract to pull the data from Teradata and making data file using fast export. Working on SQL*Loader to load data from the flat files received from DW. Created and modified several UNIX shell scripts according to the changing needs of the project and the client requirements. Automated a process by using python script to get notification directly in email for any claims settlement process failure. Automated a process to capture the claims rejection from pharmacy switch which is in Elastic Search Database and send a report to the compliance and finance team if it is any huge number of rejections are found in last 1 hr to take necessary action. This report will run every hour. Duration Aug 2021 Dec 2022 Client Verizon Technology Oracle, Postgres, Python and Unix. Role Technology Lead, Data collection Team Project Summary: Worked with Data collection team (NPP) and taking care of data load from different source systems. Interacting with downstream system by providing data point for their testing and helping data collection team. Responsibilities: Interacting with business users to gather new requirement and enhancements. Made new python scripts to load the data from upstream Oracle to Postgres. Worked with automating data refresh steps. Created new tables for the enhancements and related procedures as per requirements. Worked with stored procedures to auto change Wave and Tactic status and letter file status in Customer Communication Portal (CCP) Worked with upstream NAR team to analysis the data for making business reports, related to migrating circuits and enhancements. Duration Jun 2018 Jun 2021 Client American Eagle Outfitters Technology Oracle 12c/Teradata/Unix Role Oracle PL/SQL Developer/DBA Project Summary: Coordinating with the Demand/Intelligent Fulfilment team with the necessary stored procedures and packages and the necessary insight into the data. Responsibilities: Making data warehouse extract to pull the data from Teradata and making data file using fast export. Working on SQL*Loader to load data from the flat files received from DW. Created and modified several UNIX shell scripts according to the changing needs of the project and the client requirements. Wrote UNIX shell scripts to process the files on daily basis like renaming the file, extracting data from the file, unzipping the file and remove the junk characters from the file before loading them into base tables. Involving in the continuous enhancements and fixing the production problems. Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances. Developed PL/SQL triggers, 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. Involving in data loading using PL/SQL and SQL*Loader calling UNIX scripts to download and manipulate files. Performed SQL and PL/SQL tuning and Application tuning using various tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE. Coordinating with the Demand/Intelligent Fulfilment team with the necessary stored procedures and packages and the necessary insight into the data. Developed scrips to support the following processes: Determine best fit Prepack vs Bulk based on Store Needs Pre-allocate an ASN arriving within an arrival window before leveraging on hands (backstock) Conversion of Prepack supply to units (after prepacks leveraged initial ASN and OH Prepack) for projection & removal prior to next batch flow. Leveraging alternate source to meet unmet store needs from primary DC Creation of identifiers, when not automatically provided in interface. Core data creation and processes associated with non-forecasted/replenished items Supersession Product Transition (Hash) input/logic Store Remodel/Relocations Inventory Reservations. Duration Nov 2016 Feb 2019 Employer Legend Solutions LLC Technology Oracle 11g/Forms 11 Role Oracle PL/SQL Developer, DBA Worked with WFO Migration project which transit the patient details legacy SPARCS to new SPRx workflow by implementing different modes comprises of Batch transaction, status trigger and traffic cop logic. In case of Batch transition of patient to SPRx, the patients are qualified and then transitioned SPRx based on the batch transition rules laid out. The status transition of patients works when the patient qualifies to be served in SPRx based on completions of referrals and refill orders, the system shall systematically transition patient and prescriptions per transition logic. In case of Traffic cop when the business is ready to start legacy patient Transition, the system shall have the ability to route all SPRx intake RxRequests for the intake referral to either SPARCS or SPRx based on the transition logic. FiOS Trouble Ticket Predictive Analytics via Machine Learning Problem Definition Verizon FiOS approximately 6M customers generate about 12M trouble tickets per year. Collaborative efforts to reduce trouble tickets are needed to eliminate this resource drain on Verizon. This project is required to develop predictive analytical models on the big-data volume of Verizon related to customer complaints, network performance, customer environment/provisioning/equipment mix, planned and unplanned outages, equipment and network alarms, and service orders to uncover correlations that can be followed up on to mitigate trouble tickets and to implement root cause fixes to eliminate problems. Such problems are solved with Python and/or R by using methods such as Random Forest, Logistics Regression, and Time-Series Forecasting. Business Opportunity Verizon currently spends close to $500M/year to reactively respond to these 12M trouble tickets and the strategic goal of 2017 is to reduce the calls Worked with Insurance Property Insurance Application System which involves in providing homeowners insurance application. This Product does not enable users to capture Dwelling applications beyond creation of Reference number. In this Release the product is enabled to capture dwelling new business applications through PIAS. Responsibilities: Gathering functional requirements from the business and understand the functional specifications and technical aspects of the project. Create the temporary tables, staging tables and DML scripts for the SPRx database. Created Audit triggers which sets the primary key field, create by, create date when inserting, update by and update by and update date when updating the record. Created after insert or after update trigger on staging table for updating the staging tables. Created PL/SQL packages, procedures and function based on the business Performed all kinds of testing and involved in all activities to move the codes to SIT, UAT and go live to production. Working with stored procedures and functions to extract data from three oracle schema to get alarm details, customer details, trouble ticket details and non-trouble ticket details. Extracting data from the Oracle Server loaded them into systems like SQL Server for analysis purpose 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. Duration Mar 2012 Oct 2016 Client Verizon, VDSI Chennai Team Size 7 Technology Oracle 10g/11g, OBIEE 11.1.1.6 Role Spec-System Dev Around 5 years of experience with expertise as Oracle PL/SQL Developer in areas of Application software development and maintenance in Oracle environment using Client/Server. Over 2 years of experience in DW/ETL Technologies. Coordinated with the front-end design team to provide them with the necessary stored procedures and packages and the necessary insight into the data. Worked on SQL*Loader to load data from flat files obtained from various facilities every day. Created and modified several UNIX shell Scripts according to the changing needs of the project and client requirements. 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. Involved in the continuous enhancements and fixing of production problems. Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances. Developed PL/SQL triggers and master tables for automatic creation of primary keys. 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. Involved in data loading using PL/SQL and SQL*Loader calling UNIX scripts to download and manipulate files. Performed SQL and PL/SQL tuning and Application tuning using various tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE. Extensively involved in using hints to direct the optimizer to choose an optimum query execution plan. Created PL/SQL scripts to extract the data from the operational database into simple flat text files using UTL_FILE package. Creation of database objects like tables, views, materialized views, procedures and packages using oracle tools like Toad, PL/SQL Developer and SQL* plus. Partitioned the fact tables and materialized views to enhance the performance. Extensively used bulk collection in PL/SQL objects for improving the performing. Created records, tables, collections (nested tables and arrays) for improving Query performance by reducing context switching. Used Pragma Autonomous Transaction to avoid mutating problem in database trigger. Extensively used the advanced features of PL/SQL like Records, Tables, Object types and Dynamic SQL. Handled errors using Exception Handling extensively for the ease of debugging and displaying the error messages in the application. Involved in migrating database from oracle 9i to 10g database. Provided assistance to OBIEE support team on scheduling daily OBIEE refreshes, monthly book close refreshes and resolving ETL/ODI job aborts Assisted and administer the deployment process of Unix, OBIEE and repository codes in development Duration Jun 2011 Mar 2012 Client Ford Technologies through Thirdware Solutions Pvt Limited Team Size 5 Technology Oracle PLSQL, MS Access, SQL Developer Role Senior Technical Analyst Worked with GIFS (Global Investment and Forecasting System) in AD (Application Development) department for new enhancement and modify the existing modules as per the new requirements from the users. I was involved in making procedures, packages and GUI. Involved in three phases and played vital role as a lead. Responsibilities include fine tune the existing queries to make is faster and modify the exiting procedures and packages based on the changes required by the Business users. Duration Oct 2009 May 2010 Client Standard Chartered Bank, Singapore though TANGSPAC Consulting Pte Limited Team Size 5 Technology Oracle 10g/11g, Red-Hat Enterprise Linux 4.0 and 5.0, TOAD Role Oracle PS/SQL Developer Responsibilities Worked with the portfolio Group Market Risk (GMR) and performed the following activities Monitoring alert log for errors, Resolving connection problems of users. Resolved several listener/TNS issues, Database and Apps Health checks. Daily and Weekly backups and Customer support over phone on 24x7 bases. Developed PL/SQL triggers and master tables for automatic creation of primary keys. Created PL/SQL stored procedures, functions and packages for moving the data from staging area to data mart. 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. Involved in data loading using PL/SQL and SQL*Loader calling UNIX scripts to download and manipulate files. Duration Jun 2008 Sep 2009 Client Deutsche Bank, Singapore though TANGSPAC Consulting Pte Limited Team Size 7 Technology Oracle 9i/10g Role Junior Oracle DBA, PLSQL Developer Responsibilities Worked with Global Markets - Transaction Management Group as a PLSQL Developer to help the CTB (Change to the Bank) team to automate their process related to trades by using MS Access and Oracle stored procedures. Involved in DBA activities like addressing database related issues. Interact with business users to gather the details regarding the current manual reconciliation process and automate it using Access and Oracle PL SQL Interacted with the Middle Office to automate their current manual process by making PLSQL stored procedures and Excel Macros. Worked with the banking products Bonds, FX, Swaps, Options during automation Worked with T0 Risk and P/L Rollout program to generate the new reports as per the user s requirements using Oracle PLSQL procedures and Excel Macros and Access. Monitoring space availability and provide Technical Supports to End users. Monitored the growth of Table spaces to see the fragmentation and available space for growth. Check for log and trace files Perform root cause analysis of difficult problems and their solutions. Managing Redo Log files in Archive log Mode for Online Backup. Monitored table spaces & their growth and took proper actions. Creating of database indexes on certain tables to improve query performance after thoroughly testing the same in the development environment. Cloning the database through RMAN as per the requirement. Duration October 2007 April 2008 Client OCBC Bank, Singapore through Kelly Services Team Size 5 Technology Oracle PL/ SQL, Bankware and UNIX Shell Scripting Role PLSQL Developer, Analyst Responsibilities Worked with ALM (Asset Asset Liability Management) team as a consultant. ALM is a risk management technique designed to earn an adequate return while maintaining a comfortable surplus of assets beyond liabilities. Takes into consideration interest rates, earning power, and degree of willingness to take on debt. Duties and responsibilities include Supporting the daily extraction of data from core banking system(SIBS Silverlake) and other treasury systems(OPICS & KPLUS) Supporting Bancware (Sungard) system that aggregates data and transforms it to ALCO reports Re-engineering of ALCO process in Access database and converted related codes into Oracle. Gather user requirements and documenting the user requirements to migrate to new ALM product Fermat. Interact with various source systems and implement changes for new products. This involved understanding the various bank products and their treatment for ALM process Involved in writing triggers, functions, stored procedures. Duration October 2006 September 2007 Client IBM-Shell, Singapore, though TANGSPAC Consulting Pte Limited Team Size 12 Technology VBA, MS Access, Oracle PL/SQL and SAP Role Senior Consultant Responsibilities Main objective of this project is Migrating data from SAP, Excel and manual data to GSAP(Global SAP) using transformation tools which are designed by the central team which is located in UK. Involved in migrating all its county data into Global SAP by country wise and I played vital role in data migration for Singapore. My responsibilities included in Extracting data from SAP(P30) and transforming data based on DOS(Document Object Specification) with all the rules and making the LSMW files. . Duration October 2004 September 2006 Client Carlson Marketing, Singapore thru Kelly Services (Singapore) Pte Ltd Team Size 7 Technology VBA, Crystal Reports Back End Tools Oracle Role Software Developer Responsibilities Maintaining all weekly reports and Monthly reports, writing scripts, stored procedures and automating the existing reports using VBA. Duration January 2003 September 2004 Client Stats Chippac Pvt Ltd, Singapore through Saicom Systems Ptd Ltd Team Size 7 Technology Excel VBA, Crystal Reports Back End Tools Sybase Role Software Developer Responsibilities Enhanced / supported existing Excel-based P&L reporting system. Utilized Crystal Reports Designer to develop report templates for Daily P&L application. Coordinated testing with external application groups dependent on legacy system. Built Excel add-in to upload data from disparate controller groups to Sybase database. Created Visual Basic 6 DLL to validate Excel data prior to upload to Sybase. Keywords: cprogramm quality analyst active directory rlang information technology golang hewlett packard microsoft procedural language Ohio South Carolina