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.
Course info
Level
Beginner
Updated
May 27, 2016
Duration
3h 33m
Table of contents
Course Overview
Overview
Exporting Data Using SQL Developer
Importing Data Using SQL Developer
Querying Flat Files Using External Tables
Importing Data Using SQL*Loader
SQL*Loader - External Table & Direct Path
Description
Course info
Level
Beginner
Updated
May 27, 2016
Duration
3h 33m
Description

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.

About the author
About the author

Experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and software development.

More from the author
Oracle REST Data Services
Beginner
5h 31m
Feb 13, 2018
Getting Productive with SQL Developer
Beginner
3h 53m
Apr 20, 2017
More courses by Pankaj Jain
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hello, welcome to Pluralsight. My name is Pankaj Jain, and I'm very excited to present to you this course on Importing and Exporting Oracle Data for Developers. As a developer, working with multiple databases, you would often have a need to move data between databases, export data out from an Oracle database in formats like Excel, this can be passed to business users, or other popular formats like JSON, XML, etc. In this course, we will explore how to export and import data in various formats quickly and effectively using Oracle SQL Developer, which is a free and very powerful IDE provided by Oracle. We will then take a deep dive into external tables, which is a very cool feature of Oracle. It allows you to point at an external data file in the file system, and query it just as if it were a regular table using all the power of SQL and PL/SQL at your disposal, without actually loading the data in the database. We will then look at SQL Loader, a very widely used data loading tool, which is feature rich and extremely flexible in accepting several data formats. All you need to follow along is a very basic understanding of SQL and Oracle programming. I invite you to join me on this exciting journey, at the end of which you would find yourself well equipped with several useful tools and methods to do those data transfer tasks easily and effectively.

Exporting Data Using SQL Developer
SQL Developer is a very powerful IDE provided by Oracle. It also provides a lot of convenience when you want to get data out of Oracle in various formats. Hello, my name is Pankaj Jain, and welcome to this module on Exporting Data Using SQL Developer. In this module, we will see how to use SQL Developer to get data out of Oracle in various popular and commonly used formats. Oftentimes we want to export data out in CSV format for it to be loaded in another database including another schema or instance of Oracle itself. We will start off by seeing how to export data out of Oracle in CSV format. Sometimes we want to have the data out in Excel format for us to quickly pass it on to some other end users or work with it directly. We can open a CSV file also in Excel, but equally easily, we can export data out of Oracle in Excel format using SQL Developer. Next we will see how to export data out of Oracle as a file with insert statements. This is a convenient way to insert the data in another database schema. JSON is a very popular format. We can produce simple HTML output format for displaying data on a browser. Similarly, we can easily pass on the data for someone to review it in a PDF format and XML format, and SQL Developer makes it very easy to do that. We will also look at various SQL hints we can use in SQL Developer to export different data formats. We can also easily export table definitions along with data. This allows us to recreate them in another schema. We will see how we can export multiple table definitions, along with their data using SQL Developer. Alright, let's get started. Let's start off by jumping into a demo to see how to export data in CSV format.

Importing Data Using SQL Developer
In the last module, we looked at how to get data out of an Oracle database in various formats using SQL Developer. In this module, let us take a look at how we can get data into Oracle. SQL Developer again makes it very convenient to do so. Hello, my name is Pankaj Jain, and welcome to this module on Importing Data Using SQL Developer. In this module, we will see how we can get data in an Oracle database. In the last module, we looked at how to get data, as well as table definition out of Oracle in the CSV format. We will start off by looking at how we can use those scripts to recreate the table, as well as bring its data along in another schema of Oracle. We will import the data using the. csv file generated. Next we will do the import for the data in an Excel file. Here we will see how we can map the Excel columns to the table columns if they are not in the same order, and if the Excel column names do not match the table column names. Let's get started.

Querying Flat Files Using External Tables
External tables is a very powerful feature of the Oracle database, which allows you to access data in external files as if they are regular Oracle tables. So it allows you to query, slice and dice data, transform and insert it other tables using all the power of SQL and PL/SQL. Hello, my name is Pankaj Jain, and welcome to this module on Querying Flat Files Using External Tables. Let us take a high-level overview first to see how the module is going to flow. We will start talking about the concept of external tables and how they work. We will talk about some of the typical use cases where they provide value. We will define the syntax and see how to create external tables. We will then jump into a demo to see external tables in action, and see how SQL Developer makes it easy for us to generate a table script. We will then talk about how we can write data out of a database and external files using external tables, and then easily move that file around to another schema or instance to read or copy data in that schema. Along the way, we will talk about some of the performance considerations, and see how we can enhance it using compression and the parallel feature. This module should give you a fair idea to start working and being productive with external tables, and then provide you a solid base to further continue your learning. Let us now start off by understanding how external tables work.

Importing Data Using SQL*Loader
SQL Loader is another powerful utility in Oracle, which allows us to load data from external files into an Oracle database. It is a powerful data parsing engine, which can accommodate flexible data formats. Hello, my name is Pankaj Jain, and welcome to this module on importing data using SQL Loader. In this module, we will start off by talking about some of the great features of SQL Loader, which makes it so useful to work with. We will then take a conceptual overview of the load process. Fixed-width format is a very common data format. We will see how to load the fixed-width format files with SQL Loader using the position based syntax. We will then look at field definitions, the different data types, which can be used, how to specify the enclosure and termination for delimited data. Delimited data, like the comma separated values, or CSV files, is another popular format. SQL Loader also allows us to transform the data before loading it in the database, and we will see how to do that. Next, we will talk about the various command line parameters, which help us control the load process. For instance, maximum number of errors to accept, the number of rows to commit in a batch, etc. We will also talk about conditional load, and how we can use it to load data into multiple tables. We can also load data from multiple files. We will continue our discussion of SQL Loader in the next module where we will talk about SQL Loader's external table feature, and talk about the powerful direct path option. Alright, there is a lot of exciting stuff to talk about in this module, which will provide us a solid understanding of SQL Loader. Let us start off by talking about some of the features SQL Loader offers.

SQL*Loader - External Table & Direct Path
SQL Loader makes it very easy to generate external table load scripts and even execute them, and it can offer us a tremendous performance gain when loading large files using the direct path option. Hello, my name is Pankaj Jain, and welcome to this module on SQL Loader - External Tables and Direct Path Option. As I'd mentioned earlier, SQL Loader can help us generate and execute scripts for external tables with all the steps right from creating a directory if it does not exist to loading data in target tables using external tables. We will examine this option in this module. The direct path option provides us performance gains sometimes in orders of magnitude, as it does not incur the overhead, which occurs in normal SQL processing. We will take a look at how to execute direct path loads and some of its options. We will then talk about some of the ways to improve SQL Loader load performance. We will conclude by comparing external table load with SQL Loader load, and see where it makes sense to use one versus the other. Alright, let's get started.