Showing QR Code in Oracle Reports. Generation of QR Code using PL/SQL and showing it in Oracle Reports.

Sree
16 Aug, 2022
6



In this blog, we will be generating a qr code using pl/sql and then the code will be shown in our oracle reports.

Requirement.

Generate a QR code and display it in oracle reports.

Some of the Possible solutions 

  1. Buy and use a third party qr code generators and use their APIs.
  2. Generate QR code in database using a java
  3. Use any QR code API to generate QR code using HTTPURITYPE

Solution we are using


Create QR code in database using the awesome PLSQL Package from Zorantica and show the qr code in the report.

How we do it.

Download the PLSQL Package script and create the package in our database.
Create a blob column in our sample invoice table.
Create a trigger to generate qr code to the blob column.
Show blob column in the report.

Let's do it.

1. Download the package
    You can download the complete repository from here. This contains the database packages and Apex plugin.
Or you can download database package from below google drive links.

2. Create the package in database. This step is elaborated in another post in this blog. you can check it out here.

3. Create a sample table for our report and create a trigger to generate QR code into the same table. / [Edit /start]  Or you can get the qr code from the report query. please check rdf file for the details.[Edit /End]

you can download complete script here.


Now we will be having BLOB data in our DR_INVOICE_HD table. Lets create a report with this table.

I shall show you the screenshots important screens.



F_INV_QR is the our code item. Let's see the properties.


and the output is

here is how the QR is scanned in my mobile

Report rdf (Oracle 11g) for your reference. Qr_report.rdf
Report output pdf for your reference. Qr_report.pdf

Downloads


Detailed post on DB package         View Post
GitHub Profile of Zorantica                 Zorantica GitHub Profile
Complete package from Zorantica      Download
DB package Specification Only           Package Specification
DB package Body Only                      Package Body
Sample Table and trigger script          Download
Sample Report RDF                           Qr_report.rdf
Sample Report Output                       Qr_report.pdf

Next Post Previous Post
6 Comments
  • Sree
    Sree 15 August 2022 at 10:11

    Update : In report itself you can call the function and can generate qr code dynamically.
    This will be recommended option as image files take more space and if you are having a lot of QR codes to be generated, it may take a considerable amount of storage.
    This option is added there in the rdf file.

    Reply
  • wang
    wang 22 September 2022 at 20:22

    Thanks a million...a million...sharing :)

    • Sree
      Sree 24 September 2022 at 03:52

      Happy that it helped.

    Reply
  • shahriar
    shahriar 16 November 2022 at 01:43

    Helped Lot

    Reply
  • Unknown
    Unknown 6 December 2022 at 04:24

    Hi Sree,
    I am using this code in the rdf file and it's working fine, the QR code data is correct upon scanning from phone . But when I am using this rdf file to create an XML Publisher report , the text in the QR code appear as junk . Do you have any workaround for that ?

    • har
      har 14 December 2022 at 11:43

      I am also facing same issue if you find any solution please post here

    Reply
Add Comment
comment url