Solved

Oracle Export to csv nightly / daily

Posted on 2014-10-14
9
398 Views
Last Modified: 2014-10-21
We are currently using oracle 9i for our ERP system.
Is there a way I can run a script daily which would export the select query to an csv file.

select
ip.part_no,
ip.description,
ip.contract,co.name,
ip.unit_meas,
ip.dim_quality,
ip.estimated_material_cost,
pc.total_accum_cost as Invent_Value,
ipos.qty_onhand,
ip.part_product_code as category

from ifsapp.inventory_part ip, ifsapp.company co, ifsapp.part_cost pc, ifsapp.inventory_part_in_stock ipos

where co.company = ip.contract and
ip.part_no = pc.part_no and
pc.cost_set = '1' and
ip.Part_status != 'O' and
ipos.part_no = ip.part_no and
ip.part_product_code < '500' and
ipos.location_type = 'Picking'

order by ip.part_no

This basically dumps all our inventory parts out of our system c/w description, dim, prices and stock values.

Massive thanks in advance.
0
Comment
Question by:thegiantsmurf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40380178
Can the resulting concatenation of the columns exceed 4000 character?

If not then try the following using sqlplus:

set lines 4000
set pages 0
set feedback off

spool myfile.csv
select
 ip.part_no || ',' ||
 ip.description || ',' ||
 ip.contract,co.name || ',' ||  
 ip.unit_meas  || ',' ||
 ip.dim_quality || ',' ||
 ip.estimated_material_cost || ',' ||
 pc.total_accum_cost || ',' ||
 ipos.qty_onhand  || ',' ||
 ip.part_product_code
from ...

spool off
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40380180
This should be your query:

select 
ip.part_no || ',' || 
ip.description || ',' || 
ip.contract,co.name || ',' ||
ip.unit_meas || ',' ||
ip.dim_quality || ',' ||
ip.estimated_material_cost || ',' ||
pc.total_accum_cost as Invent_Value || ',' ||
ipos.qty_onhand || ',' ||
ip.part_product_code as category
from ifsapp.inventory_part ip, ifsapp.company co, ifsapp.part_cost pc, ifsapp.inventory_part_in_stock ipos
where co.company = ip.contract and
ip.part_no = pc.part_no and
pc.cost_set = '1' and
ip.Part_status != 'O' and
ipos.part_no = ip.part_no and 
ip.part_product_code < '500' and
ipos.location_type = 'Picking'
order by ip.part_no

Open in new window


If using SQL*Plus, just use the spool command to get it to a file.

If your data contains a comma character, then you would need to add quotes.  If not, this should work.
0
 

Author Comment

by:thegiantsmurf
ID: 40380192
where would this file be placed ?
Should I not add

set lines 4000
set pages 0
set feedback off

spool c:\myfile.csv
.......

?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40380200
>>where would this file be placed ?

Create a file called something like myfile.sql

Place all those commands and your select in there.

then:
sqlplus username@database @myfile.sql

You will be prompted for the password.

Some will have you add the password to the command line.  This is a bad habit to start since it is a security risk.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 total points
ID: 40380602
Also add this formatting command if you use SQL*Plus to run a query like this and spool the output:
set trimspool on

Your other option would be to make your query be a cursor in a PL\SQL stored procedure.  Then you can call procedures in the supplied PL\SQL package: UTL_FILE, to open the file, write a line for each row returned by the cursor, then close the file.  You can schedule this procedure as job to run automatically each night using DBMS_JOB.  This will create the output file on the Oracle database server (or on a network drive that is accessible by your database server).

Here is an example of a simple procedure that uses UTL_FILE to read a (possibly large) file and write just a few lines of it to a small file.  For your situtation, you would have to change the "while ... loop" to a cursor loop, and you wouldn't need the "in_file" lines.

create or replace procedure sample (path_nm in varchar2, file_nm in varchar2,
 lines_to_read in number default 3, chars_per_record in number default 999,
 output_path in varchar2 default 'C:\temp') as
-- This procedure opens a text file in any directory, reads three lines from it
--  and writes them to C:\temp\temp.txt.
-- This is intended for large text files, to quickly get a small section of the file that can
--  be opened and browsed more easily in a text editor.
  in_file   utl_file.file_type;
  out_file  utl_file.file_type;
  text_str  varchar2(4000);
  lines     pls_integer;
  err_text  varchar2(200);
begin
  lines := 0;
  out_file := utl_file.fopen(output_path,'temp.txt','W');
  in_file := utl_file.fopen(path_nm,file_nm,'R',chars_per_record);
  while lines < lines_to_read loop
    begin
      utl_file.get_line(in_file, text_str);
      utl_file.put_line(out_file,nvl(text_str,to_char(lines)));
      lines := lines +1;
    exception
      when others then
        err_text := substr(sqlerrm,1,200);
        lines := lines +1;
        utl_file.put_line(out_file,err_text);
    end;
  end loop;
  utl_file.fclose(in_file);
  utl_file.fclose(out_file);
end;
/
0
 
LVL 23

Expert Comment

by:David
ID: 40381309
For my part, I'd have you drop all the concatenation with:
set colsep '","'     -- separate columns with a comma

A good discussion with variations at http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus .

Bonus points, look into the help available with Oracle (free) SQL*Developer, if you can run the query interactively.  The tool comes with smart hints that will format the result set for you (e.g., /* +CSV */ ).

Regards,
dvz
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40381311
GUI's are overrated.


colsep is the WORST way to do this.

It tends to pad and wrap columns.  More votes on StackOverflow doesn't always mean the 'best' answer...  Scroll down farther in that link and look at string concatenation...

Here's the output of what I';m talking about:
SQL> drop table tab1 purge;

Table dropped.

SQL> create table tab1(col1 varchar2(10), col2 varchar2(10));

Table created.

SQL> insert into tab1 values('Hello', 'World');

1 row created.

SQL> insert into tab1 values('a', 'b');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> set colsep ","
SQL> set pages 0
SQL> set lines 4000
SQL>
SQL> select * from tab1;
Hello     ,World
a         ,b

SQL>

Open in new window

0
 
LVL 23

Expert Comment

by:David
ID: 40381341
Ah, but in that thread I also read of eliminating the white space -- quoting, and not yet tested by me:

sed 's/^/"/;s/$/"/;s/\s *"/"/g;s/"\s */"/g' $outfile > $outfile.csv
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40381353
First:  Sure if you scroll down to a 3 upvote and happen to catch that specific command.

Second:  sed implies Unix or some flavor of it.  What if this is Windows?

Third:  Why involve an extra step it isn't necessary.

Fourth you get into a column that contains "Hello       ,         World" where the space needs to be preserved.  Does that sed command account for that?  I don't have *nix access to test and you even claim to have not tested it.

String concatenation could easily account for that by adding double quotes to the processing and do it all in one step.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question