Oracle Sql
Resume:
[pic]
Prashanth
*********.*********@*****.***
__________________________________________________________________________
__________
SUMMARY
. Over 8 years of experience as Oracle Applications Developer and
Application DBA Software Analyst, which include minimum 5 years in each of
the following areas of - Systems Analysis / Design, Business Analysis,
Database Logical and Physical Design, Oracle Applications Programming,
Development Tools, Developing the supporting framework for applications.
. Over 3 years of Technical Development Experience using Oracle e-Business
Suite of Applications - Release 11.5.10 Modules - Order Management (OM),
Inventory (INV), Bill of Material (BOM), development, Coding Standards,
RICE Component development - Reports, Interfaces, Customizations and
Extensions to Oracle Applications and Data Migrations, System
Administration.
. Involved in complete Project Life Cycle (SDLC) that includes Requirements
gathering & Analysis, Database Design & Development, Application
Development, Testing, Implementation and Support of the application
software.
. Excellent knowledge in Cluster Ready Services, Enterprise Manager Grid
Control, Data Replication ( Oracle Streams), Data Guard, Relational
Databases, Data Modeling and Managing Schema objects.
. Experience in developing PL/SQL Stored Procedures, Functions, Packages,
Database Triggers, Exception Handlers, Referenced Cursors, Database
Objects and Collections.
. Implementation of High Availability solutions with Oracle 10g RAC and
Replication using Oracle Streams.
. Extensively worked on Performance Tuning by using Explain plans and
various Hints.
. Improved application performance by examining SQL using Oracle Enterprise
Manager; reviewed explain plan results and modified database parameters,
added indexes, or re-wrote SQL to resolve performance issues.
. Over 3 years of Developing Web based Oracle Applications using Oracle
Applications Express (Apex 2.x, 3.x) with development expertise spanning
Requirements Analysis, Design, Development, Testing, Deployment,
Installing Apex and Configuring the database,
. Created numerous reports using Oracle Discoverer 9i/4i/3i.
. Involved in data replication and high availability design scenarios with
Oracle Streams.
. Hands-on experience in Data Conversion, Data Migration, Developing
Inbound and Outbound Interfaces, Customizing/Developing Forms & Reports,
Mapping, Testing and Implementation, Customizing Workflows in various
Oracle Financial and Manufacturing Modules.
. Involved in a Legacy system of Data Migration, Development Systems and
Production Support.
. Involved in System study by interacting with the end users, design and
development, testing of Client-Server projects using Visual Basic, JAVA,
JSP, Servlets, JDBC, Oracle, Oracle HTTP Server and MS-SQL Server.
. Experience using Modeling tools, logical and physical database design
using ERWIN.
. Responsible for identifying, defining and validating data elements
extracted from legacy systems.
. Performance Tuning by resolving user's issues and problems, monitoring
production database.
. Experience in Shell Programming on Oracle Database running in UNIX
environment.
. Experienced on SEI quality procedures, Preparation of Test Cases, Version
Control, (CVS) task allocation, delivery schedule, Project Management.
. Self-motivation and organizational ability to effectively set priorities
to achieve immediate & long-term goals and meet Operational deadlines.
. Provided Product support Experience in 24/7 for customers.
. Extensive programming experience in Object Oriented Programming.
. Ability to work in Self-managed and Team environments.
. Highly motivated, quality minded analyst, possess good interpersonal,
analytical presentation Skills.
EDUCATION
. Master in Business Administration, ITU
. MS in Computer Science, West Virginia University, US
. BS in Computer Science, JNTU, Hyderabad.
TECHNICAL SKILLS
Languages: WEB PL/SQL, PL/SQL, UNIX Shell, Perl, C,
C++, Pro *C, JAVA, JCL, COBOL, GAWK
Web Technologies: Web PL/SQL, Java, ASP, JDBC, EJB, XML, JSP,
HTML, Microsoft Visio, CFMX, Cold fusion, Java Script and
VB Script
Databases: Oracle Applications 11i, Oracle Database
10g/9i/8i, Oracle Discoverer 9i/4i/3i, MS SQL Server
7.0/ 2000, MS Access, UDB DB2, Siebel 7.5.3.8
Other Products: TOAD, SQL *PLUS, SQL Developer, Oracle Database
Integrator (ODI), Oracle Streams 10g, Microsoft
Visio, Power Designer, SQL*Loader, Oracle Apex
2.0/3.0/3.2, Crystal Reports XI, MS Office Product
Suite, Clear Case, Control-M, AutoSys, Erwin
4.1/5.0, SQL Navigator.
Operating Systems: Windows 98/2000/NT 4.0/XP, UNIX (Solaris, Red Hat
Linux) & MS-DOS.
PROFESSIONAL EXPERIENCE
Reliant Energy, Inc., Houston TX
Nov 2009 - Present
Sr. Oracle Developer (APEX) / Application DBA
Project - I
Market Data Repository (MDR) - ZONAL to NODAL Conversion
MDR receives Market data from ERCOT on a daily basis. Since ERCOT moved
from Zonal operating structure to Nodal market most of the existing file
formats have changed. This impacts all underlying MDR objects which are to
be re-designed to match the new file formats. Impact analysis was carried
out initially to identify all the Core Modules and list of Database
objects. The data is extracted from the source systems to the Oracle
database using EDI interface which loads the flat file outputs to stage
area. The mappings are invoked from UNIX shell scripts using Control-M
scheduling tool.
Responsibilities:
. Involved in full development cycle of planning Analysis, Design,
Development, Testing & Implementation.
. Did impact analysis of a change in business process, production table,
etc. Identified all the Core modules and database objects which are
impacted.
. Interacted with the users in gathering the requirements.
. Created Error Files and Log Tables containing data with discrepancies
to analyze and re-process the data.
. Developed PL/SQL packages to consolidate data from several reporting
tables and publish data in one common place for system users to be
able to run / build adhoc queries.
. Developed SQL scripts to Compute Variances between transactions
originating from different source systems.
. Created prompt based JSP reports which are built using REF cursors
declared within Oracle packages.
. Developing both data processing PL/SQL modules and analytical SQL
queries for reports.
. Extensively developed UNIX Shell scripts to transfer and archive
files.
. Wrote UNIX scripts for creating the heavy-duty batch jobs to automate
the process of updating files.
. Developed UNIX Shell scripts to process the Input ZIP files for
generating CSV file outputs. These csv files will be loaded to Stage
tables using EDI interface.
. Setup Control-M Jobs to schedule Job streams which run at specific
times of the day.
. Tuned database SQL statements and procedures by monitoring run times
and system statistics. Inserted hints, indexes, table partitioning and
rewrote code as required.
. Rectification of errors reported during System Test and User
Acceptance Test.
. Troubleshoot, fix, and implement bug fixes and enhancements within
tight deadlines.
. Documented business rules, functional and technical designs, test
cases, and user guides.
Environment: Oracle 11g, PL/SQL, APEX 3.1/4.0, SQL Developer / TOAD,
SQL*Loader, UNIX, Windows NT, Enterprise Scheduler Control-M, Citrix.
Project - II
Retail Portfolio Data Mart
The portfolio data is not very transparent and it requires days of work to
produce standard reports. The main goal of the project is to develop a Data
Mart, a User Interface and Automated Reports that provide on-demand
visibility to End Users. Some of the KPI's that users are interested in:
(a) Counts of customers by product type & offer/rate category at past and
future dates based on pending transactions, (b) Customer Attrition Profiles
by product type & offer/rate category, (c) Product Performance Analysis,
(d) Typical Customer profile for product etc...
The benefit of this project is to provide quick and easy access to
portfolio data for End Users in order to provide key required information
for decision-making. Bringing this data into the teams making the
recommendations will streamline decision-making and reduce turnaround time.
Responsibilities:
. Involved in full development cycle of planning Analysis, Design,
Development, Testing & Implementation.
. Interacted with the users in gathering the requirements.
. Developed a Web Portal Interface using Oracle APEX 4.0/3.2 integrated
with Oracle 11g database.
. Configured LDAP for Authenticating Users in the Application and
installed SDK for SSO to external 3rd party application
. Developed Security Package which controls User Authorization
privileges in the application.
. Developed PL/SQL Stored Procedures, Packages, functions and Database
Triggers.
. Created SQL Scripts for loading Data to Hyperion Essbase Cube from
Oracle summary tables.
. Developed automated SQL scripts to
reconcile Essbase data load with Oracle source data.
. Constructed automated web-based reports using Cognos. Formatted layout
for each report, created prompts using Power Prompts.
. Created various Crosstab reports and Drill through reports as per the
user requirements using conditional formatting.
. Developed Job Schedules and monitored the Informatica Sessions.
. Addressing the Performance and other Production issues with
the Cognos 8 reports and Powerplay cubes.
. Created various SQL for the test cases and also used some User-Defined
SQL's.
Environment: Oracle Application Express 4.0/3.1/3.2, SAS, Informatica Power
Center, Oracle 11g/10g RAC, Hyperion Essbase, PL/SQL, TOAD, SQL*Loader,
COGNOS, Windows NT, MS Visio Enterprise Scheduler Control-M, Citrix.
Kellogg Brown Root (KBR) Inc, Houston TX
Jun 2009 to Nov 2009
Project: LOGCAP Quickbase to Oracle APEX Migration
Oracle PL/SQL / Apex Developer
KBR is a leading global engineering, construction and services company
supporting the energy, petrochemicals, government services and civil
infrastructure sectors. As part of KBR's LOGCAP Quickbase to Oracle APEX
migration project, I was involved in developing the Oracle Apex
applications with Oracle 10g serving as a Back-end.
Responsibilities:
. Prepared Technical documents for all the Application Modules developed
& generated.
. Generated DDL statements for creation of new Database Objects like
Tables, Views, Sequences, Functions, Synonyms, Indexes, Triggers,
Packages and Stored Procedures.
. Developed several applications using the Oracle Apex UI, and
integrated with Oracle 10g database.
. Generated SQL Scripts and create views for reports.
. Involved in Performance Optimization to increase operational
efficiency using SQL Trace, TKPROF and Explain Plan Utilities.
. Used Oracle Streams for managing data.
. Developed an Import Utility Module, which would import CSV files
(which has random Column mappings), to final Target tables.
Extensively used UTL_FILE and External tables.
. Extensively used Dynamic SQL (DBMS_SQL, DBMS_DDL) in functions and
procedures.
. Involved in System Integration Testing and Documentation.
. Responsible in coding, trouble shooting and debugging.
. Optimizing/Tune/Monitor the procedures based on the throughput of the
results.
. Participated in the planning and execution of integration test, system
test, and user acceptance test activities, including developing and
documenting test plans.
Environment: Oracle Application Express 3.1, Oracle 10g RAC, PL/SQL, SQL
Developer, TOAD, SQL*Loader, Windows NT, MS Visio, Team Foundation Server
(TFS).
Freelife International, Milford, CT
Jul 2007 - May 2009
Project: Implementation of iPS (inPowerSuite)
Oracle PL/SQL Developer
Freelife International is a company which manufactures and markets the
"Goji Juice". The existing Freelife Business happens on an ACCPAC
accounting package and Jenkon system, a pretty outdated system. The main
aim of this project was to replace the old Jenkon system with the Oracle
iPS (InPowerSuite) & ACCPAC with the Oracle 11i implementation.
Responsibilities:
. Identified all the pieces of Jenkon that need to be written in the
iPS.
. Wrote PL/SQL stored procedures that would extract sales orders from iPS
and send them to third party companies such as ESTAFETA (Postal system in
Mexico) for order shipment.
. Modified and tested custom forms, open interfaces and APIs in
Forms/Reports and PL/SQL; created scripts to perform data quality
diagnostics; programmed custom APIs and extensions (fiscal year rollover,
out-year processing, job scheduler).
. Worked on Sales Order Conversion Program to load Sales Orders from Legacy
system (Jenkon).
. Integration between iPS and Oracle 11i (Revenue and Inventory).
. Involved in Importing the Orders data from legacy system to Order Entry
Interface tables using SQL*Loader.
. Generated financial web reports using HTML, JavaScript, and SQL; designed
and deployed web applications in Application Express (APEX).
. Created standardized reports using APEX to reconcile financial
transactions.
. Imported Requisition Details (Requisition Import) using PL/SQL UTL file
package wrote interface program to import Requisitions into the Interface
tables.
. Responsible for developing reports using Pipelined functions. These
pipelined functions are called from crystal reports for PDF output and
scheduled to run daily, weekly, monthly.
. Optimizing/Tune/Monitor the procedures based on the throughput of the
results.
. The existing Jenkon orders are stored in MSSQL Server, as a stop gap
solution the integration between MSSQL and Oracle was taken up and
performed using the linked server and DBMS heterogeneous Services.
. Prepared Erwin Data model(s) and deploy model(s) to Oracle instances.
. Used Explain Plan, Indexing and Hints for performance tuning.
. Extensively used Oracle Advanced Queuing to process the on line jobs.
. Designed and implemented a backup & recovery system
. Implement Oracle failover technology (RAC).
. Developed Stored Procedures, Functions, Packages and Triggers using
PL/SQL to extract Data efficiently.
. Designed and developed application in Oracle Application Express (APEX)
to house budget accounting structure and balances; implemented AJAX
facility to allow for data validations against value set tables prior to
inserts and updates.
. Re-wrote PL/SQL job scheduler to improve efficiency by running every
hour; delivered version continuously updated data resulting in unnecessary
database processing
. Utilized JDeveloper 10g ERD and database object creation facility for
custom development
. Performed DBA activities in terms of Troubleshooting and resolving
Performance bottlenecks as and when needed.
. Designed the Shopping Cart with Inventory Control & creation of Purchase
order using Oracle Web Application express (APEX).
. Involved in Scheduling the Jobs using AUTOSYS Tool & UNIX Shell Scripts.
. Participated in the planning and execution of integration test, system
test, and user acceptance test activities, including developing and
documenting test plans.
Environment: Oracle Applications 11i, Oracle Apex 3.2, Oracle 10g, Mod/Web
PL/SQL, Crystal Reports XI, Oracle Data Integrator (ODI),
Oracle TAR Management (Metalink), Pro*C, TOAD, SQL Server,
ERWIN 4.1, SQL*Loader, AUTOSYS, Windows NT, Linux.
Medtronic Inc, Minneapolis, MN Mar
2006 - Jun 2007
Project: Spinal Neuro Systems
Database Developer
Medtronic Inc. is the world leader in medical technology providing lifelong
solutions for people with chronic disease. We offer products, therapies and
services that enhance or extend the lives of millions of people. The main
aim of this project is extracting the data feeds from neurological Systems
into BI data base. Implementing this System helps Medtronic's to quickly
identify and remedy problem area, the application facilitates increased
efficiency in Medtronic's overall business Process.
Responsibilities:
. Worked extensively with ER and UML Data Models.
. Involved in Data Cleansing of Contacts, Events and Activity Plans
data.
. Designed and Developed new Data Marts along with modifying existing mart
to Support additional business requirements.
. Installed WebDB, Portal, and Application Express (APEX).
. Designed and developed Web based Customer Contract application with
Oracle Application Express (formerly HTMLDB).
. Prepared Erwin Data model(s) and deploy model(s) to Oracle instances.
. Used Explain Plan, Indexing and Hints for performance tuning.
. Developed Mappings and complex mapplets using Informatica 6.2 designer
for real time migration of data.
. Extensively used Transformations like Expression, Lookup, Aggregator,
Joiner and Update Strategy.
. Identified and resolved performance bottlenecks in various levels like
Source, Target, Mappings and Sessions.
. An event-driven architecture was designed using Oracle Advanced Queuing.
Designed the queues and their underlying queue table using an object type
implementation in order to pass XML messages via a Character Large Object
(CLOB).
. Developed JOB Schedules to automate the update process and informatica
Sessions.
. Implemented web based credit card training system in Oracle Portal to
accomplish training requirements in accordance with federal regulations
. Coded WebDB/Portal based trouble ticket system for monitoring and
documenting support requests and resolution
. Developed Stored Procedures, Functions, Packages and Triggers using
PL/SQL to extract Data efficiently.
. Analyzed existing Pro * C programs and translated to ETL environments.
. Involved in the Implementation and Production Support of the system.
. Used Stored Procedure transformation to determine the Volume and Value of
Shares traded on a given day.
. Involved in Scheduling the Jobs using AUTOSYS Tool & UNIX Shell Scripts.
. Participated in the planning and execution of integration test, system
test, and user acceptance test activities, including developing and
documenting test plans.
Environment: Oracle 10g/9i, Oracle Apex 3.0, Oracle Metalink, Informatica
Power Center 6.2, Siebel 7.5.3.8, Siebel Tools, Visio, Pro*C,
TOAD, BO, Maestro JS Console, Crystal Reports 10, ERWIN 4.1,
SQL*Loader, AUTOSYS, CRM, PVCS, UML,Windows 2000, HP-UX.
CITIBANK, DALLAS, TX May
2005 - Feb 2006
Database Consultant
Citibank is one of the Worlds leading financial institution. Project was
concerned with Migrating data from Legacy systems to their existing system.
The project also included the deployment of Cold fusion application
integrated with a SQL Server 2000 database which stored and processed pdf
documents for clients. As an Oracle developer I was involved in Migrating
data using SQL * Loader and performing Validations on the staging tables
and developing PL/SQL packages and procedures for transfer of data into the
target.
Responsibilities:
. Developed PL/SQL Stored Procedures, Packages, functions and Database
Triggers to dump the data from staging tables into Base Table.
. Developed SQL * Loader Control files to load the data files into the
Staging tables.
. Wrote SQL*Loader scripts to migrate the data from the text files and
populated the intermediate tables like GL_INTERFACE and
GL_BUDGET_INTERFACE.
. Used PL/SQL for validating the data from legacy system to interface
tables. Run journal import to load the data into Oracle application
tables.
. Analyzed various pro *C programs to develop a new set of programs
which suit the environment.
. Deployed web-applications in Application Express (APEX).
. Developed and Modified Existing Packages, Stored Procedures, Stored
Functions and Cursors.
. Responsible for creating Database Triggers for the application.
. Developed complex SQL statements using Joins and Sub Queries.
. Develop enhancements and new functionality for custom mortgage
banking/lending software in Cold Fusion MX 7, XML, SQL, JavaScript, and
XHTML.
. Responsible for integrating system with outside vendor systems via
XML.
. Reviewed data loads for accuracy and configured ETL tool to extract
SAP data.
. Generated Stored procedure skeletons from ERWIN/Oracle schema input.
. Installed Informatica software on the Client systems and configured
Informatica Server.
. Incorporated the use of a cftree and iframe structure to dynamically
display the pdf documents.
. Built the core cold fusion files to connect and return information via
XML.
. Supported local and remote web developers and content developers.
. Customization of User Interface using Forms 6i.
. Created sessions, database connections and batches using Informatica
server manager.
. Tuned the Performance for ETL jobs by tuning the SQL used in
Transformations.
. Used AUTOSYS Tool to Schedule the ETL jobs daily, Weekly and monthly
based on the business requirement
. Used TOAD, PL/SQL Developer tools for faster application design and
developments.
. Involved in testing the application to ensure proper Functionality and
Data Accuracy.
Environment: Oracle Applications 11.5.9, Oracle Apex 2.9 Oracle 9i, Forms
6i, Reports 6i, Informatica Power Center/Power Mart
5.1/6.1/6.2, TOAD, Cold Fusion, XML, XHTML, ASP 3.0, IIS, SQL
*Loader, SQL*Plus, Erwin, PL/SQL, Pro *C, AutoSys, UDB, DB2,
Windows XP, UNIX.
Nextel International (NII Holdings)
Sep2004-Apr 2005
Reston, VA.
Oracle Consultant
NII holdings, formerly known as Nextel international (NII) is one of the
worlds leading providers of fully integrated wireless communication
services. The project was aimed at loading data from the legacy systems to
their existing system. As an oracle developer I was involved in creating
staging tables and loading the data into them from legacy system and
created forms as per business requirements.
Responsibilities:
. Prepared Technical documents for all the Application Modules
developed & generated.
. Generated DDL statements for creation of new Database Objects like
Tables, Views, Sequences, Functions, Synonyms, Indexes, Triggers,
Packages and Stored Procedures.
. Developed Database Triggers to enforce security and also used
Ref cursors.
. Developed PL/SQL Stored Procedures, Functions and Packages and
Triggers to implement business rules into the application.
. Registered forms, menus, and concurrent programs into Application
Object Library.
. Involved in Data Loading and Data Migration. Used SQL*Loader to load data
from Excel file into temporary table and developed PL/SQL program to load
data from temporary table into base Tables.
. Modified Existing Pro *C programs for a faster Execution.
. Used Dynamic SQL (DBMS_SQL, DBMS_DDL) in functions and procedures.
. Used Export & Import to transfer data from production to testing
instances & to other databases using UTL_FILE.
. Designed and developed forms using Forms 6i based on client
requirement.
. Developed Reports using Reports 6i based on client requirements.
. Generated SQL Scripts and create views for reports.
. Used TOAD to Extract and test the data in the database.
. Involved in Performance Optimization to increase operational efficiency
using SQL Trace, TKPROF and Explain Plan Utilities.
. Developed Shell scripts to execute PL/SQL programs and invoke PL/SQL
objects. Wrote the Cron Jobs to schedule concurrent jobs. Developed
batch files to execute the back-end PL/SQL programs and Oracle Reports.
. Involved in Data Extraction using UTL_FILES in PL/SQL.
. Involved in System Integration Testing and Documentation.
. Responsible in coding, trouble shooting and debugging.
. Generated UNIX Shell scripts to automate Batch Jobs and Loading
Process.
Environment: Oracle9i, SQL, PL/SQL, Forms 6i, Reports 6i, SQL*Loader, Pro
*C, TOAD, Windows NT/XP, HP UNIX.
G.E Global Consumer Finance, India
Mar 2002 - Aug 2004
Oracle Developer
GE Capital Global Consumer Finance (GCF) is a leading provider of
financial services to retailers, manufacturers, and consumers in 28
countries around the world.
Responsibilities:
. Worked in Design, Development, Coding and Testing and Implementation
of the database units.
. Created Database objects like Tables, Views, Triggers, Packages &
Stored Procedures.
. Generated server side PL/SQL scripts for Data manipulation &
validation and created various Materialized views for remote
instances.
. Modified existing Stored Procedures in order to implement the
suggested changes in the System.
. Used SQL *Loader to load data from External System and developed PL/SQL
programs to dump the data from staging tables into Base Tables.
. Worked in creation of Tables using storage parameters and clusters.
. Worked in writing Pro*C programs, Sub Queries, PL/SQL Stored
procedures and Database Triggers.
. Fine Tuned SQL for the maximum efficiency in various in various
schemas across databases using Oracle Hints, Explain plans and SQL
Trace Sessions.
. DBMS utilities were used to extend the functionality of PL/SQL
programs such as DBMS_JOB for scheduling, UTL_FILE to read and write
from database to generate data file & DBMS_SQL to write Dynamic SQL.
. Worked in Installation, configuration of Oracle 9i database.
. Developed both inbound and outbound interfaces using PL/SQL to load
data into targeted database and extract the data from the database to
flat files.
. Coordinated and prioritized outstanding defects and system requests
based on business requirements.
. Documented the Design and Technical documentation of custom
applications developed.
Environment: Oracle 8i/9i, AIX-UNIX, Windows 2000, Pro *C, SQL*Loader,
TOAD, Visual Source Safe.
Indian Oil Corp, India
Aug 2001 - Feb 2002
Roles: Developer, Designer, Integration.
Project was to automate the HR department of Indian Oil Corp. Ltd. This
aims at minimizing the paper work that needs to be maintained and makes the
day today operations of the department easier.
Responsibilities:
. Took part in project designing by applying Object Oriented concepts.
. Design and Development of Objects using Object Oriented Design in C++.
. Developed Business logic using C++.
. Designed and developed various user interfaces by using Java 2.0 and
Swing.
. Developed client side programming using Java Applet for an
Environmental Application.
. Developed Multithreaded Swing Client applications and server
applications.
. Tested Java/JSP web applications and network modules to access remote
systems using sockets and URL connections.
. Designed and developed XML Reports by exporting the data from this
analysis tool.
. Developed a parser for user end calculations.
. Designed, built and maintained extensive project tracking and budget
forecasting system for accounting department.
. Involved in Unit and Integrated testing by using Numega and Probes for
performance and memory leakage.
Environment: JDK1.2, RMI, SWING, C/C++, XML, XSL, XSLT and SAX, DOM
Parsers, SQL Server, MS Access, WindowsNT/2000,
UNIX.
Contact this candidate