Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert Code128 using SQL only

User_U66J8Feb 24 2022

Hi All,
We have a requirement to display a specific numeric value to Code 128 ( this is a barcode font).
We have found a PL/SQL function which converts that with no problem but our restriction here is that we are using Oracle Cloud ( Fusion ERP) and therefore we can't create any objects in the DB.
Any pointers/hints if the below is doable in SQL only, please?

Our ORacle DB version is 19c.

Thanks in advance!!!
Aleks..

CREATE OR REPLACE FUNCTION Code128(P_INPUT varchar2) return varchar2
DETERMINISTIC
IS
/*
* This function is governed by the GNU Lesser General Public License (GNU LGPL)
* Parameters : a string
* Return : * a string which give the bar code when it is dispayed with CODE128.TTF font
* * an empty string if the supplied parameter is no good
*
* the CODE128.TTF font can be source from http://sourceforge.net/projects/openbarcodes
*
* written by David Pyke (eselle@sourceforge.net)
* based on code found at http://grandzebu.net/informatique/codbar-en/codbar.htm
* http://sourceforge.net/projects/openbarcodes
*
*/
i NUMBER;
chksum NUMBER;
mini NUMBER;
dummy NUMBER;
tableB BOOLEAN;
c_pinput_length CONSTANT NUMBER := LENGTH(p_input);
v_retval VARCHAR2(100);
FUNCTION usetableC(p_start NUMBER, p_cnt NUMBER)
RETURN BOOLEAN
IS
-- Determine if the p_cnt characters from p_start are numeric
BEGIN
FOR x in p_start .. p_start+p_cnt LOOP

  IF(x > c\_pinput\_length ) THEN  
    RETURN FALSE;  
  END IF;  
  IF ASCII(SUBSTR(p\_input,x,1)) \<48 or ASCII(SUBSTR(p\_input,x,1)) >57 THEN  
    RETURN FALSE;  
  END IF;  
END LOOP;  

RETURN TRUE;  

END usetableC;
BEGIN
IF c_pinput_length = 0 THEN
RETURN NULL;
END IF;
--Check for valid characters
--for c in 1..length(p_input) loop
-- if not 21 -> 126 or 203.. return null
--END LOOP;\
tableB := true;
i := 1;
FOR c in 1..c_pinput_length LOOP
EXIT when I >c_pinput_length;
IF tableB THEN
IF i =1 or (i+3) =length(p_input) THEN
mini := 4;
ELSE
mini := 6;
END IF;
IF usetableC(i,mini) THEN
IF i=1 THEN
v_retval := CHR(210); --start with tableC
ELSE
v_retval := v_retval||CHR(204); --switch to tabelC
END IF;
tableB := FALSE;
ELSE
IF i = 1 THEN
v_retval := CHR(209);
END IF; --Starting with table B
END IF;
END IF;
IF NOT tableB THEN
--We are on table C, try to process 2 digits
mini := 2;
if usetableC(I,MINI) THEN
dummy := TO_NUMBER(SUBSTR(p_input, i, 2));
IF(dummy < 95) THEN
dummy := dummy +32;
ELSE
dummy := dummy +105;
END IF;
v_retval := v_retval || CHR(dummy);
i := i + 2;
ELSE
v_retval := v_retval || CHR(205);
tableB := TRUE;
END IF;
END IF;
IF tableB THEN
--Process 1 digit with table B
v_retval := v_retval || SUBSTR(p_input, i, 1);
i := i +1;
END IF;
END LOOP;
--Calculation of the checksum
FOR i IN 1 .. LENGTH(v_retval) LOOP
dummy := ASCII(SUBSTR(v_retval,i,1));
IF(dummy < 127) THEN
dummy := dummy -32;
ELSE
dummy := dummy -105;
END IF;
IF i =1 then chksum := dummy; END IF;

chksum := MOD((chksum + (i-1)\*dummy),103);  

END LOOP;

-- Calculation of the checksum ASCII code
IF chksum <95 THEN
chksum := chksum +32;
ELSE
chksum := chksum +105;
END IF;
--Add the checksum and the STOP codes
v_retval := v_retval || CHR(chksum) || CHR(211);
return v_retval;
END Code128;
/

This post has been answered by mathguy on Mar 4 2022
Jump to Answer
Comments
Post Details
Added on Feb 24 2022
15 comments
2,830 views