CREATE TABLE STUDENT_FILES
(
FILE_ID NUMBER,
FILE_NAME VARCHAR2 (100),
FILE_TYPE VARCHAR2 (500),
FILE_CONTENT BLOB,
IS_PROCESSED CHAR (1) DEFAULT 'N',
CONSTRAINT STUDENT_FILES_PK PRIMARY KEY (FILE_ID)
);
CREATE TABLE STUDENT_DETAILS
(
ID NUMBER,
NAME VARCHAR2 (100),
ADDRESS VARCHAR2 (500),
DOB DATE,
GENDER CHAR (1),
CONSTRAINT STD_DTLS_PK PRIMARY KEY (ID)
);
CREATE TABLE APP_DEBUG (LOG_DATE DATE,LOG_MSG VARCHAR2(2000));
-- student_details.csv
ID,NAME,ADDRESS,DOB,GENDER
1,SIVA,"#3-85,Gorantla,pin-515231",29-Dec-90,M
2,RAJ,"#3-85,Nidimaidi Village and Post,Gorantla,pin-515110",10-Aug-91,M
3,HARI,"#3/34,Anantapur,pin-515222",1-Jan-91,F
4,JANA,"#3/34,Anantapur,pin-444444",1-Jan-91,
-- View/Download Uploaded files
SELECT FILE_ID,
FILE_NAME,
FILE_TYPE,
DBMS_LOB.GETLENGTH (FILE_CONTENT) AS FILE_CONTENT,
IS_PROCESSED
FROM STUDENT_FILES;
-- upload file to the table
DECLARE
v_file_id NUMBER;
BEGIN
SELECT NVL (MAX (file_id), 0) + 1 INTO v_file_id FROM student_files;
INSERT INTO student_files
SELECT v_file_id,
name,
mime_type,
blob_content,
'N'
FROM apex_application_temp_files
WHERE name = :P18_FILE_BROWSE;
DELETE FROM apex_application_temp_files
WHERE name = :P18_FILE_BROWSE;
COMMIT;
END;
-- insert file data to the table
DECLARE
p_id NUMBER;
p_clob CLOB;
p_delim VARCHAR2 (100) := ',';
p_optionally_enclosed VARCHAR2 (100) := '"';
--
CARRIAGE_RETURN CONSTANT CHAR (1) := CHR (13);
LINE_FEED CONSTANT CHAR (1) := CHR (10);
--
l_char CHAR (1);
l_lookahead CHAR (1);
l_pos NUMBER := 0;
l_token VARCHAR2 (32767) := NULL;
l_token_complete BOOLEAN := FALSE;
l_line_complete BOOLEAN := FALSE;
l_new_token BOOLEAN := TRUE;
l_enclosed BOOLEAN := FALSE;
--
l_lineno NUMBER := 1;
l_columnno NUMBER := 1;
l_id student_details.id%TYPE;
l_name student_details.name%TYPE;
l_address student_details.address%TYPE;
l_dob student_details.dob%TYPE;
l_gender student_details.gender%TYPE;
l_count NUMBER := 0;
i_flag CHAR (1) := NULL;
v_error VARCHAR2 (2000) := NULL;
BEGIN
SELECT FILE_ID, FILE_CONTENT
INTO p_id, p_clob
FROM ( SELECT *
FROM STUDENT_FILES
WHERE IS_PROCESSED = 'N'
ORDER BY FILE_ID ASC)
WHERE ROWNUM < 2;
INSERT INTO app_debug
VALUES (SYSTIMESTAMP, 'File Upload~begin');
LOOP
-- increment position index
l_pos := l_pos + 1;
BEGIN
-- get next character from clob
l_char := DBMS_LOB.SUBSTR (p_clob, 1, l_pos);
EXCEPTION
WHEN OTHERS
THEN
l_char := '-';
END;
-- exit when no more characters to process
EXIT WHEN l_char IS NULL OR l_pos > DBMS_LOB.getLength (p_clob);
-- if first character of new token is optionally enclosed character
-- note that and skip it and get next character
IF l_new_token AND l_char = p_optionally_enclosed
THEN
l_enclosed := TRUE;
l_pos := l_pos + 1;
l_char := DBMS_LOB.SUBSTR (p_clob, 1, l_pos);
END IF;
l_new_token := FALSE;
BEGIN
-- get look ahead character
l_lookahead := DBMS_LOB.SUBSTR (p_clob, 1, l_pos + 1);
EXCEPTION
WHEN OTHERS
THEN
l_lookahead := '-';
END;
-- dbms_output.put_line('position='||l_pos);
-- inspect character (and lookahead) to determine what to do
IF l_char = p_optionally_enclosed AND l_enclosed
THEN
IF l_lookahead = p_optionally_enclosed
THEN
l_pos := l_pos + 1;
l_token := l_token || l_lookahead;
ELSIF l_lookahead = p_delim
THEN
l_pos := l_pos + 1;
l_token_complete := TRUE;
ELSE
l_enclosed := FALSE;
END IF;
ELSIF l_char IN (CARRIAGE_RETURN, LINE_FEED) AND NOT l_enclosed
THEN
l_token_complete := TRUE;
l_line_complete := TRUE;
IF l_lookahead IN (CARRIAGE_RETURN, LINE_FEED)
THEN
l_pos := l_pos + 1;
END IF;
ELSIF l_char = p_delim AND NOT l_enclosed
THEN
l_token_complete := TRUE;
ELSIF l_pos = DBMS_LOB.getLength (p_clob)
THEN
l_token := l_token || l_char;
l_token_complete := TRUE;
l_line_complete := TRUE;
ELSE
l_token := l_token || l_char;
END IF;
-- process a new token
IF l_token_complete
THEN
INSERT INTO app_debug
VALUES (
SYSTIMESTAMP,
'File Upload~'
|| 'R'
|| l_lineno
|| 'C'
|| l_columnno
|| ': '
|| NVL (l_token, '**null**'));
DBMS_OUTPUT.put_line (
'R'
|| l_lineno
|| 'C'
|| l_columnno
|| ': '
|| NVL (l_token, '**null**'));
IF l_lineno > 1
THEN
CASE l_columnno
WHEN 1
THEN
l_id := l_token;
WHEN 2
THEN
l_name := l_token;
WHEN 3
THEN
l_address := l_token;
WHEN 4
THEN
l_dob := l_token;
WHEN 5
THEN
l_gender := l_token;
END CASE;
END IF;
l_columnno := l_columnno + 1;
l_token := NULL;
l_enclosed := FALSE;
l_new_token := TRUE;
l_token_complete := FALSE;
END IF;
-- process end-of-line here
IF l_line_complete
THEN
INSERT INTO app_debug
VALUES (SYSTIMESTAMP, 'File Upload~' || l_lineno);
DBMS_OUTPUT.put_line ('-----');
IF l_lineno > 1
THEN
SELECT COUNT (*)
INTO l_count
FROM student_details
WHERE id = l_id;
IF l_count > 0
THEN
i_flag := 'U';
UPDATE student_details
SET name = l_name,
address = l_address,
dob = l_dob,
gender = l_gender
WHERE id = l_id;
ELSE
i_flag := 'I';
INSERT INTO student_details (id,
name,
address,
dob,
gender)
VALUES (l_id,
l_name,
l_address,
l_dob,
l_gender);
END IF;
IF i_flag = 'I'
THEN
IF SQL%ROWCOUNT = 0
THEN
NULL;
-- not inserted
ELSE
NULL;
-- inserted
END IF;
END IF;
IF i_flag = 'U'
THEN
IF SQL%ROWCOUNT = 0
THEN
NULL;
-- not updated
ELSE
NULL;
-- updated
END IF;
END IF;
i_flag := NULL;
END IF;
l_lineno := l_lineno + 1;
l_columnno := 1;
l_line_complete := FALSE;
END IF;
END LOOP;
UPDATE STUDENT_FILES
SET IS_PROCESSED = 'Y'
WHERE FILE_ID = p_id;
EXCEPTION
WHEN OTHERS
THEN
UPDATE STUDENT_FILES
SET IS_PROCESSED = 'E'
WHERE FILE_ID = p_id;
v_error :=
'File Upload ~ Error at line no='
|| DBMS_UTILITY.format_error_backtrace
|| '-->'
|| SQLERRM;
INSERT INTO app_debug
VALUES (SYSTIMESTAMP, v_error);
DBMS_OUTPUT.put_line (v_error);
END;
