RT
oracle中如何读写磁盘文件?
并切显示出磁盘内容
文件格式不论
oracle中如何读写磁盘文件?
并切显示出磁盘内容
文件格式不论
解决方案 »
- 数据库数据复制sql语句????急
- 非归档模式下,能使用rman做完全备份,并且是热备份吗?
- .net 如何把datatable的数据保存到oracle
- oracle递归问题,寻求解决办法
- dmp备份档比前天备份增加了40G而实际增加数据只有3G
- SQL Server中的 update from 子句在Oracle中对应语法是什么?
- 怎样可以查看到以前被创建但现在已被删除的索引(Index)?急求!!!
- oracle8i怎么安装不了啊,
- Procedure中不能用create user,grant语句吗?
- orcale登录问题,菜鸟一个,请大神指点
- 急需答案。新手提问
- ejb 3.0 jpql
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读写文件相当慢