OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » upload/import works only if last column contains NON NULL values

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » upload/import works only if last column contains NON NULL values (oracle 10g xe, apex 2.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

upload/import works only if last column contains NON NULL values [message #521389]

Mon, 29 August 2011 09:23

hudo
Messages: 165
Registered: May 2004

Senior Member

To upload csv-files I use something like

TYPE line_tab_type IS TABLE OF VARCHAR2 (4000)
       INDEX BY BINARY_INTEGER;

in the package header
and the procedure itself looks like

PROCEDURE get_mitglieder_csv (p_file_name IN VARCHAR2,
                           p_rec_sep IN VARCHAR2,
                           p_header IN VARCHAR2,
                           p_blzkto IN VARCHAR2
                           ) IS
 
 
    v_binary_file BLOB;
    v_text_file   CLOB;
    -- Conversion Variables
    v_dest_offset  INTEGER := 1;
    v_src_offset   INTEGER := 1;
    v_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    v_warning      INTEGER;
    -- Parsing Variables
    v_rec_sep_len PLS_INTEGER;
    v_start_pos   PLS_INTEGER := 1;
    v_end_pos     PLS_INTEGER := 1;
    v_line_num    PLS_INTEGER := 1;
    v_file_length PLS_INTEGER;
    -- Parsing Line Variables
    v_field_array wwv_flow_global.vc_arr2;
 
    p_lines line_tab_type;
    doszeilen  CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
    unixzeilen CONSTANT VARCHAR2(1) := CHR(10);
    geloescht BOOLEAN := FALSE;
 
 	err_code 	NUMBER;
	err_msg		VARCHAR2(400);
    
   
  
  BEGIN
    IF p_file_name IS NULL THEN
      raise_application_error(-20000, 'Dateiname wird benoetigt');
    END IF;
  
    IF p_rec_sep IS NULL THEN
      raise_application_error(-20000, 'Feldtrenner wird benoetigt');
    END IF;
  
    IF (UPPER(p_rec_sep) LIKE '%DOS%') THEN
      v_rec_sep_len := LENGTH(doszeilen);
    ELSE
      v_rec_sep_len := LENGTH(unixzeilen);
    END IF;
  
 
    SELECT blob_content
      INTO v_binary_file
      FROM my_wwv_flow_files
 
     WHERE my_wwv_flow_files.name = p_file_name
          --AND mime_type = 'text/plain'
       AND doc_size > 0;
  
    DBMS_LOB.createtemporary(v_text_file, TRUE);
    DBMS_LOB.converttoclob(v_text_file,
                           v_binary_file,
                           DBMS_LOB.lobmaxsize,
                           v_dest_offset,
                           v_src_offset,
                           DBMS_LOB.default_csid,
                           v_lang_context,
                           v_warning);
  
    IF v_warning = DBMS_LOB.warn_inconvertible_char THEN    -- error converting
      raise_application_error(-20000, 'Kann Datei nicht konvertieren');
    END IF;
  
    v_file_length := DBMS_LOB.getlength(v_text_file);
  
--INSERT INTO DEBUG_TAB (a) VALUES (v_file_length);
  
    LOOP
      EXIT WHEN v_start_pos > v_file_length;
      -- erste Vorkommen von p_rec_sep in v_text_file, starte suche bei v_start_pos
    
      IF (UPPER(p_rec_sep) LIKE '%DOS%') THEN
        v_end_pos := DBMS_LOB.INSTR(v_text_file, doszeilen, v_start_pos);
      ELSE
        v_end_pos := DBMS_LOB.INSTR(v_text_file, unixzeilen, v_start_pos);
      
      END IF;
 
--INSERT INTO DEBUG_TAB (a,b) VALUES ('p_rec_sep',UPPER(p_rec_sep));
--INSERT INTO DEBUG_TAB (a,b) VALUES ('v_end_pos',v_end_pos); 
 
    
      IF v_end_pos = 0 --- nichts gefunden, leeres v_text_file
       THEN
        v_end_pos := v_file_length + 1;
      END IF;
    
      IF v_end_pos - v_start_pos > 4000 --- mehr als 4000 Zeichen in Zeile
       THEN
        raise_application_error(-20000, 'Zeile hat mehr als 4000 Zeichen, Dateiformat beachten');
      END IF;
    
      --- DBMS_LOB.SUBSTR(source, amount, position) 
      p_lines(v_line_num) := DBMS_LOB.SUBSTR(v_text_file,
                                             v_end_pos - v_start_pos,
                                             v_start_pos);
    
      --- Change the ',' field delimiter to ':' , to use the built-in string_to_table function
		--- optionale Hochkomma " entfernen
       p_lines(v_line_num) := REPLACE(p_lines(v_line_num), '"', '');
       p_lines(v_line_num) := REPLACE(p_lines(v_line_num), ':', ' ');
 
		--- passende Feldtrenner auswaehlen ,  ;  |
      p_lines(v_line_num) := REPLACE(p_lines(v_line_num), '|', ':');
--      p_lines(v_line_num) := REPLACE(p_lines(v_line_num), ',', ':');
--      p_lines(v_line_num) := REPLACE(p_lines(v_line_num), ';', ':');
    
      v_field_array := wwv_flow_utilities.string_to_table(p_lines(v_line_num));
    
      IF v_field_array.COUNT <= 1 THEN
        raise_application_error(-20000, 'Benoetige mindestens 2 Spalten');
      
      ELSE
       
        BEGIN
        IF geloescht = FALSE THEN
      
        EXECUTE IMMEDIATE 'TRUNCATE TABLE UP_MITGLIEDER';
        geloescht := TRUE;
        END IF;
 
 
 		IF (  (v_line_num = 1 )   AND  (UPPER(p_header) = 'MITKOPF')  ) THEN
 			NULL;
 
------ mit Konto-Daten ------------------  24 Felder
		ELSIF ( UPPER(p_blzkto) = 'MITBLZ' )
		THEN
		
		       EXECUTE IMMEDIATE 'INSERT INTO UP_MITGLIEDER(
		        	MG_NR
                                , ...
		        	,MG_ZS
		        	,MG_KONTONR
		        	,MG_BLZ
		
		        )
		
		  		VALUES ( TRIM(:1), TRIM(:2), TRIM(:3), TRIM(:4), TRIM(:5), TRIM(:6), TRIM(:7), TRIM(:8), TRIM(:9), TRIM(:10),
		   					TRIM(:11), TRIM(:12), TRIM(:13), TRIM(:14), TRIM(:15), TRIM(:16), TRIM(:17), TRIM(:18), TRIM(:19), TRIM(:20),
		   					TRIM(:21), TRIM(:22),  
		   					TRIM(:23), TRIM(:24)	)'
		
		--   		VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22 :23 :24 )'
		
		
		          USING
		          	v_field_array(1), v_field_array(2), v_field_array(3), v_field_array(4), v_field_array(5),
					v_field_array(6), v_field_array(7), v_field_array(8), v_field_array(9), v_field_array(10),
		          	v_field_array(11), v_field_array(12), v_field_array(13), v_field_array(14), v_field_array(15),
					v_field_array(16), v_field_array(17), v_field_array(18), v_field_array(19), v_field_array(20),
					v_field_array(21), v_field_array(22), v_field_array(23), v_field_array(24);
		
 
