오라클 프로시저 엑셀파일 생성

-- 오라클 프로시저로 엑셀파일 생성

Generating Excel files using PL/SQL

Introduction:

Over the last decade or so I've written innumerable SQL queries in response to ad-hoc data requests from business users. As Excel is ubiquitous on the corporate desktop, users generally ask that the results be sent to them in an Excel file. Providing there are on ly a few datasets involved, the easiest approach is to use SQL followed by a cut-and-paste. But what if there are several datasets to be generated, each on e differing from the others on ly in a query parameter or two? It would be stupendously sub-optimal, not to mention patently painful, to use the cut-n-paste method. Fortunately the task can be done programatically using PL/SQL and the built-in UTL_FILE package. This article demonstrates how.

Preliminaries:

Let's get some preparatory work out of the way first. The demonstration uses the EMP table that comes with the SCOTT schema in Oracle. A script to create and populate all tables in the SCOTT schema can be found at <ORACLE_HOME>/sqlplus/demobld.sql, where <ORACLE_HOME> is the Oracle home (installation) directory. Note that you'll need on ly SCOTT.EMP for this demo.

You will also need a couple of system privileges (which can be granted on ly by someone with DBA privileges). The required privileges are: CREATE PROCEDURE, and the ability to read/ write to an operating system directory from within Oracle. CREATE PROCEDURE is a straightforward Oracle system privilege so no more will be said about it. Read/write privileges on an OS directory can be granted in on e of two ways. The first is to create an Oracle directory object like so:

create directory reports as 'C:\reports'

This creates an Oracle directory object called "reports" which points to the OS directory "C:\reports". The demo schema then needs to be granted access to the directory. This is done via the following SQL:

grant read, write on directory reports to scott

assuming the demo schema is SCOTT. The second (and deprecated) way to grant OS directory access is to set the oracle initialization parameter UTL_FILE_DIR to the directory name ('C:\reports' in the demo). Assuming you are using a stored parameter file (spfile), the command to do this is:

alter system set utl_file_dir='C:\reports' scope=spfile

Note that you will need to bounce (restart) the database for the new setting to take effect.

As mentioned earlier the first method is preferred, and is the on e that will be used in this demonstration.
Generating Excel files from the database:

The application requirement is as follows: for each manager in the EMP table, generate an Excel file containing a list of direct reports with name, hire date and salary. The output files should be written to c:\reports.

The solution is achieved through a PL/SQL procedure, which I'll first list and then deconstruct. Here it is:

CREATE OR REPLACE procedure print_reports is

  cursor c_mgr is
    select
      t1.ename,
      t1.empno
    from
      emp t1
    where exists
      (select
        'x'
      from
        emp t2
      where
        t1.empno=t2.mgr);

  cursor c_direct_reports (cv_mgr number) is
    select
      empno,
      ename,
      job,
      hiredate,
      sal
    from
      emp
    where
      mgr=cv_mgr;

  wfile_handle utl_file.file_type;
  v_wstring varchar2 (100);
  v_header varchar2(100);
  v_file varchar2(100);
  v_date varchar2(20);

begin

  select
    to_char(sysdate,'dd_mon_yyyy')
  into
    v_date
  from
    dual;

  v_header :='empno'||chr(9)||'ename'||chr(9)||'job'||chr(9)||'hiredate'||chr(9)||'sal';

  for r_mgr in c_mgr loop

    v_file := r_mgr.ename||'_direct_reports_'||v_date||'.xls';

    wfile_handle := utl_file.fopen ('REPORTS',v_file, 'W');

    utl_file.put_line(wfile_handle,v_header);

    for r in c_direct_reports(r_mgr.empno) loop

      v_wstring := r.empno||chr(9)||r.ename||chr(9)||r.job||chr(9)||to_char(r.hiredate,'dd/mm/yyyy')
        ||chr(9)||r.sal;

      utl_file.put_line(wfile_handle,v_wstring);

    end loop;

    utl_file.fclose (wfile_handle);

  end loop;

end print_reports;

참조싸이트 : http://blog.daum.net/won-bo/16839168