To create a file, we need to create a directory and have the read write permission as
1) create or replace directory MYCSV as '/home/oracle/mycsv';
Note: /home/oracle/mycsv has to be physical location on disk.
2) grant read, write on directory MYCSV to scott;
Following is the pl/sql sample code to create CSV file
DECLARE
F UTL_FILE.FILE_TYPE;
CURSOR C1 IS SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;
C1_R C1%ROWTYPE;
BEGIN
F := UTL_FILE.FOPEN('MYCSV','EMP_DEPT.CSV','w',32767);
FOR C1_R IN C1
LOOP
UTL_FILE.PUT(F,C1_R.EMPNO);
UTL_FILE.PUT(F,','||C1_R.ENAME);
UTL_FILE.PUT(F,','||C1_R.SAL);
UTL_FILE.PUT(F,','||C1_R.DEPTNO);
UTL_FILE.PUT(F,','||C1_R.DNAME);
UTL_FILE.NEW_LINE(F);
END LOOP;
UTL_FILE.FCLOSE(F);
END;
/
After the execution of above procedure, a file (EMP_DEPT.CSV) would have been created at "/home/oracle/mycsv/" location.
You may check it on linux by
cd /home/oracle/mycsv
cat EMP_DEPT.CSV
7369,SMITH,800,20,RESEARCH
7499,ALLEN,1600,30,SALES
7521,WARD,1250,30,SALES
7566,JONES,2975,20,RESEARCH
7654,MARTIN,1250,30,SALES
7698,BLAKE,2850,30,SALES
7782,CLARK,2450,10,ACCOUNTING
7788,SCOTT,3000,20,RESEARCH
7839,KING,5000,10,ACCOUNTING
7844,TURNER,1500,30,SALES
7876,ADAMS,1100,20,RESEARCH
7900,JAMES,950,30,SALES
7902,FORD,3000,20,RESEARCH
7934,MILLER,1300,10,ACCOUNTING
Related Post:
- Load CSV file in Oracle using PL/SQL
- Create External Table from CSV File
- Create XML file using PL/SQL
- Load XML File in Oracle Table
- Generate XML Output from SQL
- Generate XML data using SQL in Oracle Database
- Convert XML to Rows and Columns in Oracle
it is a neat & clean example. thanks !!!
ReplyDeleteawesome. Thanks.
ReplyDeleteThanx for appreciation, keep visiting for new & useful articles
ReplyDeleteThanks for this. You might want to insert few comments on the code e.g C1_R C1%ROWTYPE; // what's this doing
ReplyDeleteC1_R C1%ROWTYPE is declaration for a record having fields of cursor C1
DeleteHELLO
DeleteI WANT TO CREATE A CSV FILE FROM A FUNCTION , IS IT POSSIBLE
This comment has been removed by the author.
ReplyDeletethere's no need to declare that rowtype
ReplyDeleteC1_R C1%ROWTYPE; not required
ReplyDeletegood good good.
ReplyDeletevery good work
ReplyDeleteThnks
ReplyDelete1) create or replace directory MYCSV as '/home/oracle/mycsv';
ReplyDeleteNote: /home/oracle/mycsv has to be physical location on disk.
2) grant read, write on directory MYCSV to scott; ---->>>>
how do u do this ?
execute these commands using DBA privileged user (SYS or SYSTEM).
DeleteKool.
DeleteWhat if i need the column names too for each of the columns.
hi
ReplyDeleteI have an issue..
Database server is configured in other country, i have all DML privilages, some times i need to take the backup of some tables few row. so can i take the backup on my personal computer as CSV before any update ...?
better use export
DeleteThanx very usefull !!
ReplyDeleteHi,
ReplyDeleteThanks for the post very informative.
What if I want to export all the columns from my table to CSV file? Is there a work around for this or I have to specify each column name in the loop?
yes, you need to.
Deletehow can we apply column type?
ReplyDeleteNice & Simple Example ...
ReplyDeleteThank You,
Shri
INVALID DIRECTORY PATH ERROR WHAT CAN I DO ?
ReplyDeleteI wanted to create export csv file using plsql function, how can it be done
ReplyDeleteI have created csv file with above procedure.Now I want to create four csv file from this csv file with 5 records in each csv file.
ReplyDeleteAnybody know how to create?
Hi Sir, I have created in SQL developer in windows its is successfully executed but unable to find the CSV file , directory and plsql has no issue
ReplyDeleteThanks you so much.
ReplyDeleteRegards,
Jagan
Thank you so much.
ReplyDeleteRegards,
Jagan
Thanks for sharing such worthy content, this information is useful for knowledge seekers. Waiting for a more upcoming post like this.
ReplyDeleteGraphic Designer Roles
What Can Graphic Designers Do