A recently made project, which has a function of exporting data as an Excel file. I used Oracle's UTL_FILE package. Mainly achieved: 1. Declare a record to store the exported data; 2. Use the cursor to take data into the record; 3. Use utl_file to write data in the record to the Excel file; 4. Encycle steps 2 and 3, Complete the export of data. The main problems in the process of doing: The application is as follows: Select u.user_name || chr (9), u.account || CHR (9) from User U. .put () automatically jumping into the Excel file When you write data, you will automatically jump 2. The problem of the type of the type in the record is mainly due to the strong transformation of the type. When the U.account is Number type, Oracle will perform strong transformation when adding || CHR (9); Of course, the conversion here does not have problems (Number transforms to varchar2), but when this strong conversion is still If there is a problem, if a property declares that a property declare is a type of user's own definition, Oracle cannot be converted, it will be reported. The solution is simple, you can declare the properties of the record as a varchar type, but pay attention to the length. 3. Export File Storage Path Question UTL_FILE When writing files, the file's storage path must be set in Oracle initialization parameter UTL_FILE_DIR to restart service to take effect. Later, after investigation, I can create a Directory first, using it during the stored procedure. Building Directory: Create or Replace Directory FilePath AS 'Path' "; Example 2 (Note: Path is the path to the store, such as C: / Temp)
Here is the stored procedure I simply handle Hr.Jobs table data: create or replace process (p_file_name in var) *** Process file name, need to include extensions (XLS for writing Excel files) *** -) AS - *** Defines and declares a record of storage traffic asset information *** - *** record_define start *** - TYPE JOB_RECORD_TYPE IS Record (job_id hr.jobs.job_id% type, job_title HR .jobs.job_title% type, min_salary varcha2 (30)); job_rec job_record_type; - *** record_define end *** - *** Define Job information cursor *** - - *** Cursor_Define start *** - cursor c_jobs is select job_id || chr (9), - *** chr (9) is a TAB character, guaranteed that data is output to Excel to automatically change to the next column *** - job_title || CHR (9), min_salary || CHR (9) from hr.jobs; - *** CURSOR_DEFINE END *** - L_FILE UTL_FILE.FILE_TYPE; - *** Handle of File Operations *** - - Begin L_File: = UTL_FILE.FOPEN ('filepath', p_file_name, 'w'); --filepath is the path UTL_FILE.PUT_LINE (L_File, 'JOBS table exporting data ") for the store export file established by the previous user. Open c_jobs; loop fetch c_jobs INTO JOB_REC.JOB_ID, Job_rec.job_title, job_rec.min_salary; exit when c_jobs% notfound; utl_file.put (l_file, job_rec.job_id); - *** Data Write *** - UTL_FILE.PUT (L_File, Job_Rec.Job_title ); utl_file.put_line (l_file, job_rec.min_salary); END LOOP; CLOSE c_jobs; utl_file.fflush (l_file); utl_file.fclose (l_file); EXCEPTION WHEN others THEN IF utl_file.is_open (l_file) THEN utl_file.fclose (l_file ); END if; end; . MSN: LinTaowen@hotmail.com
