- Over 8 years of professional experience in IT industry as PL/SQL and Oracle developer in Analysis, Design, Data Modelling (Logical and Physical) and Implementation of Business Applications using Oracle products.
- Lead SDLC activities including Architecture, Analysis, Design, Development, Testing, Implementation & Maintenance of application software in the Client/Server and multi - tiered environments.
- Expertise in working with various Oracle tools and utilities including TOAD, SQL*Navigator, SQL*Loader, SQL*Developer, Forms 11g/10g//6i, Reports 11g/10g/6i and Discoverer for both development and customizations as per client specifications.
- Proficient in development, maintenance and extending the functionality of Reports using Reports Builder 6i/9i/10g/11g, BI Publisher 10g, Discoverer 10g, OBIEE and MARS application.
- Strong Knowledge on Informatica, oracle architecture and database design, related tools and utilities in Creating transformations (Expression, Aggregator, Lookup, Router, Rank, Update Strategy, and Filter) and developing Mappings using Informatica.
- Used Oracle Data Integrator Designer (ODI) to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
- Experienced in migrating data between OLTP and OLAP systems. Extensively worked on OLTP systems for extracting and loading data.
- Proficient in developing and maintaining of packages, functions, application procedures, stored procedures, indexes, using Oracle PL/SQL database programming language.
- Used supplied packages to extend the functionality of PL/SQL programs such as DBMS JOB for scheduling, UTL FILE to read and write from database, DBMS SQL to write Dynamic SQL etc.
- Experience in Performance Tuning Analytics Dashboards / Reports and Data Warehouse database.
- Good experience in client/server technology, GUI design, Relational Database Management Systems (RDBMS).
- Experience in using DBMS SCHEDULER to run jobs in parallel to improve time-constrained performance.
- Expertise in Database Performance Tuning, Performance Monitoring and Optimization using Oracle Hints, SQL Trace, Explain plan and TKPROF.
- Good Knowledge on Version Control software. Worked on approval, testing and migration closely coordinating with teams.
- Proven ability to work efficiently in both independent and team work environments.
- Excellent communication skills and leadership qualities with analytical problem solving abilities including playing vital role in coordinating multiple business meetings and following up on open items.
Databases: Oracle 11g/10g/9i, SQL Server(2005, 2008), MS Access
Languages: SQL, PL/SQL, Pro*C, C/C++, JAVA,XML,UNIX Shell Scripting, Perl Scripting
Oracle Tools/ GUI: Forms 6i/10g/11g, Reports 6i/10g/11g, Discoverer 4i/10g, Oracle APEX 3.2
Database Development Tools: TOAD, PLSQL Developer, SQL*DEVELOPER, SQL*Loader, Oracle SQL*Plus, SQL Navigator, BI Publisher
Data Modeling Tools: MS Visio, ERWIN, ER Studio, Toad Data Modeler
ETL/ Data ware housing Tools: Informatica Power Center 9.6/8.1/8.6, Oracle Data Integrator, Oracle Warehouse Builder, ODI
Web Technologies: HTML, HTML5, XML
Other Scripting Tools: Secure Shell, Putty, WINSCP, DOS Prompt
Oracle Utilities: SQL Loader, External Tables, UTL FILE, TRACE, TKPROF, EXPLAINPLAN, DBMS Utilities, etc.Operating Systems: Windows Family, HP Unix, Sun Solaris, AIX, Oracle Linux
Version Control Systems: Visual Source Safe, IBM Rational Clear Case
Ticketing Systems: Service Now, Track Studio, HP Service Center, JIRA
Confidential, Cincinnati, OH
Sr. Oracle\ ODI Developer
- Worked with the Business analysts and analyzed various sources for requirements gathering, business analysis, testing, metrics and project coordination.
- Prepared the Technical Assessments, Technical Specification, Technical Design Documents based on the functional, publishing requirements.
- Created databases and schema objects including tables, indexes and applied constraints, connected various applications to the database and written functions, stored procedures and triggers
- Performed operations like Data reconciliation, validation and error handling after Extracting data into SQL Server.
- Used Execution Plan, SQL Profiler and Database Engine Tuning Advisor to optimize queries and enhance the performance of databases.
- Creation of database objects like tables, views, materialized views, procedures and packages using oracle tools like Toad, PL/SQL Developer and SQL* plus.
- Improved the performance of the application by rewriting the SQL queries.
- Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances.
- Documented business rules, functional and technical designs, test cases, and user guides.
- Developed and maintained the system using Oracle Forms, Oracle Reports, SQL*Plus and PL/SQL.
- Migrated forms from 6i to 11g and recompiled them for validating any decommissioned features.
- Developed procedures to read data from a file into Oracle table using UTL FILE utility
- Involved in all working on all phases of Software development life cycle (SDLC). Has taken active part in Analysis, Development & Testing Phases during working with projects.
- Responsible for Documentation of all the project developments and methodologies followed for the defect-fix, as per the standards defined and the system requirements.
- Designed and developed UNIX shell scripts as part of the ETL process to automate the Process of loading, pulling and pushing data from and to different servers.
- Worked closely with reporting team supporting large database clusters running state-of-the-Art Vertical column store database.
- Generated scenarios and deployed them using ODI Operator for code migration one environment to another environment.
- Used various knowledge modules and customized them if required based on source and target technologies for developing Interfaces using ODI.
- Developed various ODI interfaces to load data from Flat file & Relational Sources to OracleDataMart.
- Used ODI Designer to develop complex interfaces (mappings) to load the data from the various sources, reverse engineering, to develop projects, and release scenarios.
- Created Custom Knowledge Modules for loading the data from SQL Server for performance.
- Designed ETL mappings for bringing in data from various data sources using ODI.
- Performed loading of delta changes to target table with CDC and Time stamp process.
- Topology Manager is used to create database connection, physical and logical schema in order to achieve the whole infrastructure.
- Executing XML files in Putty using UNIX commands
- Expertise in handling errors using Exceptions with RAISE application clause.
- Developed UNIX Scripts for the calling of Packages, and SQL loader jobs.
- Worked as a module lead and managed roles and responsibilities for testing, defect fixing, applying performance tuning methodologies and migrating ETL components to DIT (Development Independent Testing), SIT (System Independent/Integration Testing), UAT (User Acceptance Testing) and production environment
Environment:: Oracle Database 11g, PL/SQL, Oracle Data Integrator 10.1.3.4, TOAD, SQL*Loader, Flat files, SQL Navigator, UNIX Shell Scripting, AIX, WINDOWS XP, MS Visio, Informatica Power Center 9.1, WinSCP, Putty.Confidential, EI Segundo, CA
Oracle and ETL Developer
- Identified customer requirements, wrote functional designs, reviewed technical designs, developed test schedules and reviewed testing plans, documented development and results of these plans.
- Designed and Developed Tables, views, materialized views, functions, procedures, triggers, cursors and packages using PL/SQL and SQL to transfer data into the database table and automated the Existing Database Table Loads.
- Extensively used SQL*Loader scripts to load legacy data into Staging tables and relevant Packages to validate and cleanse data.
- Created Cursors and database triggers for maintaining complex integrity constraints and implementing the complex business rules.
- Developed PL/SQL Procedures to load data from Flat files and Oracle into the target Oracle database.
- Analysis and design of ETL Processes design documents and detail design documents.
- Experience in developing Extraction-Transformation-Loading (ETL) plans and process using Informatica ETL
- Proficient experience in working on conceptual, physical and logical data models(3NF) using various Data Modeling tools like Erwin, Power Designer, etc
- Gathered requirements and design of Data warehouse and Data mart entities.
- Developed ETL process to merge the data from various source systems using PLSQL packages and procedures.
- Worked with Data migration team, providing the mapping between the source and target systems.
- Involved in gathering requirements from Business users
- Coordinated with DBA in creating and managing tables, indexes, DB links and privileges.
- Worked to translate business information requirements into technical data definitions. Worked closely with business analysts to understand and document business needs for decision support data.
- Experience in application development using various features of Oracle like Bulk Collections, Table Functions, and Autonomous Transactions, Dynamic SQL, Object types, Records and External tables.
- Created Unix Shell script to automate the FTP processes for receiving and sending the files
- Performance tuning of SQL statements using Hints, Indexes, Explain Plan and TKPROF and identify system calls to reduce costs.
- Automated the entire system using proficient shell programming methodologies and CRON Setup.
- 24/7 production support and involved in implementation of coding standards and Data Base change requests.
- Involved in Technical Documentation, Performance testing, User Acceptance testing of the application and participated in Code and Design Reviews, Status meetings and Walkthroughs.
Environment:: Oracle Database 12c/11g,, SQL*Plus, SQL, PL/SQL, SQL Server 2008, Windows 8,UNIX, UNIX Shell scripting, SQL*Loader, Discoverer 10g, TOAD, ERWIN, Informatica Power Center 9.6Confidential, KS
Sr. PLSQL Developer
- Involved in gathering requirements for the new enhancements and participated in Database Design changes and prepared documentation of the application.
- Co-ordinate with Business and Data Analysts in reviewing the requirements, posing questions and translating business requirements to technical specifications.
- Involved in database development by creating Oracle PL/SQL Functions, Procedures, Triggers, Packages, Exception Handling, Records and Collections.
- Creation of the database objects like tables, views, Materialized views, sequences, Synonyms, database links and indexes.
- Effectively used Oracle Reports for generating PDF reports according to the client’s requirements.
- Transferred the data between databases using Export, Import and Data pump utilities.
- Wrote UNIX Shell Scripts for automating the daily/weekly/monthly data process.
- Wrote shell scripts to transfer files using FTP to all the UNIX servers at one time for daily data processing.
- Used advanced Bulk techniques (FOR ALL, BULK COLLECT) to improve performance..
- Wrote packages, stored procedures for storing the user information.
- Developed batch files to manage batch order processing and scheduled them using Windows scheduler.
- Created views pointing to different databases to get the data from interface modules.
- Created indexes for avoiding full table scans and disks sorts, and improved the performance and tuned the oracle queries.
- Created triggers to apply complex business logic and for auditing.
- Optimized critical queries to eliminate full table scans using Explain plan, TKPROF.
- Created SQL Loader scripts and External tables to load the data from flat files and validated the data with PL/SQL procedures.
- Sorted issues of long time taking queries by using dynamic SQL statements like Execute Immediate and DBMS SQL.
- Involved in Tuning Database & Application performance using Explain Plan.
- Involved in different phase of testing like User Acceptance Testing, Unit testing.
- Involved in writing Test cases and performing data validation and process testing for application moving into production.
Environment: Oracle Database 9i/10g, SQL Server 2005, TSQL, SQL*Plus, PL/SQL Developer, Windows XP Pro,Confidential
Oracle Reports and Forms Developer
- Developed Packages to validate raw data from Flat Files and insert into tables.
- Involved in the data transfer creating tables from various tables, coding using PL/SQL, Stored Procedures and Packages.
- Developed back end interfaces using PL/SQL Stored Packages, Procedures, Functions, Collections and Triggers.
- Created the integrity constraints and Database Triggers for the data validations.
- Prepared the complete data mapping and report definitions for the project which involved around 33 reports.
- Designed Aggregate Tables modeled the repository for improving performance of reports.
- Customized the Reports in Cross Tab Layout and Tabular Layout Formats.
- Developed user interface screens using Forms 11g and developed Dynamic HTML reports by embedding HTML in the PL/SQL stored procedures using PL/SQL web toolkit.
- Extensively worked with developing reports using Oracle Report 11g for the business users readability and scheduled jobs for the reports to be emailed to the respective people.
- Developed Time Series objects for computing Year Ago, YTD, MTD, WTD measures. Also created Fragmentation content for few fact tables
- Customized Customer Transaction Analysis Report to change its layout like changing size of the fields, adding some extra field into the display, changing the display of the Report. Writing after Parameters Triggers for the Validation of the data.
- Developed various form-level and DML triggers to perform validation checks
- Worked Extensively in Menu Modules in forms Builder
- Responsible for the upgrade process of Oracle Forms and Reports 6i to 10g, and also have sound knowledge of Oracle Designer.
- Working on Troubleshooting various performance issues and bugs within packages, forms, and reports and provided solutions as needed.
- Used the FTP commands efficiently to migrate the files and logs have been created successfully in proper directories on completion of the transfer process.
- Designed and developed user test scripts for the UAT and migration of the developed codes. Scheduled the jobs with heavy dependencies.
- Involved in 24/7 production support as per the need basis.
Environment: Oracle Database 10g, Oracle Forms 11g, Oracle Reports 11g, BI Publisher, Oracle 11g, Windows XP, UNIX, SQL Developer, XML, SQL, PLSQL, Unix Scripting, Clear Case, Clear Quest, WINSCP, Putty.Confidential
Oracle Reports Consultant
- Involved in defining system requirements, designing & prototyping, developing, testing, training and implementation of the applications.
- Designed and implemented the process for loading the monthly feeds coming from third party legacy system(in the form of flat files, csv etc.,) into staging tables in the Oracle Database using Shell scripts and SQL Loader.
- Validated data loaded into the staging tables using PL/SQL and created adhoc month end reports.
- Developed packages for processing data in the staging tables according to the Client’s requirements.
- Created complex forms for 'Customer transaction loading' (GUI) for day to day business utility, using Forms9i.
- Assisted in setting up the Forms and Reports Services on standalone 10g App Server.
- Developed custom libraries for the existing applications, used canvases, blocks, and visual attributes in developing front end using forms 10g.
- Imported and Exported data from one table space to another and also loaded data from flat files to the staging area using SQL Loader in UNIX environment.
- Written wrapper UNIX Shell scripts to wrap Perl Script, SQL queries as well as ETL jobs to automate the process of analyzing, gathering statistical data, clearing the alert logs, trace dump files.
- Extracted data into comma or pipe delimited files using UTL FILE package/utility.
- Used Exception handling extensively for the ease of debugging and displaying the error messages in the application.
- Generated reports using Pro*C and PL/SQL packages.
- Created new aggregation tables for the data warehouse enhancement using Materialized Views.
- Hands of experience in using TOAD and SQL Navigator.
Oracle PL/SQL Developer
- Actively participated in gathering Business Requirements and System Specification from system users.
- Responsible for Analyzing requirements, designing, maintaining and updating data models
- Analyze the current data management procedures in practice and suggest ways for automating the process or improving the existing system.
- Experience in using Oracle Developer for modifying interfaces based on JSP pages
- Developed stored procedures to pull the data from the distributed systems.
- Involved in Developing and handling the PL / SQL Packages, Procedures and Database Triggers .
- Involved in tuning of SQL queries by using quest central tools and manual by EXPLAIN PLAN .
- Developed Templates for new forms and generate scripts for database objects using oracle designer 10g
- Developed and implemented complex data models and logical database designs.
- Used Perl to upload files to various servers and use comprehensive error handling on failures.
- Extensively used SQL*Loader to load the data from the flat files to the database and validated the data with the PL/SQL procedures .
- Provided production support to the clients on Oracle 9i database administration aspects such as database backup using Oracle Export Utility and database crash recovery from the backup dumps and redo logs.
- Integrated the mailing system to alert various group on the status of data pulls and data processing.
- Developed and implemented dozens of automated batch processing jobs, running in numerous development, test and production environments.
- Involved in importing the production data to work environment through TOAD .
- Involved in handling the changes in compiling scripts according to the database changes.
Environment: Oracle 9i/10g, PL/SQL, Toad, SQL Optimizer, Windows, Java Script, perl.