------------ ohne Konto-Daten , bis MG_ZS ---- 22 Felder
		ELSE 			
       
		--        EXECUTE IMMEDIATE 'INSERT INTO EINS(LFD,BEM) VALUES(:1,:2)'
		       EXECUTE IMMEDIATE 'INSERT INTO UP_MITGLIEDER(
		        	MG_NR
 ,...
		        	,MG_ZS
		
		        )
		
		  		VALUES ( TRIM(:1), TRIM(:2), TRIM(:3), TRIM(:4), TRIM(:5), TRIM(:6), TRIM(:7), TRIM(:8), TRIM(:9), TRIM(:10),
		   					TRIM(:11), TRIM(:12), TRIM(:13), TRIM(:14), TRIM(:15), TRIM(:16), TRIM(:17), TRIM(:18), TRIM(:19), TRIM(:20),
		   					TRIM(:21), TRIM(:22)  )'
		
		--   		VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22 )'
		
		
		          USING
		          	v_field_array(1), v_field_array(2), v_field_array(3), v_field_array(4), v_field_array(5),
					v_field_array(6), v_field_array(7), v_field_array(8), v_field_array(9), v_field_array(10),
		          	v_field_array(11), v_field_array(12), v_field_array(13), v_field_array(14), v_field_array(15),
					v_field_array(16), v_field_array(17), v_field_array(18), v_field_array(19), v_field_array(20),
					v_field_array(21), v_field_array(22);
	
         END IF;  --- v_line_num = 1 AND p_header
 
        END;  --- von Begin im Else-Zweig
      
      END IF;
    
      --INSERT INTO DEBUG_TAB(a)
      --VALUES ('P_lines: ' || p_lines(v_line_num));
      
