PL/SQL Developer
Resume:
Naveen R
Senior Consultant
Summary:
Having over 11+ years of experience as an MS SQL/Oracle/PostgreSQL/Snowflake developer in analysis, and implementation of business applications.
Use database objects Stored procedures, Stored functions, Packages, TYPE Objects, Indexes, Triggers, cursors, REF cursors, Parameterized cursors, Views, Materialized Views, PL/SQL collections, Table Partitioning, and Dynamic SQL for my daily activities. Worked with tools like PL/SQL Developer, SQL Developer, SQL*Loader, TOAD, SQL plus.
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 Waterfall and SCRUM agile methodologies.
Well-versed in developing various database objects like Packages, Stored Procedures, Functions, Triggers, Tables, Indexes, Constraints, Materialized Views.
Good experience in technical documentation and status report preparation.
Good experience in writing complex MS SQL queries and PL/SQL procedures to extract data from various source tables.
Proficient in loading data from flat files into database tables using SQL*Loader scripts.
Worked with Collections, Records, Dynamic SQL and Exception Handling.
Involved in Performance Tuning which included creating indexes, partitioning tables, providing Hints, modifying tables using Explain Plan, SQL trace and TKPROF.
Experience in handling very large data files using Export - Import Utilities and SQL*Loader.
Work on Snowflake utilities such as SnowSQL, SnowPipe, Tasks, Streams, Time travel, Optimizer, Metadata Manager, data sharing. Automating data loading, extraction, reports generation using UNIX Shell scripting.
Good understanding of Data Dictionary, performance tuning using Query Explain plan, Hints, DBMS_PROFILER and Normalization Techniques
Develop codes using UTL_FILE, DBMS_SQL, DBMS_JOB, RETURN TABLE packages and SQL/JSON functions to extract and load data from/to operational database.
Write UNIX commands, create and modify UNIX shell Scripts according to the changing needs for data excursion.
Create SQL scripts and schedule them using crontab, generating and sending HTML reports as per client requirement.
Write Python scripts using various libraries to analyze the data in different formats (JSON, XML, CSV, excel) further using the data in generating multi tab excel reports.
Hands-on experience with Amazon services like EC2, S3, SES, Lambda, IAM, Step functions, Athena, Event Bridge, and Cloud9 used to conduct data analysis and report on results.
Develop scripts on connecting to PostgreSQL through Amazon lambda and fetching configurations to implement business rules on files in S3.
Expertise in writing Database Stored Procedures, Functions, Packages, Triggers, Cursors, Exception Handlers, Dynamic Cursor programs, Native Dynamic MS SQL and Data Collections using MS SQL & Oracle PL/SQL
Good working experience with the usage of Collections, Bulk Insert, Global temp table, Nested tables, External tables, advanced queuing, Materialized Views, Inline Views, Analytical functions and REF cursors.
Executing, monitoring various UNIX ksh scripts, escalating and bug fixing the related issues as required, providing solutions, implementing code logic as per functional, adhoc requirements.
Expertise in implementing Data modeling, Erwin, Dimensional Modeling, Ralph Kimball Approach, Star/Snowflake Modeling, Datamarts, OLAP, FACT & Dimensions tables, Physical & Logical data modeling and Oracle Designer.
Expertise in designing, developing Extraction, Transformation, and Load (ETL) scripts utilizing SQL, PL/SQL utilities and provide solutions to critical issues enhancing performance and productivity to the project.
Extensive experience in integration of data from heterogeneous sources (ERP, RDBMS, Flat Files and Web Applications). Proficient in working and conceptual knowledge of Oracle and Reports.
Extensively worked on Data Migration using Flat files and Data Mapping through SQL* Loader Imp/Exp utilities, UTL-File Package.
Experience in Performance tuning, Application tuning and Query Optimization using Hints, Explain plan, TKPROF
Understanding of ERP Systems (SAP, Oracle Applications) and worked with Manufacturing, Sales and Distribution, Inventory (Material Movement), AR and AP modules’ Rapid Mart Customization / Development.
Expert level understanding of Oracle database capabilities and DW best practices including appropriate use of indexing, material views, partitioning, snapshot capture and referential integrity.
Expertise in implementation of Data Warehousing and Decision Support systems in UNIX/Windows environments.
Hands on experience with tools like SQL Navigator, Toad, Sql*Plus, Export and Import Utilities along with Oracle Enterprise Manager (OEM). Expertise in writing Wrapper/Scheduler jobs and hands on experience in Autosys Tool.
Also worked on MS SQL Server, Access Databases and developed T-SQL, SQL, Stored Procedures, Cursors, Triggers, Views, Indexes and Constraints, Query Optimizer
Commercial experience in Data Modeling using design tools like ERWIN and MS VISIO, created ER Diagrams, Data Flow Diagrams using Design tools. Hands-on experience with source control tools like Visual Source Safe (VSS)
Major strengths are familiarity with multiple software systems, tools and the ability to learn quickly new technologies and adapt to new environments Expertise in writing Crystal Reports and MS SQL Server Reporting Services.
Worked through all phases of System Development Life Cycle (SDLC): requirements gathering, analysis, design, development, testing, UAT, production and post-production support
Good experience on debugging Informatica ETL mapping with various transformations, Workflows and monitor to ensure proper flow of data across databases and schemas.
Involve in impact analysis of the defects to the associated functionalities and modules in the production environment and code migration using Azure DevOps.
Build queries that will process data from our databases and provide results via API endpoints efficiently.
Requirement and feasibility analysis for any new development request raised by the client and making sure to meet client expectations.
Develop Triggers for audit and to facilitate consistent data into the database and using collection objects for faster data accessibility. Excellent interpersonal and communication skills, self-motivated and good team player.
Create Sqoop jobs to import and export data from HDFS to databases which internally runs MapReduce jobs.
Handling errors using Exception handling extensively for the ease of debugging, ensuring all business criteria are met.
Technical Skills:
Programming Languages: SQL, PL/SQL, Python scripting, Shell scripting
Databases: Oracle 10G/11G, 12C, 19C, PostgreSQL, Snowflake
ETL Tool: Informatica 9.1
Operating Systems: Linux, Windows
Cloud Technologies: AWS, Snowflake
Tools: SQL Developer, WinSCP, Putty, Toad, Jira, GitHub, Postman, PyCharm, pgAdmin
Certifications:
Oracle Certified Associate (OCA)
Academic:
Bachelor’s Degree in Mathematics in the year 2009 from Kakatiya University, Warangal.
Master’s Degree in Computers in the year 2014 from Jawaharlal Nehru Technological University, Hyderabad.
Trainings:
Snowflake, Bigdata Analytics (Hive, Scoop, HDFS), Azure Databricks, Microsoft Power BI, AWS Cloud Practitioner
Experiences:
Client: Mizuho Bank, Atlanta, USA(Remote)
Feb 2024 to current
Sr. MS SQL PL/SQL Developer
Description:. Directed the development and implementation of a cutting-edge Data Integration and Analytics Platform tailored to the Telecom and Banking domain, harnessing the power of UNIX Server Oracle 12c/19c, PostgreSQL, Toad, UNIX, Putty, GitHub, Informatica, AWS, and Python. This project aimed to revolutionize data processing and enhance decision-making capabilities. deep understanding of both Oracle and MS SQL databases.
Responsibilities:
Involved in data loading using PL/SQL and SQL*Loader calling UNIX scripts to download and manipulate files.
Extensively involved in using hints to direct the optimizer to choose an optimum query execution plan.
Used Bulk Collections FOR ALL for better performance and easy retrieval of data, by reducing context switching between SQL and PL/SQL engines, extensively used PLSQL Collections.
Created PL/SQL scripts to extract the data from the operational database into simple flat text files using UTL FILE package. Developed PL/SQL triggers and master tables for automatic creation of primary keys.
Generated custom reports on Oracle Reports using MS SQL Reports builder and published the reports using report server. Created adhoc reports to users in Tableau by connecting to various data sources.
Created test packages to validate all new and existing procedures and functions in Unit Testing Phase.
Involved all SIT, QA, UAT and Production deployment.
Created batch jobs to load data into our database using UNIX Shell Script and Control - M scheduling.
Created multiple SǪL models to transform data and created aggregated tables to use the same in dashboards.
Partnered with business leaders to identify and define business requirements.
Having regular meetings with Stakeholders and business. Understand user need and provided guidance in articulating them
Created Trade based money laundering report (TBML) on transactions for Singapore and Hong Kong locations. The objective of this report is to TBML activities can be identified and mitigated in the transactions.
Created process for Identifying the money laundering activities involved in the transactions such as falsified or multiple commercial, proforma invoices of the trade bills pertains to Letter of credit, Bank Guarantee, import and export transactions.
Written SQL for Identifying and quality check on the high value trade transactions swift payments.
Created the process to Identify the transactions pertains to over-shipment /under-shipment, misrepresentation of goods in the invoices, Shell companies, unusual payment methods.
Sanity checks on the quantity of restricted goods based on the HSN codes to be imported and exported by the clients.
Documented business processes and workflows to promote clarity and consistency. Created Business and Functional requirement documents
Collaborated with cross-functional teams to ensure project success.
Environment: SǪL, Snowflake, Tableau and JIRA, Oracle12c, 19c, PostgreSQL, Toad, UNIX, putty, GitHub, Informatica, AWS, Python, SQL*Loader, SQL Developer.
Client: Thomson Reuters.
Sr. MS SQL PL/SQL Developer
Dec 2022 – Dec 2023
Description: Led the development and implementation of a comprehensive Data Integration and Analytics Platform for Thomson Reuters, leveraging MS SQL,SQLPlus, TOAD, SQLLoader, SQL Developer, Shell Scripts, UNIX, Windows XP, Tableau, and Informatica 9.1. This project aimed to streamline data processing, enhance decision-making capabilities, and drive business growth by harnessing the power of advanced analytics within the Thomson Reuters ecosystem.
Responsibilities:
Coordinated with the front-end design team to provide them with the necessary stored procedures, functions, packages to view appropriate data at GUI.
Worked on SQL*Loader to load data from flat files obtained from various facilities every day.
Generated server-side PL/SQL scripts for data manipulation, validation and materialized views for remote instances.
Created new mappings, modified existing mappings using Informatica Power center 8.6
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 enhancements in the application using TOAD.
Created indexes on the tables for faster retrieval of the data to enhance database performance.
Used several objects - Vertical, Horizontal, Blank, Tiled, Floating, Text, Web-page, Image in order to create efficient dashboard using tableau desktop. Partitioned the fact tables and materialized views to enhance the performance.
In order to improve the performance of the tableau desktop EXTRACT connection is used to get the data into Tableau data engine for faster retrieval of data. If the database is fast enough to retrieve the data then we usually go for LIVE connection. Handled Tableau admin activities granting access, managing extracts and Installations.
Generated Dashboards with Quick filters, Parameters and sets to handle views more efficiently.
Creation of database objects like tables, views, materialized views, procedures and packages using MS SQL tools like Toad, PL/SQL Developer and SQL* plus. Involved in Logical & Physical Database Layout Design.
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. Created queries to minimize transactions table data size and copy into historical tables.
Performed Database Administration of all database objects including tables, clusters, indexes, views, sequences packages and procedures. Upgraded the existing database from Oracle 12c to Oracle 19c.
Modified exiting UNIX Shell Scripts to schedule daily jobs.
Improved the performance of the application by rewriting the SQL queries.
Involved application deployment process and co-ordinate with DBA Team
Environment: Oracle 19c/12c, SQL * Plus, TOAD, SQL*Loader, SQL Developer, Shell Scripts, UNIX, Windows XP, Tableau, Informatica 9.1
Client: Verizon
Oracle And MS SQL Developer - Senior Consultant
Duration: Oct 2021 - Nov 2022
Project: ADM
Description:. As a Senior Consultant specializing in Oracle and MS SQL development, you will be a crucial part of our client's data management team. You will leverage your extensive experience in database design, development, and optimization to support critical business operations. This role requires a deep understanding of both Oracle and MS SQL databases, data warehousing solutions, and ETL processes. drive business growth within the Telecom industry through advanced data analytics and insights
Responsibilities:
Developed PL/SQL Stored Packages, Procedures, Functions, Triggers according to business logic and Collections for improving Query performance by reducing context switching and fetch complex data from different tables in remote databases using joins, sub queries and database links
Used SED and AWK in UNIX Shell scripting to correct the data issues in the MS SQL Loader data file in addition, commands to perform daily activities
Created scripts to extract the data from the operational database into simple flat text files using UTL_FILE, SQL/JSON package Involved in migration of Oracle 9i database to Oracle 11g.
Responsible for creating and developing highly complex applications using Oracle as back-end with expertise in design and development of MS&Oracle PL/SQL Packages and Procedures.
Analyze System Requirements to create a Technical Design with the assistance of other Sr. Developers/Architects/Tech. Leads.
Responsible for helping set development standards for database development within PL/SQL.
Act as a technical resource, with a high level of technical and analytical expertise in programming languages, data communications and a strong understanding of the business operation/customer. Implement and support code migration into production.
Constructed complex MS SQL queries with sub-queries, inline views as per the functional needs in the Business Requirements Document (BRD)
Created Views and Triggers required for the Incident, Investigation, Consequence and Action Items modules based on the business requirements.
Designed, developed and tested MS PL/SQL packages, stored procedures, and functions to push the incident data from SCIPR to FIM.
Developed automated MS SQL scripts to schedule the load of employee/contractor user information from active directory on daily basis.
Extensively worked on ETL (Extraction, transformation and loading) using PL/SQL to Extract, transform and load incident data into MIS (Downstream HSSE Management Information Systems) data warehouse.
Played a pivotal role in the migration of open Incidents from legacy application databases to Oracle 10g in a planned manner
A reporting data mart was created to support the MIS capabilities by designing and developing Oracle PL/SQL packages, stored procedures, functions, and triggers.
Designed and created Entity-Relationship diagrams, data models and other database related information by reverse engineering defined data dictionary, database entities, parent child relationship keys, identifying primary and referential integrity constraints, sequences, stored procedures
As part of performance tuning, used TKPROF, and Explain Plan in tuning PL/SQL code and SQL queries
Created Unit and Assembly Test Scripts and performed the test cases.
Provided Ad-hoc Reports whenever the business requested and maintained a good relation with Business Customer
Worked on connecting to PostgreSQL through Amazon lambda and fetching configurations to implement business rules on files in S3 Written Python scripts using various libraries to analyze the data in different formats.
Hands On experience in debugging Informatica mappings and identify any issues in data flow.
Requirement and feasibility analysis for any new development request raised by the client and making sure for flawless code migration using GitHub.
Conduct code tuning to improve performance of the application, use Bulk binds, in-line queries, Dynamic SQL, Analytics and Subquery factoring.
Used Snowflake utilities such as SnowSQL, SnowPipe, Tasks, Streams, Time travel, Optimizer, Metadata Manager, data sharing.
Environment: UNIX Server,MS SQL, Oracle12c, 19c, PostgreSQL, Toad, UNIX, putty, GitHub, Informatica, AWS, Python
Client: Flipkart
MS SQL Developer - Senior Associate
Duration: Jun 2018 - Sep 2021
Project: MTL
Description: Directed the development of an advanced E-Commerce Data Analytics Platform for Flipkart, leveraging MS SQL and PL/SQL to optimize data processing, analysis, and reporting. This project aimed to empower Flipkart with actionable insights into customer behavior, product performance, and market trends, enabling data-driven decision-making and strategic business growth.
Responsibilities:
Developed PL/SQL Stored Packages, Procedures, Functions, Triggers according to business logic and collections for improving Query performance by reducing context switching
Implemented various customized Oracle reports using different techniques in MS SQL/ PL/SQL.
Created scripts to extract the data from the operational database into simple flat text files using UTL_FILE package
Handled errors using Exception handling extensively for the ease of debugging and displaying the error messages in the application. Worked on various backend Procedures and Functions using PL/SQL
Created various MS SQL and PL/SQL scripts for verification of the required functionalities
Created various Database triggers using PL/SQL
Generated a number of reports for management to review for system functionality as against the old legacy system
Worked with various functional experts to implement their functional knowledge into working Procedures
Worked on optimizing existing procedures and functions using PL/SQL
Used Object Libraries and Form Template using Oracle Forms5.0
Designed and attached toolbar and menu bar to forms in Oracle Forms5.0
Involved in setting up Business Area for MS SQL Objects on Discoverer, granting access to users
Created and registered Discoverer queries in MS SQL Discoverer Administration 3.1
Used Oracle Reports Builder to create various Report Formats including Sub Reports, Cross Tab Reports, Standard Reports and Master/Detail Reports
Built queries that will process data from our databases and provide results via API endpoints efficiently.
Conduct code tuning to improve performance of the application, used Bulk binds, in-line queries, Dynamic SQL, Analytics and Sub-query factoring.
Involve in impact analysis of the defects to the associated functionalities and modules in the production environment and code migration
Wrote packages to fetch complex data from different tables in remote databases using joins, sub queries and database links.
Environment: UNIX Server,MS SQL, Oracle 11g,12c, bash Shell, putty, Jira, Toad, SQL developer, Shell Scripting
Client: Cisco
Oracle Developer
Duration: Jan 2017– May 2018
Project: Cisco Network Monitoring and Management System
Description: Spearheaded the development of a cutting-edge Cisco Network Monitoring and Management System utilizing Oracle SQL and PL/SQL to optimize network performance, enhance security, and streamline operations in a Cisco domain environment. This project aimed to provide comprehensive visibility into network infrastructure, automate monitoring tasks, and facilitate proactive maintenance and troubleshooting.
Responsibilities:
Developed PL/SQL packages, procedures, triggers, functions, Indexes and Collections to implement business logic
Worked with transformations like Expressions, Aggregators, Filters, Look Up, Sequence Generator, and Update Strategy Worked as part of Crystal reports development team.
Building optimized SQL queries to suit the report requirements from multiple tables.
Used ODBC connecting drivers to connect to the database
Crystal Reports 9, SQL topics like floor function, temp tables and case SQL statements were utilized.
Mapping the DB fields on the Crystal report pages
Building Sub reports and passing data through variables to Main reports
Building formulae and sub queries behind the mapped fields and sections as per the report complexities.
Reports testing and quality assurance. Enhancements of existing reports as per the new business requirements.
Developed mappings to transform the data according to the business requirement and load data from source flat file and relational tables into the target database
Created records, tables, collections (nested tables and arrays) for improving Query performance by reducing context switching
Exported the database and imported the same into development and test environment whenever required.
Developed Triggers for audit and to facilitate consistent data entry into database Used collection objects for faster data accessibility
Handled errors using Exception handling extensively for the ease of debugging and displaying the error messages in the application.
Write packages to fetch complex data from different tables in remote databases using joins, sub queries and database links.
Export/Import the data into development and test environment whenever required from production environment.
Environment: UNIX Server, Windows, Oracle 11g, bash Shell, putty, Toad, SQL developer, Shell Scripting, Informatica.
Client: Mobistar
Junior MS SQL&Oracle Developer
Duration: Jan 2014 - Dec 2016
Project: Telecom Data Analysis and Management System
Description: The objective of this project is developed a comprehensive Telecom Data Analysis and Management System leveraging MS&Oracle SQL and PL/SQL to optimize data processing, storage, and retrieval for a telecom domain environment. The project aimed to enhance operational efficiency, improve decision-making processes, and ensure seamless management of telecom data assets.
Responsibilities:
Loading data into database from the out files, and exporting the tiff image data into text files
Data segregation for counting the tiff image details which are in the database.
Involved in de-duplication process, which is an Automated Fingerprint Identification System (AFIS) for Filtering and weeding out biometric duplicate data.
Developed backend interfaces using PL/SQL Stored Packages, Procedures, Functions, Collections and Triggers
Used PL/SQL Tables and Records in payment generation process
Handled errors using Exception handling extensively for the ease of debugging and displaying the error messages in the application
Conducted code tuning to improve performance of the application, used Bulk binds, in-line queries, Dynamic SQL, Analytics and Sub-query factoring.
Involved in development and testing of application modules of the different applications.
Automated Quality Check tasks by creating PL/SQL procedures, cursors, functions, and dynamic SQL.
Environment: UNIX Server, Windows, Oracle 11g,MS SQL developer.
Contact this candidate