Solved

Oracle Export to csv nightly / daily

Posted on 2014-10-14
9
365 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
9 Comments
 
LVL 76

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 34

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
 
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now