Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Oracle Export to csv nightly / daily

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
thegiantsmurf
Asked:
thegiantsmurf
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
thegiantsmurfAuthor Commented:
where would this file be placed ?
Should I not add

set lines 4000
set pages 0
set feedback off

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

?
0
Industry Leaders: 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!

 
slightwv (䄆 Netminder) Commented:
>>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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
slightwv (䄆 Netminder) Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now