A simple procedure for create a flat csv file from table

Sometimes can be useful to produce a CSV file semicolon separated from a table. Here a simple dynamic procedure that works without write sql scripts pl sql code. Just put in some parameters and execute it from everywhere. With little customizations you can add a parameter for separator, add “” for strings, columns to avoid etc.

CREATEORREPLACEprocedureTABLETOCSV(

P_OWNERinvarchar2,–owner of the table

P_TABLENAMEinvarchar2, –tablename

P_PATHinvarchar2default‘/’, — server shared path where file can be putted in

P_FILENAMEinvarchar2,–exported filename

P_HEADERinvarchar2default‘Y’,–flag Y/N for header presence

P_DATEFORMATinvarchar2default‘dd/mm/yyyy’ –-dateformat in csv

)as

    sql_scriptvarchar2(2000);

    initnumber:=0;

    v_ownervarchar2(30):=upper(P_OWNER);

    v_table_namevarchar2(100):=upper(P_TABLENAME);

    flg_headervarchar2(1):=P_HEADER;

    v_headervarchar2(1000);

    dt_fvarchar2(10):=P_DATEFORMAT;

    fieldvarchar2(50);

    fnamevarchar2(100):=P_FILENAME;

    fpathvarchar2(100):=P_PATH;

      TYPEItemRecISRECORD(       

        fieldvarchar2(2000));

    TYPEItemSetISTABLEOFItemRec;

    fieldsItemSet;   

    ftypeutl_file.file_type;

begin

    sql_script:=‘select ‘;

    forrecin(select * fromall_tab_columns

                whereOWNER=v_ownerand

                TABLE_NAME=v_table_name

                orderbycolumn_id)

        loop

            field:=”;

            ifflg_header=‘Y’then

                v_header:=v_header||rec.column_name||‘;’;

            endif;

            ifinit=1then

                sql_script:=sql_script||‘||”;”||’;

            endif;

            ifrec.data_type=‘DATE’then

                field:=‘to_char(‘||rec.column_name||‘, ”’||dt_f||”’)’;

              else

                field:=rec.column_name;

            endif;

            sql_script:=sql_script||field;

            init:=1;

        endloop;

        sql_script:=sql_script||‘ recline from ‘||v_table_name||‘ order by 1 ‘;

        –DBMS_OUTPUT.PUT_LINE(sql_script); –test 1

        executeimmediate(sql_script)bulkcollectintofields;

        –DBMS_OUTPUT.PUT_LINE(fields(1).field); –test 2

        ftype:=utl_file.fopen(fpath,fname,‘W’);

        ifflg_header=‘Y’then