Oracle File Handling

Name: Oracle File Handling
Rating: 4.3 (25 reviews)

Oracle File handing operation using UTL_FILE, SQL Loader and External Tables

Created bySiva LK

Last updated 4/2018

English

What you'll learn

Learn the options available in Oracle to handle/manage "Operating system files".
Use "UTL_FILE" package to read/write/copy/delete/rename files (in operating system).
Gain understanding on using BLOB column to store & retrieve binary files.
Use "SQL loader" utility to load data from text files into oracle database table.
Understand "External Tables" to import data from text files to database.
Understand "External Tables" to export data from database to text file.

Course content

6 sections • 36 lectures • 2h 31m total length

Introduction1:55
This tutorial helps you to understand file handling operation from oracle database using oracle supplied package (UTL_FILE), external table and SQL loader utility.

What you will be learning at the end of this section? (UTL_FILE)1:00
After completing this section, you will be able to

1. Understand file handling operation in oracle database using UTL_FILE package.
2. Read content from text file using PLSQL code.
3. Write into text file using PLSQL code.
4. Copy file from source to destination folder.
5. Remove a file from operating system.
6. Generate HTML reports of data using PLSQL code.
UTL_FILE - Introduction and Environment setup10:57
1.Learn about UTL_FILE package

2.Understand the preparatory steps (or environment setup) to do file handling operation using UTL_FILE package
Example 1 : Read first line from a file6:41
1. First program to read content from file
2. Understand the open,read & close APIs of UTL_FILE package
Example 2 : Read all the content of a file4:08
Understand how to read all the contents of file using UTL_FILE package
Example 3 : Write text data into file5:49
Example 4 : Write the content of table into a file3:21
1. Understand how to use UTL_FILE package and PLSQL program to write dynamic content of tabe or SQL output to file.
Example 5 : Generate a HTML report of employee list3:47
1. A simple HTML report generation using API's of utl_file package
Example 6 : Copy a file from one directory to another directory3:55
1. Understand data copy from source file to destination using UTL_FILE provided api.
Example 7 : Rename a file2:03
1. Understand how to rename a file using UTL_FILE package.
Example 8 : Remove a file from operating system.1:04
Understand how to remove files in operating system using fremove api
Handle exception during file handling operation.2:40
Understand exception handling during file handling operation
Doucment for reference ( Demo scripts & Explanations)0:02
Reference document containing demo scripts and details of the scripts.

What you will be learning at the end of this section? (External Table)0:42
External Table - Learning Objectives

By the end of this section, you will understand the external table, and how to use sqlloader and oracle data pump driver to load & unload data.
External table - Introduction6:54
Understand external table concept
Read data using "External Tables"1:48
Example to load data using oracle external table.
Export data using "External Tables"5:08
Example to load and unload data using external table.
Doucment for reference ( Demo scripts & Explanations)0:02
Reference document containing demo scripts and details of the scripts.

What you will be learning at the end of this section? (SQL Loader)0:51
After completing this section, you will be able to write sql loader scripts to load data from flat file to oracle database tables.
What is SQL Loader? Introduction7:49
1. Introduction to SQL Loader.
2. Understand the SQL loader command, and files associated with SQL load operation
Example 1 : Load data from flat file into database table7:25
Understand how to load data using SQL loader.
Example 2 : How to get details on records failed during load operation5:55
Understand how to get the details of records failed during load operation
Example 3 : Append data to table3:14
Understand how to add rows to non empty table using SQL loader "append" option
Example 4: Understand "Begindata" key word2:39
Understand how to use "Begindata" keyword to pass data in the control file itself
Example 5: How to escape field terminator5:04
Understand how to escape field terminator.
Example 6: How to load from positional based data file4:21
Understand how to load from positional data file using SQL loader
Example 7: How to manipulate data during load3:56
Understand how to use single row functions to manipulate the data during load
Example 8: How to load data from multiple data files6:39
Understand how to load data from multiple data file using SQL loader
Example 9: How to load data into multiple table4:21
Understand how to load data into multiple table using SQL loader
Example 10: Selective data load using SQL loader3:43
Understand how to use SQL loader to do selective data load
Example 11: Use SQL Loader to Store Text and Images into CLOB and BLOB columns13:05
This video explains about how to store the content of text file into CLOB column, and storing binary files into BLOB column using SQL loader utility.
Doucment for reference ( Demo scripts & Explanations)0:02
Reference document containing demo scripts and details of the scripts.

How to save binary file into oracle database table?10:01
Understand how to load binary data into oracle table
How to retrieve the saved binary file from database table to operating system?6:26
Understand how to retrieve the saved binary file from database table to operating system folder.
Doucment for reference ( Demo scripts )0:02
Attached the scripts used in demo to upload and download the binary content into/from database.

Requirements

Students should be familiar with basic Oracle SQL & PLSQL programming.
Students should be comfortable is using any one tool like SQL developer, TOAD etc.
Students should be familiar with basic SQLPlus commands

Description

This is a course on file handling operation in Oracle database using UTL_FILE package, SQL Loader Utility and External table.

After completing this course, you will be able to

1. Understand file handling operation in oracle database using UTL_FILE package.
2. Read content from text file using PLSQL code.
3. Write into text file using PLSQL code.
4. Copy file from source to destination folder.
5. Remove a file from operating system.
6. Generate HTML reports of data using PLSQL code.
7. Store and retrieve binary data into/from oracle database.
8. Read data from flat files using SQL loader utility.
9. Read data from files using "External table" functionality.
10. Export data using "External table" functionality.

Who this course is for:

This course is for oracle PLSQL developers who want to do file handling operations from database.
To know the different options available in oracle database with respect to file handling.
Anyone interested in learning file handling operations in operating system from oracle database.
For any enthusiastic oracle learner.

Oracle File Handling

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 2min

Oracle UTL_FILE Package12 lectures • 45min

Oracle External Table5 lectures • 15min

SQL loader14 lectures • 1hr 9min

Additional Lecture3 lectures • 16min

Summary1 lecture • 4min

Requirements

Description

Who this course is for: