Coder Forum

SQL Code

DECLARE 
	a varchar2(10);	--Lieferantennummer
	b varchar2(3);	--Länderschlüssel
	c varchar2(35);	--Lieferanten_Name
	d varchar2(35);	--Lieferanten_Ort
	e varchar2(10);	--Lieferanten_PLZ
	v_file_handler UTL_FILE.FILE_TYPE; 
	v_line VARCHAR2(255); 
BEGIN 
	v_file_handler := UTL_FILE.FOPEN('/supplier', 'P99-LIEFERER.DAT','r'); 
 
	BEGIN 
		LOOP 
			UTL_FILE.GET_LINE(v_file_handler, v_line); 
                  a :=SUBSTR(v_line,64,10);
			b :=SUBSTR(v_line,60,3);
			c :=SUBSTR(v_line,95,35);
			d :=SUBSTR(v_line,178,35);
			e :=SUBSTR(v_line,223,10);
 
                  INSERT INTO bom.tabsupplier (NID,VCSUPPLIERNUMBER,VCCOUNTRY,VCNAME,VCLOCATION,VCPLZ) VALUES
			(
			(SELECT max(nid)+1 FROM tabsupplier),
			a,
			b,
			c,
			d,
			e
			);
			commit;
 
		END LOOP;
                  EXCEPTION 
			WHEN NO_DATA_FOUND THEN 
			EXIT;
 
	END; 
 
	UTL_FILE.FCLOSE(v_file_handler); 
END;

Source code

SQLWKS> set serveroutput on
Server Output                   ON
SQLWKS> set echo on 
Echo                            ON
SQLWKS> set timing on
Timing                          ON
SQLWKS> 
SQLWKS> DECLARE 
     2> 	a varchar2(10);	--Lieferantennummer
     3> 	b varchar2(3);	--Länderschlüssel
     4> 	c varchar2(35);	--Lieferanten_Name
     5> 	d varchar2(35);	--Lieferanten_Ort
     6> 	e varchar2(10);	--Lieferanten_PLZ
     7> 	v_file_handler UTL_FILE.FILE_TYPE; 
     8> 	v_line VARCHAR2(255); 
     9> BEGIN 
    10> 	v_file_handler := UTL_FILE.FOPEN('XMLDIR', 'supplier.dat','r'); 
    11> 	
    12> 	BEGIN 
    13> 		loop
    14> 			UTL_FILE.GET_LINE(v_file_handler, v_line); 
    15>                   a :=SUBSTR(v_line,65,10);
    16> 			b :=SUBSTR(v_line,61,3);
    17> 			c :=SUBSTR(v_line,96,35);
    18> 			d :=SUBSTR(v_line,173,35);
    19> 			e :=SUBSTR(v_line,218,10);
    20> 			 dbms_output.put_line(a);
    21> 			  dbms_output.put_line(b);
    22> 			   dbms_output.put_line(c);
    23> 			    dbms_output.put_line(d);
    24> 			     dbms_output.put_line(e);
    25>             insert INTO bom.tabsupplier (NID,VCSUPPLIERNUMBER,VCCOUNTRY,VCNAME,VCLOCATION,VCPLZ) VALUES
    26> 			(
    27> 			(select max(nid)+1 from tabsupplier),
    28> 			a,
    29> 			b,
    30> 			c,
    31> 			d,
    32> 			e
    33> 			);                      
    34> 			--commit;
    35> 
    36> 		END LOOP;
    37> 	EXCEPTION      
    38> 		WHEN NO_DATA_FOUND THEN dbms_output.put_line('Keine Daten gefunden');
    39> 		WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('VALUE ERROR'); 
    40> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR'); 
    41> 		WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('INVALID PATH'); 
    42> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR'); 
    43> 		WHEN UTL_FILE.INVALID_MODE THEN DBMS_OUTPUT.PUT_LINE('INVALID_MODE'); 
    44> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR'); 
    45> 		WHEN UTL_FILE.INVALID_FILEHANDLE THEN DBMS_OUTPUT.PUT_LINE('INVALID_FILEHANDLE'); 
    46> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR'); 
    47> 		WHEN UTL_FILE.INVALID_OPERATION THEN DBMS_OUTPUT.PUT_LINE('INVALID_OPERATION'); 
    48> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR'); 
    49> 		WHEN UTL_FILE.READ_ERROR THEN DBMS_OUTPUT.PUT_LINE('READ_ERROR'); 
    50> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR'); 
    51> 		WHEN UTL_FILE.WRITE_ERROR THEN DBMS_OUTPUT.PUT_LINE('WRITE_ERROR'); 
    52> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR'); 
    53> 		WHEN UTL_FILE.INTERNAL_ERROR THEN DBMS_OUTPUT.PUT_LINE('INTERNAL_ERROR'); 
    54> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR'); 
    55> 		WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('UN-HANDLED'); 
    56> 		RAISE_APPLICATION_ERROR(-20100,'FILE ERROR');
    57> 		
    58> 	END; 
    59> 	
    60> 	UTL_FILE.FCLOSE(v_file_handler); 
    61> END;
    62>                  
    63> 
ORA-20100: FILE ERROR
ORA-06512: in Zeile 40
VALUE ERROR