Dumping data into sql file from database

b) - For loading data from a text file you can use SQL Loader, it is from Oracle.

Could you explain what you want in a bit more detail?

The best option for this that I see is using Oracle's exp utility. This utility creates a logical dump to file of all objects and data in a database or schema.

If you are transferring from one Oracle database to another of a different version, you need to use the export utility of the older version to create the export and use the newer version of imp to import it.

An example use would be:

exp scott/tiger@mydb file=scott.dmp

move scott.dmp to the new host, then

imp scott/tiger@mynewdb file=scott.dmp

Also, Quest's Toad has an option for exporting all tables to flat files containing "insert" statements. Along with it's ability to create scripts for structural elements, this could serve your purposes, but not for a process that you must reproduce periodically.

Hi,

a) see link http://www.oracle.com/oramag/code/tips2001/index.html?093001.html

b) As Henka have said, SQL Loader (in version 9i of oracle there exist a very good opportunity with external tables )

best regards,
Marius Nicoras

The simplest way to put the data from any one Oracle table into a ASCII test file is to do the following in SQL*Plus:
spool my_file.txt
select * from [table_name];
spool off;

You may need to add some SQL*Plus commands to get a cleaner output file.  Here is a sample *.SQL file I use to produce a text file from one of our tables:
set linesize 90;
set trimspool on;
set space 0;
set echo off;
set termout off;
set pagesize 0;
spool f:\ora_in\box_line.txt;
select * from conx_box_line;
spool off;

Be aware that you may lose precision with number columns (if you have any decimal values) or lose the time portion of date columns (depending on your NLS_DATE_FORMAT).  You may need a statement like this to make sure that the time portion of date columns is included:
alter session set NLS_DATE_FORMAT = 'YYYYMMDD HH24MISS';

There are many other variations of the date/time format that can be used, like: 'MM/DD/YYYY HH:MI:SSAM', etc.  You may want one that will be best understood by whatever application will be using the text files.

For number columns that have money values or other decimal values you will need "column" commands to format them appropriately.  For example if you have a column named "item_cost" that has values in dollars and cents (but less than $1,000,000) you would add a command like this:
column item_cost format 999999.99

This "column" command must be before the "select..." statement.

There is no automated way in Oracle to put the data from all Oracle tables into one (or many) text files.

To load data into Oracle, use SQL*Loader (a utility that is included with the Oracle database software).  You need to write a control file though for each table that will be loaded.

i wrote this utility to dump all tables in a schema to a comma delimited flat file.  check it out on the tip site


http://www.oracle.com/oramag/code/tips2001/index.html?093001.html


good luck,d
daniels@asix

sujatharathinam,
You have already been asked to clean up your opened questions.  I will give you 3 days to maintain these open questions before I ask administration to suspend your account.  I have provided easy links to your questions below.  You must take care of each opened question by the time I return. I will be posting this in each of your opened questions.  If you need help with deletions, post a commnet in your question if experts have replied indicating your desire to delete, then post a question in community support and list the questions: https://www.experts-exchange.com/Community_Support/

Experts, leave your recommendations for this question's disposition or I may simply delete this question without a refund.

SpideyMod
Community Support Moderator @Experts Exchange

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.

Questions Asked
20
Last 10 Grades Given
A A B B A A B A A A
Question Grading Record
10 Answers Graded / 10 Answers Received

https://www.experts-exchange.com/questions/20282856/Shell-Scripting.html
https://www.experts-exchange.com/questions/20330448/Dumping-data-into-sql-file-from-database.html
https://www.experts-exchange.com/questions/20495382/Symmetric-Algorithm.html
https://www.experts-exchange.com/questions/20488726/Need-a-resolution-for-Reference-counting-error.html
https://www.experts-exchange.com/questions/20326321/Differences-between-JavaBeans-and-Enterprise-JavaBeans.html
https://www.experts-exchange.com/questions/20292542/Can-I-know-the-list-of-printers-in-the-network.html
https://www.experts-exchange.com/questions/20292539/Can-I-know-the-list-of-printers-in-the-network.html
https://www.experts-exchange.com/questions/20270570/How-to-read-the-jdbc-url-from-a-property-file.html
https://www.experts-exchange.com/questions/20150319/From-JTable-Swing.html
https://www.experts-exchange.com/questions/20179283/How-do-I-convey-a-message-to-the-client-browser-that-a-operation-is-in-progress.html