Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to export some data into a file. The data are selected with a query that will often be made of multiple joins. I want the import operation to be as easy as possible. I was thinking of building an INSERT file with such a method: Oracle PL/SQL: Dump query result into file

But I am wondering if there is some tool already existing to export data based on a query. If this is the case, I might as well be happy with some binary file, as long as importing this file afterward is straight forward. I see there is exp, but it doesn't seem to really be able to handle queries, only basic WHERE clauses.

share|improve this question

1 Answer 1

Have you looked into using the UTL_FILE package ? Using that, you can write to ( and read from ) files really easily and directly from PL/SQL, with great robustness. You need to configure a Directory object in your database, and then you can write to it with ease.

Here's some material on the subject : http://psoug.org/reference/utl_file.html , http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm

share|improve this answer
    
No sure what you mean by 'insert file' but i'll assume you mean a file with insert statements. For exporting data you have multiple options. You don't tell us your oracle version. Let's assume it is 10g or later. So you can use oracle/sql developer or most other client gui tools which can export query result sets as 'insert statements'. Or you can get creative and use your query to create a external table, and using oracle_datapump driver you can create a file that can be read as external table on destination. You can also use sqldeveloper to generate nice csv output if you prefer that. –  Raj May 8 at 19:32
    
It all depends on how frequently you will want to do this and how hands-on/automated you will want to make this. –  Raj May 8 at 19:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.