Export data in CSV file ??

[GKramer]

Hi,

I face the following challenge; how can I export the content of a table (all data) into a CSV file automatically (using a stored procedure on a 9ir2 environment)?

Regards, Gkramer.

[DaPi]

Lots of options here:
http://asktom.oracle.com/~tkyte/flat/index.html

[pareshg]

Hi Grammer,

I think u want to export data from Table to CSV file.
Example:

Table Dividend:
CREATE TABLE DIVIDEND (
SEDOL NUMBER,
STOCK_CRCY VARCHAR2 (4),
TRANSACTIONDATE DATE,
VALUEDATE DATE,
UNITHOLDING NUMBER,
DIVIDEND_CRCY VARCHAR2 (4),
DIVIDENDNET VARCHAR2 (20),
BOOKNET VARCHAR2 (20),
BROKERACCOUNTNO VARCHAR2 (10),
SETTLEMENTNET VARCHAR2 (20),
CLIENT VARCHAR2 (12),
PORTFOLIO VARCHAR2 (7),
CUSIP NUMBER,
SETTLEMENT_ACCOUNT NUMBER,
SETTLEMENT_CRCY VARCHAR2 (4),
BOOK_CRCY VARCHAR2 (4) ) ;

Procedure to load DIVIDEND tables data into csv file:
/**************************************************************/

PROCEDURE prc_dividend_csv IS

cv_dividend_csv dividend%rowtype;

CURSOR c_dividend_csv IS
SELECT SEDOL
,STOCK_CRCY
,TRANSACTIONDATE
,VALUEDATE
,UNITHOLDING
,DIVIDEND_CRCY
,DIVIDENDNET
,BOOKNET
,BROKERACCOUNTNO
,SETTLEMENTNET
,CLIENT
,PORTFOLIO
,CUSIP
,SETTLEMENT_ACCOUNT
,SETTLEMENT_CRCY
,BOOK_CRCY
FROM dividend;

ln_Rec_Count number(6) := 0;
lv_file_ptr UTL_FILE.FILE_TYPE;
cv_header varchar2(200);

CURSOR c_header is select PARAM_VALUE from gen_param where PARAM_ID=2;

BEGIN

open c_header;
fetch c_header into cv_header;
close c_header;

lv_File_Ptr := UTL_FILE.FOPEN('c:\oracle', 'dividend.csv', 'W', 10000);

UTL_FILE.PUT(lv_file_ptr,cv_header);
UTL_FILE.NEW_LINE(lv_file_ptr);

FOR cv_dividend_csv IN c_dividend_csv LOOP

UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.SEDOL, '')||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.STOCK_CRCY, '')||',');
UTL_FILE.PUT(lv_file_ptr, 'DIVI,');
UTL_FILE.PUT(lv_file_ptr, cv_dividend_csv.TRANSACTIONDATE||',');
UTL_FILE.PUT(lv_file_ptr, cv_dividend_csv.VALUEDATE||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.UNITHOLDING, '')||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.DIVIDEND_CRCY, '')||',');
UTL_FILE.PUT(lv_file_ptr, '"'||NVL(cv_dividend_csv.DIVIDENDNET, '0')||'"'||',');
UTL_FILE.PUT(lv_file_ptr, '"'||NVL(cv_dividend_csv.BOOKNET, '0')||'"'||',');
UTL_FILE.PUT(lv_file_ptr, '"'||NVL(cv_dividend_csv.SETTLEMENTNET, '0')||'"'||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.CLIENT, '')||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.PORTFOLIO, '')||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.CUSIP, '')||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.SETTLEMENT_ACCOUNT, '')||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.SETTLEMENT_CRCY, '')||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_dividend_csv.BOOK_CRCY, '')||',');

ln_Rec_Count := ln_Rec_Count + 1;
UTL_FILE.NEW_LINE(lv_file_ptr);

END LOOP;

UTL_FILE.FCLOSE(lv_file_ptr);

end prc_dividend_csv;

/**************************************************************/

I think this should help u.

Paresh

[GKramer]

dapi

A lot off info on the url but could not exacly find where I was looking for, but thanx anyway.

Pareshg

execlent and simple sollution ( made some adjustments though ), thank you very much.

To all who want to use it some extra tips;
adjust the parameter UTL_FILE_DIR ( running and SPFile )
More info Metatlink;
( http://metalink.oracle.com/metalink/...NOT&id=44307.1 )

Regards GKramer

******************************************************
as
--
CURSOR c_one IS
SELECT module_code,session_code,item_code,answer_code, answer_time
from item_answer
order by 1,2,3;
cv_c_one c_one%rowtype;

ln_Rec_Count number(6) := 0;
lv_file_ptr UTL_FILE.FILE_TYPE;
cv_header varchar2(200);


BEGIN

lv_File_Ptr := UTL_FILE.FOPEN('c:\aap\', 'test.txt', 'W', 10000);

UTL_FILE.PUT(lv_file_ptr,cv_header);
UTL_FILE.NEW_LINE(lv_file_ptr);

FOR cv_c_one IN c_one LOOP

UTL_FILE.PUT(lv_file_ptr, NVL(cv_c_one.module_code,0)||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_c_one.session_code, 0)||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_c_one.item_code, 0)||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_c_one.answer_code, 0)||',');
UTL_FILE.PUT(lv_file_ptr, NVL(cv_c_one.answer_time, 0)||',');

ln_Rec_Count := ln_Rec_Count + 1;
UTL_FILE.NEW_LINE(lv_file_ptr);

END LOOP;

UTL_FILE.FCLOSE(lv_file_ptr);

end;