Export data in CSV file ??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Export data in CSV file ??

  1. #1
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

  3. #3
    Join Date
    May 2001
    Location
    London
    Posts
    149
    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

  4. #4
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73
    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
  •  


Click Here to Expand Forum to Full Width