Extract data from table in oracle using single command

Question

I want to extract the data from 400 tables. how to extract the data with a single command using sqlplus.

example table name

EMPLOYEE
STUDENT
.....
SCHOOL_NAME

example table structure

EMPLOYEE ( NAME VARCHAR(255), AGE VARCHAR(255))
STUDENT (NAME VARCHAR(255), AGE VARCHAR(255), SEX VARCHAR(255))
SCHOOL_NAME (SCHOOL VARCHAR(255), LOCATION VARCHAR(255))

if i'm using a sqlplus with a delimiter format, it would be so many command that i've to create, if there is another simple command, please share..

Note : every table has a different column name

Can you please post the scructure of your tables, the fields you need to extract, conditions to apply? — Aleksej, Feb 18 '16 at 09:51
Are all the table names the same but with _ +1? (name_1 , name_2...) or they don't have to be continuously — sagi, Feb 18 '16 at 09:52
extract - what does it mean. Save data to file or show on screen? — Arkadiusz Łukasiewicz, Feb 18 '16 at 09:54
Can you please give an example with only 3 or 4 tables, posting their structure and the result you need? — Aleksej, Feb 18 '16 at 09:57
Also, is this all your db tables? how can we know which tables you want to select from — sagi, Feb 18 '16 at 09:57
updated, i try to export the data for all tables on a CSV format — Amir Rachman, Feb 18 '16 at 10:02
You want to put all data from all your tables in a file (or multiple files?), in CSV format, from SQL\*Plus, with a single command? What will you do with the data once you have the file(s)? You could generate the commands to do this (one per table), but it would be easier to use existing tools like SQL Developer has. You may really be looking to `expdp` though. — Alex Poole, Feb 18 '16 at 10:07

score 2 · Accepted Answer · answered Feb 18 '16 at 10:11

2

Spool the select statements to a file and then invoke that script. Something like this

spool c:\temp\script.sql
select 'select * from ' || table_name || ';'
  from user_tables;

@@c:\temp\script.sql

Of course you have to change the settings for header, linesize, etc

answered Feb 18 '16 at 10:11

Shriram M.

383
1
7

It probably isn't that simple, depending on how the data will be used. String values may need to be enclosed in double-quotes in case they contain the delimiter; single-quotes in string values *may* have to be escaped; whitespace between columns might not be wanted; etc. – Alex Poole Feb 18 '16 at 10:36
Hi @Shriram if using a single spool, if there is a 400 tables, i've to create a script for 400 tables.. – Amir Rachman Feb 19 '16 at 03:32
is that a question? I can't understand what you're trying to say. You can create single script or multiple scripts, You can have multiple select statements within the same sql script also. Sqlplus willl run one after the other. It depends on what you want as output and whether you want it in a single file or multiple files. It is a way scripting language, so you can do a lot of things. – Shriram M. Feb 19 '16 at 14:07

Extract data from table in oracle using single command

1 Answers1