OraFAQ Forum: SQL & PL/SQL » utl

Home » SQL & PL/SQL » SQL & PL/SQL » utl_file load routine

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

utl_file load routine [message #7760]

Mon, 07 July 2003 03:52

poncet
Messages: 2
Registered: July 2003

Junior Member

i have found a UTL_FILE load routine, but i can't execute it .
Is there sommebody to show me how???
thank you

the routine is :

create or replace PROCEDURE utlload
--===========================================================================
-- Copywright 2002, joel crainshaw & chet west
-- http://www.oracletricks.com
--===========================================================================
-- NAME: utlload
-- PURPOSE: generic loader code to load delimeted text into tables
-- in a similar manner as SQLLOADER
--
-- NOTES:
-- minimum requirements are:
-- * properly configured utl_file dir
-- * delimeted file or fixed width
-- * tablename to load into
--
-- PARAMETERS and other stuff
-- p_table = table name to insert into
-- p_filename = delimeted source file to load from
-- p_delimeter = delimeter; default = ","; entering
-- delimeter of FIX assumes fixed width columns
-- p_cols = string of columns to load in the order they
-- appear in the text file; separate each col
-- by any standard delimeter (COMMA,SEMI,FWDSLASH,
-- BACKSLASH, or PERIOD); in absence of this
-- param, code reads USER_TAB_COLUMNS to get the
-- columns and ordering; also, if a " is found in the
-- 1st position of any col, that col is assumed
-- to be quoted (ie. "hello",world);
--
--
-- if p_delimeter = FIX then format for column entry
-- is columnname#length,colname#length,...
-- (ex. name#20,address#50,)
-- p_commitrows = commit after each x rows processed defaults to 100
-- p_where = optional param to identify the rows to be
-- inserted based on a column value in the source file;
-- ex: name = BILL and amount > 100
--
-- errors
-- doesnt really handle errors other than what oracle
-- kicks out
--
-- future enhancements???
-- better error handling, input, masking, control file,
-- output dir for results, etc... all similar to sqlloader
-- not so obvious: nested quoting (ie. """hello""","there"
-- will not properly load as this code doesnt look for nests)
--
--
-- MODIFICATION HISTORY
-- PERSON DATE COMMENTS
-- ---------- ---------- ------------------------------------------
-- joel 7/22/02 initial development
-- joel 7/26/02 handle multiple columns in where clause
-- and fixed width column files
--===========================================================================

(
p_table IN VARCHAR2
,p_filename IN VARCHAR2
,p_delimeter IN VARCHAR2 DEFAULT ','
,p_cols IN VARCHAR2 DEFAULT NULL
,p_commitrows IN INTEGER DEFAULT 100
,p_where IN VARCHAR2 DEFAULT NULL
) IS
-----FILE HANDLING STUFF-----
v_filehandle UTL_FILE.file_type;
v_filedir VARCHAR2 (2000);
v_filename VARCHAR2 (80);
-----
v_intval BINARY_INTEGER;
v_strval VARCHAR2 (256);
v_partyp BINARY_INTEGER;
----- PROCESSING STUFF -----
--
-- convert tabs and CRs to spaces in where clause
--
v_where VARCHAR2 (400)
:= LTRIM (
RTRIM (
REPLACE (
TRANSLATE (p_where, CHR (32) || CHR (10) || CHR (9), '~~~')
,'~'
,CHR (32)
)
)
)
|| CHR (32);
v_wherebuf VARCHAR2 (400);
v_wherecnt INTEGER := 0;

TYPE t_wherecols IS RECORD (
v_wherecol VARCHAR2 (100)
,v_whereopr VARCHAR2 (20)
,v_whereval VARCHAR2 (400)
,v_wheresqlopr VARCHAR2 (20)
,v_wherepos INTEGER
);

TYPE pltab_wherecols IS TABLE OF t_wherecols
INDEX BY BINARY_INTEGER;

r_wherecols pltab_wherecols;
-----
v_colcnt INTEGER := 0;
v_colbuf VARCHAR2 (4000);
v_col VARCHAR2 (200);

TYPE t_fixedlencol IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;

v_flen t_fixedlencol;
v_fixed CHAR (3) := UPPER (SUBSTR (p_delimeter, 1, 3));
-----
v_insok BOOLEAN := TRUE ;
v_insrows INTEGER := 0;
v_linebuf VARCHAR2 (32000);
v_sublen INTEGER := 0;
v_subbuf VARCHAR2 (32000);
v_sqt INTEGER := 0;
v_eqt INTEGER := 0;
-----
dqt CHAR := '"';
sqt CHAR := '''';
com CHAR := ',';
lpar CHAR := '(';
rpar CHAR := ')';
sp CHAR := ' ';
----- DYNAMIC SQL STUFF -----
cursid INTEGER;
cexec INTEGER;
v_stmt1 VARCHAR2 (16000);
v_stmt2 VARCHAR2 (16000);

CURSOR c_tabcols IS
SELECT column_name
FROM USER_TAB_COLUMNS
WHERE table_name = p_table
ORDER BY column_id;

---------------
PROCEDURE closefile IS
BEGIN
UTL_FILE.fclose (v_filehandle);
END;
---------------

BEGIN

------------------------------------
---------- GET UTL_FILE DIR INFO
------------------------------------
BEGIN
v_partyp := DBMS_UTILITY.get_parameter_value (
'utl_file_dir'
,v_intval
,v_strval
);

IF v_strval IS NOT NULL THEN
v_filedir := LTRIM (RTRIM (v_strval));
ELSE
v_filedir := '.';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_filedir := '.';
END;

----------------------------------------
----- open file and start process
----------------------------------------
v_filename := NVL (p_filename, v_filename);
v_filehandle := UTL_FILE.fopen (v_filedir, v_filename, 'r', 32767);

----------------------------------------
----- parse where clause
----- format MUST BE
----- COLUMN oper VALUE sqlopr
----- ex: amount = 10 and
----- amount > 99
----- (supports AND sqlopr only; sqlopr
----- is ignored and AND always used)
----------------------------------------

-----
----- strip multiple spaces from where clause
-----
LOOP
EXIT WHEN INSTR (v_where, sp || sp) = 0;
v_where := REPLACE (v_where, sp || sp, sp);
END LOOP;

r_wherecols.DELETE;
r_wherecols (v_wherecnt + 1) := NULL;

IF v_where IS NOT NULL THEN
v_wherecnt := 1;

FOR i IN 1 .. LENGTH (v_where) LOOP
v_wherebuf := v_wherebuf || SUBSTR (v_where || sp, i, 1);

IF SUBSTR (v_where, i, 1) = sp
AND LTRIM (v_wherebuf) IS NOT NULL THEN
IF r_wherecols (v_wherecnt).v_wherecol IS NULL THEN
r_wherecols (v_wherecnt).v_wherecol :=
LTRIM (RTRIM (v_wherebuf));
ELSIF r_wherecols (v_wherecnt).v_whereopr IS NULL THEN
r_wherecols (v_wherecnt).v_whereopr :=
LTRIM (RTRIM (v_wherebuf));
ELSIF r_wherecols (v_wherecnt).v_whereval IS NULL THEN
r_wherecols (v_wherecnt).v_whereval :=
LTRIM (RTRIM (v_wherebuf));
ELSIF r_wherecols (v_wherecnt).v_wheresqlopr IS NULL THEN
r_wherecols (v_wherecnt).v_wheresqlopr :=
LTRIM (RTRIM (v_wherebuf));
v_wherecnt := v_wherecnt + 1;
r_wherecols (v_wherecnt) := NULL;
END IF;

v_wherebuf := NULL;
END IF;
END LOOP;
END IF;

----------------------------------------
----- parse col names
----------------------------------------
v_stmt1 := 'insert into ' || p_table || lpar;
v_colcnt := 1;

IF p_cols IS NOT NULL THEN
-----
----- use this if cols are passed in
-----
FOR i IN 1 .. LENGTH (p_cols) + 1 LOOP
IF SUBSTR (p_cols || sp, i, 1) IN (',', ';', '/', '', '.')
OR i = LENGTH (p_cols) + 1 THEN
IF LTRIM (v_colbuf) IS NOT NULL THEN
v_col := LTRIM (
RTRIM (
REPLACE (
TRANSLATE (
LTRIM (RTRIM (v_colbuf))
,',;/.'
,'~~~~~'
)
,'~'
,sp
)
)
);

IF v_fixed = 'FIX' THEN
v_flen (v_colcnt) := SUBSTR (v_col, INSTR (v_col, '#') + 1);
v_col := SUBSTR (v_col, 1, INSTR (v_col, '#') - 1);
END IF;

-----
----- store the where clause position
-----
FOR j IN 1 .. v_wherecnt LOOP
IF v_col = r_wherecols (j).v_wherecol THEN
r_wherecols (j).v_wherepos := v_colcnt;
END IF;
END LOOP;

-----
----- add this col to the insert command
-----
v_stmt1 := v_stmt1 || v_col || com;
v_colcnt := v_colcnt + 1;
END IF;

v_colbuf := NULL;
END IF;

v_colbuf := v_colbuf || SUBSTR (p_cols || sp, i, 1);
END LOOP;
ELSE
-----
----- use this if cols not passed in
-----
FOR i IN c_tabcols LOOP
-----
----- store the where clause position
-----
FOR j IN 1 .. v_wherecnt LOOP
IF i.column_name = r_wherecols (j).v_wherecol THEN
r_wherecols (j).v_wherepos := i.column_name;
END IF;
END LOOP;

-----
----- add this col to the insert command
-----
v_stmt1 := v_stmt1 || i.column_name || com;
v_colcnt := v_colcnt + 1;
END LOOP;
END IF;

v_stmt1 := SUBSTR (v_stmt1, 1, LENGTH (v_stmt1) - 1);
v_stmt1 := v_stmt1 || rpar || ' values ' || lpar;
cursid := DBMS_SQL.open_cursor;

---------------------------------------
----- process input file
---------------------------------------
LOOP
BEGIN
UTL_FILE.get_line (v_filehandle, v_linebuf);
v_sublen := 0;
v_insok := TRUE ;

FOR i IN 1 .. v_colcnt - 1 LOOP
v_linebuf := LTRIM (v_linebuf);

IF NVL (v_fixed, 'x') != 'FIX' THEN
IF SUBSTR (v_linebuf, 1, 1) = dqt THEN
v_sqt := INSTR (v_linebuf, dqt, 1, 1);
v_eqt := INSTR (v_linebuf, dqt, v_sqt + 1, 1);
v_sublen := INSTR (v_linebuf, p_delimeter, v_eqt, 1);
v_subbuf := SUBSTR (v_linebuf, v_sqt + 1, v_eqt - 2);
ELSE
v_sublen := INSTR (v_linebuf, p_delimeter, 1, 1);

IF v_sublen < 1 THEN
v_sublen := 32000;
END IF;

v_subbuf := SUBSTR (v_linebuf, 1, v_sublen - 1);
END IF;
ELSIF v_fixed = 'FIX' THEN
v_subbuf := SUBSTR (v_linebuf, 1, v_flen (i));
END IF;

FOR j IN 1 .. v_wherecnt LOOP
IF (i = r_wherecols (j).v_wherepos)
AND ( ( r_wherecols (j).v_whereopr IN ('=')
AND r_wherecols (j).v_whereval <> v_subbuf
)
OR ( r_wherecols (j).v_whereopr IN ('!=', '<>')
AND r_wherecols (j).v_whereval = v_subbuf
)
OR ( r_wherecols (j).v_whereopr IN ('<')
AND r_wherecols (j).v_whereval >= v_subbuf
)
OR ( r_wherecols (j).v_whereopr IN ('>')
AND r_wherecols (j).v_whereval <= v_subbuf
)
OR ( r_wherecols (j).v_whereopr IN ('<=')
AND r_wherecols (j).v_whereval > v_subbuf
)
OR ( r_wherecols (j).v_whereopr IN ('>=')
AND r_wherecols (j).v_whereval < v_subbuf
)
) THEN
v_insok := FALSE ;
EXIT;
END IF;
END LOOP;

IF v_insok = FALSE THEN
EXIT;
ELSE
v_stmt2 := v_stmt2
|| sqt
|| REPLACE (v_subbuf, sqt, sqt || sqt)
|| sqt
|| com;
v_linebuf := SUBSTR (v_linebuf, v_sublen + 1);
END IF;
END LOOP;

-----
----- insert the text
-----
IF v_insok = TRUE THEN
v_stmt2 := SUBSTR (v_stmt2, 1, LENGTH (v_stmt2) - 1) || rpar;
DBMS_SQL.Parse (cursid, v_stmt1 || v_stmt2, DBMS_SQL.native);
cexec := DBMS_SQL.EXECUTE (cursid);
v_insrows := v_insrows + 1;

IF v_insrows >= p_commitrows THEN
COMMIT;
v_insrows := 0;
END IF;
END IF;

v_stmt2 := NULL;
EXCEPTION
WHEN UTL_FILE.read_error THEN
DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
EXIT;
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
EXIT;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line ('EOF');
EXIT;
WHEN UTL_FILE.invalid_path THEN
DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
EXIT;
WHEN UTL_FILE.invalid_filehandle THEN
DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
EXIT;
WHEN UTL_FILE.invalid_mode THEN
DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
EXIT;
WHEN UTL_FILE.invalid_operation THEN
DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
EXIT;
WHEN UTL_FILE.internal_error THEN
DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
END;
END LOOP;

DBMS_SQL.close_cursor (cursid);
closefile;
COMMIT;
END;
--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__--__

Report message to a moderator