Oracle外部表:首先创建一个目录对象,在SYSTEM下: SQL>CREATE OR REPLACE DIRECTORY tmp AS 'C:\TEMP';在c:\temp下建立一个文件文件,如dept.dat,内容如下:10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia1 50,Sales1,Virginia 60,Accounting1,Virginia 70,Consulting1,Virginia 80,Finance1,Virginia1 90,Sales9,Virginia 91,Accounting9,Virginia 92,Consulting9,Virginia 9x,Finance9,Virginia1建立外部表,在system下CREATE TABLE mydept ( "DEPTNO" NUMBER(2), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY tmp ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE 'loader2.bad' LOGFILE 'loader2.log' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM REJECT ROWS WITH ALL NULL FIELDS ( "DEPTNO" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "DNAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "LOC" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ) ) location ( 'dept.dat' ) )REJECT LIMIT UNLIMITED 执行查询:SELECT * FROM mydept;
... 2楼的看不是很懂 read demo create table test ( fld1 VARCHAR2(20), fld2 VARCHAR2(20));CREATE OR REPLACE PROCEDURE read_demo(file_name VARCHAR2) IS vSFile utl_file.file_type; vNewLine VARCHAR2(200); BEGIN vSFile := utl_file.fopen('ORALOAD', file_name,'r'); IF utl_file.is_open(vSFile) THEN LOOP BEGIN utl_file.get_line(vSFile, vNewLine); IF vNewLine IS NULL THEN EXIT; END IF; INSERT INTO test (fld1, fld2) VALUES (vNewLine, file_name); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; COMMIT; END IF; utl_file.fclose(vSFile); utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', 'x.txt', TRUE); EXCEPTION WHEN utl_file.invalid_mode THEN RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter'); WHEN utl_file.invalid_path THEN RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location'); WHEN utl_file.invalid_filehandle THEN RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle'); WHEN utl_file.invalid_operation THEN RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation'); WHEN utl_file.read_error THEN RAISE_APPLICATION_ERROR (-20055, 'Read Error'); WHEN utl_file.internal_error THEN RAISE_APPLICATION_ERROR (-20057, 'Internal Error'); WHEN utl_file.charsetmismatch THEN RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent'); WHEN utl_file.file_open THEN RAISE_APPLICATION_ERROR (-20059, 'File Already Opened'); WHEN utl_file.invalid_maxlinesize THEN RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K'); WHEN utl_file.invalid_filename THEN RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name'); WHEN utl_file.access_denied THEN RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By'); WHEN utl_file.invalid_offset THEN RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0'); WHEN others THEN RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error'); END read_demo; /谁解释下
create table test ( fld1 VARCHAR2(20), fld2 VARCHAR2(20));CREATE OR REPLACE PROCEDURE read_demo(file_name VARCHAR2) IS vSFile utl_file.file_type; vNewLine VARCHAR2(200); BEGIN --打开ORALOAD目录下的file_name文件 vSFile := utl_file.fopen('ORALOAD', file_name,'r'); --判断是否打开成功 IF utl_file.is_open(vSFile) THEN --循环读取文件内每一行的内容 LOOP BEGIN utl_file.get_line(vSFile, vNewLine); IF vNewLine IS NULL THEN EXIT; END IF; --将读取的内容插入到表中 INSERT INTO test (fld1, fld2) VALUES (vNewLine, file_name); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; END LOOP; COMMIT; END IF; --关闭文件 utl_file.fclose(vSFile); utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', 'x.txt', TRUE); --捕获各种可能出现的异常,如:路径不正确,文件格式无法识别,文件打开失败等等 EXCEPTION WHEN utl_file.invalid_mode THEN RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter'); WHEN utl_file.invalid_path THEN RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location'); WHEN utl_file.invalid_filehandle THEN RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle'); WHEN utl_file.invalid_operation THEN RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation'); WHEN utl_file.read_error THEN RAISE_APPLICATION_ERROR (-20055, 'Read Error'); WHEN utl_file.internal_error THEN RAISE_APPLICATION_ERROR (-20057, 'Internal Error'); WHEN utl_file.charsetmismatch THEN RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent'); WHEN utl_file.file_open THEN RAISE_APPLICATION_ERROR (-20059, 'File Already Opened'); WHEN utl_file.invalid_maxlinesize THEN RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K'); WHEN utl_file.invalid_filename THEN RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name'); WHEN utl_file.access_denied THEN RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By'); WHEN utl_file.invalid_offset THEN RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0'); WHEN others THEN RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error'); END read_demo; /
utl_file.frename这个是对文件重命名
首先我们得先建立一个 ORACLE的目录对象 指向 C:\create or replace directory DIR as 'C:\';--然后我们对这个目录对象进行授权 其实这步可以忽略grant read, write on directory DIR to 用户;--以上前奏完成了! 我们可以写PLSQL 进行操作文件了declare isto_file utl_file.file_type; --定义变量的类型为utl_file.file_type begin isto_file := utl_file.fopen('DIR', 'test.txt', 'W'); --指定为DIR 目录下面的test.txt文件写操作 utl_file.put_line(file, 'hello,world!'); --写入字符串 utl_file.fflush(file); --刷缓冲 utl_file.fclose(file); --关闭文件指针 end;--下面我们可以使用读操作把写入的文件内容读出来declare isto_file utl_file.file_type; --如上 fp_buffer varchar2(4000); --没必要说了吧? begin isto_file := utl_file.fopen('DIR', 'test.txt', 'R'); -- 指定为读操作 utl_file.get_line (file , fp_buffer ); --读取一行放到 fp_buffer 变量里面 dbms_output.put_line(fp_buffer);--在终端输出结果看看 utl_file.fclose(file); --关闭文件指针 end;执行这段的话 出先文件操作无效的提示...
先说下我的环境是9i windows 的 grant execute on utl_file to user的时候会出错 提示无权限 我是用的wys操作的 wys默认是dba权限的
1、到处为文件: sys登陆下 CREATE DIRECTORY D_OUTPUT AS 'E:';创建目录 grant read, write on directory DIR to 用户 操作用户下 CREATE OR REPLACE PROCEDURE emp_test IS fileID UTL_FILE.FILE_TYPE; BEGIN fileID := UTL_FILE.FOPEN ('D_OUTPUT', 'test.txt', 'W',32767);--目录(路径)、文件名、操作类型(A为追加)、字符大小(32767最大) /* Quick and dirty construction here! */ FOR emprec IN (SELECT area_code,area_name FROM tb_area) LOOP UTL_FILE.PUT_LINE(fileID,to_char(emprec.area_code)||','||emprec.area_name,true); END LOOP; UTL_FILE.FCLOSE (fileID); END;2、导入库 建议用sqlldr.utl_file读写文件相当慢
SQL>CREATE OR REPLACE DIRECTORY tmp AS 'C:\TEMP';在c:\temp下建立一个文件文件,如dept.dat,内容如下:10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia1
50,Sales1,Virginia
60,Accounting1,Virginia
70,Consulting1,Virginia
80,Finance1,Virginia1
90,Sales9,Virginia
91,Accounting9,Virginia
92,Consulting9,Virginia
9x,Finance9,Virginia1建立外部表,在system下CREATE TABLE mydept
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY tmp
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE 'loader2.bad'
LOGFILE 'loader2.log'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"DNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"LOC" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'dept.dat'
)
)REJECT LIMIT UNLIMITED
执行查询:SELECT * FROM mydept;
2楼的看不是很懂
read demo
create table test (
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));CREATE OR REPLACE PROCEDURE read_demo(file_name VARCHAR2) IS
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
BEGIN
vSFile := utl_file.fopen('ORALOAD', file_name,'r'); IF utl_file.is_open(vSFile) THEN
LOOP
BEGIN
utl_file.get_line(vSFile, vNewLine); IF vNewLine IS NULL THEN
EXIT;
END IF; INSERT INTO test
(fld1, fld2)
VALUES
(vNewLine, file_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
utl_file.fclose(vSFile);
utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', 'x.txt', TRUE);
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END read_demo;
/谁解释下
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));CREATE OR REPLACE PROCEDURE read_demo(file_name VARCHAR2) IS
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
BEGIN
--打开ORALOAD目录下的file_name文件
vSFile := utl_file.fopen('ORALOAD', file_name,'r');
--判断是否打开成功
IF utl_file.is_open(vSFile) THEN
--循环读取文件内每一行的内容
LOOP
BEGIN
utl_file.get_line(vSFile, vNewLine); IF vNewLine IS NULL THEN
EXIT;
END IF;
--将读取的内容插入到表中
INSERT INTO test
(fld1, fld2)
VALUES
(vNewLine, file_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
--关闭文件
utl_file.fclose(vSFile);
utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', 'x.txt', TRUE);
--捕获各种可能出现的异常,如:路径不正确,文件格式无法识别,文件打开失败等等
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END read_demo;
/
isto_file utl_file.file_type; --定义变量的类型为utl_file.file_type
begin
isto_file := utl_file.fopen('DIR', 'test.txt', 'W'); --指定为DIR 目录下面的test.txt文件写操作
utl_file.put_line(file, 'hello,world!'); --写入字符串
utl_file.fflush(file); --刷缓冲
utl_file.fclose(file); --关闭文件指针
end;--下面我们可以使用读操作把写入的文件内容读出来declare
isto_file utl_file.file_type; --如上
fp_buffer varchar2(4000); --没必要说了吧?
begin
isto_file := utl_file.fopen('DIR', 'test.txt', 'R'); -- 指定为读操作
utl_file.get_line (file , fp_buffer ); --读取一行放到 fp_buffer 变量里面
dbms_output.put_line(fp_buffer);--在终端输出结果看看
utl_file.fclose(file); --关闭文件指针
end;执行这段的话
出先文件操作无效的提示...
授权成功。已连接。
已连接。
declare
*
ERROR 位于第 1 行:
ORA-29283: 文件操作无效
ORA-06512: 在"SYS.UTL_FILE", line 449
ORA-29283: 文件操作无效
ORA-06512: 在line 5
declare
*
ERROR 位于第 1 行:
ORA-29283: 文件操作无效
ORA-06512: 在"SYS.UTL_FILE", line 449
ORA-29283: 文件操作无效
ORA-06512: 在line 4警告: 创建的过程带有编译错误。declare
*
ERROR 位于第 1 行:
ORA-29283: 文件操作无效
ORA-06512: 在"SYS.UTL_FILE", line 449
ORA-29283: 文件操作无效
ORA-06512: 在line 4
http://topic.csdn.net/u/20080422/15/9a8c3fe3-036e-48f5-aab2-2d08a3a68fe4.html
grant execute on utl_file to user的时候会出错
提示无权限
我是用的wys操作的
wys默认是dba权限的
sys登陆下
CREATE DIRECTORY D_OUTPUT AS 'E:';创建目录
grant read, write on directory DIR to 用户
操作用户下
CREATE OR REPLACE PROCEDURE emp_test
IS
fileID UTL_FILE.FILE_TYPE;
BEGIN
fileID := UTL_FILE.FOPEN ('D_OUTPUT', 'test.txt', 'W',32767);--目录(路径)、文件名、操作类型(A为追加)、字符大小(32767最大) /* Quick and dirty construction here! */
FOR emprec IN (SELECT area_code,area_name FROM tb_area)
LOOP
UTL_FILE.PUT_LINE(fileID,to_char(emprec.area_code)||','||emprec.area_name,true);
END LOOP; UTL_FILE.FCLOSE (fileID);
END;2、导入库
建议用sqlldr.utl_file读写文件相当慢