Loading data from a text file to a table in oracle

Question

I have got 2 questions.

1. Is there any way to load data to a table in Oracle from a .txt file other than using SQL loader?
2. How to unload data from the table to a text file?

Someone please help me.

Thanks in advance.

Answer 1

There are a bunch of options. It depends on the context - your Oracle version, data format, is this on the client or the server, etc. Here's a quick list, there are probably many other methods:

File Input:

• UTL_FILE
• SQL*Loader
• DBMS_XSLPROCESSOR.READ2CLOB
• External Tables
• xmltype.createXML with BFILE
• Oracle SQL Developer or other third-party tool

File Output:

• UTL_FILE
• Data Dump Utility (wrapper around UTL_FILE)
• DBMS_XSLPROCESSOR.CLOB2FILE
• External Tables
• SQL*Plus script
• Oracle SQL Developer or other third-party tool

Answer 2

1. Load data from txt file other than sql loader - External table,UTL_FILE package
2. Unload data - UTL_FILE package

Use google with these key words you will find lot of information.

Answer 3

Oracle has a free tool SQL Developer (http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html) you can use to do these.

Answer 4

For unloading the data you can just spool the file. In SQLPLUS create a spool file and then run a select query that reads from a table, format the data and return a string. The data will be written to the file.

Other solutions have already been mentioned. The tools including TOAD have features to create files.

Answer 5

Curreent Solution: Load data with SQL* Loader (2016 using Windows 10 and Oracle client 12c)

https://stackoverflow.com/a/39649776/3377472

Answer 6

This is an old topic but I think the following info can help a lot:

|-----------------|----------------|----------------|
| LOAD METHOD     | DATA ON CLIENT | DATA ON SERVER |
|-----------------|----------------|----------------|
| SQL*Plus        | Yes            | No             |
| SQL*Loader      | Yes            | No             |
| External Tables | No             | Yes            |
| BFILES          | No             | Yes            |
|-----------------|----------------|----------------|

Source: https://blogs.oracle.com/searchtech/loading-documents-and-other-file-data-into-the-oracle-database

Answer 7

Haven't used it for a few years but PL/SQL Developer has a text importer tool. I'm fairly sure that I have it documented somewhere but you need PL/SQL Developer as a starting point. If you Google "sql developer text importer" you'll get all the info you need.