A simple procedure for create a flat csv file from table


Normal
14
false
false
false
IT
X-NONE
X-NONE


/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Tabella normale”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}
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.

Normal
14
false
false
false
IT
X-NONE
X-NONE


/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Tabella normale”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}

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

 

 

 

Potrebbero interessarti anche...