--      hilfscounter := v_field_array.COUNT;
    
      --INSERT INTO DEBUG_TAB(a)
      --VALUES ('v_field_array.count: ' || TO_CHAR(hilfscounter));
 
		-- neue Zeile    
	      v_line_num  := v_line_num + 1;
    	  v_start_pos := v_end_pos + v_rec_sep_len;
    
    END LOOP;
  
    DBMS_LOB.freetemporary(v_text_file);
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      raise_application_error(-20000,
                              'Datei existiert nicht in my_wwv_flow_files, ist keine Textdatei (text/plain) oder hat die Groesse 0');
    WHEN OTHERS THEN
		err_code 	:= SQLCODE;
		err_msg		:= SUBSTR(SQLERRM, 1, 400);
 
      raise_application_error(-20011,
                              'Datei entspricht nicht erwartetem Format ! '
                              ||CHR(13) || CHR(10)||'  '||err_msg||CHR (10)|| v_line_num ||CHR (10)|| p_lines(v_line_num));
 
 
END get_mitglieder_csv;

If the last column e.g. MG_ZS contains NULL-values the import into UP-MITGLIEDER does not work , stops with error

Fehler 	ORA-20011: Datei entspricht nicht erwartetem Format !

Why isn't it possible to import Null values from the last column ?

Report message to a moderator

