Q

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

Continued from Part 2...

-- *************************
-- **  n e x t _ p a r t  **
-- *************************

--  next_part returns the beginning portion of in_out_text,
--  up to (but not including) the first unquoted occurrence
--  of in_sep_text.  When this function returns, all of
--  in_out_text through the first unquoted occurrence of
--  in_sep_text (inclusive) will be removed.  If in_out_text
--  begins with in_quote_text, then the returned value will
--  include everything up to (but not including) the next
--  single occurrence of in_quote_text (possibly including
--  in_sep_text), with doubled occurrences of in_quote_text
--  replaced by single ones.

--  Example 1: after this code
-- s := '"This "","" is not a separator", or is it?';
-- x := next_part (s, ',', '"')
--  x is equal to 'This "," is not a separator', and s is ' or is it?'.

--  Example 2: after this code
-- s := '"This "","" is not a separator": or is it?,Blah, blah';
-- x := next_part (s, ',', '"')
--  x is equal to 'This "," is not a separator', and s is 'Blah, blah'.
--  The unclaimed text between the closing quote and the next
--  separator (': or is it?') is stored in a package variable, which
--  you can retrieve by calling get_unclaimed_text.

--  Note that if the first segment contains in_quote_text, but
--  does not start with it, then the quotes will not be
--  treated specially.

--  in_sep_text and in_quote_text may be longer than one character.

FUNCTION next_part
(
  in_out_text IN OUT VARCHAR2,
  in_sep_text IN VARCHAR2 -- separator
  DEFAULT ',',
  in_quote_text IN VARCHAR2 -- enclosing string
  DEFAULT NULL
)
RETURN VARCHAR2
IS
  quote_len     PLS_INTEGER := LENGTH (in_quote_text);
  quote_pos     PLS_INTEGER;      -- position of enclosing string
  return_text   VARCHAR2 (32767);
  sep_len       PLS_INTEGER := LENGTH (in_sep_text);
  sep_pos       PLS_INTEGER;      -- position of separator
BEGIN
  IF  in_out_text  IS NULL
  THEN  -- NULL input, NULL output
    return_text := '';

  ELSIF  INSTR (in_out_text, in_quote_text) = 1
  THEN  --  it begins with quote
    quote_pos := end_quote_pos ( in_out_text,
                                 in_quote_text
                               );
    return_text := unquote ( SUBSTR ( in_out_text,
                                      1,
                                      quote_pos + quote_len - 1
                                     ),
                             in_quote_text
                            );
    in_out_text := SUBSTR (in_out_text, 
                           quote_len + quote_pos
                          );

    -- deal with unclaimed text following closing quote
    unclaimed_text := '';
    sep_pos := INSTR (in_out_text, in_sep_text);

    IF  sep_pos = 1
    THEN  --  no stray text, discard separator
      in_out_text := SUBSTR (in_out_text, 1 + sep_len);
    ELSE  -- there was stray text
      unclaimed_text := SUBSTR ( in_out_text,
                                 1,
                                 sep_pos - 1
                               );
      in_out_text := SUBSTR ( in_out_text,
                              sep_pos + sep_len
                            );
    END IF;

  ELSE  --  nothing special
    sep_pos := INSTR (in_out_text, in_sep_text);

    IF  sep_pos = 0
    THEN  -- No separator; return whole string
      return_text := in_out_text;
      in_out_text := '';
    ELSE  -- Return part before separator
      return_text := SUBSTR ( in_out_text,
                              1,
                              sep_pos - 1
                            );
      in_out_text := SUBSTR ( in_out_text,
                              sep_pos + 1
                            );
    END IF;

  END IF;

  RETURN  return_text;
END next_part;

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