Importing and Exporting Oracle Data for Developers
Moving data between databases and schemas is an important need in today's data-centric world. In this course, you will learn the various tools and methods to get data in and out of Oracle quickly and efficiently in several popular formats.
What you'll learn
Today, more then ever, data is central to any organization to provide useful insights and competitive advantages. There is also a growing need to move data around, from one database to another or within schemas of the same database, for better analysis, testing, and review. Data warehouses are crucial for predictive analysis, and the need to have efficient tools to extract, transform, and load data in these environments is extremely important. In this course, you will learn how to get data quickly and efficiently in and out of Oracle using popular formats like Excel, XML, JSON, plain insert scripts, and SQL loader format, among others. We will learn about the flexible SQL*Loader tool and its powerful direct path option which can improve load performance by orders of magnitude. We will explore the powerful External table feature, which allows you to query data in external data files using the power of SQL and PL/SQL. After taking this course, as a database developer you should be well equipped with the skills and knowledge to perform data transfer tasks effectively.
Table of contents
- Module Overview 2m
- Exporting Data and Table Definitions in .CSV Format 9m
- Exporting Data in Excel Format 3m
- Exporting Data as Insert Statements 1m
- Exporting Data in JSON Format 1m
- Exporting Data in XML, .pdf, and HTML Formats 4m
- Using SQL Hints to Generate Output Formats 3m
- Exporting Data and Definition for Multiple Objects 4m
- Summary 1m
- Module Overview 2m
- Concepts 3m
- Use Cases 2m
- Steps to Create & Use External Tables 6m
- Demo: External Tables in Action 7m
- Access Parameters 7m
- Field Definitions 6m
- Demo: Access Parameters & Field Definitions 9m
- Column Transform Clauses 4m
- Demo: Column Transform Clauses 3m
- Oracle Data Pump Driver 7m
- Demo: Oracle Data Pump Driver 3m
- Altering External Table 2m
- Demo: Altering External Table 4m
- Performance Considerations 4m
- Summary 1m
- Module Overview 2m
- Features & Concepts 5m
- Control File with Fixed Data Format 6m
- Demo: SQL*Loader Process 4m
- Field Definitions & Datatypes 8m
- Delimited Data Format 5m
- Dealing with Short Records 1m
- SQL*Loader Generated Values 3m
- SQL Expressions 3m
- Filler Fields, DEFAULTIF & NULLIF 7m
- Demo: Filler Fields, DEFAULTIF & NULLIF & SQL Expressions 6m
- Conditions 2m
- Loading into Multiple Tables 2m
- Command Line Parameters 8m
- Loading Data from Multiple Files 2m
- Demo: Loading from Multiple Files & Loading into Multiple Tables 6m
- Summary 2m
- Module Overview 1m
- External Table Load 2m
- Demo: External Table Load 4m
- Direct Path Load 3m
- Direct Path Load Considerations 2m
- Constraints Handling with Direct Path Load 3m
- Parallel Loading 2m
- Demo: SQL*Loader Direct Path, Constraint, and Index Handling 7m
- SQL Loader Performance Improvement Tips 2m
- SQL Loader vs. External Tables 1m
- Summary 1m
About the author
Pankaj Jain is an experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and software development. He has worked on an array of technologies, with focus on Oracle at the database layer and Java at the web tier. He is a certified Oracle 11g DBA. He lives in Seattle with his wife Simita and boys Ronit and Rohak. When he is not playing with technology, he likes spending time with his kids, travelling and doing photography.