ERPstuff - Oracle EBS Community

Posted: **Sun Jun 30, 2024 6:55 am**

Generation of QR code, EAN8 and Code128 was offered by Oracle first time in Oracle APEX release 23.2. You can use PL/SQL to generated using provided package APEX_BARCODE. Here I have provided code the way you can generate these code and practice in your Oracle APEX instance.

Classic Report

basrcodes.jpg

classic_qr.jpg

Data Entry Form using QR Code Page Item and Display as Image
Page item is only for QR codes so other Barcodes will not appear using Page item.

pageite_qrcode.jpg

qr_form.jpg

Sample Table Creation

Code: Select all

CREATE TABLE "ERPSTUFF_BARCODES" (
    "ID"               NUMBER
        GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 1 NOCACHE,
    "NAME"             VARCHAR2(50),
    "EMAIL"            VARCHAR2(100),
    "PHONE"            VARCHAR2(15),
    "WEBSITE"          VARCHAR2(100),
    "BARCODE_DATA"     VARCHAR2(100),
    "BARCODE_TYPE"     VARCHAR2(3),
    "BARCODE_CODE_PNG" BLOB,
    PRIMARY KEY ( "ID" )
        USING INDEX enable
);

CREATE OR REPLACE TRIGGER erpstuff_barcodes_trg BEFORE
    INSERT ON "ERPSTUFF_BARCODES"
    FOR EACH ROW
DECLARE BEGIN
	-- This function even can be called in the insert statement
    :new.barcode_code_png := erpstuff_barcode_png(:new.barcode_data, :new.barcode_type);
END;
/

Sample Data Insertion

Code: Select all

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Malik Sikandar Hayat', 'info@erpstuff.com', '123-456-7890', 'https://erpstuff.com', 'QR', 'https://erpstuff.com');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Malik Sikandar Hayat', 'sikandar@jadeedservices.com', '234-567-8901', 'https://jadeedservices.com', 'QR', 'https://jadeedservices.com');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Alice Doe', 'alice.doe@example.com', '345-678-9012', 'http://alicedoe.com', 'QR', 'alicedoe-website.com');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Bob Doe', 'bob.doe@example.com', '456-789-0123', 'http://bobdoe.com', 'QR', 'bobdoe-website.com');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('John Smith', 'john.smith@example.com', '567-890-1234', 'http://johnsmith.com', '128', 'ABC123');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Jane Smith', 'jane.smith@example.com', '678-901-2345', 'http://janesmith.com', '128', 'DEF456');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Alice Smith', 'alice.smith@example.com', '789-012-3456', 'http://alicesmith.com', '128', 'GHI789');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Bob Smith', 'bob.smith@example.com', '890-123-4567', 'http://bobsmith.com', '128', 'JKL012');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('John Johnson', 'john.johnson@example.com', '901-234-5678', 'http://johnjohnson.com', 'EAN', '12345670');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Jane Johnson', 'jane.johnson@example.com', '012-345-6789', 'http://janejohnson.com', 'EAN', '23456781');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Alice Johnson', 'alice.johnson@example.com', '123-456-7890', 'http://alicejohnson.com', 'EAN', '34567892');

INSERT INTO erpstuff_barcodes (NAME, EMAIL, PHONE, WEBSITE, BARCODE_TYPE, BARCODE_DATA) 
VALUES ('Bob Johnson', 'bob.johnson@example.com', '234-567-8901', 'http://bobjohnson.com', 'EAN', '45678903');

Function to call for different types of codes

Code: Select all

CREATE OR REPLACE FUNCTION erpstuff_barcode_png (
    p_value     IN VARCHAR2,
    p_code_type IN VARCHAR2
) RETURN BLOB IS
    l_png BLOB;
BEGIN
    IF p_code_type = 'QR' THEN
        l_png := apex_barcode.get_qrcode_png(p_value => p_value, p_scale => 5, -- PNG scale (integer 1-10)
         p_quiet => 1, p_eclevel => apex_barcode.c_eclevel_type_high, -- ('L', 'M', 'Q' or 'H')
         p_foreground_color => apex_barcode.c_default_foreground_color,
                                            p_background_color => apex_barcode.c_default_background_color);
    ELSIF p_code_type = '128' THEN
        l_png := apex_barcode.get_code128_png(p_value => p_value, p_scale => 5,-- PNG scale (integer 1-10)
         p_foreground_color => apex_barcode.c_default_foreground_color, p_background_color => apex_barcode.c_default_background_color
        );
    ELSIF p_code_type = 'EAN' THEN
        l_png := apex_barcode.get_ean8_png(p_value => p_value, p_scale => 5, -- PNG scale (integer 1-10)
         p_foreground_color => apex_barcode.c_default_foreground_color, p_background_color => apex_barcode.c_default_background_color
        );
    END IF;

    RETURN l_png;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;

Report Query

Code: Select all

SELECT
    id,
    name,
    email,
    phone,
    website,
    barcode_type,
    barcode_data,
    dbms_lob.getlength(barcode_code_png) qr_code
FROM
    erpstuff_barcodes
WHERE
    barcode_type = :p1_selection

Oracle APEX Documentation 23.2

Youtube Video to Explain it Further

ERPstuff - Oracle EBS Community

Oracle APEX QR-Codes, EAN-8, and Code-128 Comprehensive Explanation, Demo & Scripts

Oracle APEX QR-Codes, EAN-8, and Code-128 Comprehensive Explanation, Demo & Scripts