Exporting tab delimited or CSV from Oracle

Here is a simple function which will help to export any tab delimited file or for CSV file from oracle database.
The basic way to export is using any GIU tool like SQL Developer or SQL PLUS by spooling the file from any SQL query that we want to run.

By just passing any SQL query to the function will generate the text or csv file to the directory which has to be created on Oracle server side.
And the user which need to execute this function must have read and write privileges on the directory and also UTL file execute access.

The code was successful only by getting help from Experts-Exchange.com.

Few days earlier I was facing few issues while exporting the SQL queries to tab delimited files with few special requirement. Initially the function was working fine but was really required to make that better by taking some help from the Oracle great experts. And when ever I think of this then I know only one way i.e. check in experts-exchange.com and finally where the below code was ready to roll on any oracle schema.

The expert behind the success of the code is slightwv to know more about the expert please check this link below:

Here is the code for the Function i.e. EXP_DATA:


/***************************************************************************
-- FUNCTION EXP_DATA
-- PURPOSE: This Function exports the data to a tab delimited file.
-- This is a generic function which pass any SQL query which will
-- be executed and the rows along with the header will be exported
-- to the directory where we have already defined on our oracle
-- server. Read and Write access should be there on directory.
-- PARAMETERS:
-- P_QUERY
-- Pass the SQL query for which the export has to be done.
--
-- P_SEPARATOR
-- Pass the separator i.e. "|" for tab delimited file
-- or for CSV file use ",".
--
-- P_DIR
-- Name of the directory where the file is been placed.
-- Note: The grant has to be given for the user to the directory
-- before executing the function
-- P_FILENAME
-- The name of the flat file(a text file)
--
-- AUTHOR:
-- Sloba
-- Version 1.0
**************************************************************************/

CREATE OR REPLACE
 FUNCTION exp_data(
 p_query IN VARCHAR2,
 p_separator IN VARCHAR2 DEFAULT ',',
 p_dir IN VARCHAR2 ,
 p_filename IN VARCHAR2 )
 RETURN NUMBER
 IS
 l_output utl_file.file_type;
 l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;
 l_columnValue VARCHAR2(2000);
 l_status INTEGER;
 l_colCnt NUMBER DEFAULT 0;
 l_separator VARCHAR2(10) DEFAULT '';
 l_cnt NUMBER DEFAULT 0;
 l_descTbl dbms_sql.desc_tab;
 BEGIN
 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 FOR i IN 1 .. 255
 LOOP
 BEGIN
 dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
 l_colCnt := i;
 utl_file.put( l_output, l_separator || l_descTbl(i).col_name || '|' );
 EXCEPTION
 WHEN OTHERS THEN
 IF ( SQLCODE = -1007 ) THEN
 EXIT;
 ELSE
 raise;
 END IF;
 END;
 END LOOP;
 utl_file.new_line( l_output );
 dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
 l_status := dbms_sql.execute(l_theCursor);
 LOOP
 EXIT
 WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
 l_separator := '';
 FOR i IN 1 .. l_colCnt
 LOOP
 dbms_sql.column_value( l_theCursor, i, l_columnValue );
 utl_file.put( l_output, l_separator || l_columnValue);
 l_separator := p_separator;
 END LOOP;
 utl_file.new_line( l_output );
 l_cnt := l_cnt+1;
 END LOOP;
 dbms_sql.close_cursor(l_theCursor);
 utl_file.fclose( l_output );
 RETURN l_cnt;
 END exp_data;

Here is the PLSQL block to run the above function:


DECLARE
 l_rows NUMBER;
BEGIN
 L_ROWS := exp_data( 'select * from <<YOUR TABLE>>', '|', '<<ORACLE DIRECTORY>>', '<<FILE NAME>>..txt' );
 dbms_output.put_line( TO_CHAR(l_rows) || ' rows extracted to ascii file' );
END;

I also should not forget “Gyrene” who corrected me on my current post with recognition to the expert behind this post success.

Thanks a ton to Gyrene and slightwv .

Advertisements

11 thoughts on “Exporting tab delimited or CSV from Oracle

    • Thanks Gyrene for your comment..
      If you go through the question that I posted on Experts-Exchange..the function which I have written was working but needs to be modified as what I was thinking to achieve .. and yes slightwv really helped me to get that fixed.

      I don’t think it’s bad if I share something thing that helps others from my blog or anyone’s other.

      • slobaexpert –
        I have been to that question on EE and several others where you have asked for help.
        When someone else has helped you work through a problem, you need to recognize their assistance and not post code as though you alone came up with it.

      • Thanks for your reply again and pointing out on recognition to other experts too.

        I will definitely keep this in my mind and in future too with regards to my post.
        Let me edit the post and make it as per your inputs.

        I kept my blog in order to help others as well as I should have kept the other experts information too.

        Editing my post right now..

        Will love to see your comments on the same and on others too..

        Thanks a lot again for correcting me(already mentioned on my blog as if anyone find something is not right then please feel free to comment on that ).

        Thanks & Regards,
        Sloba

  1. Hi sloba ,
    I hereby, convey my heartfelt thanks to you for the valuable help to others 🙂
    i like your comment what ever u said
    “I kept my blog in order to help others as well as I should have kept the other experts information too”
    I totally agree and admire your courage to share with everyone your articles through “SLOBA BLOG”
    I am sure many people feel exactly the same but cannot even come forward and share it.(like Old Gyrene)
    as a followers of blog I’ve always been extremely happy with your professionalism.”
    With you all the best..!!
    Thanks & Regards,
    Aditya.C

    • Thanks a lot Aditya…But I also feel that initially when I didn’t kept name of other experts are not correct from my end. Actually I never thought about it. But when Old Gyrene pointed out the same then I realized what mistake I did.. I would encourage everyone to share but also keeping in mind that we should keep the information about the original expert behind the solution that we achieved.

      Thanks,
      Sloba(Swadhin )

  2. […] But normally when we export to CSV or any flat file format we use UTL_FILE utility package from Oracle, Now the bigger issues appear, like how to bring the question name (which has more than 4000 characters to the column name). In my earlier blog if you see I am using similar way to export the column and data by passing the SQL query to the EXP_DATAfunction:  https://slobaexpert.wordpress.com/2012/08/21/exporting-tab-delimited-or-csv-from-oracle/ […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s