Using PL/SQL to read delimited ASCII text files... Part 4
Continued from Part 3...
-- ************************************* -- ** 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...