Java Stored Procedure in Oracle Subd. By Example of Forming a PDF File

Good day to all!

Recently, I had the experience of creating functions (stored procedures) in the Java language in Oracle DBMS (Java Stored Procedures). I will try to describe the steps for creating such functions by examining an example of working with a pdf-file.



Here is what is required for this:

• Oracle DBMS (in my case version 10)
• iText libraries (version 1.4.8)
• Java code that implements the required functions

It is important to know that, depending on the version of the Oracle DBMS, a different version of the JVM (Java Virtual Machine) can be installed with it (with the system). In the 10th version of the DBMS installed JVM 1.4. And this means that when writing Java code, the version of the language should be used the same.

Library loading in DBMS

Java code operates on the existing methods of the iText library classes. In this regard, we must first load the library in subd. The loadjava command will help us with this. The libraries themselves must be compiled using java version 1.4. That's why I chose itext 1.4.8. In addition, it was required to download companion libraries. Here is the iText library download sequence:

• loadjava -resolve -verbose -thin -user user / password @ ip-addres: port: TNS_NAME bcprov-jdk14-149.jar
• loadjava -resolve -verbose -thin -user user / password @ ip-addres: port: TNS_NAME bcpkix-jdk14-149.jar
• loadjava -resolve -verbose -thin -user user / password @ ip-addres: port: TNS_NAME bcmail-jdk14-149.jar
• loadjava -resolve -verbose -thin -user user / password @ ip-addres: port: TNS_NAME itext-1.4.8.jar

This can be done remotely. For this, user data and server connection parameters are specified.

')

Download our java code

Before creating the functions in the database we have to load our own code into the DBMS. In the example, I open an existing file, add images and save to a new file. Here is the code:

create or replace and compile java source named "MyJavaPdf" as import java.io.FileOutputStream; import java.io.IOException; import com.lowagie.text.Annotation; import com.lowagie.text.DocumentException; import com.lowagie.text.Image; import com.lowagie.text.pdf.PdfContentByte; import com.lowagie.text.pdf.PdfReader; import com.lowagie.text.pdf.PdfStamper; public class PdfGenerator{ public static final float LLX1 = 50; public static final float LLY1 = 50; public static final float W1 = 100; public static final float H1 = 100; public static final float LLX2 = 200; public static final float LLY2 = 50; public static final float W2 = 100; public static final float H2 = 100; public static final float LLX3 = 350; public static final float LLY3 = 50; public static final float W3 = 100; public static final float H3 = 100; public static String addSignsToPdf(String path, String input, String output, String imm1, String imm2, String imm3) { String result = null; try { System.out.println("PDF Editing: START..."); System.out.println("Files path: " + path); System.out.println("Input file: " + input); PdfReader reader = new PdfReader(path + input); System.out.println("Output file: " + output); PdfStamper stamper = new PdfStamper(reader, new FileOutputStream(path + output)); PdfContentByte canvas = stamper.getOverContent(1); canvas.saveState(); canvas.setRGBColorFill(0xFF, 0xFF, 0xFF); canvas.rectangle(LLX1, LLY1, W1, H1); canvas.rectangle(LLX2, LLY2, W2, H2); canvas.rectangle(LLX3, LLY3, W3, H3); canvas.fill(); canvas.restoreState(); System.out.println("Image 1: " + imm1); System.out.println("Image 2: " + imm2); System.out.println("Image 3: " + imm3); putImage(canvas, Image.getInstance(path + imm1), "IMMAGINE 1", LLX1, LLY1, W1, H1); putImage(canvas, Image.getInstance(path + imm2), "IMMAGINE 2", LLX2, LLY2, W2, H2); putImage(canvas, Image.getInstance(path + imm3), "IMMAGINE 3", LLX3, LLY3, W3, H3); stamper.close(); result="OK"; System.out.println("PDF Editing: TERMINATED."); sendStaffMailWithAttachment(path + output); } catch (IOException e) { result="ERROR"; for(int i=0; i<e.getStackTrace().length;i++){ result += "\n\t\t\t" + e.getStackTrace()[i]; } return result; } catch (DocumentException e) { result="ERROR"; for(int i=0; i<e.getStackTrace().length;i++){ result += "\n\t\t\t" + e.getStackTrace()[i]; } return result; } catch (IllegalArgumentException e) { result="ERROR"; for(int i=0; i<e.getStackTrace().length;i++){ result += "\n\t\t\t" + e.getStackTrace()[i]; } return result; } return result; } private static void putImage(PdfContentByte canvas, Image img, String url, float llx, float lly, float w, float h) throws DocumentException { img.scaleToFit(w, h); float offsetX = w / 2f; float offsetY = h / 2f; img.setAbsolutePosition(llx + offsetX, lly + offsetY); img.setAnnotation(new Annotation(0, 0, 0, 0, url)); canvas.addImage(img); } }; 

“Compile” instructs to compile this code into a class. In addition to this option, you can use the existing file * .class

Creating a function using the java class

Now we can create a function based on our java-class. It is quite simple:

 create or replace function createPdf( files_path in VARCHAR2, input_file in VARCHAR2, output_file in VARCHAR2, picture1 in VARCHAR2, picture2 in VARCHAR2, picture3 in VARCHAR2) return VARCHAR2 as language java name 'PdfGenerator.addSignsToPdf(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return java.lang.String'; / 

We specify here the input parameters and the type of the return value.

Setting access to directories

The original and generated files are stored on the database server, so we need to create a directory in Oracle and distribute the rights to it:

 create directory PDFDIR as '/home/oracle/PDF' ; commit; grant ALL on directory PDFDIR to PUBLIC; exec dbms_java.grant_permission('SchemaUser','java.util.PropertyPermission','*','read,write'); exec dbms_java.grant_permission('SchemaUser','java.net.SocketPermission','*','connect, resolve'); exec dbms_java.grant_permission('SchemaUser', 'SYS:java.io.FilePermission', '/home/oracle/PDF/*', 'read,write'); commit; / 

An example of using the function

Now we can use this function:

 select createPdf('/home/oracle/PDF/', 'INPUT.PDF', 'OUTPUT.PDF', 'picture1.jpg', 'picture2.jpg', 'picture3.jpg') from dual; 

I hope that someone will find this example useful. Do not judge strictly, this is my first post on Habré.

I wish you all success! Thanks for attention!