Oracle SQL Loader Examples


FTP Data Directly to SQL Loader

This post describes how to minimize the data load time when using FTP to load large amount of data. It takes considerable amount of time, when we FTP the data first to a target location and invoke SQL Loader to load the FTPed data. Instead of doing this, we will attempt to save time and some amount of disk space by making the FTP operation to feed the data directly to SQL Loader. This essentially ....

SQL Loader Keyword Options

userid -- ORACLE username/password for the database on which the extract needs to be loaded control -- Name of the control file log -- Name of the log file bad -- bad file details data -- data file name discard -- discard file name discardmax -- number of discards to allow (Default all) skip -- number of logical records to skip (Default 0) load -- ....

SQL Loader Express Mode Data Loading Tips

We discussed a brief introduction to the express mode in SQL Loader in Oracle 12C with an example in one of our earlier posts. In this post, let us discuss some of the FAQs / Tips on data loading in this mode with suitable examples. This is a pretty long post, so you can jump to your section suitably by looking into the summary section below. How to specify a different input file name in ....

Column Order in Oracle 12C SQL Loader- Express Mode

In the last post, we introduced express mode in SQL Loader with an example. In that post, we mentioned that the input data file for express mode should match the column order in which the table is created. In this post, we will see why this is important and what happens when this is not followed. To understand this better, let us create a simple table with some character and number columns as ....

Oracle 12C SQL Loader Express Mode Basic Example

SQL Loader - Oracle 12C - Express Mode I got introduced to the new capabilities of SQL Loader in Oracle 12C today. One such feature is the express mode that is available in 12C and in this post let us experiment this new approach with a basic example. To get this example working, you would require a working version of Oracle 12C available at your end. Some theory to start with, that explains ....

SQL Loader - Load BLOB data into a table

Loading blob data into an Oracle table is always a challenge, and that too when we do not have access to the server file system, creating directory objects is just not possible. SQL loader is a handy tool in this case, using which we will be able to push an image directly to a table in Oracle. Following article explains how to do that with a sample table:- Let us first create a table ....

Using Decode Function in SQL Loader

In this short blog post, we will explain how to use Decode Function in the control file of SQL Loader to Load data. Let us setup a simple test case with some data which we will insert into a table through SQL Loader. We have a test data as shown below for explaining Decode Usage: Samsung I900 White H/Set 16GB,Samsung, IPhone 3GS 16GB Black,Apple, IPhone 3G S 16GB White,Apple, ....

SQL Loader - Automation - Exit Codes

Oracle SQL Loader comes with a capability that can be very handy for automating SQL Loader jobs. This feature is exit codes, which helps to understand the status of the execution for a given job. This can be really useful when we are trying to automate the SQL Loader task. Eventhough this can be read from the log files, reading this information from exit codes can be much faster and eliminates ....

SQL Loader - Load XML data into XMLTYPE column

In this example, we will see how to load XML data into XMLTYPE column in an Oracle table. In one of my earlier posts, I'd described how to load BLOB type data into an Oracle table using SQL Loader. The link for that post is available here. To load XMLTYPE data, just assume that the column image_data is of XMLTYPE and not BLOB. The rest of the process is the same. Prepare your XML data in separate ....

SQL Loader - Load Multiple Input Files Into Multiple Tables - Conditionally

So, what problem you are facing now? I have three data files. Order1.csv, Order2.csv, Order3.csv. Each of these csv files contain order information from four different countries and order reference number. And I want to load them into multiple tables in Oracle using SQL Loader.. Order1.csvIND,REF1101AUS,REF1102BRZ,REF1103NZL,REF1104SAF,REF1105 Order2.csvIND,REF2101AUS,REF2102BRZ,REF2103NZL, ....

SQL Loader - Direct Path Loading-Example

I was experimenting between direct path loading and conventional loading abilities of Oracle SQL Loader and whoop, what a difference direct path loading makes..It loaded 23K odd rows into a table in Oracle in less than 1 second.. Amazing, the speed of loading data in direct path loading is incredibly fast..my logs below SQL*Loader: Release 10.2.0.1.0 - Production on Wed Feb 23 11:24:01 2011 ....

No comments:

Post a Comment