In this post, I will write a PL/SQL package that helps us to write a EXCEL file in XML format. Then, we will save it some directory. This is a basic way to create an Excel file. There is no formatting type in the cells. We will just write something in the cells. I will try to develop this package with the formatting commands. Anyway, before we write our package, we shoul know about something oracle’s directory policy. We should tell to ORACLE, I have a directory path and I will name it. I mean, we will use create directory command of oracle.
CREATE OR REPLACE DIRECTORY MY_ORACLE_DIR AS 'C:\oracle_test\';
To run this command, we have the following privileges;
GRANT CREATE ANY DIRECTORY TO HR; >>>>>HR is the schema name!
On the other hand, you created an directory in another schema. You should grant the directory to your shema.
GRANT read, write on DIRECTORY MY_ORACLE_DIR to HR;
After defining the directory to oracle, we can write our package. I passed a variable referencing the XML body of the file as CLOB in the procedures.
CREATE OR REPLACE PACKAGE pkg_excel_export IS /** * @author : Özay AKDORA * @version : 1.0 * * Name of the Application : pkg_excel_export.sql * Creation/Modification History : 5-Jan-2009 * * Overview of Package/Sample :Create Excel files via PL/SQL * write the file to a directory * **/
PROCEDURE excel_open(l_xml_body IN OUT NOCOPY CLOB); PROCEDURE excel_close(l_xml_body IN OUT NOCOPY CLOB);
PROCEDURE worksheet_open ( l_xml_body IN OUT NOCOPY CLOB, p_worksheetname IN VARCHAR2 ); PROCEDURE worksheet_close(l_xml_body IN OUT NOCOPY CLOB);
PROCEDURE row_open(l_xml_body IN OUT NOCOPY CLOB); PROCEDURE row_close(l_xml_body IN OUT NOCOPY CLOB);
PROCEDURE cell_write ( l_xml_body IN OUT NOCOPY CLOB, p_content IN VARCHAR2 );
PROCEDURE excel_get ( l_xml_body IN OUT NOCOPY CLOB, p_filename IN VARCHAR2 ); PROCEDURE prc_write_file ( p_filename IN VARCHAR2, p_dir IN VARCHAR2, p_clob IN CLOB );
END pkg_excel_export; /