Your videos are really helpful and they way you explain is very clear and organised.. You are a master in teaching what you clearly understand.. You had mastered that.. Thanks a lot
Hi Siva, very well explained. Have a suggestion, I understand it’s difficult to cover all interview questions, but if u can include few. 1 can we load multiple external files into a single table? 1. Can one file be loaded into multiple tables? 3. Can we skip columns ? 4. Can we limit the number of rows ? 5. Types of sql loader and which is faster?
Ya Vipul, You are right, Its bit difficult to cover all the questions, however this videos would just give some starting point for some readers to start with. However, I will try to cover all these questions in some other videos. Thanks for your comment. Siva.
Thank you for your indepth details, Sir.... Once data is loaded can we manipulate do DML functions, can we join that table with other tables... and store it in new table...?
Hi Siva, There are tables A and B and each having 1 lakh records. Truncated table A nd deleted table B. If write query to find out count which one will return faster..
Thanks for the valuable information.. I have CSV file and file name with date format..example data_load_12112020.csv Kindly help me to write control file..
Awsome video sir,very well explained..pls make a video on advanced topics like.. local and global index, explain plan and nested loop join ,hash join,sort merge join and pipeline function , performance tuning etc..
Hello sir. Your videos are very much helpful. I have one question, being asked in interview. How to load unique records from flat file to Oracle table? Can we achieve this using SQL loader? Kindly answer sir
Thanks for the clear explanation, I would like to know if there are thousands of records to load and if everyone of them fail, what is the outcome? Also is there a limit to load the data, or if it needs to be set?
Its quite interesting question to answer, i will cover in detail as part of some other viedo. In short, the Cloud database is managed somewere in the newtwork ( or say managed by cloud service providers like AWS etc) and we just access as if it is a normal db. Its just a DB service by cloud. However in-memory database is sometimes used to save the frequently accessed data in the OS RAM itself, rather than accessing from actual underlying DB to avoid roundtrip to/from DB. you can look into REDIS db , an inmemory DB thats being used these days.
@@SivaAcademy load data infile 'E:\A\Book1.csv' Truncate into table Test fields terminated by ',' ( ID, NAME "WHEN NAME IS NOT NULL" ) is this correct bro????
@Shaik SQLLOADER - used to transfer data from files into database.( Files should have a standard format), Mainly to used to bulk load data from external files to tables.( Only one way of loading, we cannot export back to file from database using sqlloader utility) DATAPUMP - Used to export & import the data from/to database. Mainly used to transfer database objects, metadata, and data from one database to another. The dump will be of binary format, hence only the datapump uitliy only can be used to import back the data.
Hi, I've loaded a data but the log, bad and discarded.txt files are not shown in the folder. In command prompt, it shows an error called 'Commit point reached - logical record count 4 and 5'. can you please help me?
The data format I have used in the example is simple comma separated data, however sql loader supports other formats too. We need to define the format in the control file, then sql loader will take care of loading the data.
I hope you are asking about the SQL Loader control file only. Its just a txt file only. You can open in any text editor like notepad etc, and you can edit and save the changes.
HI , How we can convert "Wed May 21 00:00:00 EDT 2008" as date and store using the SQL loader Column name street,city,zip,state,beds,baths,sq__ft,type,sale_date,price,latitude,longitude Record 3526 HIGH ST,SACRAMENTO,95838,CA,2,1,836,Residential,Wed May 21 00:00:00 EDT 2008,59222,38.631913,-121.434879
@suyog, you can use fixed position method, rather than using delimiter. sample control file for your reference load data infile 'emp.txt' insert into table emp ( field_1 position( 1:5), field_2 position(6:10), field_3 position(11:25) )
Sample data for your reference. First 5 char is student id 6th postition to 9th postition is Student Name 10th to 12th postition is department name. 10000SIVAECE 10002RAVIEEE 10003KINGCSE field_1 position( 1:5), field_2 position(6:9), field_3 position(10:12) You can refer to this link for exact syntax and a good example explained here www.oracle-dba-online.com/sql-loader-case-studies.htm
Failed Record - Records selected for insertion, but due to some error like data type mismatch or length etc, if sql loader unable to insert into target table, then those are failed records, moved to bad file. Rejected records - by specifying a condition in sql loader control file ( like"when deptno="10"), we are asking sql loader to load records that matches the condition. Any record thats not matched will be moved to discard file or you can say rejected data
Hi Arun, With just error message its bit difficult to say, unless we the see the like of code that throws this error. so, if possible you can share the code to my mail id, and mention the line number from where this error is being throwed.
SQL Loader is an oracle specific UTILITY for loading the data from files into oracle tables. ETL is the process( in-fact a series of operation) of extracting , transforming and loading the data. SQL Loader is can be used as part of ETL process, however we cannot say SQLLOADER is full fledged ETL. There are many ETL tools, like informatica, Talend, ODI available in the market to specially do ETL operation end to end.
![Natanael Cano - Amor Eterno [Official Video]](http://i.ytimg.com/vi/EO4prCs6WpM/mqdefault.jpg)
Your videos are really helpful and they way you explain is very clear and organised.. You are a master in teaching what you clearly understand.. You had mastered that.. Thanks a lot
Thank you
Hi Siva, very well explained. Have a suggestion, I understand it’s difficult to cover all interview questions, but if u can include few. 1 can we load multiple external files into a single table? 1. Can one file be loaded into multiple tables? 3. Can we skip columns ? 4. Can we limit the number of rows ? 5. Types of sql loader and which is faster?
Ya Vipul,
You are right, Its bit difficult to cover all the questions, however this videos would just give some starting point for some readers to start with.
However, I will try to cover all these questions in some other videos.
Thanks for your comment.
Siva.
Very well Explained. Thank You Siva.
Welcome Vikrant 🙏
Too late to watch this video. Again this was a masterpiece. Thanks Siva bhai
Welcome bro
Good and understanding way of explanation. 👍👍👍👍
🙏🙏🙏
Hello Sir, Thank you so much. You are a gem :)
💐💐thanks for your comments bro
Thank you for your indepth details, Sir....
Once data is loaded can we manipulate do DML functions, can we join that table with other tables... and store it in new table...?
yes, you can do bro
Hi Siva,
There are tables A and B and each having 1 lakh records. Truncated table A nd deleted table B. If write query to find out count which one will return faster..
Partially answered in this video.
ruclips.net/user/edit?o=U&video_id=evktGJrgORA
However, i will cover this in detail some time later.
Thanks,
Siva.
Very nice explanation . thanks.
Very Nice
Very good explanation 👌👌👋👋👍👍
Thank you bro
Hello siva bro any Oracle coaching available from siva academy... Your way of teaching is amazing bro
Welcome bro
When I use the sqlldr script in a SQL file and try to execute it throws unknown error. Can you please show the syntax
Review "LoaderDataPlus" tool to load flat files and to create sintax to import data on: MariaDB, MySQL, PostgreSQL, ORACLE, SQLServer y SQLite.
Super explation thank you
Welcome bro
Thanks for the valuable information..
I have CSV file and file name with date format..example data_load_12112020.csv
Kindly help me to write control file..
Perfectly and clearly explained 😊☺
My pleasure
Hi Sir, can you make a video about some of the advanced features in PLSQL like function overloading, synonyms, etc. ?
sure, Please stay tuned for more advanced features in PLSQL
Thanks a lot Sir. Nicely explained
Welcome bro
Excellent and clear explanation
Glad it was helpful!
Clear cut explanation
Thank you bro
Very clear explanation 😍
Thank you
Nice and Clear explanation Siva... :)
Thank you bro
thanks
Thank you
Tq u so much my dear bro
My pleasure bro 💐
Awsome video sir,very well explained..pls make a video on advanced topics like.. local and global index, explain plan and nested loop join ,hash join,sort merge join and pipeline function , performance tuning etc..
Sure Shashank, Will do soon. Working on sql performance tuning videos, will try to post soon.
Thanks for your comment.
Siva.
Hello sir, can you please make a Video on UTL_FILE package.Thanks.
Sure please stay tuned
Hi Siva,Can you please make a video for loading a csv/text file into table using dbms utl file package?Thanks!
Hello sir. Your videos are very much helpful.
I have one question, being asked in interview.
How to load unique records from flat file to Oracle table? Can we achieve this using SQL loader?
Kindly answer sir
Thank you, sure I will cover in upcoming videos, please stay tuned
@@SivaAcademy sure I will watch
Thanks for the clear explanation, I would like to know if there are thousands of records to load and if everyone of them fail, what is the outcome? Also is there a limit to load the data, or if it needs to be set?
Hi Sujatha, Yes all the errored out records will be moved to log files
In SQL loader control file you can mention the number of records you want to load. There is a parameter called Load which you need to set.
Great explanation Sir
@samprati jain, Thanks for your comment 🙏
To the point.
Great Video
Thank you
Hello Sir
Your explanation over any topic is phenomenal
Can you please make videos on interview questions on data warehouse also??
Sure bro, please send me few questions to start with
@@SivaAcademy different types of facts and dimension tables??
Different data warehouse modelling concept??
Sure bro
Can you share demo how to improve performance while loading data using SQL loader if I have txt file of 20gb
Nice explain
🙏 Thank you
Useful
Thank you
great video..can you please cover direct and conventional load as well
Yes, sure
How can we handle error message if the data file not available in the path..
👌
Welcome
How to exclude extra last line from data file using SQL Loader
SIR can we move data from csv file to csv file using SQL*LOADER ...?
Thanks for the Video, is it possible that sqlldr can be used in PL/SQL block. Thanks in advance.
No, SQLLDR is a operating system command line utility
@@SivaAcademy Thanks for reply
What is Normalisation ,asked in 3round of CTS interview
Hi Neha,
Can you share your mail id so that I can connect with you for interview guidance on plsql
what is difference between in- memory databases and Cloud database??
Its quite interesting question to answer, i will cover in detail as part of some other viedo.
In short, the Cloud database is managed somewere in the newtwork ( or say managed by cloud service providers like AWS etc) and we just access as if it is a normal db. Its just a DB service by cloud.
However in-memory database is sometimes used to save the frequently accessed data in the OS RAM itself, rather than accessing from actual underlying DB to avoid roundtrip to/from DB. you can look into REDIS db , an inmemory DB thats being used these days.
Sir is it mandatory to save files in .txt or .bad or . discarded file or .log file please Explain
Its not mandatory
Is there any way to Restrict Null values while loading datas using SQL loader.....
Yes, you can use when clause in control file to restrict based on condition
@@SivaAcademy
load data
infile 'E:\A\Book1.csv'
Truncate
into table Test
fields terminated by ','
(
ID,
NAME "WHEN NAME IS NOT NULL"
) is this correct bro????
No,
Something similar to the below one
Load data
infile 'E:\A\Book1.csv'
Truncate
into table Test
fields terminated by ','
WHEN NAME ''
(
ID,
NAME
)
what is difference between SQL loader and external table?
@Sri Kanth, Posted the difference between SQL Loader and External table as a seperate video,
ruclips.net/video/Gx31qIvI7MQ/видео.html
Thanks,Siva
What is difference between sqlloader vs datapump?
@Shaik
SQLLOADER - used to transfer data from files into database.( Files should have a standard format), Mainly to used to bulk load data from external files to tables.( Only one way of loading, we cannot export back to file from database using sqlloader utility)
DATAPUMP - Used to export & import the data from/to database. Mainly used to transfer database objects, metadata, and data from one database to another. The dump will be of binary format, hence only the datapump uitliy only can be used to import back the data.
Hi, I've loaded a data but the log, bad and discarded.txt files are not shown in the folder. In command prompt, it shows an error called 'Commit point reached - logical record count 4 and 5'. can you please help me?
Please check whether the table loaded with data
@@SivaAcademy yes the table is loaded but the log,bad,discarded files are not displayed
Please make sure you are checking in the same directory where you are executing the sql loader command from
Thank you, I've got it 👍
Welcome
is it like autosys or controlm mq for db migration
This is just a utility to load data from external files to database, and using scripting, this can be automated, and can be used...
What is data sets and data patterns???
The data format I have used in the example is simple comma separated data, however sql loader supports other formats too.
We need to define the format in the control file, then sql loader will take care of loading the data.
how to check the content of the control file?how to recreate control file in oracle database?
I hope you are asking about the SQL Loader control file only. Its just a txt file only. You can open in any text editor like notepad etc, and you can edit and save the changes.
@@SivaAcademy thanks
🙏👌
Welcome 🙏
HI , How we can convert "Wed May 21 00:00:00 EDT 2008" as date and store using the SQL loader
Column name
street,city,zip,state,beds,baths,sq__ft,type,sale_date,price,latitude,longitude
Record
3526 HIGH ST,SACRAMENTO,95838,CA,2,1,836,Residential,Wed May 21 00:00:00 EDT 2008,59222,38.631913,-121.434879
Sir how can we load fixed record file by using sql ldr the file like
1s10
2b20
How i load these data
@suyog, you can use fixed position method, rather than using delimiter.
sample control file for your reference
load data
infile 'emp.txt'
insert
into table emp
(
field_1 position( 1:5),
field_2 position(6:10),
field_3 position(11:25)
)
@@SivaAcademy sir this position can give acoording to datatype length or something else
Sample data for your reference.
First 5 char is student id
6th postition to 9th postition is Student Name
10th to 12th postition is department name.
10000SIVAECE
10002RAVIEEE
10003KINGCSE
field_1 position( 1:5),
field_2 position(6:9),
field_3 position(10:12)
You can refer to this link for exact syntax and a good example explained here
www.oracle-dba-online.com/sql-loader-case-studies.htm
Thank you sir
Sir make this video slow,not understanding in speed
ok bro, will try to make it slow for upcoming videos, pls stay tuned
what is meaning of above two stms
Please mention the statements here.
hi sir what is mutating table error in oracle how to solve it.
Hi Gowri,
Thanks for asking, Its very interesting question, I will try to post a video soon.
Thanks,
Siva
please upload it
Sure :-)
Difference between failed data and rejected data
Failed Record - Records selected for insertion, but due to some error like data type mismatch or length etc, if sql loader unable to insert into target table, then those are failed records, moved to bad file.
Rejected records - by specifying a condition in sql loader control file ( like"when deptno="10"), we are asking sql loader to load records that matches the condition. Any record thats not matched will be moved to discard file or you can say rejected data
@@SivaAcademy thank you very much
Please help sir how to solve
ora-00900 invalid sql statement
When execute procedure show this errors on Oracle 11g SQL developer mode
Hi Arun, With just error message its bit difficult to say, unless we the see the like of code that throws this error.
so, if possible you can share the code to my mail id, and mention the line number from where this error is being throwed.
C:\DEMO>sqlldr (here which path i write ) control=control_file.ctl.txt
what is difference between sql loader and etl?
SQL Loader is an oracle specific UTILITY for loading the data from files into oracle tables.
ETL is the process( in-fact a series of operation) of extracting , transforming and loading the data.
SQL Loader is can be used as part of ETL process, however we cannot say SQLLOADER is full fledged ETL.
There are many ETL tools, like informatica, Talend, ODI available in the market to specially do ETL operation end to end.