Ask the Expert

Using PL/SQL to read delimited ASCII text files... Part 4

Continued from Part 3...

    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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: