- 8 years of extensive experience as a PL/SQL, Oracle Developer with ETL experience
- Expertise in Software Development Life Cycle(SDLC) of client implementations including Requirements Gathering, Business Analysis, System Configuration, Design, Development, Testing, Technical Documentation and Support.
- Well versed in developing various database objects like Packages, Stored Procedures, Functions, Triggers, Tables, Indexes, Constraints, Materialized Views and others in Oracle 11g/10g /9i database.
- Expertise in developing and customizing Oracle Forms and Reports using Oracle Forms10g/9i/6i, Reports10g/9i/6i, Discoverer 9i/10g, XML Publisher and developing concurrent programs using SQL, PL/SQL.
- Strong experience in providing technical documentation and status reports of the applications as per the requirement.
- Solid experience in writing complex 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 Arrays, 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.
- Worked with tools like PL/SQL Developer, SQL Developer, SQL*Loader, TOAD, SQL plus.
- Automating data loading, extraction, reports generation using UNIX Shell scripting.
- Experience in handling very large data files using Export - Import Utilities and SQL*Loader.
- Efficient in creating Test cases and performed data validation and process testing for application moving into production.
- Good Knowledge in Oracle Applications EBS.
- Working Experience in Data extraction, Transmission and Loading (ETL).
- Extensively used ETL methodologies for supporting data Extraction, Transformation and Loading (ETL) process in a corporate-wide-ETL solution using Informatica Power Center
- Experience in using Data sources/targets such as Oracle 11g/10g/9i/8i, SQL Server 2008/2005, Teradata, Netezza, DB2, XML and Flat files
- Worked extensively on various Informatica Data Integration components - Repository Manager, Designer and Workflow Manager/Monitor
- Vast experience in Designing and developing complex mappings from varied transformation logic like Unconnected and Connected Lookups, Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, Update Strategy etc
- Designed Source to Target mappings, Code Migration, version control, scheduling tools, Auditing, shared folders, data movement, naming in accordance with ETL best practices, Standards and Procedures
- Worked with Business Managers, Analysts, and end users to correlate Business Logic and Specifications for ETL Development
- Good understanding of Data modeling concepts like ER Diagrams, UML, Use Cases, Normalization and De-normalization of Tables.
- Sound Skills in Report Development, Modeling (Relational, DMR) in Cognos 8.4
- Knowledge of Principals of Data Warehouse using Fact Tables, Dimension Tables and Star/Snowflake schema modeling.
- Highly organized and efficient to prioritize and manage multiple tasks.
- Proven ability to work efficiently in both independent and team work environments.
Databases: Oracle 8i/9i/10g/11g, SQL Server 2005/2007, MS Access, DB2
Languages: SQL, PL/SQL, Pro*C, C/C++, JAVA, .NET, XML, UNIX Shell Scripting, Perl Scripting
Oracle Tools/ GUI: Forms 6i/9i/10g, Reports 6i/9i/10g, Discoverer 4i/9i/10g
Tools: TOAD, SQL DEVELOPER, SQL*Loader, Oracle SQL*Plus, SQL Navigator, UNIX/LINUX Utilities
Web Technologies: HTML, Apache, Microsoft Internet Information Server (IIS), VBScript, Java Script, CSS, DHTML, XML.
Operating Systems: Windows Family, MS-DOS, Unix, Solaris
Confidential, Tulsa, OK
Sr. Oracle Developer
- Involved in all phases of software development including gathering requirements, creating specifications, development of database objects, developing code and validating programs.
- Performed various activities like creating, debugging and modifying data base objects and User defined functions and finely tuned them for performance.
- Developed daily, weekly, monthly CAFM Reports and scheduled using Cron Jobs.
- Developed and customized existing reports as per client specific requirements using Oracle Reports Builder 10g.
- Worked on Unix scripting for scheduling the jobs.
- Created and monitored the Oracle DBMS JOBS as a part of weekly tasks.
- Developed PLX reports by modifying the report template XML and created the GWDS dashboards for moves survey.
- Involved in various design discussions and requirement discussions with the end users.
- Involved in identifying all the Data Models for all the applications.
- Involved in creating reusable control table driven ETL Architecture.
- Created ETL Process control architecture and integrated the same architecture with reporting applications.
- Worked closely with all the application owners to no about the functionality of these applications in a very detail level to understand the data models being used by them.
- Studied various Planning applications to understand the source data for FDW.
- Created ETL Mapping specifications using functional specifications.
- Created Technical Specifications document based on functional specifications
- Managed the Web servers and Data base servers of Production system, Backup DR and Development system.
- Involved in the migration of data from Archibus to Tririga.
- GoldenGatetroubleshooting using Log miner.
- Worked with Golden Gateto replicate data between tables
- Involved in fixing bugs within Packages and Stored Procedures.
- Involved in Tuning Database & Application performance using Explain Plan.
- Involved in the Daily maintenance of the database dat involved monitoring the daily run of the scripts as well as troubleshooting in the event of any errors in the entire process.
- Performed Unit Testing and Integration Testing of the Application. Involved in preparing documentation and user support documents.
- Used Perforce for Code check in, GUTS and Buganizer for ticketing system.
Environment: Oracle Database 10g, Windows 7, UNIX, SQL Developer, XML, SQL, PLSQL, Unix Scripting, Perforce, WinSCP, Putty.
Confidential, NYC, NY
Sr. Oracle Developer
- Reviewed the Business/ Functional/ System Requirement Specs, High Level Design Documents, and Detailed Design Documents.
- Worked with Business Analysts and Business Objects team to understand the requirements and translate the requirement into technical specifications.
- Developed officer profile module using Stored Procedures, Functions, Packages, Triggers, Tables, synonyms, Materialized Views, sequences, indexes which is compatible with the .NET Application.
- Experience in application development using various features of Oracle like Bulk Collections, Table Functions, Autonomous Transactions, Dynamic SQL, Object types, Records and External tables.
- Created Cursors and Ref Cursors in the PL/SQL Blocks.
- Developed complex ETL mappings dat involve parallel processing of multiple instances basing on certain parameters in control table.
- Created complex Mappings using different Transformations like Filter, Router, Joiner, Connected & Unconnected Lookups, Sorter, Aggregator and Sequence Generator to pipeline data to Data Warehouse
- Developed workflows with multiple sessions and instances for same subject area.
- Created post-sessions and pre-sessions for all the sessions to update the etl process table which is used to understand the current status of ETLs.
- Developed Dimension mappings dat load the dimension from landing zone table involving Type 2 and Type 1 transformations.
- Involved in tuning Informatica ETL mappings analyzing them thoroughly.
- Involved in identifying various bottle necks at different levels (database, mapping, session, and workflow) and came up with solution to improve the performance.
- Created table partitions for each of the country, operating company and period combination which enables faster retrieval of data.
- Validated and loaded data into Oracle Database using DB links and external tables from CSV files.
- Autosys job scheduling- created the jil scripts and unix shell scripts to automate the jobs scheduling
- Created batch jobs in regular maintenance, production support to load the data using unix shell scripting
- Designed and Developed scripts for uploading data from flat files and XML files into database, performing business validations on the data using PL/SQL Procedures.
- Improve the performance of the slow SQL queries by implementing indexes, using BULK LOAD, BULK COLLECT and developed various procedures, functions and packages to implement thenew business.
- Created database triggers for audit and data validation.
- Worked as a release coordinator, raised Work Requests and Change Requests and also deployment of code in various environments.
- Ggs Heterogeneous replication usingGoldenGate( Oracle 126.96.36.199 to SQL Server)
- GoldenGatetroubleshooting using Log miner
- Migrated Database between servers using Data pump Export/Import
- Involved in Unit testing and System Integration testing for PL/ SQLStored Procedures andFunctions.
- 24/7 production support and involved in implementation of coding standards and Data Base change requests.
- Used SVN for version controlling and ALM (Application Lifecycle Management) for ticketing system.
- Involved in creating ETL model (snowflake schema), normalizing and documenting.
- Worked with Informatica to create source/target definitions, mappings and sessions to extract, transform and load data into staging tables from various sources.
Environment: Oracle Database 11g, Windows 7, UNIX, Toad, XML, SQL, PLSQL, Unix Scripting, SVN, ALM, PAC2000, WinSCP, Putty.
Confidential, Albany, NY
- Involved in analysis, design and coding of Oracle processes in PL/SQLto built packages.
- Design and Development of data validation, load processes, test cases usingPL/SQL, Stored Procedures, Functions, Triggers, Cursors using Oracle 10g.
- Validated and loaded data into Oracle Database using DB links and external tables from CSV files.
- Developed Ref Cursors to accommodate the business rules.
- CreatedTables, Views, Materialized views, and constraints.
- Created a Stored Procedure to do a partition swap from staging to FDW which is called in ETL to load the Fact data for various tables which are passed as parameters to the stored procedure.
- Create Oracle Stored Procedures to implement the ETL Process control logic.
- Created Oracle Stored Procedures for Segment Dimension which holds all the dimension keys.
- Designed Oracle views dat generate these extracts for upstream applications and created ETLs to read these views and drop them in a file.
- Created a model for Audit mechanism and included the Audit counts in each of the ETLs to verify the source and target counts and sums.
- Identified various extracts needed for upstream reporting applications and planning applications and designed structures for the same.
- Documented all the ETL and Oracle Procedures developed.
- Created Unit Test plans for various ETLs developed.
- Performed Unit Testing and Integration testing for the ETL’s
- Designed and developed the Forms and Reports for the user Interface.
- Customized Form filed like enabling, disabling form filed and incorporating oracle alerts using form builder 9i.
- Handled errors using Exception Handling extensively for the ease of debugging and displaying the error messages in the application.
- Developed Grouping report and matrix reports using oracle reports 9i report builder.
- Involved in testing backend SQL queries for query optimization.
- DevelopedUNIX scriptsusingVI editorto create FTP Jobs and to schedule the jobs.
- Creating the concurrent programs and registering the functions to the oracle applications (EBS).
Environment: Oracle Database 10g, Oracle EBS, Windows XP, UNIX, SQL Loader, SQL Developer, PL/SQL Developer Oracle Forms 10g, Oracle Reports 10g, Discoverer10g, XML, SQL, PLSQL, VSS, WinSCP, Putty.
Confidential, Warren, NJ
Oracle PL/SQL Developer
- Created heavy stored procedures for financial transaction by using procedural parameters & worked with packages, blocks & exception handling.
- Designed and Developed Oracle database Tables, Views, Indexes, and Synonyms with proper privileges.
- Developed PL/SQL packages for generating various feeds from oracle database using UTL FILE utility in the client’s secure data transmission server.
- Developed UNIX scripts for transmitting files from the secure server to the customer specified server, using various FTP batch processes.
- Used SQL*Loaders to download the data into database from the text files and spread sheets.
- Extracted data from the XML files transmitted to the bank and loaded them efficiently into oracle temp tables for day to day customer financial transaction reconciliation.
- Developed UNIX scripts for sending mails to the bank and the business team, when the external feeds to the bank are received.
- Involved in supporting/fixing production defects and day to day business transaction issues.
- Used SQL Navigator, a popular tool which increases user productivity and application code quality with rich features to manage database objects, develop and debug PL/SQL and create, execute and optimize SQL queries.
- Designed and implemented client-server application using Oracle Forms 10g.
- Involved in the generation of User Interface using oracle forms by extensively creating forms as per the client requirements.
- Developed various new reports from scratch utilizing Oracle Reports 10g for day to day validations.
- Developed ETL mappings, transformations using Informatica Power Center 8.1.
- Created various adhoc reports as of when required for the production analysis and bug fixing.
- Converted reports to browser supportable XML format.
- Extensively utilized TOAD for performance tuning by generating explain Plans.
Environment: Oracle Database 10g, UNIX, Windows XP, SQL * Loader, TOAD, SQL Navigator, Oracle Reports 10g, Oracle Forms 10g, Discoverer 10g, PL/SQL, UNIX, Perl, XML, JSP
Confidential, Columbus, OH
- Gathering business requirements from end users.
- Used various Optimizer Hints to speed up the SQL queries.
- Used SQL*Loaders to download the data into warehouse database from the flat files and other data files across different platforms.
- Developed SQL and Shell scripts to create temporary tables and developed SQL*Loader scripts to load data into the temporary staging tables.
- Designed and developed forms using Oracle Forms 10g to allow the end users to manage the customer relation TEMPeffectively and efficiently.
- Worked on creating Materialized Views to maintain copy of the remote data at local node.
- Mostly involved in code reviewing and performance tuning with optimization techniques in PL/SQL.
- Solving the complicated run time errors while running the process along with DBA team members.
- Involved in the development of database objects in both OLTP and OLAP databases.
- Addressing day-to-day issues with business users using tools like TOAD.
- Having good noledge on Claims, Benefits, Provider and Contract affiliations.
- Created documentation for migration and performed extensive testing during the various phases of product implementation.
- Designed and developed the logic for handling Slowly Changing Dimension tables load by flagging the record using update strategy for populating the desired
- Developed reusable mapplets and transformations for reusable business calculations
- Used exception handling logic in all mappings to handle the null values or rejected rows
- Tuned the ETL components to gain the performance and to avoid business continuity.
- Worked with Persistent Caches for Conformed Dimensions for the better performance and faster data load to the data warehouse
- Involved in performance tuning and optimization of Informatica Mappings and Sessions using partitions and data/index cache to manage very large volume of data
- Performed query overrides in Lookup Transformation as and when required to improve the performance of the Mappings
- Used Clear case for version controlling and Clear Quest for ticketing system.
Environment: Oracle Database 11g/10g, Oracle APEX 3.2 Windows XP, UNIX, SQL Loader, TOAD, Oracle Forms 10g, Oracle Reports 10g, Discoverer10g, XML, SQL, PLSQL, VSS, Informatica Power Center 8.1
Oracle PL/SQL Developer
- 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.
- 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.
- Handled production issues with high pace and efficiency.
- Hands of experience in using TOAD and SQL Navigator.
- Involved in gathering the requirements from users to develop the application from the scratch
- Involved in working with analysts for gather more information on data flow designs dat required for the project to develop.
- Developed Stored Procedures to maintain details of the vendors and maintained them in Packages.
- Designed database tables using various Normalization techniques and database rules.
- Developed reports as per the requirements collected from the users using Discoverer and Oracle Reports 10g.
- Good Experience working on TOAD, PL/SQL Development for database access.
- Generate and maintain Keys, Constraints and Relationships between the tables for OLTP & OLAP instances.
- Developed complex SQL queries to perform the back end testing of the Oracle database using SQL and UNIX shell commands.
- Created Shell Scripts for automating the execution process.
- Involve in migrating applications to the new release versions.
Environment: Oracle 10g, Oracle Forms 10g, Oracle Reports 10g, Perl Scripting, PL/SQL TOAD, UNIX.