Create CSV file using PL/SQL

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

Hello Data Templates

Hello Data Templates

By Tim Dexter-Oracle on Dec 13, 2006

Hopefully you have downloaded and installed the Data Template Test Bed I provided in my last post, you may have even loaded and run the sample I provided ... hopefully sucessfully. You may have even gone off and got started on your own. Well we are going to take a step back here and get back to basics and then move up to the more powerful stuff like filters, triggers and flexfields ... we're going to get there quickly so please be patient.

Being the consumate geek and not wanting to break tradition when it comes to starting out with a new technology we're going to build and test a Hello World data template ... now known as 'DTs' for brevity. First lets take a look at the anatomy of a DT, it'll help later.

<dataTemplate name="EMPLOYEES" defaultPackage="" description="Employee Data">
   <properties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="true"/>
      <property name="xml_tag_case" value="upper"/>
      <property name="db_fetch_size" value="500"/>
      <property name="scalable_mode" value="off"/>
      <property name="include_rowsettag" value="false"/>
      <property name="debug_mode" value="off"/>
   </properties>
   <parameters>
      <parameter name="pDeptNo" dataType="number" defaultValue=""/>
   </parameters>
   <lexicals/>
   <dataQuery>
  <sqlStatement name="Q1" dataSourceRef=""><![CDATA[SELECT DEPTNO,DNAME,LOC 
 from dept where deptno = nvl(:p_DeptNo,deptno)
 order by deptno]]>
 </sqlStatement>
  <sqlStatement name="Q2" dataSourceRef=""><![CDATA[SELECT  EMPNO,ENAME,JOB
 ,MGR,HIREDATE,SAL,nvl(COMM,0) COMM  
 from EMP 
 WHERE DEPTNO = :DEPTNO]]>
 </sqlStatement>
  </dataQuery>
   <dataStructure>
  <group name="G_DEPT" source="Q1" groupFilter="">
   <element name="DEPT_NUM" value="DEPTNO" function=""/>
   <element name="DEPT_NAME" value="DNAME" function=""/>
   <element name="LOCATION" value="LOC" function=""/>
   <group name="G_EMP" source="Q2" groupFilter="">
    <element name="EMPNO" value="EMP_NUM" function=""/>
    <element name="EMP_NAME" value="ENAME" function=""/>
    <element name="JOB_TITLE" value="JOB" function=""/>
    <element name="MANAGER" value="MGR" function=""/>
    <element name="HIRE_DATE" value="HIREDATE" function=""/>
    <element name="SALARY" value="SAL" function=""/>
    <element name="COMMISSION" value="COMM" function=""/>
   </group>
  </group>
 </dataStructure>
</dataTemplate> 

I have highlighted the main players in a DT:

• dataTemplate - header element where you provide a name and description for your DT. The name will be used as the root name of the element. 
• properties - these are the runtime switches for the extraction engine:
  
  
  
  • include_parameters - should the parameter values be extracted to the XML or not 
  • include_null_Element - if a value when extracted is null do you want an empty element in the xml 
  • xml_tag_case - upper or lower case tag names 
  • db_fetch_size - maximum number of records to fetch 
  • scalable_mode - if you know this is going to be a large dataset or an intensive extract then set this to true 
  • include_rowsettag - for simple XML with no hierarchy do you want ROWSET as the root element 
  • debug_mode - generate a debug file while processing or not. The test bed I posted will always do this for you.

Moving into the main section:

• parameters - these are the runtime parameters for the DT 
• lexicals - these are EBS specific and will allow you to fetch flexfield values ... more on these later 
• dataQuery - heres where we start to define the
  sqlStatement - this is where we define the query(s) 
• group - this section defines the structure of the data we want to see and contains 
• element - the reference to the column in a specific query. These also have a name for the resulting XML element and you can include functions here.

So thats a quick overview, lets do the HelloWorld ... its as simple as it gets when it comes to DTs.

<dataTemplate name="HELLOWORLD" defaultPackage="" description="Hello World DT">
   <properties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="true"/>
      <property name="xml_tag_case" value="upper"/>
      <property name="db_fetch_size" value="500"/>
      <property name="scalable_mode" value="off"/>
      <property name="include_rowsettag" value="false"/>
      <property name="debug_mode" value="off"/>
   </properties>
   <parameters/>
   <lexicals/>
   <dataQuery>
  <sqlStatement name="Q1" dataSourceRef=""><![CDATA[select 'Hello World!' WELCOME from dual]]></sqlStatement>
 </dataQuery>
   <dataStructure>
  <group name="HELLO" source="Q1">
   <element name="WELCOME" value="WELCOME"/>
  </group>
   </dataStructure>
</dataTemplate>

Running it thru the test bed, this is going to generate the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<HELLOWORLD>
 <HELLO>
  <WELCOME>Hello World!</WELCOME>
 </HELLO>
</HELLOWORLD>

Simple stuff, you can see the relationship now between the DT name - HELLOWORLD, group - HELLO and element name WELCOME.
So thats the basics, next we'll build on the HR schema and introduce some more features.

Defining Business Group in Oracle apps

Defining Business group
==========================

HRMS -> WorkStucture -> Organization -> Description

Name,type -Business Group
Date From
Location,Loc Addr,Internal/External,
Org Classification-Business Group,check the check box.

Clikc on Other button-->select business group info
Ente All the values
Shortname
Emp Num Genereation
applicatgion number generation
contingent work number
grade flex field stuc
grooup FF struc
job ff str
costing ff str
position ff st
comeptence ff st
legislation code
curreny
fiscal year start
mim working age -18
max working age - 56
clikc on OK and save the record
=================