oracle procedure error- Loading data into external table

PROCEDURE load_purple_top_data IS
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_UNIT_ID VARCHAR2(25);
	  V_HGB NUMBER;
      V_PLT NUMBER;
	  v_procedure_c constant varchar2(30) := 'load_purple_top_data';
	  v_location_i integer;	  	
	  v_donor_id varchar2(10);
	  v_gender   varchar2(1);
	  v_date_of_birth date;
	  v_first_name varchar2(25);
	  v_last_name varchar2(30);
	  v_middle_name varchar2(25);
	  v_blood_type varchar2(5);
	  v_ethnic_group varchar2(5);
	  v_street_name varchar2(50); 
	  v_suite_apt varchar2(10);
	  v_city   varchar2(40); 
	  v_state  varchar2(2); 
	  v_zip varchar2(5);
	  v_email varchar2(128);
	  v_d_phone_no varchar2(15); 
	  v_h_phone_no varchar2(15);
	  v_coll_date date;
	 

Cursor C1 is
select distinct d.donor_id,gender,date_of_birth,first_name,
       last_name,middle_name,blood_type,ethnic_group,
	   street_name, suite_apt,city, state, zip, da.email,
	   d_area_code||d_phone_no as d_phone_no, 
	   h_area_code||h_phone_no as h_phone_no,do.coll_date
  from donors_don d, donations_don do,
       donor_addresses_don da,
       valid_city_state_don vcs 
  where d.donor_id = do.donor_id
    and da.donor_id = d.donor_id
	and da.usps_id = vcs.usps_id
	and do.unit_id = v_unit_id;
	
BEGIN
v_location_i := 1000;

	  F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', '06022015.csv', 'R');
v_location_i := 2000;	  
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
          IF V_LINE IS NULL THEN
            EXIT;
          END IF;
		  v_location_i := 3000;
		  V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);
          V_HGB := substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 );
          V_PLT := substr(V_LINE, instr(V_LINE,',',1,2)+1);
 v_location_i := 4000;       
		OPEN C1;
          LOOP
           FETCH C1 INTO v_donor_id,v_gender,v_date_of_birth,v_first_name,
                         v_last_name,v_middle_name,v_blood_type,v_ethnic_group,
	                     v_street_name, v_suite_apt,v_city, v_state, v_zip, v_email,
	   					 v_d_phone_no, v_h_phone_no,v_coll_date;
		  EXIT WHEN C1%NOTFOUND;
		     INSERT INTO DS_PURPLE_TOP (DONOR_ID, 
			 			 				  GENDER, 
										  DATE_OF_BIRTH, 
										  FIRST_NAME, 
										  LAST_NAME, 
										  MIDDLE_NAME, 
										  BLOOD_TYPE, 
										  ETHNIC_GROUP, 
										  STREET_NAME, 
										  SUITE_APT, 
										  CITY, 
										  STATE, 
										  ZIP, 
										  EMAIL, 
										  D_PHONE_NO, 
										  H_PHONE_NO, 
										  UNIT_ID, 
										  HGB, 
										  PLT,
										  COLL_DATE)--HEIGHT_FT, HEIGHT_INCHES, WEIGHT 
			                       VALUES(v_donor_id,
								 		  v_gender,
										  v_date_of_birth ,
										  v_first_name,
										  v_last_name ,
										  v_middle_name ,
										  v_blood_type ,
										  v_ethnic_group ,
										  v_street_name ,
										  v_suite_apt ,
										  v_city  , 
										  v_state  , 
										  v_zip ,
										  v_email ,
										  v_d_phone_no , 
										  v_h_phone_no,
										  v_unit_id,
										  v_hgb,
										  v_plt,
										  v_coll_date);
          COMMIT;
		  END LOOP;
		CLOSE C1;
		EXCEPTION
        WHEN NO_DATA_FOUND THEN
          	 EXIT;
        when PU_FAILURE then
      		 raise PU_FAILURE;
		when OTHERS then

--    Send email notification that the process failed.
                             
      dbms_output.put_line('Error in ' || pg_package_c || '.' || v_procedure_c ||
                    ' at line: ' || v_location_i);
      dbms_output.put_line(sqlerrm);
      
      tsc_spu_pkg.capture_error_vars;
      tsc_spu_pkg.analyze_error(tsc_globals_pkg.sg_system_prefix_c);
      tsc_spu_pkg.log_error(tsc_globals_pkg.sg_system_prefix_c,
                            pg_package_c,
                            v_procedure_c,
                            v_location_i,
                            tsc_globals_pkg.sg_current_user_c,
                            'No Parameters.');
      raise_application_error(-20000, tsc_spu_pkg.get_friendly_error);
		END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END load_purple_top_data;

