OraclePL/SQL calls Java to generate Excel (instance tutorial), oracleplexcel

OraclePL/SQL calls Java to generate Excel (instance tutorial), oracleplexcel

There is a need to write an oracle stored procedure to generate an Excel file to the specified directory, but oracle's own API seems to be not powerful, therefore, Java can only be implemented through another more powerful language. There is a Java framework called POI, which is very useful for processing Excel. Now I have recorded the process:

1. Download the jar package of POI

The version of my test database is 11 GB, so the downloaded jar package cannot be too new. Otherwise, a version error will be reported when importing the jar package, but another problem occurs, of course, the jar package of the old version is not fully functional, including the Excel encryption function that I previously valued. I have tried many versions for this feature, but I have to give up. I don't know what the oracle 12c version will do. Please try again later...

Step into the topic, we should try to update the jar package as much as possible, so we should first look at the oracle 11g Java version:

[oracle@centos6 bin]$ cd $ORACLE_HOME/jdk/bin/[oracle@centos6 bin]$ javac -versionjavac 1.5.0_17

If the javac version is 1.5, the imported jar package cannot be compiled by version 1.6 or later. Well, after unremitting efforts, I downloaded the jar package of POI 3.0.1,

2. Import the jar package of POI to the database

1. Upload the jar package to a directory on the server. Here is the/java/directory:

2. Authorize the user:

To operate Java, common oracle users must grant policyspriv permission through sys:

GRANT JAVASYSPRIV TO SCOTT;

3. Run the loadjava command to import the cd file to the upload directory:

Basic Syntax of loadjava:

loadjava {-user | -u} username/password[@database]   [option ...] filename [filename] ...

For details about the options, refer to other materials.

Run the following commands to import data:

Loadjava-r-f-o-user scott/tiger @cat poi-3.0.1-FINAL-20070705.jar

Loadjava-r-f-o-user scott/tiger @cat poi-contrib-3.0.1-FINAL-20070705.jar

Loadjava-r-f-o-user scott/tiger @cat poi-scratchpad-3.0.1-FINAL-20070705.jar

Errors will still be reported when such an old version is imported, but it does not matter. The errors are all classes not needed in this example. If the latest version is used, not so lucky.

You can use SQL to query the import information:

SELECT OBJECT_NAME,       DBMS_JAVA.LONGNAME(OBJECT_NAME) JAVA_CLASS_NAME,       O.STATUS  FROM USER_OBJECTS O WHERE OBJECT_TYPE LIKE 'JAVA CLASS';

Use spelling SQL to generate deletion statements for invalid classes:

SELECT 'execute immediate ''drop JAVA CLASS "' || OBJECT_NAME || '"'';'  FROM USER_OBJECTS O WHERE OBJECT_TYPE LIKE 'JAVA CLASS'   AND O.STATUS <> 'VALID';

After executing the generated statement, the useless and invalid classes are deleted:

BEGIN  EXECUTE IMMEDIATE 'drop JAVA CLASS "/5e316ace_CommonsLogger"';  EXECUTE IMMEDIATE 'drop JAVA CLASS "/eb16769d_HSSFCellUtil"';  EXECUTE IMMEDIATE 'drop JAVA CLASS "/ff8d85a1_HSSFRegionUtil"';END;

3. Compile a Java Excel program and create a JAVA SOURCE

Here I wrote a general Java class, as long as the method is passed into the SQL string, the file path and the name of the string, the two parameters can complete the function, the Code is as follows, and put it in

Create Java Source in PL/SQL Developer:

