SQL>
SQL> CONN POS1018@ORCLPDB
Enter password:
Connected.
SQL> DECS ITMES
SP2-0042: unknown command "DECS ITMES" - rest of line ignored.
SQL> DESC ITEMS
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER(10)
BARCODE NOT NULL VARCHAR2(50)
TYP CHAR(1)
NAME NOT NULL VARCHAR2(50)
DESCRIPTIONS VARCHAR2(1000)
BRANDS NUMBER(10)
UNIT_TYPE NUMBER(10)
CATEGORIES NUMBER(10)
SUB_CATEGORIES NUMBER(10)
BUY_PRICE NUMBER(11,2)
SELL_PRICE NUMBER(11,2)
STOCK NUMBER(10)
IMAGE BLOB
IMAGE_MIMETYPE VARCHAR2(2000)
IMAGE_FILENAME VARCHAR2(2000)
IMAGE_CHARACTERSET VARCHAR2(2000)
IMAGE_LAST_UPDATE DATE
ENTRY_BY NUMBER(10)
ENTRY_DATE DATE
UPDATE_BY NUMBER(10)
UPDATE_DATE DATE
BRANCH NOT NULL NUMBER(10)
MANUFACTURE NUMBER(10)
SQL> CREATE OR REPLACE FUNCTION generate_ean13_barcode (
2 manufacturer_code IN NUMBER, -- Manufacturer code (4 digits)
3 product_code IN NUMBER -- Product code (4 digits)
4 ) RETURN VARCHAR2 IS
5 barcode_without_check_digit VARCHAR2(12);
6 check_digit NUMBER;
7 sum_odd_pos NUMBER := 0;
8 sum_even_pos NUMBER := 0;
9 total_sum NUMBER := 0;
10 BEGIN
11 -- Ensure manufacturer_code and product_code are 4 digits
12 IF manufacturer_code NOT BETWEEN 0 AND 9999 OR product_code NOT BETWEEN 0 AND 9999 THEN
13 RAISE_APPLICATION_ERROR(-20001, 'Manufacturer code and product code must be 4-digit numbers.');
14 END IF;
15
16 -- Convert manufacturer_code and product_code to 4-digit strings with leading zeros if necessary
17 barcode_without_check_digit := LPAD(manufacturer_code, 4, '0') || LPAD(product_code, 4, '0');
18
19 -- Calculate the sum of odd and even positions (starting after the country code)
20 FOR i IN 1..8 LOOP
21 IF MOD(i, 2) = 0 THEN
22 sum_even_pos := sum_even_pos + TO_NUMBER(SUBSTR(barcode_without_check_digit, i, 1)) * 3;
23 ELSE
24 sum_odd_pos := sum_odd_pos + TO_NUMBER(SUBSTR(barcode_without_check_digit, i, 1)) * 1;
25 END IF;
26 END LOOP;
27
28 -- Calculate total sum
29 total_sum := sum_odd_pos + sum_even_pos;
30
31 -- Calculate check digit
32 check_digit := (10 - MOD(total_sum, 10)) MOD 10;
33
34 -- Return the full 13-digit barcode (including the '590' country code)
35 RETURN '746' || barcode_without_check_digit || check_digit;
36 END;
37 /
Function created.
SQL>
PL/SQL code
SELECT NVL(MAX(ID), 1000) + 1 INTO :P32_ID FROM ITEMS;
:P32_BARCODE := generate_ean13_barcode(:P32_MANUFACTURE, :P32_ID);
