PL/SQL Developer (Oracle)
Resume:
Santoshi. S
********.*****@*****.***
PL/SQL Developer
SUMMARY:
• Around 5+ years of experience as Oracle PL/SQL Developer in Oracle 12c, 11g, 10g, 9i & 8i versions and IT experience in Software Development/Maintenance & Production Support Projects.
• Technical areas of experience related to PL/SQL, Informatica, UNIX & Shell Scripting.
• Experienced in complete life cycle Implementation of PL/SQL Development.
• Experienced in working in OLAP and OLTP Database environments.
• Proficient in writing complex SQL Queries, Stored Procedures, Normalization, Database Design, creating Indexes, Functions, Triggers, Sub Queries, Collections, Performance Tuning and Query Optimization.
• Experienced in PL/SQL Performance Tuning using Bulk Collections and other optimization techniques,
• Expertise in Dynamic SQL and Exception Handling.
• Expertise in Data Integration of data from various data sources like SQL Server, Oracle, Flat Files, and XML files and Data Migration.
• Worked with various performance tuning techniques using tools provided by Oracle like SQL*TRACE, TKPROF, HINTS, DBMS_STATS etc.
• Experienced in importing data from Flat files from Legacy Applications to Oracle DB using SQL Loader.
• Creating Users, Roles and Profiles, Privilege management with role-based security.
• Expertise in Data Warehousing and ETL concepts.
• Good exposure to Development, Testing, Production Support, Debugging, Implementation and Documentation.
• Experienced in Requirements Gathering and Preparing Use Cases.
• Experience with Performance Tuning for Oracle RDBMS using Explain Plan and Hints.
• Comprehensive and in-depth experience using the System Development Life Cycle (SDLC).
• Expertise in working with Projects in Agile Methodology.
• Created Shell Scripts for invoking SQL scripts and scheduled them using crontab.
• A self-motivated, responsible, and reliable team player with a set of strong technical skills.
• Strong conceptual, business, analytical skills, and innovative problem-solving abilities.
• Good communication, interpersonal and problem-solving skills.
• Expertise in Client-Server application development using Oracle 11g/10g/9i/8i, PL/SQL, SQL *PLUS, TOAD and SQL*LOADER.
• Experience in Design, Development, and Enhancement and Maintaining applications in Oracle 9i/10g/11g/12c using SQL, PL/SQL.
• Experience in both Agile and Waterfall Software Development methodologies.
• Strong experience in Data warehouse concepts, ETL.
• Good knowledge on logical and physical Data Modelling using normalizing Techniques.
• Created Tables, Views, Constraints, Index (B Tree, Bitmap and Function Based).
• Developed Complex database objects like Stored Procedures, Functions, Packages and Triggers using SQL and PL/SQL.
• Experience in Oracle supplied packages, Dynamic SQL, Records and PL/SQL Tables.
• Loaded Data into Oracle Tables using SQL Loader.
• Experience with Oracle Supplied Packages such as DBMS_SQL, DBMS_JOB and UTL_FILE.
• Created Packages and Procedures to automatically drop table indexes and create indexes for the tables.
• Developed and Automated batch load process using SQL*Loader, Shell scripts and Crontab. TECHNICAL SKILLS:
Databases Oracle 8i/9i/10g/11g/12c, MySQL 5, MS SQL SERVER 2005/2008, DB2/UDB, SAP Tables and MS Access
Other Technologies Oracle E-Business Suite 12, Oracle Recovery Manager (RMAN), Oracle Enterprise Manager
(OEM), Oracle Applications Manager (OAM)
Programming
Languages
SQL, PL/SQL, Unix shell scripts, SAS, C/C++/C#, Perl, XML, HTML ETL Tools IBM Info Sphere Data Stage, Informatica, Talend Data Warehousing Star & Snow-Flake Schema Modeling, Erwin, Sybase Power Developer, SSRS Dev. Tools Windows/Linux/Mac OS Environment, Eclipse, Git, Subversion (SVN). Tools TOAD, SQL*PLUS, SQL Developer, SQL*Loader, Crystal Reports, Forms 6i/9i/10g, Reports 6i/9i/10g, Workflow Builder and XML Publisher.
Professional Experience:
Client: State Street Bank And Trust Company, Boston, MA. March 2023 – Present Role: Sr PL/SQL Developer
Responsibilities:
• Gather requirements for change requests with the Business team and create design documents.
• Provide User Access to Business End Users to the Front-end tools after getting the required approvals
• Create PL/SQL packages for implementing Change Requests for adding new functionality to the application.
• Created database objects like Tables, Views, Sequences, Synonyms, Stored Procedures, Functions, Packages, Cursor, Ref Cursor and Triggers.
• Wrote complex SQL Statements, Complex Joins, Co-related Sub-queries, and SQL Statements with Analytical Functions
• Performed SQL and PL/SQL tuning using tools like EXPLAIN PLAN, SQL*TRACE, TKPROF and AUTOTRACE
• Extensively used Oracle Hints to direct the optimizer to choose an optimum query Execution Plan
• Extensively used Bulk Collection in PL/SQL Objects for improving the performance
• Handled errors using Exception Handling extensively for debugging and maintainability.
• Automated Oracle and Informatica workflows using Unix Cron Utility in Unix Environment.
• Responsible for writing Unix Shell scripts for loading data using SQL*Loader. The Control Files for the tables were created and automated through UNIX shell scripts to perform data load into Oraclables. Used SQL Loader and PL/SQL scripts to load data into the system application.
• Performed Data Cleansing to weed out erroneous Orders from the DWH tables
• Extensively used Toad to develop Oracle PL/SQL Packages.
• Create Procedures and Functions in SQL Server to migrate data from Legacy servers to Oracle Datawarehouse tables.
• Play an active role in the Oracle HCM practice and contribute to its development.
• Experience as an end user or with functional / technical implementation in Oracle HCM Cloud or Oracle eBusiness Suite.
• Create Informatica workflows (7.0 for Flat Roll/9.0 for Tubular implementations) for implementing business logic
• Worked on Sessions & workflows for variety of loads starting from Source to Target.
• Created Parameter Files to override session properties for source directory, Bad file Directory, Source, Target & created Mapplet parameters for Mapplets.
• Experience with functional / technical implementation in Oracle HCM Cloud or Oracle eBusiness Suite
• Created Mappings using Designer, Extracted the data from the flat files and other RDBMS databases into staging area and loaded onto Data Warehouse.
• Developed the Informatica Mappings by using Aggregator, SQL Overrides by using Lookups, source filter by using Source Qualifiers and Data Flow Management into multiple targets using Router Transformation.
• Developed reusable Workflows, Worklets, Mappings and Mapplets using transformations including Router, Aggregator, Lookup, Filter, Expression, Sequence Generator, Update Strategy, Joiner and Union.
• Create Shell scripts for automating the business logic.
• Hands on experience in multiple Full Life Cycle implementation of the Oracle HCM Cloud (Fusion) product, from planning to configuration.
• Create ABPP (Agile Business Process Platform) workflows which sequences the execution of Oracle Packages, Informatica workflows, Shell scripts as per the business logic
• Deploy the code across the environments to main code consistency and update the same in Clear Case for version control
• Create Test Cases for verification and validation of the Change Requests Environment: Oracle 10g / 11g/12c, SQL, PL/SQL, SQL*PLUS, Informatica, SQL*Loader, Oracle Forms 9i/10g, Reports 9i/10g, CVS, SOAP, REST, TOAD, UNIX, Shell scripting. Client: Omega Healthcare Management Service Pvt Ltd, IND May 2020 - Dec 2021 Role: Oracle PL/SQL Developer
Responsibilities:
• Requirements Gathering, Analysis, Design, Development, Testing, and Support.
• Created the database objects like Tables, Views, Stored Procedures, Functions, Packages, DB triggers, Indexes, and Analytical functions.
• Used External Tables and SQL*Loader to create ETL packages for flat file loading and error capturing into log tables.
• Used Bulk Collections for better performance and easy retrieval of data, by reducing context switching between SQL and PL/SQL engines.
• Performed SQL and PL/SQL tuning and Application tuning using various tools like Explain Plan, SQL*Trace and TKPROF.
• Effectively made use of Table Functions, generated columns, Indexes, Table Partitioning, Collections, and Materialized Views.
• Used Ref Cursors, Synonyms, Indexes, Joins and Exceptions extensively in coding. Tuning of the SQL queries, which takes long time to process the request using Explain Plan, Hints to reduce the response time.
• Worked on various tables to create Indexes to improve query performance and partitioning large Tables.
• Worked in implementing table partitions using Range, Hash, Composite techniques.
• Good understanding of Star Schema, Snowflake Schema, Dimension and Fact table.
• Extensive use of Unix Shell Scripts and Autosys to automate process.
• Used Autosys to schedule the batch jobs for Intraday and End of the business day cycles.
• Worked on UNIX and Perl scripts for scheduling Autosys jobs in UAT/SIT/PFIX/PROD Environments.
• Worked on Version Control tool such as Tortoise SVN and used the JIRA bug tracking tool.
• Responsible for troubleshooting back end UAT and production issues.
• Worked on troubleshooting, issue escalation, applying the patches and fixes using Change Requests.
• Worked extensively to deploy the UNIX and Perl code in Linux environment.
• Understanding the PL/SQL code for issues in the database and advice the development team with logs.
• Production support and involved in implementation of coding standards and Data Base change requests. Environment: Oracle 10g / 11g/12c, SQL, PL/SQL, SQL*PLUS, SQL*Loader, Oracle Forms 9i/10g, Reports 9i/10g, CVS, TOAD, UNIX, SOAP, REST, Shell scripting, Oracle Warehouse Builder 11.2.0.2, Subversion (SVN), CVS, Perl. Client: CA Technologies, IND Aug 2018 – Mar 2020
Role: Oracle Developer
Responsibilities:
• Gathered the requirements from the data analysts and architects and performed technical analysis to reach a viable solution.
• Responsible for Analysis, Design, Coding, Debugging and Deployment.
• Worked with the team to design, develop, test & implement system. Creation of database objects like tables, procedures using Oracle tools like PL/SQL, TOAD. Written Stored Procedures using PL/SQL.
• Worked in preparing functional document, user document, process flow diagram and flow chart.
• Developed PL/SQL Packages, Procedures and Functions accordance with Business Requirements.
• Created PLSQL procedure, used the DBMS_SCHEDULER for automatic weakly sending emails to the users.
• Rich experience in writing SQL queries, Views, Materializedviews, cursors, collections, Ref cursor, cursor variables, System reference cursor, Dynamic SQL.
• Written complex Oracle SQL queries with complexity using Inner/Outer joins, Indexes, Sequences, Union All, Intersect & Minus set operators.
• Troubleshoot performance issues and bugs within anonymous blocks, Partitioning dbms_output and Explain plan.
• Create various Data Mapping Repository documents as part of Metadata services (EMR)
• Short term assignment to assist Oracle in a data clean up and conversion / migration task using Oracle Warehouse Builder 10G Rel. 2, to migrate mainframe legacy to Oracle eBusiness Suite.
• Worked as an Informatica developer to design and develop Informatica mapping, to perform the Extraction, Transform and Load process by studying the business requirement from the users and the mapping documents.
• Customized Reports and Forms to meet user requirements mainly in financial modules (AP, GL, and AR).
• Developed Scripts for creation of database objects like tables, views, indexes etc.
• Provided Production Support for the application and business users.
• Handled Configuration Management Support for Release activities.
• Involved in deployment and post-deployment support.
• 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.
• Created extensive documentation on the design, development, implementation, daily loads, and process flow of the Informatica mappings
• Wrote PL/SQL Database triggers to implement the business rules in the application
• Analyze SQL statements, reorganize database objects and design indexes to improve response of queries.
• Developed reports using complex queries as per client request as a part of production support.
• Provided on call support, solved the user issues, and made necessary changes. Environment:Oracle 10g, PL/SQL, UNIX, TOAD, Oracle Forms 9i/10g, Reports 9i/10g, Erwin, IBM Web Sphere Data Stage, MKS Integrity, CVS, SUN Solaris, MS SQL Server
Contact this candidate