Sr. Pl/sql Developer Resume
Seattle, WA
SUMMARY
- Around 9 yearsof programming experience as anOracle PL/SQLDeveloper in Analysis, Design and Implementation of Business Applications using the Oracle Relational Database Management System (RDBMS).
- Involved in all phases of theSDLC (Software Development Life Cycle)from analysis, design, development, testing, implementation and maintenance with timely delivery against aggressive deadlines.
- Experience withData flow diagrams, Data dictionary, Database normalization theory techniques, Entity relation modellinganddesign techniques.
- Expertise in Client - Server application development using Oracle11g/10g/9i/8i, PL/SQL, SQL *PLUS, TOADandSQL*LOADER.
- Experience in both Agile and Waterfall Software Development methodologies.
- Effectively made use ofTable Functions, Indexes, Table Partitioning, Collections, Analytical functions, Materialized Views, Query Re-WriteandTransportable table spaces.
- Strong experience inData warehouse concepts, ETL.
- Good knowledge on logical and physicalData Modellingusingnormalizing Techniques.
- CreatedTables, Views, Constraints, Index(B Tree, Bitmap and Function Based).
- Developed Complex database objects likeStored Procedures, Functions, Packages and Triggers using SQL and PL/SQL.
- Extensively created and used various Teradata Set Tables, Multi-Set table, global tables, volatile tables, temp tables.
- Experience in utilizing Automation Scheduling instruments like Autosys and Maestro.
- Developedmaterialized viewsfor data replication in distributed environments.
- Excellent technical and analytical skills with clear understanding of design goals of ER modelling forOLTPand dimension modelling forOLAP.
- Experience inOracle supplied packages, Dynamic SQL, RecordsandPL/SQL Tables.
- Loaded Data into Oracle Tables usingSQL Loader.
- Partitioned large Tables usingrange partition technique.
- Experience with Oracle Supplied Packages such asDBMS SQL, DBMS JOB.
- CreatedPackagesandProceduresto automatically drop table indexes and create indexes for the tables.
- Performed comprehensive system test, including the validation of the input data that was loaded byInformatica program into the Oracle database.
- Worked onInformatica Power Center client tools - Mapping designer, Workflow manager, Workflow monitor, Repository manager, Mapplet designer and Transformations.
- Worked extensively onRef Cursor, External TablesandCollections.
- Expertise inDynamic SQL, CollectionsandException handling.
- Experience in SQLperformance tuningusing Cost-Based Optimization (CBO).
- Good knowledge of key Oracle performance related features such asQuery Optimizer, Execution PlansandIndexes.
- Experience withPerformance Tuningfor Oracle RDBMS usingExplain PlanandHINTS.
- Experience inETLtechniques and Analysis and Reporting including hands on experience with the Reporting tools such asCognos.
- CreatedShell Scriptsfor invoking SQL scripts and scheduled them using crontab.
- Excellent communication, interpersonal, analytical skills and strong ability to perform as part of a team.
TECHNICAL SKILLS
ETL Tools: Oracle Data Integrator(ODI) 12C/11G, Informatica.
Reporting Tools: OBIEE, Cognos
Database: Oracle 12C/11g/10g, MS SQL Server (2005 & 2008), MS Access, DB2
Languages: SQL, PL/SQL, C, HTML
Operating System: UNIX/ Linux, Windows 95/98/2000/XP, Windows NT.
Database Tool: TOAD, SQL Developer, SQL Plus
Other: Visio, MS Office, WebSphere, Adobe Acrobat.
PROFESSIONAL EXPERIENCE
Confidential, Seattle, WA
Sr. PL/SQL Developer
Responsibilities:
- Designed high performance mappings by using iterative variable logic in Expression Transformation.
- UsedSQL *Loader for loading the stage tables.
- Performed unit and integrated testing.
- Prepared documentation for business data flow from source to mart.
- Move the mappings and scripts between various environments like the Development, QA and production.
- Documented the business data flow from multiple sources.
- Worked with DBAs closely to optimize and distribute workflow across multiple networked CPU nodes.
- Worked onInformatica Power Center client tools - Mapping designer, Workflow manager, Workflow monitor, Repository manager, Mapplet designer and Transformations.
- Involved in automation of batch processing to run Informatica Workflows usingAutosys.
- Identify source systems, connectivity, and captured data using CDC techniques, tables, and fields, ensure data suitability for mapping.
- Extensively created and used various Teradata Set Tables, Multi-Set table, global tables, volatile tables, temp tables.
- Developed complex mappings with transformations like lookup, Union, router, aggregator, expression, update strategy and joiner confirming to the business rules and documented.
- Implemented error handlings in mappings.
- Performance tuning of mappings, transformations and sessions to optimize session performance.
- Created various database objects, including tables, indexes, synonyms, complex PL/ SQL stored procedures, functions and packages using aggregate and analytical functions and optimized performance by providing partitions, index and analyzing the table.
- Raised CCR (Change Control Request) and deployed the mappings and scripts between various environments like the Development, QA and production.
- Interacting with UNIX,Informatica and Data base administrators, if any issues/concerns occurred.
- Develop and manage moderate to complex PL/ SQL applications including - performance tuning with indexing, analyzing andSQL trace with reports; native compilation; Highly Complex Procedures, Triggers, Materialized Views; Bulk Collects, Bulk loads etc.
- Train and manage developers, advising other groups in organization on Oracle development, data warehouse development, and ETL development best practices.
- Created systems for capturing, reporting, and correcting error data.Work with developers, DBAs, and systems support personnel in elevating and automating successful code to production.
- Provide on-call support to production system to resolve any issues of file arrivals, bug fixes, and human errors.
- Coordinate with business; Gather requirements and design data warehouse and data mart entities.
Environment: Flat files, Oracle 10g/9i RAC, Putty, Windows XP professional, Erwin 4.0, Toad, Tivoli, Unix scripting, Win SCP Oracle 10g, 11g, Java and XML, Java using JDBC, C/C++ using OCI. UNIX (Solaris),Informatica Power center 8.5
Confidential, Tx
PL SQL /ETL Developer
Responsibilities:
- Worked closely with the system architect, data management director, product manager, and end users to develop the requirements and system architecture for a new Regulatory reporting requirements, designed to be both the system of record for clients and the source for statistical analysis.
- Designed the Mapping Technical Specifications on the basis of Functional requirements.
- Worked closely with BA in developing Logical and Physical model for the requirements.
- Worked with various functional experts to implement their functional knowledge into working procedures.
- Performed comprehensive system test, including the validation of the input data that was loaded byInformatica program into the Oracle database.
- UsedAutosys Scheduler to Create, Schedule and control the batch jobs.
- Wrote a number of control files for loading data into Oracle tables.
- Extensively used the advanced features of PL/SQL like Subtypes, Records, Tables, Object types and DynamicSQL.
- Created, optimized, reviewed, and executed Teradata SQL test queries to validate transformation rules used in source to target mappings/source views, and to verify data in target tables.
- Reduced Teradata space used by optimizing tables - adding compression where appropriate and ensuring optimum column definitions.
- Developed Unix Shell Scripts to automate backend jobs, loading data into the Database using SQL* Loader.
- Involved in Tuning, Performance, and Optimization of queries and standardization of the codes.
- Wrote a number of shell scripts for extracting data from Oracle tables.
- Reporting Requirements to develop Purchase Order and Spending Data warehouse.
- Involved in writing complex scripts for Data Transformation, ETL process (Extract, Transform & Load).
- Implemented ETLInformatica designs and processes for the load of data from the sources to the target warehouse.
- Responsible for determining the bottlenecks and fixing the bottlenecks with performance tuning.
- Worked with Index Cache and Data Cache in cache using transformation like Rank, Lookup, Joiner, and Aggregator Transformations.
- Extensive worked on debugging and troubleshooting the Sessions using the Debugger and Workflow Monitor.
- Worked with Session Logs and Workflow Logs for Error handling and troubleshooting.
- Involved in setting up the environment for the new version.
- Worked on DB table partitioning (Range and List) and deploying various methods of indexing like local indexes and global indexes on portioned tables.
- Extensively worked on Explain plans and various hints for Performance tuning.
- Performed the uploading and downloading flat files from UNIX server using FTP.
- Attended meetings and review to discuss current issues and processes to tackle such issues.
Environment: Oracle 11g/10g, Oracle Forms 10g, Autosys, Oracle reports 10g, PL/SQL, TOAD, Informatica, SQL*loader, SQL*Plus, C#, ASP.Net, UNIX, XML, ETL, Windows XP.
Confidential, Los Angeles, CA
Sr. PL/SQL Developer/ OWB Developer
Responsibilities:
- Involved in full development cycle ofPlanning, Analysis, Design, Development, Testingand Implementation in Agile Methodology.
- Involved in development, maintenance of Triggers, Joins, Cursors and Views.
- Developed procedures, functions and packages to implements the system's business logic using PL/SQL
- Developed ETL processes using OWB 11.2 and Informatica 9.6 to extract the data from different types of sources and load it in to the Operational Data Store and Enterprise Data Warehouse.
- Created process flows (OWB) and workflows (Informatica) to execute the mappings, PL/SQL procedures and shell scripts together and in required order.
- Participated in designing the star schema for Business Objects reporting and iDashboard off MDM, ODS & the Data-warehouse systems.
- Write reports in WebFOCUS and generate graphs through Info Assist.
- Analyzed existing data and reports in WebFOCUS Metadata and MS-SQL environment for new reports in WebFOCUS.
- Understanding ofAmazon Web Services (EC2, S3, Cloud Formation, OpsWorks, etc.).
- Created alarms and notifications for EC2 instances using Cloud Watch.
- Advise, provide guidance and develop Disaster Recovery strategies for the Exadata Database machine.
- Experience in implementing Web Services based in Service Oriented Architecture (SOA) using SOAP, Restful Web Services, JAX-WS, UDDI, WSDL, Apache Axis.
- Define data transfer process through asynchronous web service using REST (HTTP).
- Solid skills to configure Exadata to meet company internal security requirement.
- Fix open ticket as created by users that includes code modification and issue data analysis.
- Created new database objects like Procedures, Functions, Triggers, Indexes and Views in Development and Production environment in PL/SQL and SQL*Plus.
- Analyzed C/Java sources to gather business rules for the application and converted them into/SQLcodes.
- Involved in maintaining and writing several PL/SQL Stored procedures and SQL queries for populating the daily and historical tables.
- Installed and configured OEM Grid control and intensively used Diagnostic & tuning packs along with AWR & ADDM reports.
- Created Shell scripts to access data and setup runtime environment, and to run Oracle Stored Procedures, Packages.
- Developed UNIX Shell Scripts. Customized and developed Oracle Forms and Oracle Reports using Oracle Forms 9i/10g and Reports 9i/10g.
- Optimized the performance using PL/SQL table types and record types.
- Did SQL and PL/SQL programming for ODI and oracle.
- Developed AR Credit memo and AR Customers forms.
- Worked extensively with ODI Designer, operator and metadata Navigator.
- Analysis and generation of mainframe commands to add users to missing mainframe groups as well as analysis of RACF permission sets and reporting of access that users may lose based on the clean-up process.
- Played a major role in designing the dimensional modelling of star schema for the enterprise data ware house of ODS.
- Utilized Oracle Enterprise Manager (OEM), Grid control to manage users, privileges, profiles, and schemas.
- Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances.
- Developed DAO layers in applications to access the dB objects from Java.
- Analysed queries using SQL Trace facility and Explain Plan utility to obtain the execution process. Optimized the queries by modifying the data access methods, Index strategies, Join types and operations and providing hints.
- Review data transformation rules and provide technical suggestions in data transformation logic and pseudo code (PL/SQL).
- Built custom forms using Oracle Forms 9i/10g Builder to fulfill the business requirements of the client.
- Wrote SQL queries to developed test data for use during test execution.
- Perform Inbound Interface by creating Temporary tables, then Validate data and then Load into Interface Tables by using SQL Loader.
- Worked on MS Access to Oracle 10g database conversion including tables, queries, standard procedures, forms and triggers.
- DataStage jobs were scheduled, monitored, performance of individual stages was analysed and multiple instances of a job were run using DataStage Director.
- Created complicated reports using reporting tool Cognos.
- Used DS Erwin MetaBroker to import Erwin 4.x Metadata into DataStage Repository.
- Used Datastage Manager for importing metadata from repository, new job categories and creating new data elements.
- Created & Deployed SSIS (SQL Server Integration Services) Projects, Schemas and Configured Report Server to generate reports through SSRS SQL Server.
- Involved in Unit testing and Integration testing of forms and reports.
- Coding of documents in XML for data transformation.
- Developed test procedures to Unit test the application.
- Worked on Oracle OLAP tool to produce analytic measures, including time-series calculations, forecasts.
- Implemented table and index partitioning of various tables with huge data.
- Wrote a Perl-based server to generate reports of trades, and tickets in PostScript and HTML formats.
- Provided Post production Technical Support to the users.
Environment: Oracle 10g / 11g/12c, SQL, Agile, PL/SQL, SQL*PLUS, SQL*Loader, Oracle Forms 9i/10g, Reports 9i/10g, CVS, TOAD, UNIX, Shell scripting, HP PPM, Oracle Warehouse Builder 11.2.0.2, Subversion (SVN), CVS, Perl.
Confidential, Hartford, CT
PL/SQL Developer
Responsibilities:
- Created Database links to connect to remote databases and to be able to pull data, perform joins to manipulate the data.
- Created Stored Procedures, Packages, Functions to massage data, validate data and to implement complex business rules.
- As the project deals with huge amount of usage data, Partitioned tables to enhance the performance, manageability, availability and eliminate the down time on frequently used usage related tables.
- Developed database objects such as stored procedures, packages, functions, types.
- Reviewed the business requirement, functional requirement document for various modules so as to have good understanding of the application.
- Developed Database Triggers to enforce Data integrity and additional Referential Integrity.
- Developed complex SQL queries for data retrieval from various database objects including tables and views.
- Played an important role in performance tuning most complex SQL queries using Hints, Indexes.
- Extensive documentation of the source and target tables and the description of the objects in the Excel document and developed Excel Report using the procedural code.
- Worked on Table structures along with the Triggers, Indexes like Bitmap and Function based Indexes.
Environment: Oracle 11g, PL/SQL, SQL*Plus, Agile, SQL developer, TOAD.
Confidential, NewYork, NY
PL/SQL Developer
Responsibilities:
- Worked on HR database of Direct TV to move it from different sources to a specific format.
- Parameterized sql scripts based on the business logic to move them into core tables.
- Created views in Vertica to implement logics and casting the columns to desired format.
- Performance tuned queries for the faster and efficient transactions.
- Created JSON files for implementing and populating the data in to HUB tables.
- Worked with operations and deployed the required scripts and JSON files.
- Created materialized views, views, tables based on the data model.
- Used SQL Loader to retrieve the data into the database tables from flat files and comma separated files (CSV).
- Developed database objects including Indexes, views, sequences, packages, triggers, functions and procedures to troubleshoot any of the database problems.
- Worked on advanced concepts of PL/SQL such as Records, Collections and Dynamic SQL.
- Widely used PL/SQL tables and Bulk Collect for processing the load on tables and retrieving into oracle.
- Participated in code review meetings with the development team.
- Involved in writing SQL queries, PL/SQL programming and created new packages and procedures and modified and tuned existing procedure and queries using TOAD.
- Performed SQL and PLSQL tuning and Application tuning using various tools like EXPLAINPLAN, SQL*Trace .
- Worked on creating PL/SQL Tables, Views, Stored Procedures, Triggers and Functions which are used in generating the Crystal Reports and also in the INFORMATICA Mapping.
- Developed complex SQL queries to perform the back end testing of the Oracle database using UNIX shell commands.
- Worked on converting row based data into column based data i.e., from Oracle to Vertica database.
- Used Toad Data point to work on converting codes from oracle based to vertica.
- Performed DBA activates of taking backups (Cold and Hot). Table level, schema level and table space level backups.
Environment: TOAD, Sql*Loader, Vertica, DB Visualizer Agile, Toad Data Point, Oracle 11g, Autosys, Shell, Tortoise SVN, APEX data loader.
Confidential, Miami, FL
SQL Developer
Responsibilities:
- Developed Advance PL/SQL packages, procedures, triggers, functions, Indexes and Collections to implement business logic using SQL Navigator .Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances.
- Created management analysis reporting using Parallel Queries, Java stored procedure, HTTP Package and WEB.SHOW DOCUMENT. Participated in change and code reviews to understand the testing needs of the change components. Worked on troubleshooting defects in timely manner.
- Involved in creating UNIX shell Scripting. Defragmentation of tables, partitioning, compressing and indexes for improved performance and efficiency. Involved in table redesigning with implementation of Partitions Table and Partition Indexes to make Database Faster and easier to maintain.
- Experience in Database Application Development, Query Optimization, Performance Tuning and DBA solutions and implementation experience in complete System Development Lifecycle.
- Used SQL Server SSIS tool to build high performance data integration solutions including extraction, transformation and load packages for data warehousing. Extracted data from the XML file and loaded it into the database.
- Designed and developed Oracle forms & reports generating upto 60 reports.
- Performed modifications on existing form as per change request and maintained it.
- Used Crystal Reports to track logins, mouse over's, click-through, session durations and demographical comparisons with SQL database of customer information.
- Worked on SQL*Loader to load data from flat files obtained from various facilities every day. Used standard packages like UTL FILE, DMBS SQL, and PL/SQL Collections and used BULK Binding involved in writing database procedures, functions and packages for Front End Module.
- Used principles of Normalization to improve the performance. Involved in ETL code using PL/SQL in order to meet requirements for Extract, transformation, cleansing and loading of data from source to target data structures.
- Involved in the continuous enhancements and fixing of production problems. Designed, implemented and tuned interfaces and batch jobs using PL/SQL.
- Involved in data replication and high availability design scenarios with Oracle Streams. Developed UNIX Shell scripts to automate repetitive database processes.
Environment: VB 6, JAVA, Oracle 10g/11g, PL/SQL, SQL*LOADER, Oracle Streams 10g (Replication), SQL PLUS, HTML, SQL Server SSIS, TOAD, XML, HP-UNIX shell scripting.
Confidential, Auburn Hills, Michigan
Oracle Developer
Responsibilities:
- Involved in development and enhancement of various modules.
- Involved in all phases of SDLC from requirement, design, development, and testing.
- Involved in discussions with data analysts in gathering the requirement and with client for any better enhancement of the application.
- Wrote packages, stored procedures for storing the client information and modifying the views to resolve any possible bugs.
- Created database objects such as tables, views, synonyms, indexes and sequences.
- Created views based on multiple tables and performed data changes using Instead of triggers for the underlying tables.
- Setup run time environment, creating and maintaining shell scripts to run stored procedures, packages to populate the valid data in staging tables and invalid data into error tables which will be sent back to sender notifying the errors.
- Constructed the required data involving complex JOIN statements, including outer-joins, intersect and union all.
- Worked with Collections and improved the performance of multi-row queries by using Bulk Collect and Bulk binds.
- Used DBMS JOB to automate the regular tasks and improve time-constrained performance.
- Tuned SQL statements using Explain plan, TKPROF for maximum efficiency and performance.
- Wrote SQL, PL/SQL programs to retrieve data from data repository using cursors and exceptional handling.
- Performed the uploading and downloading flat files from UNIX server using FTP.
- Created PL/SQL Procedures, Functions, Triggers and Packages to meet the business requirement of the project.
- Avoided mutating table errors using the new functionality in Oracle 11g i.e., Compound triggers.
- Used Global Temporary tables to access the repeated data within a session and improved the performance.
- Used SQL loader for importing the flat files which consists of several records of customer's information into the different tables in the database.
- Unit testing the PL/SQLcode.
Environment: Oracle 11g, SQL, PL/SQL, EXPLAIN PLAN, SQL developer, UNIX.