Q

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

This was last published in July 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close