declare
   v_blob_data    blob;
   v_clob_data    clob;
   v_blob_len     pls_integer;
   v_clob_len     pls_integer;
   v_position     pls_integer;
   v_line_end_pos pls_integer;
   v_chunk_len    pls_integer;
   v_loop_count   pls_integer:=0;
   
   v_line         varchar2(32767) := null;
   v_data_array   wwv_flow_global.vc_arr2;
   v_sr_no        number := 1;
   v_filename     varchar2(4000);
   v_file_count   pls_integer := 0;

   v_mime_type    varchar2(512);
   v_charset      varchar2(32);
   v_char_sperator varchar2(1):=:P1_SEPERATOR ; -- ';';

   v_starttime    timestamp := systimestamp;
  
   v_Seconds      number;
   v_rec_count   pls_integer;

   v_message      varchar2(4000) := 'Prozessing File Insert' || '<br/>';

   --
   v_l            pls_integer := 0;
   v_rchar        varchar2(10) := '-';
   v_rbefore      varchar2(10) := '-';
   v_match        boolean := false;


   --  PL/SQL function to convert a BLOB to a CLOB

   function convertToClob(p_blob_in in blob, p_charset varchar2)
      return clob
   as
      v_clob           clob;
      v_dest_offset    number := 1;
      v_src_offset     number := 1;
      v_lang_context   number := 0;
      v_warning        number;
   begin
      dbms_lob.CREATETEMPORARY(v_clob, true);



      dbms_lob.converttoclob(v_clob
                            ,p_blob_in
                            ,dbms_lob.lobmaxsize
                            ,v_dest_offset
                            ,v_src_offset
                            ,nls_charset_id(p_charset)
                            ,v_lang_context
                            ,v_warning);

      return v_clob;
   end convertToClob;
   
   -- get the runtime information
   function getRunTime ( p_starttime timestamp)
    return number
   is
    
     v_endtime      timestamp;
     v_runtime      interval day to second;
      v_Seconds      number;
   begin
         -- get the runtime of the load process
      v_endtime := systimestamp;
      v_runtime := v_endtime - p_starttime;

      select   extract(day from v_runtime) * 86400
          + extract(hour from v_runtime) * 3600
          + extract(minute from v_runtime) * 60
          + extract(second from v_runtime)
      into v_Seconds
      from dual;
   
     return v_Seconds;
   
  end getRunTime ;
   