Re: upload/import works only if last column contains NON NULL values [message #521414 is a reply to message #521389]

Mon, 29 August 2011 12:41

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

First remove your "when others". Then you'll see the real error. My guess is that the array being created doesn't have 22 (or 24 depending on which code is being accessed) entries when the last value is left blank. Take a look at the file (in a text editor) to see how many comma separated values you have, and see how many entries your array has.But why all the dynamic SQL?

Report message to a moderator

Re: upload/import works only if last column contains NON NULL values [message #521493 is a reply to message #521414]

Tue, 30 August 2011 09:19

hudo
Messages: 165
Registered: May 2004

Senior Member

First, sorry, it was not the correct error.
Correct error is ORA-01403: Keine Daten gefunden respectively ORA-01403 no data found

Here is a general example:

CREATE OR REPLACE PROCEDURE import_emp_csv (p_file_name IN VARCHAR2,
                           p_rec_sep IN VARCHAR2,
                           p_header IN VARCHAR2
                           ) IS

    TYPE line_tab_type IS TABLE OF VARCHAR2 (4000)
       INDEX BY BINARY_INTEGER;
       
    v_binary_file BLOB;
    v_text_file   CLOB;
    -- Conversion Variables
    v_dest_offset  INTEGER := 1;
    v_src_offset   INTEGER := 1;
    v_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    v_warning      INTEGER;
    -- Parsing Variables
    v_rec_sep_len PLS_INTEGER;
    v_start_pos   PLS_INTEGER := 1;
    v_end_pos     PLS_INTEGER := 1;
    v_line_num    PLS_INTEGER := 1;
    v_file_length PLS_INTEGER;
    -- Parsing Line Variables
    v_field_array wwv_flow_global.vc_arr2;

    p_lines line_tab_type;
    doszeilen  CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
    unixzeilen CONSTANT VARCHAR2(1) := CHR(10);
    geloescht BOOLEAN := FALSE;
 
 	err_code 	NUMBER;
	err_msg		VARCHAR2(400);
    

    
  
  BEGIN
    IF p_file_name IS NULL THEN
      raise_application_error(-20000, 'Dateiname wird benoetigt');
    END IF;
  
    IF p_rec_sep IS NULL THEN
      raise_application_error(-20000, 'Feldtrenner wird benoetigt');
    END IF;
  
    IF (UPPER(p_rec_sep) LIKE '%DOS%') THEN
      v_rec_sep_len := LENGTH(doszeilen);
    ELSE
      v_rec_sep_len := LENGTH(unixzeilen);
    END IF;
  

  
    SELECT blob_content
      INTO v_binary_file
      FROM my_wwv_flow_files

     WHERE my_wwv_flow_files.name = p_file_name
          --AND mime_type = 'text/plain'
       AND doc_size > 0;
  
    DBMS_LOB.createtemporary(v_text_file, TRUE);
    DBMS_LOB.converttoclob(v_text_file,
                           v_binary_file,
                           DBMS_LOB.lobmaxsize,
                           v_dest_offset,
                           v_src_offset,
                           DBMS_LOB.default_csid,
                           v_lang_context,
                           v_warning);
  

  
    IF v_warning = DBMS_LOB.warn_inconvertible_char THEN    -- error converting
      raise_application_error(-20000, 'Kann Datei nicht konvertieren');
    END IF;
  
    v_file_length := DBMS_LOB.getlength(v_text_file);
  

  
    LOOP
      EXIT WHEN v_start_pos > v_file_length;
      -- erste Vorkommen von p_rec_sep in v_text_file, starte suche bei v_start_pos
    
      IF (UPPER(p_rec_sep) LIKE '%DOS%') THEN
        v_end_pos := DBMS_LOB.INSTR(v_text_file, doszeilen, v_start_pos);
      ELSE
        v_end_pos := DBMS_LOB.INSTR(v_text_file, unixzeilen, v_start_pos);
      
      END IF;
 

      IF v_end_pos = 0 --- nichts gefunden, leeres v_text_file
       THEN
        v_end_pos := v_file_length + 1;
      END IF;
    
      IF v_end_pos - v_start_pos > 4000 --- mehr als 4000 Zeichen in Zeile
       THEN
        raise_application_error(-20000, 'Zeile hat mehr als 4000 Zeichen, Dateiformat beachten');
      END IF;
    
      --- DBMS_LOB.SUBSTR(source, amount, position) 
      p_lines(v_line_num) := DBMS_LOB.SUBSTR(v_text_file,
                                             v_end_pos - v_start_pos,
                                             v_start_pos);
    
      --- Change the ',' field delimiter to ':' , to use the built-in string_to_table function
		--- optionale Hochkomma " entfernen
       p_lines(v_line_num) := REPLACE(p_lines(v_line_num), '"', '');
       p_lines(v_line_num) := REPLACE(p_lines(v_line_num), ':', ' ');

		--- passende Feldtrenner auswaehlen ,  ;  |
      p_lines(v_line_num) := REPLACE(p_lines(v_line_num), '|', ':');
--      p_lines(v_line_num) := REPLACE(p_lines(v_line_num), ',', ':');
--      p_lines(v_line_num) := REPLACE(p_lines(v_line_num), ';', ':');
    
      v_field_array := wwv_flow_utilities.string_to_table(p_lines(v_line_num));
    
      IF v_field_array.COUNT <= 1 THEN
        raise_application_error(-20000, 'Benoetige mindestens 2 Spalten');
      
      ELSE
       
        BEGIN
        IF geloescht = FALSE THEN

        EXECUTE IMMEDIATE 'TRUNCATE TABLE UP_EMP2';
        geloescht := TRUE;
        END IF;
 

 		IF (  (v_line_num = 1 )   AND  (UPPER(p_header) = 'MITKOPF')  ) THEN
 			NULL;



		ELSE 			
		       EXECUTE IMMEDIATE 'INSERT INTO UP_EMP2(
		        	EMPNO
		        	,ENAME
		        	,JOB
		        	,MGR
		        	,HIREDATE
		        	,SAL
		        	,DEPTNO
		        	,COMM
		        	
		        )
		
		  		VALUES ( TRIM(:1), TRIM(:2), TRIM(:3), TRIM(:4), TRIM(:5), TRIM(:6), TRIM(:7), TRIM(:8)
		   				 )'
		

		
		          USING
		          	v_field_array(1), v_field_array(2), v_field_array(3), v_field_array(4), v_field_array(5),
					v_field_array(6), v_field_array(7), v_field_array(8);
	
         END IF;  --- v_line_num = 1 AND p_header

        END;  --- von Begin im Else-Zweig
      
      END IF;
    


		-- neue Zeile    
	      v_line_num  := v_line_num + 1;
    	  v_start_pos := v_end_pos + v_rec_sep_len;
    
    END LOOP;
  
    DBMS_LOB.freetemporary(v_text_file);
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      raise_application_error(-20000,
                              'Datei existiert nicht in my_wwv_flow_files, ist keine Textdatei (text/plain) oder hat die Groesse 0');
    WHEN OTHERS THEN
		err_code 	:= SQLCODE;
		err_msg		:= SUBSTR(SQLERRM, 1, 400);

      raise_application_error(-20011,
                              'Datei entspricht nicht erwartetem Format ! '
                              ||CHR(13) || CHR(10)||'  '||err_msg||CHR (10)|| v_line_num ||CHR (10)|| p_lines(v_line_num));


END import_emp_csv;

I uploaded also my test file which contains in first row in a middle element a NULL value --> no problem, and in the second row the NULL value as last element --> NO Data Found
Quote:

My guess is that the array being created doesn't have 22 (or 24 depending on which code is being accessed) entries when the last value is left blank

I think you are right, but how can this be solved ?
The second row contains the NULL value as last element, this causes the problem with no data found. If I put an extra separator, then it works:
Quote:

7500|"ALLENT"|"SALESMAN"|7698|"20.02.1981"|1600|30||

How can this be solved ?
Quote:

But why all the dynamic SQL?

If you could show me another way I would appreciate it.

Attachment: emp_imp.csv
(Size: 0.15KB, Downloaded 2329 times)

Report message to a moderator

Re: upload/import works only if last column contains NON NULL values [message #521500 is a reply to message #521493]

Tue, 30 August 2011 11:08

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

I created a test table.

create table test_tab(a number, b number, c number, d number)

I used some of your code, but stripped it down a bit just for an example. But then I'm checking if the number of entries in the table is four, and if not I add it. And you can also see that I used just a simple insert rather than using any dynamic SQL.

declare
    v_field_arr wwv_flow_global.vc_arr2;
    v_csv_file    clob;
    v_start_pos   PLS_INTEGER := 1;
    v_end_pos     PLS_INTEGER := 1;
    v_file_length PLS_INTEGER;
    v_line        varchar2(4000);
begin
    v_csv_file := '1,2,3,' || chr(10) || '4,6,5,7' || chr(10) || '2,7,5';
    v_file_length := DBMS_LOB.getlength(v_csv_file);
  
    LOOP
        EXIT WHEN v_start_pos > v_file_length;
        v_end_pos := DBMS_LOB.INSTR(v_csv_file, chr(10), v_start_pos);
        IF v_end_pos = 0
        THEN
            v_end_pos := v_file_length + 1;
        END IF;
            
        v_line := DBMS_LOB.SUBSTR(v_csv_file,
                                  v_end_pos - v_start_pos,
                                  v_start_pos);                                                   
        v_field_arr := wwv_flow_utilities.string_to_table(v_line, ',');
        if v_field_arr.count < 4 then
            v_field_arr(4) := null;
        end if;
        insert into test_tab(a,b,c,d) 
        values(v_field_arr(1), v_field_arr(2), v_field_arr(3), v_field_arr(4));

        v_start_pos := v_end_pos + 1;
    end loop;
end;

SQL> select * from test_tab
  2  /
 
         A          B          C          D
---------- ---------- ---------- ----------
         1          2          3 
         4          6          5          7
         2          7          5

I hope it helps. If you are using ApEx however you could use apex_util.string_to_table instead of wwv_flow_utilities. Apex_util will give you a null as the last field as long as you have a delimiter.

Report message to a moderator

Re: upload/import works only if last column contains NON NULL values [message #521526 is a reply to message #521500]

Tue, 30 August 2011 20:36

wucis
Messages: 60
Registered: March 2005

Member

Nice example. I need a blob, and for converting blob to clob, I used the example from http://13ter.info/blog/?p=364

But the best of your answer is the code

        if v_field_arr.count < 4 then
            v_field_arr(4) := null;
        end if;

That's I was missing, thx.

Report message to a moderator

Previous Topic:	Inline error message for report region..
Next Topic:	Modify URL in pl sql web toolkit

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 05 22:02:28 CST 2025