I am trying to load text file into an Oracle table by writing the code inside a stored procedure using SQL*Plus. Is there any way to do that with out using SQL*Loader?
You can do this a few ways. If you are using Oracle 9i, then you can "mount" the file so that it appears as a table in the database. Oracle 9i calls this new feature External Tables. It uses SQL*Loader as the engine for bringing the data from outside the database to inside the database. The benefit of this is that one the external table has been mounted to the database, you can use simple SQL statements to pull the data from that file.
If you are not using 9i, or do not wish to use this method, then you can use the UTL_FILE supplied package. This package will let you open a file for input or output. So open the file, read the data, and process it, all from within your PL/SQL block!
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.