begin
   -- check if parameter exits
   if :P1_FILENAME is not null
   then
      v_charset := nvl( :P1_CHARSET, 'WE8ISO8859P15');
      v_message :=
            v_message
         || ' Start load import file '
         || :P1_FILENAME
         || ' <br/> with the charset '
         || :P1_CHARSET
         || '<br/>';

      -- check if the file exits
      select count(ID)
        into v_file_count
        from apex_application_temp_files
       where name = :P1_FILENAME;

      -- check if the upload was sucessfull
      if v_file_count > 0
      then
                                
         -- clean the local stage table
         delete CSV_STAGE_TAB;
         commit;

         -----------------------------------------------

         -- Read data from apex_application_temp_files
         select blob_content, mime_type
           into v_blob_data, v_mime_type
           from apex_application_temp_files
          where name = :P1_FILENAME;

         -- read the data into a clob
         dbms_lob.CREATETEMPORARY(v_clob_data, true);
         v_clob_data := convertToClob(p_blob_in => v_blob_data, p_charset => v_charset);
         v_blob_len := dbms_lob.getlength(v_blob_data);
         v_clob_len := dbms_lob.getlength(v_clob_data);

         v_position := 1;

         v_message :=
            v_message || ' Read ' || to_char(v_blob_len) || ' byte and  ' || to_char(v_clob_len) || ' characters <br/>';
            
         -- check again if stage table is empty     
         -- check that this table is empty
         select count(*) into v_rec_count from  CSV_STAGE_TAB;
         
         if v_rec_count > 0 then
            RAISE_APPLICATION_ERROR  (-20101, 'Stage Table is not empty!! Stop processing the file !');
         end if;

        -- parse the file
   	    while (v_position <= v_clob_len)
			 loop
			 
			-- use bigger chunks to avoid problem with substr
			-- reading on be one is to with DBMS_LOB.substr is very slow!
			  
			v_line_end_pos:=DBMS_LOB.instr(v_clob_data,chr(10),v_position,1)+1;
			  
			-- if v_line_end_pos is 0 then throw exception
			--
			if v_line_end_pos = 0 then
				  raise_application_error( -20001 , '-- Error :: Can not upload CSV files with linesize >>32767 or without line end chr(10) Char' );  
			end if;
			  
			v_chunk_len:= v_line_end_pos - v_position;
			  
			v_line := dbms_lob.substr(v_clob_data , v_chunk_len, v_position );
			   
			   -- auf die nächste Position weiterschalten
			v_position := v_position + v_chunk_len;
           
			--  v_message :=
			--    v_message
			-- || ' DEBUG  v_line_end_pos::' ||to_char(v_line_end_pos)||' - v_chunk_len::'||to_char(v_chunk_len)||' - v_position::'||to_char(v_position)||'</br>';
         
            -- get the lenght
            v_l := length(v_line);

            -- read the string
            for i in 1 .. v_l
               loop
                  v_rchar := substr(v_line, i, 1);

                    if v_rchar ='"' and v_rbefore=v_char_sperator then
                       v_match:=true;
                    end if;
                    if v_rchar =v_char_sperator and v_rbefore='"' then
                       v_match:=false;
                    end if;

                    if  v_rchar =v_char_sperator and v_match 
                    then
                       v_line:=substr(v_line,1,i-1)||'^'||substr(v_line,i+1,10000);
                    end if;    
                    v_rbefore:=v_rchar;
                    
            end loop;

            -- replace all "
            v_line := replace(v_line, '"', '');

            -- Convert comma to : to use the wwv_flow_utilities
            v_line := replace(v_line, v_char_sperator, ':');

            -- get ',' in text fields back
            v_line := replace(v_line, '^', ',');

			-- Convert each column separated by : into array of data </span>
            v_data_array := wwv_flow_utilities.string_to_table(v_line);

            -- fill the missing records to get the 30 values
            for y in 1 .. 30
               loop
                if v_data_array.exists(y)
                then
                     null;
                else
                    -- I use this strange sign to filter out the data later in this ETL process
                     v_data_array(y) := '#';
                    --debug
                    --v_data_array(29) := v_mime_type;
                 end if;
               end loop;

               -- insert into the stage table  DWH_OPERATION.COMET_CONTRACT_INPUT3@DEDWHOPERATION
               insert into CSV_STAGE_TAB(ID
                                                  ,IMPORTFILENAME
                                                  ,FIELD01
                                                  ,FIELD02
                                                  ,FIELD03
                                                  ,FIELD04
                                                  ,FIELD05
                                                  ,FIELD06
                                                  ,FIELD07
                                                  ,FIELD08
                                                  ,FIELD09
                                                  ,FIELD10
                                                  ,FIELD11
                                                  ,FIELD12
                                                  ,FIELD13
                                                  ,FIELD14
                                                  ,FIELD15
                                                  ,FIELD16
                                                  ,FIELD17
                                                  ,FIELD18
                                                  ,FIELD19
                                                  ,FIELD20
                                                  ,FIELD21
                                                  ,FIELD22
                                                  ,FIELD23
                                                  ,FIELD24
                                                  ,FIELD25
                                                  ,FIELD26
                                                  ,FIELD27
                                                  ,FIELD28
                                                  ,FIELD29
                                                  ,FIELD30)
                    values (v_sr_no
                           ,:P1_FILENAME
                           ,v_data_array(1)
                           ,v_data_array(2)
                           ,v_data_array(3)
                           ,v_data_array(4)
                           ,v_data_array(5)
                           ,v_data_array(6)
                           ,v_data_array(7)
                           ,v_data_array(8)
                           ,v_data_array(9)
                           ,v_data_array(10)
                           ,v_data_array(11)
                           ,v_data_array(12)
                           ,v_data_array(13)
                           ,v_data_array(14)
                           ,v_data_array(15)
                           ,v_data_array(16)
                           ,v_data_array(17)
                           ,v_data_array(18)
                           ,v_data_array(19)
                           ,v_data_array(20)
                           ,v_data_array(21)
                           ,v_data_array(22)
                           ,v_data_array(23)
                           ,v_data_array(24)
                           ,v_data_array(25)
                           ,v_data_array(26)
                           ,v_data_array(27)
                           ,v_data_array(28)
                           ,v_data_array(29)
                           ,v_data_array(30));


               -- reset line
               v_line := null;
               -- rember line count
               v_sr_no := v_sr_no + 1;

               if mod(v_sr_no, 100) = 0
               then
                  commit;
               end if;

               -- free the collecio
               v_data_array.DELETE;
            
         end loop;

         commit;
         
         v_seconds:=getRunTime ( p_starttime => v_starttime);
   
         v_message := v_message || '<br/> Insert into stage ' || to_char(v_sr_no) 
                                || ' rows  after '|| to_char(v_Seconds, '999G990D099') 
                                || ' Seconds</br>';


        
      else
         v_message := v_message || ' Can not find file with the name :: ' || v_filename;
      end if;
   end if;

   -- get the runtime of the load process
   -- get the runtime of the load process
   --
   v_seconds:=getRunTime ( p_starttime => v_starttime);

   v_message := v_message || '<br/> File load needs ' || to_char(v_Seconds, '999G990D099') || ' sec';

   apex_application.g_print_success_message := '<span style="color:yellow">' || v_message || '</span>';
exception
   when others
   then
      v_message := v_message || ' ' || sqlerrm;
      apex_application.g_print_success_message := '<span style="color:red">' || v_message || '</span>';
end;