Data Sql
Resume:
SRILATHA
Sr. Oracle PL/SQL Developer
**********@*****.***
Professional Summary:
Oracle PL/SQL Developer with 8+ years of IT experience in full system lifecycle design and analysis, development and implementation using Oracle databases.
Has good knowledge of Software Development Life Cycle (SDLC) and Agile Methodology.
Expert level experience in advance PL/SQL that includes developing and using cursors, Ref-Cursors, Procedures, Stored Procedures Function, Packages, Oracle Supplied Packages, Objects, Collections, Partitions Tables, Triggers, Views, Materialized Views, Indexing, Performance Tuning, Nested Tables and Oracle Objects.
Experience in query optimization, Performance Tuning (PL/SQL) using SQL Trace, TKPROF, Explain Plan, Indexing, Hints, Bulk Binds, Bulk Collect, Creation of global temporary tables and table partitioning.
Used Bulk binding techniques like BULK COLLECT and FOR ALL to improve performance.
Enforcing security by creating roles, granting system and object privileges on the tables, stored procedures.
Automating data loading, extraction, reports generation using UNIX Shell scripting.
Experience in developing applications using forms (11g/10g/6i) and reports (11g/10g/6i).
Used supplied packages to extend the functionality of PL/SQL programs such as DBMS_JOBS/ DBMS_SCHEDULER for scheduling, UTL_FILE to read and write from database, DBMS_SQL to write Dynamic SQL.
Extensive experience in Relational and Dimensional Data modeling for creating Logical and Physical Design of Database and ER Diagrams using multiple data modeling tools.
Experience in Database design using Normalization and E/R Diagrams.
Extensively used UTL_FILE package to write the data from oracle tables to a flat file
Experience in Linux shell scripting, Batch programming.
Experienced in using Oracle utilities Import/Export, SQL loader, creation of control files to load the data from flat files, Data Pump to load the data in to the database.
Created Informatica mappings with PL/SQL procedures/functions to build business rules to load data.
Experienced in implementing code that performs well in a high volume OLTP Database & OLAP database environment.
Highly proficient in Data Modeling retaining concepts of RDBMS, Logical and Physical Data Modeling for data ware house methodologies in star schema.
Expert in the Data Analysis, Design, Development, Implementation and Testing using Data Conversions, Extraction, Transformation and Loading (ETL) and SQL Server, ORACLE and other relational and non-relational databases.
Excellent skills in Business process analysis, Data Analysis, Requirement Analysis and Use Case Development using UML methodology.
Proficient experience in working on conceptual, physical and logical data models using Data Modelling tool like Erwin.
Sound knowledge on scheduling jobs with Control-M.
Experience with version control, change management and defect tracking systems.
Good experience in version control of documents and database objects using tools like VSS and SVN.
Written complex SQL’s using joins, OLAP function, sub queries and correlated sub queries.
Extensive experience working with DBLinks and creating materialized views, bitmap indexes, Index Organized table, external tables etc.
Implemented Tableau reports in a variety of use cases requiring complex customizations and large data sets.
Suggested and Implemented indexes for faster retrieval of data in Weekly and Monthly Metrics reports.
Developing windows batch scripts that are used in job-scheduling and job scheduling through Control-M.
Experienced working on PERL and Korn UNIX Shell scripting.
Hands on experience with Oracle APEX (Application Express) 4.0/4.1.
Exposure to process oriented methods such as ticketing system (remedy).
Developed XML files using PL/SQL packages as per the user requirement.
Proficiency in analysis, design, development, production and testing of applications in Oracle APEX.
Deep Knowledge in creating DDL, DML queries for SQL, PL/SQL
Experience in creating Unified Modeling Language (UML) diagrams like Class, Sequence and Use Case diagrams using Rational Rose and MS Visio.
Experience in Data warehousing concepts and Data mart building, Decision Support Systems using ETL Tools.
Experience in updating, accessing and version controlling the source code using Visual Source Safe (VSS), Team Foundation Server (TFS).
Developed ETL Process (Extraction, Transformation and Loading of data into Data warehouse).
Well versed in MS Office Suite such as Visio, Word, Excel, Access and PowerPoint.
Worked on all phases of Agile/Scrum SDLC with minimal guidance.
Experience performing Unit and Integration Testing, System Integration Testing (SIT) and User Acceptance Testing (UAT).
Good knowledge on logical and physical Data Modeling using normalizing Techniques.
Highly analytical thinking with demonstrated talent for identifying, scrutinizing, improving, and streamlining complex work processes.
Excellent interpersonal skills, team player and ability to work well with all levels of an organization with effective presentation, verbal and written communication skills.
Major strengths are familiarity with multiple software systems, tools and the ability to learn quickly new technologies and adapt to new environments.
TECHNICAL SKILLS:
RDBMS
ORACLE 12c/11g/10g/9i, MS-SQL Server 2008, MYSQL, MS-Access.
Operating System
Windows XP, Vista 7, 8, UNIX & Linux
Languages
C, C++, SQL, PL/SQL, Core Java,
Scripting Languages
Java Script, UNIX Scripting, Perl Scripting, XML, HTML, SQL and PLSQL
Oracle Tools
Oracle PL/SQL, SQL PLUS, SQL Loader, Informatica, Tableau, Oracle Procedure Builder, Oracle Enterprise Manager.
Development Tools
Reports Builder 10g/9i, Forms Builder 10g/9i, SQL Developer, PLSQL Develope, TOAD, Erwin.
Other Tools
Visual Studio 2010/08, Control-M, WS-FTP, Win-SCP, Putty.
Version Control and Ticketing
VSS, SVN, Remedy, Team Foundation Server (TFS).
PROFESSIONAL EXPERIENCE:
CLIENT: Active Health Management, NYC, NY. June 2016 to Present
ROLE: Oracle PL/SQL Developer
Responsibilities:
Involved in the full development lifecycle, responsible for collecting business requirements, System Analysis, Functional Specifications, Detailed Technical Design, Application development, Data Modeling, Data Warehouse Implementation.
Analyzed customer specifications and custom design of applications successfully and implementing designs in new/existing application using PL/SQL.
Generating DDL files to create new database objects like tables, views, sequences, functions, synonyms, indexes, triggers, packages, stored procedures, roles and granting privileges by incorporating business standards throughout the files.
Worked in Oracle Cursor, Exception Handling, Collections like REF, Nested tables, VARRAYS and IOTs.
Create database objects such as tables, global temporary tables, views, synonyms, indexes, sequences and database links.
Worked on SQL Tuning by using various tools like EXPLAIN PLAN, DBMS_PROFILER and Hints etc.
Involved in Design of Data modeling as per the requirement.
Move large tables and data utilizing different techniques including bulk collect/forall techniques.
Well acquainted in working with complex SQL queries with joins, date functions, inline functions, functions and procedures.
Worked on materialized views, DBMS_SCHEDULER for scheduling jobs and UTL_MAIL to send mails using oracle 10g.
Generating Reporting Dashboards based on user requests using Tableau
Extensively used variable like CLOB, BLOB, and XMLTYPE.
Streamline the reporting process develop using reusable code, easing the troubleshooting needs, meeting complex scheduling requirements.
Used Bulk collect, bulk insert, update functions in Queries
Modify existing ETL scripts to fix defects at the root cause as enhancement part of the project.
Writing database Objects like Packages, Procedures and Functions.
Created database link for remote instances to retrieve data from other databases.
Create materialized views to maintain copies of remote data on the local node.
Worked on Unix Shell Scripting for Scheduling Batch Jobs using Crontab.
Created SQL*Loader control scripts to load data from flat files into the target database tables.
Converted Sql result to XML format and did DML operations from XML Targets by using DBMS_XMLGEN Package
Handled errors by using Exception Handing extensively for the ease of debugging and displaying the error messages in the application.
Wrote complex SQL scripts and was responsible for Performance tuning of SQL Queries.
Extensively used analytical functions such as lag, lead, rank, dense rank.
Used PL/SQL and SQL*Loader to create ETL packages for flat file loading and error capturing into log tables.
Involved in development of User Interface Objects (Forms and Reports) and Testing of entire module.
Migrated forms from 6i to 11g and recompiled them for validating any decommissioned features.
Developing user interface for database applications using the Oracle Application Express (Apex).
Created and monitored the scheduled jobs as a part of support on regular basis
Identifying the data issues and worked on calling the data issue tickets.
Design, develop, implement, and assist and in validating ETL processes.
Created Unit Test plans and involved in primary unit testing.
Extensively worked with developing reports using Oracle Report 10g for the business user’s readability and scheduled jobs for the reports to be emailed to the respective person.
Involved in Developing and Generating Reports.
Used SVN for all code modification, by creating a working directory, creating tags, updating trunks, merging, checking out and committing any changes
Responsible for troubleshooting, debugging, problem solving and tuning for improving performance of the backend application programs.
Using existing processes and procedures for application deployment and provided 24x7 production support as Primary.
Environment: Oracle Database 11g/9i, SQL Developer, SQL Loader, Forms Builder 11g/10g, Reports Builder 11g/10g, ETL, Informatica, ERWIN, APEX, XML, SQL, PLSQL, Microsoft Office Tools, Unix Shell Scripting, SVN,WINSCP, PUTTY.
ADP, Durham, NC. Aug 2015 to June 2016
ROLE: Oracle PL/SQL Developer
Responsibilities:
Involved in all phases of System Development Life Cycle (SDLC)
Responsible for developing and testing of client application code related to US Business applications.
Worked with Business Systems Analysts and Database Administrators to understand and implement functional and non-functional requirements into the project.
Involved in the database design for better understanding the relations, associations and dependencies within the database.
Wrote Procedures and database Triggers for the validation of input data and to implement business rules.
Created programming code in PL/SQL blocks using advanced concepts of Collections (Associated Arrays, Nested Table, and Varray), Dynamic SQL, Records, and Partition methods for Oracle tables to boost performance.
Created and developed various DB objects like tables, views, materialized Views, partitioning tables, indexes, sequences, synonyms for the business application development.
Involved in writing PL/SQL packages, procedures, and functions.
Extensively used Materialized views and DB links for remote instances to retrieve data from other databases
Involved in the database design for better understanding the relations, associations and dependencies within the database.
Developed ETL process (Extraction, Transformation and Loading of data into Data warehouse).
Involved in developing scenarios for Unit, Integration testing to ensure that all the components work correctly when integrated.
Upgraded, maintained and troubleshooter for Oracle Forms and Reports
Oracle Forms created the back-end system for administrators
Interacting with the users and troubleshooting the problems involved with the development of stored procedures, Triggers and with the privileges.
Modify existing ETL scripts to fix defects at the root cause as enhancement part of project.
Exported reports into various formats like XML and MS-Word.
Developed PL/SQL Procedures and UNIX Scripts for Automation of UNIX jobs and running files in batch mode.
Extensive knowledge in implementing oracle triggers in various application tables for reports and other purposes etc.
Developed Database objects like tables, Indexes, triggers for Reporting Database.
Created Mail Scripts in UNIX to send notification on successful completion or failure of various Interfaces and Jobs, with Customized Subject and Text.
Extensively used Putty to login into UNIX environment
Extensively used software configuration management system, such as Subversion
Developed UNIX scripts for transmitting files from the secure server to the customer specified server, using various FTP batch processes.
Designed and developed Oracle Monthly Reports.
Scheduled the execution of PL/SQL Procedures using UNIX Shell scripts for updating of Base tables from Transaction Tables.
Responsible for the production support and code fixing.
Environment: PL/SQL, SQL, Oracle 11g, TOAD, Windows XP, UNIX, SQL* Loader, Putty, Oracle Forms Builder 10g, Oracle Reports Builder 10g, ERWIN, SVN.
DTCC, NYC, NY. Nov 2014 to Jul 2015
ROLE: Oracle PL/SQL Developer
Responsibilities:
Extensively involved in using Hints to direct influence the Optimizer to choose an optimum query Execution Plan
Used Bulk Bind (Bulk collect and Forall) for better performance and easy retrieval of data, by reducing context switching
Created PL/SQL Scripts to extract the data from the operational database into simple flat text files using UTL_File package.
Created Users, assigned Privileges and Roles
Modify table space and object storage.
Altered, Modified and Created Tables, Views and Sequences
Involved in working on Oracle Pseudo columns like ROWID, ROWNUM, SYSDATE, SYSTEMTIMESTAMP, USER etc.
Used various SQL Expressions and Conditions in standalone procedures and functions.
Partitioned the fact tables and materialized views to enhance the performance.
Used Oracle data pump utilities (EXPDP/IMPDP) for Copying of data from development server to test and production server.
Used features like Bulk Collect AND Bulk Insert to process large amount of data in data base.
Performed application on performance tuning by using SQL Trace and Explain plan for better performance.
Used Erwin to transform data requirements into data models.
Extensively worked with developing reports using Oracle Report 10g for the business user’s readability and scheduled jobs for the reports to be emailed to the respective person.
Used UTL_FILE Packages for handling External Files.
Worked on Forms, Menus, Object Libraries, and PL/SQL Library using Oracle Forms Developer 10g.
Involved in development of User Interface Objects (Forms and Reports) and Testing of entire module.
Configured the data warehouse application for best performance with parallel query, Materialized Views, Query Rewrite.
Created batch jobs to load data into our database using UNIX Shell Script and Control-M scheduling.
Created Database Scheduler scripts and applications using UNIX shell scripts.
Loaded and unloaded data sets from or to Oracle Databases using various kinds of data loading tools such as SQL*Loader and Oracle Import/Export Utility.
Used Explain Plan and hints to tune the SQL
Worked on UNIX environment and shell scripting for creating Batch Jobs using UNIX KORN, AWK UNIX Shell Scripts and API’s Using PERL Scripts.
Executing and maintaining the data warehouse extract transform load (ETL) process.
Use of rollup, inline views, cube operators, merge and update statement.
Environment: Oracle 10g/11g, UNIX, SQL, PL/SQL, SQL*PLUS, SQL*Loader, Forms Builder 10g, Reports Builder 10g, TOAD, Erwin, Control-M, Windows XP
BMC Software, Houston, TX. Oct 2012 to Nov 2014
ROLE: Oracle PL/SQL Developer
Responsibilities:
Involved in full development cycle of Planning, Analysis, Design, Development, Testing and Implementation.
Designed logical and physical data models for star and snowflake schemas using Erwin
Wrote sequences for automatic generation of unique keys to support primary and foreign key constraints in data conversions.
Created and modified SQL*Plus, PL/SQL and SQL*Loader scripts for data conversions
Upgraded data bases from Oracle 9i to 10g in different environments, implemented new features by retaining the existing business rules
Developed and modified triggers, packages, functions and stored procedures for data conversions and PL/SQL procedures to create database objects dynamically based on user inputs
Creation of database objects like tables, views, materialized views, procedures and packages using oracle tools like Toad, PL/SQL Developer and SQL* plus.
Wrote SQL, PL/SQL, SQL*Plus programs required to retrieve data using cursors and exception handling
Extensively used bulk collection in PL/SQL objects for improving the performing
Well experienced in developing the Efficient Data model, ER Diagrams, Data integrity and organizing the data as per the Business requirements.
Partitioned the fact tables and materialized views to enhance the performance.
Worked on XML along with PL/SQL to develop and modify web forms
Designed Data Modeling, Design Specifications and to analyze Dependencies
Creating indexes on tables to improve the performance by eliminating the full table scans and views for hiding the actual tables and to eliminate the complexity of the large queries
Fine-tuned procedures/SQL queries for maximum efficiency in various databases using Oracle Hints, for Rule based optimization
Used Discoverer 2000 to provide end users easy access to data and help in data analysis
Created custom reports and forms for the end users to check the details and errors
Environment: SQL Developer, Toad, SQL Loader, Oracle 9i to 10g, Oracle Forms Builder 11g/6i,
BRIMM TECHNO SOLUTIONS, HYDERABAD June 2011 to Sept 2012
ROLE: PL/SQL DEVELOPER
Responsibilities:
Involved in SDLC gathering requirements from end users. Developed views to facilitate easy interface implementation and enforce security on critical customer information.
Developed stored procedures and triggers to facilitate consistent data entry into the database. Written Stored Procedures using PL/SQL and functions and procedure for common utilities.
Development and maintenance work on PL/SQL, UNIX Shell Scripting.
Working closely with onsite (coordinator, Business Analyst, Solution Architect, Data Modeler, QA etc), to ensure that the end to end designs meet the business and data requirements
Coordinating with other teams like application development, QA team, to facilitate proper integration of the DB solution with the application counter part
Production support (Resolving and preventing issues that may occur in daily ETL loads, supporting code promotion, identification and analysis of data issues reported by clients of different financial services, etc)
Overseeing any DB changes or requests in any of the environments, enforcement of integration standards using knowledge of the current data environment, industry trends to identify and implement leading industry data practices
Develop detailed Data Mapping Document, technical specifications and ETL documentations.
Participated in system analysis and data modeling, which included creating tables, views, indexes, synonyms, triggers, functions, procedures, cursors and packages. Created programming code using advanced concepts of Records, Collections and Dynamic SQL.
Developed Database Triggers for audit and validation purpose. Used pipelined functions for faster data accessibility. Writing validation packages using PL/SQL package.
Used advanced Bulk techniques (FOR ALL, BULK COLLECT) to improve performance. Developed Installation scripts for all the deliverables.
Involved in performance tuning on SQL using AUTO TRACE, EXPLAIN PLAN, TKPROF utilities.
Created and manipulated stored procedures, functions, packages and triggers using TOAD. Wrote heavy stored procedures using dynamic SQL to populate data into temp tables from fact and dimensional tables for reporting purpose.
Involved in working with IMPLICIT CURSORS, EXPLICIT CURSORS, and REF CURSORS.
Created materialized views using different clauses like FAST, COMPLETE, FORCE, ON COMMIT.
Experience in using various oracle PL/SQL collections VARRAYS, NESTED TABLE, ASSOCIATIVE ARRAYS
Involved in creating various utility scripts to generate log files in UNIX using shell scripts.
Involved in migrating database from Oracle 9i to 10g database. Involved in developing screens and generating reports.
Warehousing concepts, dimensional star schema and snowflake schema methodologies.
Scheduled the jobs through Autosys, creating JILS for new jobs and monitoring the jobs through frontend.
Environment: Oracle 9i/10g, SQL, PL/SQL, SQL*Loader, Erwin, Informatica, Autosys, TOAD, UNIX
HSBC GLT, HYDERABAD. July 2009 to May 2011
ROLE: ORACLE PL/SQL Developer
Responsibilities:
Developed user defined functions based on requirements. Developed back end interfaces using PL/SQL Stored Packages, Procedures, Functions, Collections and Triggers. Created PL/SQL procedures and packages.
Production Support.
Worked as SME (Subject Matter Expert)
Writing PL/SQL Packages, Procedures, Functions, Triggers, Cursors, Anonymous block and other objects for the back-end processing of the proposed database design.
Writing scripts for creating Tables, Sequences, Views, Synonyms and Other Database Objects.
Client side validation using JavaScript
Designing of the Database Objects
Deployment support.
Conducted PL/SQL training session for co-workers to educate about the latest PL/SQL features, PL/SQL performance tuning.
Performed code reviews.
Extensive query optimization and performance tuning. Created the integrity constraints and Database Triggers for the data validations. Used PL/SQL Tables and Records in payment generation process. Written Test Plans and Involved in unit testing.
Created relevant staging tables to load the CSV files, identified the business validation rules. Created SQL Loader script generator application using UNIX shell scripting and PL/SQL.
Experience in creating the tables and sequences for the experimental data load capture. Loaded the data into the tables using TOAD and SQL*plus. Created metadata validation lookup tables and pre-populated them using SQL Loader generator application.
Designed competent PL/SQL programs using PL/SQL collections, Records types, and Object types.
Good knowledge on joins, which are inner join, left outer join and Hash join, Semi join.
Implemented various UNIX scripts to generate the data in CSV format.
Tuned several Oracle SQL statements using Explain Plan, Auto Trace utilities.
Involved in implementing table partitions using Range, Hash, Composite techniques.
Worked on external hints during the SQL optimization to fast up the SQL process.
Experience in working a variety of analytical functions, RANK, DENSE RANK, LAG, and LEAD.
Database security by authenticating users, using logon triggers. Created materialized view on remote database and automated scheduler of refreshing of materialized view.
Environment: Oracle 9i, SQL Plus, PL/SQL and Windows XP, UNIX, Windows XP, Oracle SQL Developer, Erwin, DATASTAGE, TOAD, Putty, MS Access 2000/2002.
EDUCATION
Masters in Computer Application (MCA 2009) From Osmania University, Hyderabad, India.
Contact this candidate