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

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 2

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 3

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): http://www.oracle-developer.net/content/utilities/data_dump.sql
• DBMS_XSLPROCESSOR.CLOB2FILE
• External Tables
• SQL*Plus script (see http://stackoverflow.com/questions/956645/getting-output-in-flat-file-using-oracle-on-unix)
• Oracle SQL Developer or other third-party tool

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.