E-Business Suite

This article will explain how to generate a qrcode in the database and bring this code to your bi publisher document.
Using this example is without any guarantee !!!
Zxing is licenced under Apache licence http://www.apache.org/licenses/LICENSE-2.0

Part1 - Create the Java Function and pl/sql Functinality
a) Get the java libraries
zxing.jar , zxing-core-2.0.jar , zxing-javase.jar
I used Version 3.4.1

b) Load the java libs
Change to the directory with the jar files and excecute
loadjava -user apps/password@database –resolve -verbose zxing.jar
loadjava -user apps/password@database –resolve -verbose zxing-core-2.0.jar
loadjava -user apps/password@database -resolve -verbose zxing-javase.jar
After this find and recompile invalid java classes as until there are no invalid classes.

c) Create the java stored function.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "XXHM_QRCode"
AS //
//
import java.awt.*;
import java.applet.*;
import java.lang.*;
import java.awt.image.BufferedImage; 
import javax.imageio.ImageIO ;
import java.io.ByteArrayOutputStream ;
import java.io.IOException ;
import java.util.HashMap;
import java.util.Map;
//
import oracle.sql.BLOB ;
import oracle.jdbc.driver.*;
//
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
//
//
import com.google.zxing.BarcodeFormat;
import com.google.zxing.EncodeHintType;
import com.google.zxing.MultiFormatWriter;
import com.google.zxing.NotFoundException;
import com.google.zxing.WriterException;
import com.google.zxing.client.j2se.MatrixToImageWriter;
import com.google.zxing.common.BitMatrix;
import com.google.zxing.qrcode.decoder.ErrorCorrectionLevel;
import com.google.zxing.qrcode.QRCodeWriter;
import com.google.zxing.EncodeHintType;
//
//
@SuppressWarnings("deprecation")
public class XXHM_QRCode
{
  private static Map<EncodeHintType, Object> hints = new HashMap<EncodeHintType, Object>() {
    private static final long serialVersionUID = 1L ;
    {
      put(EncodeHintType.ERROR_CORRECTION, ErrorCorrectionLevel.H); // Set the error correction level of QR QR code (H is the highest level) specific level information
      put(EncodeHintType.CHARACTER_SET, "utf-8") ; // Set the encoding method
    }
  } ;
   
  public static oracle.sql.BLOB toQRCode( String contentString ) 
  {
    //java.lang.System.out.println("Start: " + contentString ) ;
    
    OracleDriver driver    = new OracleDriver();
    Connection  conn     = null ;
    BLOB     qrCode    = null ;
    
    try { conn = driver.defaultConnection() ;
      }
      catch (SQLException e)
         { java.lang.System.out.println("SQLException defaultConnection") ;
         }
    
    try { qrCode = BLOB.createTemporary(conn, false, BLOB.DURATION_CALL) ;
      }
      catch (SQLException e)
         { java.lang.System.out.println("SQLException LOB.createTemporary") ;
         }
    
    int width = 300 ;
    int height = 300 ;
    
    QRCodeWriter qrCodeWriter = new QRCodeWriter();
    try { BitMatrix       bitMatrix   = qrCodeWriter.encode(contentString, BarcodeFormat.QR_CODE, width, height, hints) ;
       BufferedImage     bufferedImage = MatrixToImageWriter.toBufferedImage(bitMatrix) ;
       ByteArrayOutputStream baos     = new ByteArrayOutputStream() ;
       ImageIO.write(bufferedImage, "png", baos);
       
       long offset = 1 ; // 1 nicht 0 !!!
       byte[] bytes = baos.toByteArray() ;
       qrCode.putBytes(offset,bytes) ; 
      }
      catch (Exception e)
         { java.lang.System.out.println("Exception: " + e.getMessage() ) ;
         };
    return qrCode ;
  }  
}

d) Build the pl/sql package

CREATE OR REPLACE 
PACKAGE apps.xxhm_qrcode
IS

FUNCTION toQRCode
     ( p_ContentString VARCHAR2 )
      RETURN     BLOB ;

PROCEDURE testQRCode ;

END ;
/

-- If you want to use it in Apex Ords you have to grand
-- Grants for Package

GRANT EXECUTE ON apps.xxhm_qrcode TO xxhmapex
/

GRANT DEBUG ON apps.xxhm_qrcode TO xxhmapex
/
CREATE OR REPLACE 
PACKAGE BODY apps.xxhm_qrcode
IS

FUNCTION toQRCode
     ( p_ContentString VARCHAR2 )
      RETURN     BLOB
AS
LANGUAGE JAVA
NAME 'XXHM_QRCode.toQRCode(java.lang.String) return oracle.sql.BLOB' ;


PROCEDURE testQRCode
IS
  l_qrBlob   BLOB;

  l_file   UTL_FILE.FILE_TYPE;
  l_buffer  RAW(32767);
  l_amount  BINARY_INTEGER := 32767;
  l_pos    INTEGER := 1;
  l_blob_len INTEGER;

BEGIN
  -- We want see Java Output
  dbms_java.set_output(10000) ;

  -- Now Call the stored program
  l_qrBlob := xxhm_qrcode.toQRCode
        ( p_contentstring=>'Ω Felix, qui potuit rerum cognoscere causas! Ω') ;

  dbms_output.put_line(dbms_lob.getlength(l_qrBlob) ) ;

  -- Must be a directory there you have write excess
  l_file := UTL_FILE.fopen('IMPORT_MIG','QRCode.png','wb', 32767);
  l_blob_len := DBMS_LOB.getlength(l_qrBlob);

  WHILE l_pos <= l_blob_len
  LOOP
   dbms_output.put_line('Loop' ) ;
   DBMS_LOB.read(l_qrBlob, l_amount, l_pos, l_buffer);
   UTL_FILE.put_raw(l_file, l_buffer, TRUE);
   l_pos := l_pos + l_amount;
  END LOOP;

  -- Close the file.
  UTL_FILE.fclose(l_file);
END testQRCode;

END ;
/

e) Test the procedure

BEGIN
  xxhm_QRCode.testQRCode ; -- Output the results
END;

In the directory you should find the QRCode.png image.
QRCode.zip (560.86 KB)
Have fun
Ingo Zawada