Q

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

Continued from Part 1...

If you really have to do this job in PL/SQL, the main steps are:

  • Reading the data file. The supplied package utl_file can help here.
  • Parsing each record (this would be easy if you didn't have to worry about the delimiter, "|" in this case, appearing in the data.)
  • INSERTing the data into the tables
The PL/SQL package below does all this. All functions appear in alphabetic order; the one you would call to read the file is question_upload.
CREATE OR REPLACE PACKAGE BODY pk_csv
AS


-- ************************************
-- **  package variable definitions  **
-- ************************************

--  If next_part finds unbalanced quotes, it will return the
--  substring through the balanced quotes, and put the text
--  from there through the next field separator in unclaimed_text.
--  The function get_unclaimed_text may  be used to retrieve it.

unclaimed_text VARCHAR2 (32767);


-- *********************************
-- **  e n d _ q u o t e _ p o s  **
-- *********************************

--  end_quote_pos returns the starting position of the
--  closing quote in in_text.

FUNCTION end_quote_pos
(
  in_text IN VARCHAR2,
  in_quote_text IN VARCHAR2 -- quote string
                        DEFAULT '"'
)
RETURN PLS_INTEGER
IS
  cnt             PLS_INTEGER := 2; -- occurrences sought
  in_text_len     PLS_INTEGER := LENGTH (in_text);
  pos             PLS_INTEGER;
  quote_text_len  PLS_INTEGER := LENGTH (in_quote_text);
  return_val      PLS_INTEGER := -1; -- will be >= 0 at end
BEGIN
  WHILE  return_val < 0
  LOOP
    pos := INSTR ( in_text,
                   in_quote_text,
                   1,
                   cnt
                 );

    IF  pos = 0
    THEN -- not found
      return_val := 0;
    ELSE  --  See if there's another one right after it
      IF  pos + quote_text_len > in_text_len
      OR  INSTR ( SUBSTR (in_text, pos + quote_text_len),
                  in_quote_text
                ) = 1
      THEN  -- it was a double-quote
        cnt := cnt + 2;
      ELSE
        return_val := pos;
      END IF;
    END IF;
  END LOOP;

  RETURN  return_val;
END end_quote_pos;


-- *******************************************
-- **  g e t _ u n c l a i m e d _ t e x t  **
-- *******************************************

--  get_unclaimed_text returns any text unaccounted for in
--  the most recent call to next_part.  It returns NULL if
--  there was none.

FUNCTION get_unclaimed_text
RETURN  VARCHAR2
IS
BEGIN
  RETURN  unclaimed_text;
END get_unclaimed_text;

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