Dear All,
I'm facing issues in while upload csv file and insert into my table. My error is "ORA-06502: PL/SQL: numeric or value error: character to number conversion error". find the attached error snapshot. In My coding from excel sheet all varchar date type are accepting to insert into my table (TMP) but number is not able to insert in my table.
In My csv sheet only two colum (Name(Varchar) & AGE (Number)) as similar to my database table.
MY coding :
create or replace procedure "UPLOAD_PROCEDURE"
is
begin
DECLARE
v_blob_data BLOB;
v_blob_len NUMBER;
v_position NUMBER;
v_raw_chunk RAW(10000);
v_char CHAR(1);
c_chunk_len number := 1;
v_line VARCHAR2 (32767) := NULL;
v_data_array wwv_flow_global.vc_arr2;
BEGIN
DELETE TEMP_FILES_BACKUP;
DELETE wwv_flow_files;
INSERT INTO TEMP_FILES_BACKUP (ID,NAME,FILENAME,MIME_TYPE,CREATED_ON,BLOB_CONTENT) SELECT ID,NAME,FILENAME,MIME_TYPE,CREATED_ON,BLOB_CONTENT FROM APEX_APPLICATION_TEMP_FILES
where CREATED_ON= (select max(CREATED_ON) from APEX_APPLICATION_TEMP_FILES);
-- Read data from wwv_flow_files
select BLOB_CONTENT into v_blob_data from TEMP_FILES_BACKUP
where CREATED_ON= (select max(CREATED_ON) from TEMP_FILES_BACKUP);
v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;
-- Read and convert binary to char
WHILE ( v_position <= v_blob_len ) LOOP
v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
v_char := chr(hex_to_decimal(rawtohex(v_raw_chunk)));
v_line := v_line || v_char;
v_position := v_position + c_chunk_len;
-- When a whole line is retrieved
IF v_char = CHR(10) THEN
-- Convert comma to : to use wwv_flow_utilities
v_line := REPLACE (v_line, ',', ':');
-- Convert each column separated by : into array of data
v_data_array := wwv_flow_utilities.string_to_table (v_line);
-- Insert data into target table
EXECUTE IMMEDIATE 'insert into TMP(NAME,AGE)
values (:1,:2)'
USING
v_data_array(1),
TO_NUMBER(v_data_array(2));
-- Clear out
v_line := NULL;
END IF;
END LOOP;
END;
end;
Please help me out to resolve the issue.