如何使用UTL_FILE解析多行文件?在下面的示例代码片段中-以"1"开头的行是File头——每个文件一个。以"5"开头的行是事务头-每个事务一个以"8"开头的行是事务拖尾——每个事务一个以'9'开头的行是文件页脚-每个文件一个。
我需要逻辑来1)读取文件行，2)能够区分事务开始和结束的位置。每一行，我知道Oracle有instr和substr来区分不同的值。
欢迎提出任何建议!谢谢你的宝贵时间。
1TreasuryPart 201610031830MEMO
53336 Bank1Base0000001
650 3100000126 1-30-00010-00 000002126100000000000000021261USD20161003RAYMOND SOLIS jane.doe@gmail.com 205-888-9900 20161003020030000001 WEBENRAPC34560000000000 WEB
800000000010000000212610000000000000000001
53336 Bank1Base0000002
800000000000000000000000000000000000000002
53343 ToddMUD 0000001
800000000000000000000000000000000000000001
53343 ToddMUD 0000002
800000000000000000000000000000000000000002
53351 DenisTM 0000001
650 3100000128 7779026 000004000000000000000000080000USD20161003Denis Pellerin jack.doe@gmail.com 405-922-2116 20161003100421000001 WEBUNEAPC34560000000000 WEB
650 3100000128 7779026 000004000000000000000000080000USD20161003Denis Pellerin jack.doe@gmail.com 405-922-2116 20161003100421000002 WEBUNEAPC34560000000000 WEB
800000000020000000800000000000000000000001
53351 DenisTM 0000002
800000000000000000000000000000000000000002
9000006000000000003000000101261000000000000
我不太确定你在找什么。发布代码总是有帮助的，这样我们就不会猜测了。我认为你会想要这样的东西，其中定义了单独的过程来解析每个单独类型的行。
declare
l_file utl_file.file_type;
l_line varchar2(4000);
l_in_transaction boolean := false;
begin
l_file := utl_file.fopen( <<parameters>> );
loop
utl_file.get_line(l_file, l_line);
if( substr(l_line,1,1) = '1' and l_in_transaction = false)
then
parse_file_header( l_line );
end if;
if( substr(l_line,1,1) = '5' and l_in_transaction = false )
then
parse_transaction_header( l_line );
l_in_transaction := true;
end if;
if( l_in_transaction = true)
then
parse_transaction_row( l_line );
end if;
if( substr(l_line,1,1) = '8' and l_in_transaction = true)
then
parse_transaction_trailer( l_line );
l_in_transaction := false;
end if;
if( substr(l_line,1,1) = '9' and l_in_transaction = false)
then
parse_file_trailer( l_line );
end if;
end loop;
exception
when no_data_found
then
utl_file.fclose( l_file );
end;