Create or replace and compile java source named createexcel asimport java. io. fileNotFoundException; import java. io. fileOutputStream; import java. io. IOException; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; import java. SQL. statement; import org. apache. poi. hssf. usermodel. HSSFCell; import org. apache. poi. hss F. usermodel. HSSFCellStyle; import org. apache. poi. hssf. usermodel. HSSFFont; import org. apache. poi. hssf. usermodel. HSSFRow; import org. apache. poi. hssf. usermodel. HSSFSheet; import org. apache. poi. hssf. usermodel. HSSFWorkbook; public class CreateExcel {private static HSSFWorkbook wb; private static HSSFSheet sheet; private static String connStr = "jdbc: oracle: thin: @ 192. 168. *. *: 1521: cat "; private static String UserStr = "scott"; private static String pwdStr = "tiger"; // @ SuppressWarnings ("deprecation") public static void CreateExcel2003 (String SQL, String fileDir) {Connection conn = null; Statement stmt = null; ResultSet rs = null; int rowNum = 0; try {Class. forName ("oracle. jdbc. oracleDriver "); conn = DriverManager. getConnection (connStr, userStr, pwdStr); stmt = conn. createStatement (); rs = stmt.exe c UteQuery (SQL); // Create Question header if (rs. next () {wb = new HSSFWorkbook (); sheet = wb. createSheet ("Result"); // create a new sheet object // header font style HSSFFont font = wb. createFont (); font. setFontName (""); font. setFontHeightInPoints (short) 12); // set the font size. setBoldweight (HSSFFont. BOLDWEIGHT_BOLD); // HSSFCellStyle cellStyle = wb in bold. createCellStyle (); cellStyle. setFont (font); HSSFRow row = sheet. createRow (rowNum); // gets the number of Columns And column name ResultSetMetaData rsMetaData = rs. getMetaData (); int numberOfColumns = rsMetaData. getColumnCount (); // System. out. println (numberOfColumns); HSSFCell [] cells = new HSSFCell [numberOfColumns]; // obtain the column name header based on the number of columns (int I = 0; I <numberOfColumns; I ++) {cells [I] = row. createCell (short) I); cells [I]. setCellValue (rsMetaData. getColumnName (I + 1); cells [I]. setCellStyle (cellStyle) ;}// data row style HSSFCellSty Le cellStyle1 = wb. createCellStyle (); // create a data row while (rs. next () {rowNum ++; HSSFRow row = sheet. createRow (rowNum); // create a new row // obtain the number of columns ResultSetMetaData rsMetaData = rs. getMetaData (); int numberOfColumns = rsMetaData. getColumnCount (); HSSFCell [] cells = new HSSFCell [numberOfColumns]; // get data for (int I = 0; I <numberOfColumns; I ++) {cells [I] = row. createCell (short) I); cells [I]. setCellValue (rs. getStr Ing (I + 1); cells [I]. setCellStyle (cellStyle1); // automatically adjusts the column width sheet. autoSizeColumn (short) I) ;}// for (int I = 0; I <numberOfColumns; I ++) {/// automatically adjust the column width // sheet. autoSizeColumn (short) I); //} FileOutputStream fileOut = new FileOutputStream (fileDir); wb. write (fileOut); fileOut. close ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace ();} catch (FileNotFoundException e) {// TODO Auto-generated catch block e. printStackTrace ();} catch (IOException e) {// TODO Auto-generated catch block e. printStackTrace ();} catch (ClassNotFoundException e) {// TODO Auto-generated catch block e. printStackTrace ();} catch (Exception e) {// TODO Auto-generated catch block e. printStackTrace ();} finally {if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}rs = null ;}if (stmt! = Null) {try {stmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}stmt = null ;}if (conn! = Null) {try {conn. close () ;}catch (SQLException e) {e. printStackTrace () ;}conn = null ;}}}}

4. Create a stored procedure for calling Java code

The definition of the stored procedure starts with the same, and the only difference is the syntax after "as language:

{IS | AS} LANGUAGE JAVANAME 'method_fullname (java_type[, java_type]...)   [return java_type]';

Here I am:

CREATE OR REPLACE PROCEDURE CREATE_EXCEL(P_SQL VARCHAR2, P_PATH VARCHAR2) AS  LANGUAGE JAVA NAME 'CreateExcel.CreateExcel2003(java.lang.String,java.lang.String)';

The first parameter is the SQL string, and the second parameter is the file path + file name.

Note that the name of the Java class is written in single quotes. The method name (data type,...) is case sensitive.

5. Calling program:

Enter the following parameters:

begin  -- Call the procedure  create_excel(p_sql => 'select * from emp',               p_path => '/share/emp.xls');end;

Program running successful!

Look at the file directory:

The file has been generated. After downloading it from the server, open the file to see:

OK ~~

If the program has errors and Java code errors, how can we get the error information? Here you only need to enable DBMS_JAVA.SET_OUTPUT (); this process,

The syntax of this process is:

PROCEDURE DBMS_JAVA.SET_OUTPUT(buffersize NUMBER);

Usage:

SQL> SET serveroutput ON size 1000000;SQL> call dbms_java.set_output(1000000);

If there is a problem with the Java code, we can obtain the printing information of Java, including System. out. println (); printed information: