1、首先建立DIRECTORY 并赋予权限
2、注意PGA的大小 游标 PL/SQL要存放里面 要不要抛错
------------------------------------------------------------------------------------
set serverout on
DECLARE
TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
handle UTL_FILE.FILE_TYPE;
start_time NUMBER(10);
end_time NUMBER(10);
BEGIN
start_time:=dbms_utility.get_time;
SELECT * BULK COLLECT INTO emp_table FROM test;
FOR i IN 1..emp_table.COUNT LOOP
handle:=utl_file.fopen('DIR_DUMP','&OutFile_Name','a');
utl_file.put_line(handle,'INSERT INTO TEST (ename,job,sal) VALUES ('||chr(39)||emp_table(i).ename||chr(39)||','||chr(39)||emp_table(i).job||chr(39)||','||chr(39)||emp_table(i).sal||chr(39)||')');
utl_file.fclose(handle);
END LOOP;
end_time:=dbms_utility.get_time;
dbms_output.put_line('Time Total(sec):' || to_char((end_time-start_time)/100));
END;
/
转载于:https://blog.51cto.com/lovekde/308327