SQL - LOADER & EXTERNAL TABLESQL -LOADERSQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables.
To execute the SQL*Load tool, you need at least three files:
Download a sample excel sheet(here the used excel sheet has 1000 rows) for uploading the data. convert the excel into csv file an d remove the header. Move the sheet into the shared folder and then copy the same to the ORACLE HOME path Step 2: Connect to the user and create a table with columns matching the text file Step 3: create control file with .ctl extension Step 4: Use the sqlldr query The values has not been loaded into the table.Step 5: check for the log file. This is because of the data type error for the joindate column. Alter and set datatype as “varchar” Step 6: load the data again using sqlldr query Step 7: check for the count in the table SQL-EXTERNAL TABLEAn Oracle Sequence is a database object, just like a table or view, that represents a sequence of integers that can be used by any table or view. A Sequence’s values can be accessed using the NEXTVAL, and CURRVAL pseudo-columns. A Sequence can be ascending or descending. External table is a table but it does not store any data inside the database where the data will be
residing in a form of external file. Data will be stored in an external file and it will be extract
into the table while displaying. Step 1: Here the csv file is copied into a directory “utl” . A directory is to be created in DB level which is similar to the directory in os level where the csv file is maintained and grant access on directory to the desired user. create table tab2(id int, firstname varchar(20), lastname varchar(20), gender varchar(20), country varchar(20),age int, joindate varchar(20)) organization external(type oracle_loader default directory fdir access parameters(records delimited by newline fields terminated by ','(id, firstname,lastname,gender,country,age,joindate)) location('sample.csv')) parallel 5 reject limit unlimited;Step 2: check for values in table Step 3: a log file would be created which show the progress Step 4: Now let us add one row in the csv file that we has uploaded and check for the count in the table 1234, Krishna Kithari, Female, India, 31, 18/05/2022 SQL> Select Count(*) from tab2; Count(*) ---------- 1001Step 5: we can see that the values has been added. ☛ Join to Learn from Experts: Oracle SQL Training in Chennai by TesDBAcademy
« Previous
Next Topic »
(SQL - DBMS Metadata)
|