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.
thegiantsmurfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.