Home

Oracle PL/SQL - Oracle PL/SQL

[email protected]

Location: Camargo, Illinois, USA

Relocation: yes

Visa: EAD

Aravind Email: [email protected] Phone: +1 947 228 6768 Ext 12 PROFESSIONAL SUMMARY: Overall 10 years of programming experience as an Oracle PL/SQL Developer in all phases of Software Development Life Cycle (SDLC) including Analysis, Design, Data Modeling (Logical and Physical), and Implementation of Business Applications using the Oracle Relational Database Management System (RDBMS). Proficient in PL/SQL programming - Stored Procedures, Functions, Packages, SQL tuning, and creation of Oracle Objects - Tables, Views, Materialized Views, Triggers, Sequences, Synonyms, Database Links, and User Defined Data Types. Experience working with relational databases Oracle, MySQL, PostgreSQL, Redshift and AWS - RDS Experience in advanced PL/SQL concepts like Bulk collect, Multi Table insert, PL/SQL table, Cursors, Nested tables, V arrays, Records, Types and Dynamic SQL for DML operations for better performance. Strong experience in Extraction Transformation and Loading (ETL) processes using UNIX shell scripting, SQL, PL/SQL and SQL Loader. Proficient in Oracle 11g/10g/9i/8i/7.3, PL/SQL back end applications evelopment Toad, SQL Plus, and PL/SQL Developer. Setting the MySQL tunning parameters for better database performance. Experience with data flow diagrams, data dictionary techniques, Entity relation modeling and design techniques, database normalization theory. Expertise in Database Performance Tuning, Performance Monitoring and Optimization using Oracle Hints, Explain plans, Tk-Prof and Table Partitioning. Worked on sequences, functions, synonyms, indexes, triggers, packages, stored procedures, roles and granting privileges on OLTP server. Analyzing business requirements, preparation of source-target mapping documents, conduct unit testing and code walkthroughs to ensure there are no missing elements, and perform integration testing. Setting up MySQL Slaves, backup and restoring databases, optimizing queries. Experience with Crystal Reports, iReports, TLM, Business Objects, Jasper Server and Jasper ETL, Autosys, AscentialDataStage 7.5.1, Informatica Powercentre 8.6 Experience of Data Reconciliation using PL/SQL queries and Unix Shell Scripting. Experience in creating SQL loader scripts to load data from flat files into the database and also creating External Tables to manage data which is store at the OS level. Develop many front end applications using APEX 4.2. Assist in design and development of large-scale perl applications and Created perl scripts using DBI/DBD for data extraction Developed and designed dynamic configuration wrapper in perl to generate flat fund reports Design and software coding using C, C++, multi-threading, data structure and Algorithms in different OS environments like WINDOWS, LINUX. Audited and tuned production MySQL servers. Experience working on MySQL Server Installations, Databases, Backups, Restore, Replication and Administrative activities. Experienced in Tuning Queries for better performance with large volume of data. Extensive experience in developing applications using Oracle Forms / Reports. Experience in almost all the versions of Forms/Reports. Excellent analytical, problem solving, communication and inter-personal skills to manage and interact with individuals at all levels. Able to interact effectively with other members of the Business Engineering, Quality Assurance and other teams involved with the System Development Life cycle. Expertise in implementing ETL (extract, transform and load) by using Oracle Warehouse Builder (OWB). Strong experience in Data Warehouse Concepts, Data Models, Data Marts and ETL. Experience in migration of source code using version control system (CVS, Arch, Visual source safe, svn, TFS). Experience in PL/SQL Programming using Dynamic SQL, Collections, Bulk techniques, Ref Cursors, Object types, Record types to improve performance. Developed SQL*Loader control programs and PL/SQL validation scripts to transfer flat file data into oracle database. Experience in creating Recurring Alerts using Unix Scripting and PL/SQL in Production and Reporting environment. Experience in creating ETL mappings in Informatica using Transformations. TECHNICAL SKILLS: Database: Oracle 7.x,8.x, 9.x, 11g,12c, MS SQL SERVER 2005,2008,2012, PostgreSQL and MS Access. Development tools: SQL, PL/SQL, Oracle utilities. Designer tools: Oracle Forms, Oracle Reports, BI Publisher, Tableau 9.x,10.3, Oracle Database Designer, SQL Data Modular, Mongo DB 3.4. Erwin and MS-Visio, MS office ETL Tools: SQLLDR and UTL Packages, Informatica, SSIS, SSRS Cognos 10.3 OLAP Tools: Microsoft Analysis Services, Business Objects Database tools: MySQL, SQL Developer, PLSQL Developer, DB query tool & TOAD, Oracle APEX Job Schedulers: ORACLE Schedulers / SQL agent Operating Systems: Windows, Unix, Open VMS Web Technologies: Asp, JavaScript, VB Script, HTML, XML, VB.NET, ASP.NET, C#.NET, Python Programming Reporting Tools: BIRT, Jasper Server 3.1,3.7,4.0.2,4.5 PROFESSIONAL EXPERIENCE: Boston Scientific, Alpharetta, GA Nov 2023 - present Sr Oracle PL/SQL Developer Responsibilities: Develop database objects, table design and create scripts. Development of packages, procedures, functions and triggers using PL/SQL programming language. Conduct until testing and Release level testing of database components. Expertise in Dynamic SQL, Bulk Collections and Exception handling. Development of PL/SQL Packages, Procedures, Functions, Views and Triggers. Analyze existing PL/SQL code, Debug, Test and enhance the package codes. Involved in the continuous enhancements and fixing of production problems. Resolved different kinds of tickets issued to us, which involved in UNIX shell scripting, modifying database tables, hands on UI changes using HTML5. Generated server-side PL/SQL scripts for data manipulation and validation and materialized views for remote instances. Used to create Classes using VB.Net to implement high-level business logic based on the Requirement document. Responsible for performing quality assurance, data analysis and unit testing. Developed PL/SQL triggers on master tables for automatic creation of primary key values. Created scripts for new tables, views, queries for new enhancements in the application using TOAD/SQL developer. Developed & deployed Python script for enhanced security on new Users creation on Oracle & MySQL Databases. Responsible for new database interface development using PL/SQL Database Programming Language Created indexes on the tables for faster retrieval of the data to enhanced database performance. Upgraded Oracle 11g to 12g software in different environments for latest features and tested databases. Convert reports from Oracle to Jasper. Developed and modified triggers, packages, functions and stored procedures for data conversions and PL/SQL procedures to create database objects dynamically based on user inputs. Performance tuned the interfaces, queries through use of indexes, ranks, Oracle hints, exchange partitions etc. Wrote SQL, PL/SQL, SQL*Plus programs required to retrieve data using cursors and exception handling. Created tickets to other development teams for supporting applications. Involved with users to understand the problem they have and train them with work around for similar issues. Deploy the code in production during the Weekly and Monthly Enterprise releases. Hands-on experience working as Production DBA and working with Oracle Support by raising SR. Worked on performance and tuning on SQL queries wherever it is required. Expertise in Reading SAP IDOC segments from data files and process them by loading into oracle database using SQL Loader. Database design, SQL tuning, Index management & analysis, Analyze Schema, rebuild Indexes. Worked in Oracle jobs and Crontab Scheduler. Expertise in SQL Loader, logical backups and data pumps - expdp/impdp. Creation and Maintenance of materialized views for data replication in distributed environments. Database design, SQL tuning, Index management & analysis, Analyze Schema, rebuild Indexes. General database administration including the management of tablespaces, temp space, datafile maintenance, and schema management. Enforces and maintains database constraints to ensure integrity of the database. Environment: Oracle 9i, 10g, 11g, MySQL, Unix, SQL *Loader, SQL*Plus, Data Pump, RMAN, Toad, VB.Net, SQL Navigator, Putty, WinScp, SQL Developer, Jasper Server 4.5, WinFTP, Visual Source Safe, SharePoint. AT&T, TX Aug 2020 Oct 2023 Oracle PL/SQL Developer Responsibilities: Optimizing PL/SQL code and SQL queries by using Explain Plan, Analyze, Indexes and Hints. Tune SQL statements using hints for maximum efficiency and performance Data loaded from flat files to database using SQL Loader utility of Toad. Involved in development, deployment of application as per the user requirements. Involved in the process of collecting the requirements from users. Working closely with business team by attending face to face meetings for new enhancements or requirements in existing application. Developing solution for the new enhancements required by business, testing and implementing across the circle. Involved in Unit Testing, System Testing, System Integration Testing Analyzed, designed and developed Premium Number process and PAC process after multiple brain storming sessions with client and team and implementing the same resulting revenue increase. Managed all inventory of sim and mobile in ePOS database implementing PAC process. Monitoring of all daily scheduled processes and activities using PL/SQL queries, procedures, functions and scripts on daily basis to ensure smooth operations. Performed custom ETL loading of user data into company s database using Oracle SQL scripts, shell scripts, and push/pull data using Unix and Oracle, SQL Server and MYSQL. Handle interface between ePOS and BSCS using PL/SQL queries. Providing support to business users for i-Reporting reports whenever any issue is raised or proactively handled the data load issues. Providing custom reports using PL/SQL scripts, procedures and queries to MIS users. Doing data reconciliation of BSCS and HLR using PL/SQL scripts, procedures, packages, functions, queries and UNIX scripts. Resolving all tickets logged in BMC Remedy in given SLA and analyzed the type of issues logged and provide permanent solution to repeated issues. Initiating RFC (Request for change) process of change management for any kind of change in production application and implementing the changes as approved in RFC. Informing and supervising monthly down time of applications Confidential time of reconciliation or any change implementation. Providing training to business users for new functionalities added in applications so that they can use them easily. Creating training documents for new changes and processes to be followed by business users. Leading team of five members, assigning them modules for development, bug fixes and sending reports. Evaluation of team members, their feedback forms, one to one discussion also is part of my role. Environment: PL/SQL, Toad, Windows XP, UNIX, BMC Remedy, Tera -data, DWH Follett, Westchester, IL Sept 2018 July 2020 Oracle PL/SQL Developer Responsibilities: Development, Support, Testing and enhancement of business-critical PL/SQL scripts for collection. Developed Database Objects like PLSQL Packages/ Procedures/ Triggers/ Views/ Tables. Optimizing PL/SQL code and SQL queries by using Explain Plan, Analyze, Indexes and Hints. Tune SQL statements using hints for maximum efficiency and performance, create and maintain/modify PL/SQL packages, mentor others with the creation of complex SQL statements. Developed major enhancements required for business process in Order to Cash and Procure-to-Pay cycles Developed a custom shell FTP program used for transferring the bank statements from Citi Bank server to Oracle. Customized Move Order Pick slip Report Customized pack slip Report Responsible for Unit testing, Issue resolution during QA testing and UAT. Designing Tables, Constraints, Views, and Indexes etc. in coordination with the application development team Used TOAD for faster application design and developments Independently handled and supported the CNCS application and resolved all operational issues. Many Oracle batches were fine tuned using indexes, hints on PL/SQL scripts and altering logic approach to double fold the performance. Reduced execution time of PL/SQL scripts fetching reports of all customers for collection for a particular bill cycle from 8 hours to 4 hours. Streamlined Dunning and allocation processes of CNCS application. Provided support for DMS and FILENET application to business users and being SPOC for these. SPOC in Delhi circle for all business users and central Filenet team for launch of Filenet in place of DMS application for document scanning and storing. Handled data migration from DMS application to Filenet application using a Java utility and maintained records in excel sheets and completed in time. Ensured smooth launch of Filenet application by doing pre-launch testing, post- testing, and educating users well in advance, getting opened all network ports and login credentials for all existing users. Supported for OSSAPPS application for issuing bulk requests to BSCS instead of single request Confidential a time. PL/SQL scripts are developed to issue bulk command to billing system for users for barring, unbarring, payment and adjustments as a batch. Monitored these batched and ensured smooth execution and a report is generated using PL/SQL queries. Resolving all tickets logged in BMC Remedy in given SLA and analyzed the type of issues logged and provide permanent solution to repeated issues. Initiating RFC (Request for change) process of change management for any kind of change in production application and implementing the changes as approved in RFC. Created user education manual for how to use new application Filenet in place of DMS and provided training. Environment: Toad, Oracle, BMC Remedy, SAS Reporting GGK Technologies, India July 2015 Mar 2018 Oracle PL/SQL Developer Responsibilities: Monitoring all pending commands using PL/SQL queries and scripts. Resolving all discrepancies/failed commands from BSCS to HLR or vice versa using SQL queries, PL/SQL scripts, update data in production tables. Doing Billing and Rating process which includes generating mobile bills of customer of any cycle using PL/SQL queries and UNIX scripts. Doing bill audits for all bill cycles before bill runs to ensure accurate bills and help understand the CRM team understand the billing of any particular customer using KV and RA Modules of BSCS along with SQL queries. Involved with EBP and Billing Operations team to ensure timely generation of CG bills for audit and bill cycle run. Configuring new rate plans, promos and free units in BSCS as per client requirement by updating data using DML queries and PL/SQL scripts. Support and maintenance of CRM application with the vendor and cross checking in BSCS using SQL queries. Analysis and resolution of any charging related concern using lookup tables, configuration tables and SQL queries. Providing logs of all activities of EMG application for prepaid numbers to business users as required for example, caller tune, net pack. Configuration of prepaid product in EMG and Top up database using DML queries. Generating hourly report of status of commands to billing system using PL/SQL script Generated alert to email status of hourly report to all concerned using PL/SQL procedure and function. Created new keywords like Confidential by creating new tables, configuring all details in billing server, EMG and Top-up server and generating report using PL/SQL queries, procedures and scripts. Resolving all tickets logged in BMC Remedy related to billing system discrepancies, pending commands, EMG etc as per SLA. Involved in the complete life cycle of software development for new enhancements: Analysis, Design, Development, Testing and Implementation Created Functions, Stored Procedures, Views, Sequences and Synonyms Confidential the back end using SQL and PL SQL. Involved in pre live testing in test environment, code upload and UAT in production environment after go live. Resolved discrepancies in systems by checking HLR status and billing system using UNIX commands and resolving the same by correcting the status in BSCS using DML queries. C2N-IT, India Sept 2013 June 2015 Oracle PL/SQL Developer Responsibilities: Involved in gathering business requirements. Analyzed the requirements and converted them into technical specifications. Created technical design document for the dependant down-stream systems. Manage and co-ordinate with offshore team in India-IBM Created new Tables, Indexes, Views, Materialized Views, Table Partitions as per the requirement. Involved in writing data Migration scripts/create table scripts to load database successfully from old schema to new structured schema using SQLLDR, Export/Import. Created new mappings, modified existing mappings using Informatica Powercentre 8.6 Modified existing and created new SQL, PL/SQL Packages, Procedures, Functions and views as per the requirement. Extensively used PL/SQL Collections, Table Types and Record Types. Used Bulk Collect and For All for processing huge volumes of data. Extensively used UTL FILE, DBMS XMLGen for creating flat files and XML files used by the down stream systems. Extensively used DBMS SQL, Execute Immediate for dynamic sql. Extensively used Analytical functions for complex reporting purposes and hierarchial data Extensively worked on Cursor, RefCursor for data processing. Used Exception Handling to handle runtime & user defined exceptions. Modified existing Shell Scripts and created new Korn Shell Scripts. Used AWK, Sed utilities. Scheduled Jobs using Crontab. Created Test scripts and involved in Unit, Integration and System testing. Used Import/Export utility to move data from one DB to other. Used Explain Plan and TKProf utilities to fine tune and improve the performance of application and database. Created new and modified existing Universe items of Business Objects. Prepared System level and user level maintenance documentation. Coordinate with Off Shore team. Used Reflections for UNIX and Citrix to connect to the UNIX box. Environment: Oracle 9i(9.2.0.1.0), Oracle 10g (10.2.0.3.0 ), SQL, PL/SQL, TOAD 9.7.2, PLSQL Developer, Unix, Korn Shell Scripting, Windows NT, Reflection for Unix, Citrix, Informatica Powercentre 8.6, Business Objects Keywords: cprogramm cplusplus csharp quality analyst user interface business intelligence database information technology golang microsoft procedural language Colorado Georgia Illinois Texas