create or replace directory utlfile as 'D:\Tencent';
grant read,write on directory utlfile to dsp;
grant execute on utl_file to dsp;
create or replace procedure read_demo(file_name in varchar2)
is
v_file utl_file.file_type;
v_newline varchar2(300);
begin
v_file:=utl_file.fopen(utlfile,file_name,'r');
if utl_file.is_open(v_file) then
loop
begin
utl_file.get_line(v_file,v_newline);
if v_newline is null then
exit;
end if;
insert into dtcda01(member_alias,parent_member_id) values(file_name,v_newline);
end;
end loop;
end if;
commit;
end;
--declare fname varchar2(66):='dd.txt';
begin
read_demo('dd.txt');
end;
grant read,write on directory utlfile to dsp;
grant execute on utl_file to dsp;
create or replace procedure read_demo(file_name in varchar2)
is
v_file utl_file.file_type;
v_newline varchar2(300);
begin
v_file:=utl_file.fopen(utlfile,file_name,'r');
if utl_file.is_open(v_file) then
loop
begin
utl_file.get_line(v_file,v_newline);
if v_newline is null then
exit;
end if;
insert into dtcda01(member_alias,parent_member_id) values(file_name,v_newline);
end;
end loop;
end if;
commit;
end;
--declare fname varchar2(66):='dd.txt';
begin
read_demo('dd.txt');
end;
read_demo('c:\dd.txt');
end;
CREATE OR REPLACE PROCEDURE READ_DEMO(FILE_NAME IN VARCHAR2) IS
V_FILE UTL_FILE.FILE_TYPE;
V_NEWLINE VARCHAR2(300);
BEGIN
V_FILE := UTL_FILE.FOPEN('UTLFILE', FILE_NAME, 'r'); IF UTL_FILE.IS_OPEN(V_FILE) THEN LOOP
BEGIN
UTL_FILE.GET_LINE(V_FILE, V_NEWLINE);
INSERT INTO DTCDA01
(MEMBER_ALIAS, PARENT_MEMBER_ID)
VALUES
(FILE_NAME, V_NEWLINE);
EXCEPTION --跳出循环错误,用no_date_found 跳出循环
WHEN no_data_found THEN
EXIT;
END;
END LOOP; END IF;
COMMIT;END;