AboutElliot Mak Expertise I can answer questions with regarding to Oracle DB (8i, 9i, 10g) installation, configuration, administration, Data Guard, and SQL.
Experience I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.
Expert: Elliot Mak Date: 6/9/2008 Subject: creating a CSV file from oracle 10g database
Question QUESTION: I am using oracle 10g with unix server,what i need is to export the database into a CSV file and that file must store on server.i want to do it using packages,canyou help me out and send some demo code if possible.
I would be grateful for your quick response.
ANSWER: Good day Amit,
I have been using this code created by Mark Powell to generate CSV file from Oracle table.
This technique cannot be used against columns of BLOB, CLOB, and LONG columns.
********************************************
Code Start Here
********************************************
set echo off
rem
rem SQL*Plus script to create comma delimited output file from table
rem
rem 20000614 Mark D Powell Automate commonly done task
rem
set pagesize 0
set verify off
set feedback off
set linesize 130
accept owner prompt 'Enter table owner => '
accept tblname prompt 'Enter table name => '
spool csv2.sql
select 'select ' from sys.dual;
select decode(column_id,1,column_name,
'||'',''||'||column_name)
from sys.dba_tab_columns
where table_name = upper('&&tblname')
and owner = upper('&&owner')
order by column_id;
select 'from &&owner..&&tblname;'
from sys.dual;
spool off
undefine owner
undefine tblname
This code produces screen output like:
UT1> @csv
Enter table owner => mpowel01
Enter table name => emp
select
EMPNO
||','||ENAME
||','||JOB
||','||MGR
||','||HIREDATE
||','||SAL
||','||COMM
||','||DEPTNO
from mpowel01.emp;
This output is stored in the file csv2.sql which in turn produces a file with contents like:
UT1> @csv2
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
********************************************
Code End Here
********************************************
This can be embedded in within PL/SQL to perform a batch extract.
**This is a sample code, minor adjustment may require for different environment **
Hope this helps. Please don't hesitate to contact if you shall have any questions. I am happy to help.
Cheers
Elliot
---------- FOLLOW-UP ----------
QUESTION: Thanks Elliot for reply,your solution is fine but it won't solve my problem,because here you are using unix to create CSV file,but i require that the CSV file will be created by oracle package(i.e. UTL_FILE)on unix server.
please reply if can help me out THANKS.
ANSWER: Good day,
Tom Kyte has provided a very good example in how to perform the extract using PL/SQL that calls the UTL_FILE.
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output, l_separator ||
l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
/
You would use that for example like this:
create or replace procedure test_dump_csv
as
l_rows number;
begin
l_rows := dump_csv( 'select *
from all_users
where rownum < 25',
',', '/tmp', 'test.dat' );
end;
/
Make sure to read about the INIT.ORA parameters (utl_file_dir) you need to setup !!!
------------------------------------------
End Here
------------------------------------------
---------- FOLLOW-UP ----------
QUESTION: thanks elliot,I used your code as it is,but i am getting some errors as follows:-
BEGIN test_dump_csv; END;
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "PMSFINAL.DUMP_CSV", line 17
ORA-06512: at "PMSFINAL.TEST_DUMP_CSV", line 5
ORA-06512: at line 1
may be i have to set some parameters, but i m not sure.so please help, I am waiting for your response thankfully.
Answer Hi
It looks like the Oracle cannot access the directory specified due to the following:
1. Oracle does not have the permission to access the specified directory
2. The directory object is not created or it is not created using SYS account.
3. If you are not using directory object, then you are probably using UTL_FILE_DIR parameter in which is not set in the init.ora file.
Check out those three possibilities, if it is not, send me a copy of your code, i can look into a little bit more for you.
