-
Export data in CSV file ??
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.
-
-
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
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|