Re: newbie: simple sql query to create .csv file for excel import

Volker Peter wrote:

> Hello you all out there,
>
> i'm new with Oracle SQL programming and i have a simple SQL Question:
>
> I must run a select statement on my Adress-Table and write down this
> data to a .csv file for importing this into winword or excel for working
> with that data.
> I read some documentation about Oracle Export but this seems to me that
> this won't work as i expected it to work. If you have any comments or
> ideas regarding this problem, please let me know - also if there is a
> internet website or a usergroup website where some example coding is
> shown to me so that i can learn from that sql code.
>
> If you have any ideas please let me know.
>
> Thank you !
>
> Volker

As far as I know, you have two different solutions :

1. Use simple SQL statement and SQL*Plus using the spool command of SQL*Plus. Example : Table_1 contains the columns a and b.

spool results.csv
select a||','||b from table_1;

2) Use PL/SQL Programming with the package UTL_File to write anything you want in a file.

Example :

declare

• Declaration of the necessary type for file access. file UTL_FILE.FILE_TYPE; file_name VARCHAR(20) := 'results.csv'; msg VARCHAR(100);

cursor query is
select a,b from table_1;

begin

• Opening of the file file := UTL_FILE.FOPEN('c:\', file_name,'w');
• Data retrieving via the cursor For cur in query loop
• Concatenate the columns before writing it in the file msg := cur.a || ',' || cur.b;
• Writing of the line in the file UTL_FILE.PUTF(file,'%s\n',msg); end loop;
• Closing of the file UTL_FILE.FCLOSE_ALL;

Hope it helps.

Sebastien. Received on Thu Aug 12 1999 - 06:49:51 CDT