Requires Free Membership to View
-- *************************************
-- ** q u e s t i o n _ u p l o a d **
-- *************************************
-- question_upload reads a file of pipe-separated values and
-- inserts them into the question table.
-- The file to be read must be on the Oracle directory upload_dir,
-- created (by SYSTEM) like this:
-- CREATE DIRECTORY upload_dir AS 'd:produpload';
-- GRANT READ ON DIRECTORY upload_dir TO csv_admin;
FUNCTION question_upload
(
in_file_name IN VARCHAR2
)
RETURN VARCHAR2
IS
input_cnt PLS_INTEGER := 0; -- number of lines read
input_file utl_file.file_type;
input_text VARCHAR2 (32767);
max_len PLS_INTEGER := 32767; -- size of input_text
return_val VARCHAR2 (4000) := '';
BEGIN
input_file := utl_file.fopen
(
'UPLOAD_DIR',
in_file_name,
'r',
max_len
);
LOOP -- Will eventually fail with NO_DATA_FOUND
utl_file.get_line
(
input_file,
input_text,
max_len
);
input_cnt := input_cnt + 1;
-- Ignore first line (header), but insert a record for
-- every line read after that.
IF input_cnt > 1
THEN
question_upload_record (input_text);
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND -- End-of-file
THEN
utl_file.fclose (input_file);
RETURN return_val;
WHEN OTHERS
THEN
return_val := SQLERRM;
RETURN return_val;
END question_upload;
-- ***************************************************
-- ** q u e s t i o n _ u p l o a d _ r e c o r d **
-- ***************************************************
-- question_upload_record parses a single line of pipe-separated
-- text and INSERTs it into the question table
PROCEDURE question_upload_record
(
in_text IN VARCHAR2 -- line to be parsed
)
IS
answer_text question.answer_text%TYPE;
discard_text VARCHAR2 (32767); -- to be ignored
id_text VARCHAR2 (10); -- string form of question_id
question_text question.question_text%TYPE;
quote_text VARCHAR2 (10) := '"';
remaining_text VARCHAR2 (32767) := in_text;
sep_text VARCHAR2 (10) := '|'; -- field separator
submit_dt_text VARCHAR2 (20);
BEGIN
question_text := next_part (remaining_text, sep_text, quote_text);
answer_text := next_part (remaining_text, sep_text, quote_text);
discard_text := next_part (remaining_text, sep_text, quote_text);
submit_dt_text := next_part (remaining_text, sep_text, quote_text);
id_text := next_part (remaining_text, sep_text, quote_text);
INSERT INTO question
(
question_id,
question_text,
answer_text,
submit_dt
)
VALUES
(
TO_NUMBER (id_text, '99999999'),
question_text,
answer_text,
TO_DATE (submit_dt_text, 'DD-Mon-YYYY')
);
END question_upload_record;
Continued on the next page...
This was first published in July 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation