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
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation