Q

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

This Content Component encountered an error

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 first published in July 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close