Ask the Expert

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

Continued from Part 1...

    Requires Free Membership to View

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