Quantcast

Export Oracle table to Flat file

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Export Oracle table to Flat file

Carolina Barcenas
I am new Squirrel User.  How can I export an Oracle table (1 Million
records) to a flat file?  I am having problems with the size limit. When I
try to bring up all the table it can't handle. I could always break the
table in pieces but I am wondering if there is an easier way.  I appreciate
any suggestions.
CB

_________________________________________________________________
Can’t afford to quit your job? – Earn your AS, BS, or MS degree online in 1
year.
http://www.classesusa.com/clickcount.cfm?id=866145&goto=http%3A%2F%2Fwww.classesusa.com%2Ffeaturedschools%2Fonlinedegreesmp%2Fform-dyn1.html%3Fsplovr%3D866143



-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Export Oracle table to Flat file

Matt Dahlman
CB,

I think this is a case where SQuirreL won't be the right tool for the job.

I suspect you'll either need a hand-coding solution or a different tool.
Hand-coding could be as simple as using SQL*Plus, turning spooling on, and
running select * from your table. Of course you'll need to tweak
SQL*Plus's params a bit... but that's the basic idea. It's too bad that
Oracle doesn't offer tools like Sybase's bcp or Teradata's FastExport to
do what you want.

A different tool like Oracle Data Integrator (ODI) might be better. It has
built-in tools like OdiSqlUnload designed to unload data from a table to a
file. It may be using a canon to swat a fly--there may be more setup and
config time needed than your relatively modest requirement merits. But
it's an option (it's free to download from oracle.com).

Then again, maybe one of the SQuirreL gurus will know of a clever way to
easily redirect the output of a query to a flat file without requiring the
whole dataset to be held in memory. It doesn't seem like it ought to be
too difficult to achieve. Good luck.

-Matt


-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of
Carolina Barcenas
Sent: Sunday, 08 April, 2007 6:03
To: [hidden email]
Subject: [Squirrel-sql-users] Export Oracle table to Flat file

I am new Squirrel User.  How can I export an Oracle table (1 Million
records) to a flat file?  I am having problems with the size limit. When I
try to bring up all the table it can't handle. I could always break the
table in pieces but I am wondering if there is an easier way.  I
appreciate any suggestions.
CB

_________________________________________________________________
Cant afford to quit your job?  Earn your AS, BS, or MS degree online in
1 year.
http://www.classesusa.com/clickcount.cfm?id=866145&goto=http%3A%2F%2Fwww.c
lassesusa.com%2Ffeaturedschools%2Fonlinedegreesmp%2Fform-dyn1.html%3Fsplov
r%3D866143




-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Export Oracle table to Flat file

Robert Manning
On 4/8/07, Matthew Dahlman <[hidden email]> wrote:
> CB,
>
> I think this is a case where SQuirreL won't be the right tool for the job.
>
I believe Matt is correct in this regard.  The current CSV/Excel
export (which is fairly new and very handy) is tied to the having the
data come from the UI.  It will take a bit of work to remove that
dependency, but it's very possible.  I would imagine you would select
a statement, and right-click to access the Export CVS/Excel menu like
you  can today with either the object tree or the SQL results panel.
For now, I would probably just use SQL-Plus, turn on spooling (spool
<filename>) and issue your query.  You may find that turning off
feedback, termout and others gets you just the data.  You may need to
deal with column formats but it's probably still faster.

Rob

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Export Oracle table to Flat file

Jeremy Winston
In reply to this post by Carolina Barcenas
Squirrel is too slow for retrieving massive data amounts. sqlplus will be slow too
You may want to write some Pro*C scripts to extract data from Oracle into flat file.
Or if to get really high-end performance for export into flat files ( 200,000 - 300,000 rows per second ) then look into commercial tools.  For instance Fastreader (http://www.wisdomforce.com) is good way to go

Carolina Barcenas wrote
I am new Squirrel User.  How can I export an Oracle table (1 Million
records) to a flat file?  I am having problems with the size limit. When I
try to bring up all the table it can't handle. I could always break the
table in pieces but I am wondering if there is an easier way.  I appreciate
any suggestions.
CB

_________________________________________________________________
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Export Oracle table to Flat file

Nathan Aaron
RE: [Squirrel-sql-users] Export Oracle table to Flat file

You can do a simple bash script that loops through each record returned by sqlplus and echos it to a text file.  Speed will greatly depend an the number and type of columns you have.  If you are familiar with bash and would like an example let me know and I will email it.

Nathan


-----Original Message-----
From: [hidden email] on behalf of Jeremy Winston
Sent: Mon 4/16/2007 3:42 PM
To: [hidden email]
Subject: Re: [Squirrel-sql-users] Export Oracle table to Flat file


Squirrel is too slow for retrieving massive data amounts. sqlplus will be
slow too
You may want to write some Pro*C scripts to extract data from Oracle into
flat file.
Or if to get really high-end performance for export into flat files (
200,000 - 300,000 rows per second ) then look into commercial tools.  For
instance Fastreader (http://www.wisdomforce.com) is good way to go


Carolina Barcenas wrote:
>
> I am new Squirrel User.  How can I export an Oracle table (1 Million
> records) to a flat file?  I am having problems with the size limit. When I
> try to bring up all the table it can't handle. I could always break the
> table in pieces but I am wondering if there is an easier way.  I
> appreciate
> any suggestions.
> CB
>
> _________________________________________________________________
>
>

--
View this message in context: http://www.nabble.com/Export-Oracle-table-to-Flat-file-tf3543462.html#a10022668
Sent from the squirrel-sql-users mailing list archive at Nabble.com.


-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users




-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Loading...