CREATE OR REPLACE PROCEDURE Xml_Insert (infile In varchar2)
As
Vinput Varchar2 (1000);
Nstart Number := 1;
Nocr Number := 2;
Nlen Number;
Vextract Varchar2 (1000);
Nprv Number := 0;
TYPE Rec Is RECORD (
Field Varchar2 (100),
Val Varchar2 (1000)
);
TYPE Tarray Is Table Of Rec
Index By Binary_integer;
Ary Tarray;
Ncount Number := 1;
Vfield Varchar2 (100);
Vval Varchar2 (1000);
Vinsert Varchar2 (2000);
Ifile Utl_file.File_type;
BEGIN
--Dbms_Output.enable (20000);
Ifile := Utl_File.fopen ('c:', infile, 'r', 1000);
-- SKIP FIRST LINE
Utl_File.get_Line (Ifile, Vinput);
LOOP
BEGIN
Utl_File.get_Line (Ifile, Vinput);
Nlen := Length (Rtrim(Vinput));
Nstart := 1;
Nocr := 2;
Nprv := 0;
ARY.Delete;
NCOUNT := 1;
IF NLEN > 0 Then
LOOP
Vextract := Substr (Vinput, Nstart, Instr (Vinput, '>', Nstart, Nocr) - Nprv);
BEGIN
-- Dbms_Output.put_Line (Vextract);
Ary (Ncount).Field := Substr (Vextract, 2, Instr (Vextract, '>') - 2);
Ary (Ncount).Val :=
Substr (Vextract,
Instr (Vextract, '>') + 1,
Instr (Vextract, '<', Instr (Vextract, '>') + 1, 1)
- Instr (Vextract, '>')
- 1
);
Ncount := Ncount + 1;
EXCEPTION
When No_Data_Found Then
Null;
When Others
Then
RAISE;
END;
Nprv := Instr (Vinput, '>', Nstart, Nocr);
Nstart := Instr (Vinput, '>', Nstart, Nocr) + 1;
IF Nstart >= Nlen
Then
EXIT;
END IF;
END LOOP;
BEGIN
Vinsert := 'insert into xmltbl (';
For I In 1 .. Ary.Count
LOOP
Vinsert := Vinsert || Ary (I).Field || ',';
END LOOP;
Vinsert := Rtrim (Vinsert, ',') || ') Values (';
For I In 1 .. Ary.Count
LOOP
Vinsert := Vinsert || Chr (39) || Ary (I).Val || Chr (39) || ',';
END LOOP;
Vinsert := Rtrim (Vinsert, ',') || ')';
-- Dbms_Output.put_Line (Vinsert);
EXECUTE Immediate (Vinsert);
COMMIT;
END;
END IF;
EXCEPTION
When No_Data_Found Then
IF Utl_File.is_Open (Ifile)
Then
Utl_File.fclose (Ifile);
END IF;
EXIT;
When Others
Then
IF Utl_File.is_Open (Ifile)
Then
Utl_File.fclose (Ifile);
END IF;
--Dbms_Output.put_Line (Vinsert);
RAISE;
END;
END LOOP;
--- process insert stmt thrg array -----
END;
/