Write Data in the oracle database to an excel file

Main Implementation ideas:
1. Declare a record to store exported data;
2. Use a cursor to retrieve data to the record;
3. Use utl_file to write the recorded data into an excel file;
4. Perform steps 2 and 3 cyclically to export data.
Main problems encountered during the process:
1. How to write data to the next column in The excle file;
The TAB character is used to complete the horizontal hop in excel. The TAB character in excel indicates the end of the cell. The chr () function is used,
The application example is as follows:
Select U. USER_NAME | chr (9), U. ACCOUNT | chr (9) from USER U
Example 1
Example 1 as the subject of the cursor, each retrieved data item contains a TAB character and uses utl_file.put () to go To the excel file.
Data Writing automatically jumps
2. Types of records declared
This problem occurs mainly when the type is strongly converted. For example, if the U. ACCOUNT type in Example 1 is number,
When adding | chr (9), oracle will perform strong conversion. Of course, the conversion here will not be problematic (number is converted to varchar2 ),
However, this type of strong conversion still has problems. For example, a property declaration in the declared record is a type defined by the user,
If oracle cannot be converted, an error is returned. The solution is simple. You can declare the record attributes as varchar type, but pay attention to the length.
3. File Storage path Export
When utl_file is writing a file, the file storage path must be set in the utl_file_dir parameter initialized by oracle,
The service must be restarted to take effect. Later, we found that we can create a directory and use it in the stored procedure.
Create a directory statement:
Create or replace directory FILEPATH as 'path '";
Example 2 (Note: path is the path of the stored file, for example, c: \ Temp)

The following is a simple stored procedure for processing data in the hr.jobs table:

Create or replace procedure SP_JOBS_DATA_OUT (
P_file_name IN VARCHAR2 -- *** name of the file to be processed, which must contain the extension (xls is used to write an excel file )***--
)

-- *** Define and declare records for storing 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 varchar2 (30)
);
Job_rec job_record_type;
-- *** Record_define end ***--


-- *** Define the cursor for obtaining job information ***--
-- *** Cursor_define start ***--
CURSOR c_jobs IS
Select
Job_id | chr (9), -- *** chr (9) is a TAB character, ensuring that data can be automatically changed to the next column when output to EXCEL ***--
Job_title | chr (9 ),
Min_salary | chr (9)
FROM
Hr.jobs;
-- *** Cursor_define end ***--

L_file utl_file.file_type; -- *** handle for file operations ***--


BEGIN
L_file: = utl_file.fopen ('filepath', p_file_name, 'w'); -- FILEPATH is the path for storing and exporting files created before export.
Utl_file.put_line (l_file, 'jobs table export data ');

OPEN c_jobs;
LOOP
FETCH c_jobs
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); -- *** write data to the excle file ***--
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;
Example 3: My oracle version is 9.2)

DECLARE
FILE_ID TEXT_IO.FILE_TYPE;
FILE_NAME VARCHAR2 (200 );
FILE_CODE VARCHAR2 (100): = 'bom '| TO_CHAR (SYSDATE, 'hhmis ');
P_file_path varchar2 (200 );
Ln_count number;

BEGIN
FILE_NAME: = '/prod/applprod/prodora/iAS/Apache/htdocs/lc_cust/' | FILE_CODE | '.xls ';
FILE_ID: = TEXT_IO.FOPEN (FILE_NAME, 'w ');

TEXT_IO.PUT (FILE_ID, convert ('parent parent material upload', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('subfoly', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('sub-partition material name', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('single-bit ', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('single-bit size', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('use', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('supplied output', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('available for temporary failed', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('for reserved bit', 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
TEXT_IO.PUT (FILE_ID, convert ('classification ', 'zht16big5', 'utf8 '));

GO_BLOCK ('check _ BOM_COMPONENT_PT ');
FIRST_RECORD;

LOOP
Text_IO.PUT (FILE_ID, chr (13 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.ASSEMBLY_SEGMENT, ''), 'zht16big5 ', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.COMPONENT_SEGMENT, ''), 'zht16big5 ', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.COMPONENT_DESCRIPTION, ''), 'zht16big5 ', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.PRIMARY_UOM_CODE, ''), 'zht16big5 ', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.COMPONENT_QUANTITY, 0), 'zht16big5', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.COMPONENT_YIELD_FACTOR, ''), 'zht16big5 ', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.MEANING, ''), 'zht16big5 ', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.SUBINVENTORY_CODE, ''), 'zht16big5 ', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.INVENTORY_LOCATOR_SEGMENT, ''), 'zht16big5 ', 'utf8 '));
Text_IO.PUT (FILE_ID, chr (9 ));
Text_IO.PUT (FILE_ID, convert (NVL (: CHECK_BOM_COMPONENT_PT.CATEGORY_SEGMENT, ''), 'zht16big5 ', 'utf8 '));

NEXT_RECORD;
Exit when: SYSTEM. CURRENT_VALUE is null;
End loop;

TEXT_IO.FCLOSE (FILE_ID );
P_file_path: = 'HTTP: // erp.lacquercraft.com: 8000/lc_cust/'| FILE_CODE | '.xls ';
Web. Show_Document (p_file_path, '_ BLANK ');
END;

20111213

 

 

If l_attachtype = 'xls 'then
Rochelle mimetype: = 'application/vnd. ms-excel ';
End if;
If l_attachtype = 'Doc' then
Rochelle mimetype: = 'application/vnd. ms-word ';
End if;
If l_attachtype = 'pdf 'then
Rochelle mimetype: = 'application/pdf ';
End if;
If l_attachtype in ('html ', 'htm') then
Rochelle mimetype: = 'text/html ';
End if;

Chr (9) Tab