Networks

Ask something
Discussion Locked

exporting from oracle to flat file...

0 helpful
8 replies

by s_joe 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am fairly new to the unix enviroment and the oracle file system, so very basic commands will help.

I would like to export a flat file from the oracle 8i running on a sun ultra 10 box. I then would like to send the file to a NT server running sql server 7. The oracle db is connected to 30 users so I don't want to do anything stupid.

Thank you in advance.

8 replies

by s_joe

if anyone could give me at least a good starting point to perform my requests it would help... (what commands do i enter a the unix server, do i need a special program to export the file to a NT server)

by darren.marynchak

Getting data out of Oracle is pretty easy. The hard part would be getting it into SS (and vice versa SS -> Oracle). The issue is that they are incompatible databases. You could use ODBC as your 'go between' file format. In other words, if you can point SS to a ODBC source (which in turn would point to Oracle), that would be a way to get the data. Also, you could use MS Access as the 'go between' database. You can hook Access to Oracle and to SS. I know that's not a great answer, but thatwould work.

In Oracle there are really only 2 ways to get data out of the DB. One is using Oracle Export (UNIX command is 'exp'). Do a 'exp help=y'. That will show you the syntax of the export command. The problem is that the exported file will be in an Oracle proprietary format (only good for turning around and doing an Oracle Import 'imp').
The other way to get data out is with SQL. If you launch SQL*Plus (sqlplus), you can write a SQL select statement that will select the data youwant from the database. Once you have that selected data looking the way you want (perhaps seperated by commas), you can use the SQL*Plus 'spool' command to get that output directed to a file. So, in sqlplus you issue following:

spool data_from_ora.txt
select col1||','||col2||','||col3
from some_table
/
spool off

This will store the results in a file (in your current working directory) called 'data_from_ora.txt'. Depending on how familiar you are with SQL, the 'col1||','||col2' mayor may not make sense. It's really just saying to take the contents of column col1 and concatenate that with a comma (,) then concatenate that with the contents of column col2, etc....

Hope that helps... Regarding UNIX, there's a great book called 'UNIX in a Nutshell'.

by darren.marynchak

Sounds like you need to look at Oracle's data dictionary. There are a number of data dictionary views that can be used to 'see what is out there'. Connect to the user that owns the tables (this is commonly referred to as the 'schema'). So, if theuser that owns that tables you are looking for is called 'JOE', then you need to connect to the database as 'JOE'. Once connected, do a 'describe' on 'user_tables'. Or you could issue the following:

select table_name from user_tables order by 1;

This will give you a list of tables that are owned by 'JOE' in sorted order.

You can also, write SQL that generates SQL. So, if you said I want to do a 'select *' from all tables that are owned by 'JOE'. The easiest way to do that would beto spool the output from:

select 'select * from '||table_name||';' from user_tables order by 1;

That will generate you a list of select statements (one for each table) in sorted order. Depending on the number of tables and rows in those tables, you probably don't want to execute that complete spooled file. It will essentially select every row (every column) of every table owned by 'JOE'. Anyway, the above examples show how you can use the Oracle data dictionary and how you can write SQL that generates SQL.

If you check TR, another user mentioned PL/SQL. That post is correct that you can use UTL_FILE. However, if you are new to Oracle, nail down SQL using SQL*Plus. Once you get you legs from a SQL perspective, then take a look at writing some PL/SQL.

Hope that helps...

by ambily

Just in addition to the previous reply, you can also use the UTL_FILE built in package in a PL/SQL stored procedure to write the contents of a table into a flat file in Oracle.

Hope that helps.

by s_joe

thanks for the help, but i didn't create the database i am working with. i need to know the names of the tables and was wondering if a command like:
select *
from some_table;

where some_table is a universal table name that holds table names.
is there a file within oracle somewhere that i can look inside that holds the table names?
thanks again for the responses.

This discussion is closed to new replies.