Hi,
I have a requirement where in I need to extract data from Oracle in excel file and the excel worksheet name should be "Data".
for eg. excel file name "AR Data_DDMMYY" and excel worksheet name "Data"
I have used the UTL_FILE API to extract the tab delimited data which can be opened in excel but it is not exactly an excel file as the worksheet name is same as the file name.
I tried using utl_file.fcopy and frename.
Is there any way to do this using PLSQL?
select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE 10.2.0.5.0 Production"
TNS for HPUX: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
Sample Code:
declare
cursor c is
select * from scott.emp;
v varchar2(100);
f utl_file.file_type;
file_name varchar2(100) := 'AR Data.xls';
dir varchar2(50) := 'CESDIR191710';
--select * from dba_directories
begin
f := utl_file.fopen(dir, file_name, 'W');
v := 'EMPNO'||chr(9)||'ENAME'||chr(9)||'JOB'||chr(9)||'SAL'||chr(9)||'HIREDATE'||chr(9)||'DEPTNO';
utl_file.put_line(f, v);
for i in c
loop
v := i.empno||chr(9)||i.ename||chr(9)||i.job||chr(9)||i.sal||chr(9)||i.hiredate||chr(9)||i.deptno;
utl_file.put_line(f, v);
end loop;
utl_file.fclose(f);
--utl_file.frename(dir, file_name, dir, replace(file_name, '.xls', '_')||to_char(sysdate, 'MMDDYY')||'.xls', false);
utl_file.fcopy(dir, file_name, dir, replace(file_name, '.xls', '_')||to_char(sysdate, 'MMDDYY')||'.xls');
end;
Thanks
Imran