6

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.

Manoj Govindan
  • 72,339
  • 21
  • 134
  • 141
Aby
  • 79
  • 1
  • 2
  • 6

7 Answers7

6

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:

File Output:

Community
  • 1
  • 1
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
1
  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.

Pravin Satav
  • 702
  • 5
  • 17
  • 36
  • In informix there is a simple function like "load from" for this purpose. What i wanted to ask is whether something like that which is simple exists in oracle or not – Aby Sep 20 '10 at 10:43
  • The closest is an EXTERNAL TABLE – Gary Myers Sep 20 '10 at 23:51
0

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

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

Community
  • 1
  • 1
Jorge T
  • 121
  • 1
  • 2
  • 9
-1

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.

Henry Gao
  • 4,858
  • 1
  • 21
  • 21
-1

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.

Vishnu Gupta
  • 39
  • 2
-1

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

zappee
  • 20,148
  • 14
  • 73
  • 129
-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.

John Townsend
  • 1