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...