I have got 2 questions. Is there any way to load data to a table in Oracle from a .txt file other than using SQL loader? How to unload data from the table to a text file? Someone please help me. Thanks in advance.

oracle-database

Aby

Reputation: 79

Loading data from a text file to a table in oracle

I have got 2 questions.

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

Someone please help me.

Thanks in advance.

Upvotes: 6

Answers (7)

John Townsend

Reputation: 1

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.

Upvotes: -1

zappee

Reputation: 22746

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

Upvotes: -1

Jorge T

Reputation: 141

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

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

Upvotes: 0

Jon Heller

Reputation: 36922

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

Upvotes: 6

Vishnu Gupta

Reputation: 39

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.

Upvotes: -1