我第一步是:
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 10G,用SYSTEM 登录的,可以酬谢!
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 10G,用SYSTEM 登录的,可以酬谢!
l_file :=utl_file.fopen('DIR_EXPORT',p_file_name,'w');
that's all right !
utl_file.put(l_file,job_rec.userid);
PUT
改成PUTLINE
就换行了