Open in new window


Error

ORA-20000: (no parser) ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HBC_DATA.HBC_PURPLE_TOP_PKG", line 319
ORA-06512: at line 2



Error in hbc_purple_top_pkg.load_purple_top_data at line: 3000
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Help in conversion of v_line.
06022015.csv
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
what is

ORA-06512: at "HBC_DATA.HBC_PURPLE_TOP_PKG", line 319
anumosesAuthor Commented:
I have a package and this is a procedure in that. As have 2 more functions. I gave only the procedure.
anumosesAuthor Commented:
ORA-06512: at "HBC_DATA.HBC_PURPLE_TOP_PKG", line 319

 v_location_i := 3000;
              V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);
          V_HGB := substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 );
          V_PLT := substr(V_LINE, instr(V_LINE,',',1,2)+1);
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
yes,  that's the problem.

without the full text, the line numbers don't mean anything.
anumosesAuthor Commented:
V_HGB := substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 );

previously it was 2.1 (number)
Now its number ;
slightwv (䄆 Netminder) Commented:
Run this:
with mydata as (
select 'W039715056535	170	172' v_line from dual
)
select to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1)) from mydata;

Open in new window


It uses a line from your file and your substr for V_PLT which is a number.
slightwv (䄆 Netminder) Commented:
Looks like the file is tab seperated not CSV?
sdstuberCommented:
those are 4 lines, only one of them can be 319.

however,  based on the datatypes and the error message it would have to be
one of these two

You are extracting text and putting it into numbers. at least one of them isn't extracting digits


 V_HGB := substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 );
V_PLT := substr(V_LINE, instr(V_LINE,',',1,2)+1);
slightwv (䄆 Netminder) Commented:
To add to my CSV comment:
You have INSTR looking for a ',', not a TAB.  This is why you aren't getting the correct values.
anumosesAuthor Commented:
with mydata as (
select 'W039715056535      170      172' v_line from dual
)
select to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1)) from mydata;

ORA-01722: invalid number
anumosesAuthor Commented:
When I run this query

with mydata as (
select 'W039715056535      170      172' v_line from dual
)
select substr(V_LINE, instr(V_LINE,',',1,2)+1) from mydata;

This is what I get.

W039715056535      170      172

I think its HGB line that is an error. This column is number(3).
anumosesAuthor Commented:
I tried this query


with mydata as (
select 'W039715056535      170      172' v_line from dual
)
select substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 ) from mydata;

No data( null value)
anumosesAuthor Commented:
csv is like this (comma seperated)

W039715056673,156,218
W039715056535,170,172
W039715056704,168,197
W039715060054,150,217
W039715060904,150,248
W039715060144,135,307
W039715056566,158,309
W039715060049,152,211
W039715060996,126,195
W039715056565,147,254
anumosesAuthor Commented:
I have a question though. CSV file has headers. Will that cause a problem?
slightwv (䄆 Netminder) Commented:
>>ORA-01722: invalid number

That is what you were supposed to get.  That shows there is a problem with the data in the file.

I pointed out what the problem was:  TAB separated not COMMA separated.

>>I have a question though. CSV file has headers. Will that cause a problem?

Yes.  If the header isn't in the data you want to process...

If I change the data to COMMA separated in the above example I posted, I get the correct number:
with mydata as (
 select 'W039715056535,170,172' v_line from dual
 )
 select to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1)) from mydata;

Open in new window

anumosesAuthor Commented:
What is the query for HGB? value is 170.

W039715056535,170,172

My procedure has

V_HGB := substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 );
slightwv (䄆 Netminder) Commented:
Set up the same test as I did using the logic you have for HGB.

copy/paste/replace current substr call for PLT with substr for HGB:
with mydata as (
 select 'W039715056535,170,172' v_line from dual
 )
 select to_number(substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 )) from mydata;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
It was the header that was giving an error. When I removed the header and used to_number it worked.
anumosesAuthor Commented:
Thanks
anumosesAuthor Commented:
This is not oracle thread.

I have a ftp powershell script help posted

http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28685644.html

Not many people came to help. Any additional help for other experts?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.