How to restrict user to create Purchase Order (PO) without Purchase Requisition (PR) in Oracle / Auto Create PO in Oracle EBS R12

 

QR Code Generator using Java Stored Procedure and PL/SQL in Oracle EBS R12

Generating QR codes within an Oracle database can be a powerful way to add quick data access capabilities to your applications. In this guide, I'll walk you through creating a QR code generator using a combination of Java stored procedures and PL/SQL. This approach is ideal for developers looking to enhance their database-driven applications with QR functionality.

Prerequisites

1. Java Libraries

   • Download the required Java libraries.

   • Unzip the jar files into a directory, e.g., C:\jars.

2. Upload JAR Files to the Database

• Use the loadjava tool to upload each jar file into the database:

• loadjava -force -genmissing -r -user username/password@database -verbose [jarfilename]

Java Stored Procedure for QR Code Generation

Create a Java stored procedure to generate QR codes with the following code:

***************************************************************

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "QRCodeGenerator" as

import oracle.sql.BLOB;

import oracle.jdbc.driver.*;

import java.sql.*;

import java.io.ByteArrayOutputStream;

import net.glxn.qrgen.QRCode;

import net.glxn.qrgen.image.ImageType;

public class QRCodeGenerator {

    public static BLOB getQrCode(String value) throws Exception {

        OracleDriver ora = new OracleDriver();  

        Connection conn = ora.defaultConnection();  

        BLOB retBlob = BLOB.createTemporary(conn, true, oracle.sql.BLOB.DURATION_SESSION);  

        try (ByteArrayOutputStream out = QRCode.from(value).to(ImageType.GIF).stream()) {  

            java.io.OutputStream outStr = retBlob.setBinaryStream(0);  

            outStr.write(out.toByteArray());  

            outStr.flush();  

        }  

        return retBlob;  

    }

}

/

***************************************************************

PL/SQL Wrapper Function

CREATE OR REPLACE FUNCTION getQRcode(p_text IN VARCHAR2) RETURN BLOB AS

    LANGUAGE JAVA NAME 'QRCodeGenerator.getQrCode(java.lang.String) return oracle.sql.BLOB';/

Testing the QR Code Generator

You can now test your QR code generator using the following SQL statement:

SELECT getQRcode('Hello, World!') FROM dual;

This will return a BLOB containing the QR code in GIF format, which can be used directly in your applications.

Conclusion

This approach allows you to generate QR codes directly within your Oracle database, making it easy to embed QR functionality into your applications. Make sure to handle BLOB data correctly when integrating this feature into your systems.

Happy coding! 🚀

The function will return blob field with a QRCode in GIF format...