我第一步是:
CREATE OR REPLACE DIRECTORY dir_export AS 'd:\oracle';
第二步:
GRANT read,write ON directory dir_export TO system;
第三步:
CREATE OR REPLACE PROCEDURE SP_JOBS_DATA_OUT(p_file_name varchar2)
as
TYPE job_record_type is RECORD(username dba_users.username%TYPE,
userid dba_users.user_id%TYPE);
job_rec job_record_type;
CURSOR c_jobs IS
select username||chr(9),
user_id
FROM dba_users;
l_file utl_file.file_type;
BEGIN
l_file :=utl_file.fopen('dir_export',p_file_name,'w');
utl_file.put_line(l_file,'jobs表导出数据');
OPEN c_jobs;
LOOP
FETCH c_jobs INTO job_rec.username , job_rec.userid ;
EXIT WHEN c_jobs%NOTFOUND;
utl_file.put(l_file,job_rec.username );
utl_file.put(l_file,job_rec.userid);
END LOOP;
CLOSE c_jobs;
utl_file.fflush(l_file);
utl_file.fclose(l_file);
EXCEPTION
WHEN others THEN
IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
END IF;
END;
第四步,SQL> execute SP_JOBS_DATA_OUT('test.xls')
PL/SQL procedure successfully completed
都成功就是没有产生文件,也没有报任何错误.烦请高手指教,否则要走人了.
CREATE OR REPLACE DIRECTORY dir_export AS 'd:\oracle';
第二步:
GRANT read,write ON directory dir_export TO system;
第三步:
CREATE OR REPLACE PROCEDURE SP_JOBS_DATA_OUT(p_file_name varchar2)
as
TYPE job_record_type is RECORD(username dba_users.username%TYPE,
userid dba_users.user_id%TYPE);
job_rec job_record_type;
CURSOR c_jobs IS
select username||chr(9),
user_id
FROM dba_users;
l_file utl_file.file_type;
BEGIN
l_file :=utl_file.fopen('dir_export',p_file_name,'w');
utl_file.put_line(l_file,'jobs表导出数据');
OPEN c_jobs;
LOOP
FETCH c_jobs INTO job_rec.username , job_rec.userid ;
EXIT WHEN c_jobs%NOTFOUND;
utl_file.put(l_file,job_rec.username );
utl_file.put(l_file,job_rec.userid);
END LOOP;
CLOSE c_jobs;
utl_file.fflush(l_file);
utl_file.fclose(l_file);
EXCEPTION
WHEN others THEN
IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
END IF;
END;
第四步,SQL> execute SP_JOBS_DATA_OUT('test.xls')
PL/SQL procedure successfully completed
都成功就是没有产生文件,也没有报任何错误.烦请高手指教,否则要走人了.
解决方案 »
- Oracle 存储过程 未找到要求的From关键字错误
- oracle问题
- 在oracle中如何取得UNICODE的编码
- 在oracle数据库中对Date类型的数据进行desc排序,为什么Date类型的这一列就显示不出数据来呢
- 用什么语句查询一个数据库中的表是属于哪个schema?
- 远程查询输入??50分
- 非常奇怪的问题,请大家有力气出力气,有点子出点子!谢谢!
- 那里有Oracle的Linux版本可以下载啊?
- The NetWork Adapter could not establish the connection错误!请大神指点
- oracle12c创建实例报错
- ORACLE 处理大量数据应该注意什么?
- 看到星星在向我挥手了!提前庆祝!进来者有分!而且是技术分!
l_file :=utl_file.fopen('DIR_EXPORT',p_file_name,'w');