Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » File upload to database table (APEX 4.0)
File upload to database table [message #502303] Tue, 05 April 2011 12:33 Go to next message
coolguy01
Messages: 64
Registered: August 2006
Member
I have the following piece of code which picks up the file from browser i select and upload it tot he DB. but then I am getting the following error when i try to upload the file --

ORA-20000: Cannot Process 1ORA-06502: PL/SQL: numeric or value error: character to number conversion error

I am confused since i am not doin any conversion from numeric or varchar.I have included the code below. Please lemme know what change i need to make as I cant figure what conversions are happenin 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; v_rows number; v_sr_no number := 1;

BEGIN
delete from SAMPLE_TABLE;
-- Read data from wwv_flow_files
select blob_content into v_blob_data from wwv_flow_files where last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)
and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);
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 </span>
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);
BEGIN
-- Insert data into target table

EXECUTE IMMEDIATE 'insert into SAMPLE_TABLE
(ACAD_TERM,
EMPLID,
STUDENT_NAME,
COURSE_ID,
COURSE_DESCR,
LEGACY_COURSE_COST,
LEGACY_ATTRIBUTE_1,
LEGACY_ATTRIBUTE_2,
LEGACY_ATTRIBUTE_3,
LEGACY_ATTRIBUTE_4,
LEGACY_ATTRIBUTE_5) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)'
USING
v_data_array(1),
v_data_array(2),
v_data_array(3),
v_data_array(4),
v_data_array(5),
to_number(v_data_array(6)),
v_data_array(7),
v_data_array(Cool,
'NA',
'NA',
'NA';

-- Clear out
v_line := NULL;
v_sr_no := v_sr_no + 1;
:P2_EXEC_HANDLE :=v_sr_no;
EXCEPTION
WHEN OTHERS THEN
:P2_EXEC_HANDLE :=v_sr_no;
:F104_SUM_LOAD_ERR_TRACK:=DBMS_UTILITY.FORMAT_ERROR_STACK;
raise_application_error(-20000 , 'Cannot Process ' || :P2_EXEC_HANDLE||sqlerrm ||'th row please rectify and try again !!');

END;
END IF;
END LOOP;
END;
Re: File upload to database table [message #502351 is a reply to message #502303] Wed, 06 April 2011 01:18 Go to previous message
Michel Cadot
Messages: 67133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the content of :F104_SUM_LOAD_ERR_TRACK

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Previous Topic: Page Processing Validations
Next Topic: Print button
Goto Forum:
  


Current Time: Wed May 27 04:08:16 CDT 2020