PL/SQL to build a unique barcode 2

[edrossi]

I need a little PL/SQL help. I am trying to create a 10 character field that has a unique set of characters in it that will be later turned into a barcode. The methodology I have to use is the following:

The first 3 characters will be hard coded, for example, "CCD"

The remaining 7 characters need to be unique and as random as possible, but can only use the following charaters:

CDFHJKLNPRTVXYZ0123456789

The final result will look something like:

CCD113K1TC
CCDP13K9XY
CCDXXJ1772
.........
.........

This will be updating a 9 million record file, so, I am also worrying about optimization of the update.

Any ideas would be much aprreciated.

ER

 

[SantaMufasa]

Ed,

Those 25 characters taken 7 digits at a time offer 6,103,515,624 combinations. Could you please advise your business need for randomizing the barcodes? Although we can offer a random-number-generation algorithm, it would certainly make it simpler if I created a sequential-number-generator function for you using the character set you mentioned.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:18 (30Sep04) UTC (aka "GMT" and "Zulu"), 20:18 (29Sep04) Mountain Time)

 

[edrossi]

SantaMufasa:

Absolutely, a sequential-number-generator would be fine, it only has to be random as possible. So, we can use a sequential-number-generator if we order the list by, say, customer number. So, no problem.

Thanks,
ed

 

[SantaMufasa]

Ed,

Pardon my dimness, but what the heck do you mean, that you want "a sequential-number-generator...to be random as possible"?

So that we can help you best, please tell us
1) The business reason for randomness
2) Why not use just numerals 0-9 to formulate your 9,000,000 numbers?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:28 (30Sep04) UTC (aka "GMT" and "Zulu"), 09:28 (30Sep04) Mountain Time)

 

[edrossi]

Santa:

1) As random as possible, meaning, it does not have to be randon, what is more important is that it is unique.

2) Because sometimes the digits are more than 7 in length, so we will use alphanumerics to increase the number of different combinations we can use.

Good questions, I hope I am making sense.

ed

 

[SantaMufasa]

Ed,

Sorry it took so long to reply. (I just finished 4 hours at the Emergency Room getting my wife stitched up from slicing her head on a hanging piece of metal.)

Now that I more clearly understand your specifications, I can offer you a function that produces unique barcode ids in the format CCDnnnnnnn, where “nnnnnnn” represents any of the 6,103,515,624 combinations of the characters “0123456789CDFHJKLNPRTVXYZ” taken 7 characters at a time.

I shall present the solution as a reverse engineering from the result back through the component pieces that together yield the result. The “barcode” function depends upon supplying a decimal value which the function translates into the final barcode value. Here is a sample invocation of the function that yields barcode values:

select barcode(5783923) from dual;

BARCODE(5783923)
----------------
CCD00JT46Y

Although one can provide random values to “barcode” (as I did in the example, above), the best method to ensure uniqueness is to generate the decimal values to barcode from an Oracle sequence generator. Here is the SQL code to create an Oracle sequence generator that begins at the value 1 and provides sequential decimal values until it reaches its physical maximum of 28 digits (base 10).

create sequence barcode_decimal_seq;

Then, to use the barcode function to assign barcode values to products, you can say:

UPDATE s_product SET barcode = barcode(barcode_decimal_seq.nextval);

33 rows updated.

To see the results of the update:

select name, barcode from s_product;

NAME                     BARCODE
------------------------ -----------
Bunny Boot               CCD0000001
Ace Ski Boot             CCD0000002
Pro Ski Boot             CCD0000003
Bunny Ski Pole           CCD0000004
Ace Ski Pole             CCD0000005
Pro Ski Pole             CCD0000006
Junior Soccer Ball       CCD0000007
World Cup Soccer Ball    CCD0000008
World Cup Net            CCD0000009
Black Hawk Knee Pads     CCD000000C
Black Hawk Elbow Pads    CCD000000D
Grand Prix Bicycle       CCD000000F
Himalaya Bicycle         CCD000000H
Grand Prix Bicycle Tires CCD000000J
Himalaya Tires           CCD000000K
New Air Pump             CCD000000L
Slaker Water Bottle      CCD000000N
Safe-T Helmet            CCD000000P
Alexeyer Pro Lifting Bar CCD000000R
Pro Curling Bar          CCD000000T
Prostar 10 Pound Weight  CCD000000V
Prostar 20 Pound Weight  CCD000000X
Prostar 50 Pound Weight  CCD000000Y
Prostar 80 Pound Weight  CCD000000Z
Prostar 100 Pound Weight CCD0000010
Major League Baseball    CCD0000011
Chapman Helmet           CCD0000012
Griffey Glove            CCD0000013
Alomar Glove             CCD0000014
Steinbach Glove          CCD0000015
Cabrera Bat              CCD0000016
Puckett Bat              CCD0000017
Winfield Bat             CCD0000018

33 rows selected.

Now, for the rest of the story. Following is a listing of the code for the “barcodee” function:

create or replace function barcode (decimal_val number) return varchar2 is
	barcode_alpha	varchar2(15)	:= 'CDFHJKLNPRTVXYZ';
	normal_alpha	varchar2(15)	:= 'ABCDEFGHIJKLMNO';
begin
	return 'CCD'||lpad(translate(baseconv(decimal_val,10,25),normal_alpha,barcode_alpha),7,0);
end;
/

function created.

A closer look shows that “barcode” depends upon another function, “baseconv”, which takes care of our base-10-to-base-25 conversion. (Base 25 since there are 25 numerals in your set of valid characters: “0123456789CDFHJKLNPRTVXYZ”.) The “baseconv” code, below, allows for conversions among any bases from base 2 to base 36. (Be sure to create the function “baseconv” before attempting to access the “barcode” function.):

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM NAME: BASECONV.SQL - PL/SQL code to translate positive or 
REM       negative integer values from any base (radix)
REM       (base 36 max.) to its counterpart value in any other
REM       base (base 36 max.).
REM
REM AUTHOR: Dave Hunt
REM         Co-principal, Dasages, LLC
REM         1-801-733-5333
REM
REM **************************************************************
REM Usage:
REM       BASECONV (<value>,<source-base>,<target-base>)
REM
REM   ...where <value> is some "number" using valid "numerals" in
REM   the <source-base>. For example, in base 2, valid numerals are
REM   "0" and "1"; in base 36, valid numerals are "0" thru "z".
REM   When using bases 11 thru 36, you specify alpha characters for
REM   numerals beyond decimal '9'. Alpha characters may be in either
REM   upper or lower case.
REM
REM   ...where <source-base> is any base (radix) between 2 and 36.
REM   ...where <target-base> is any base (radix) between 2 and 36.
REM   Be sure to specify both source and target bases in base 10.
REM   
REM Sample invocations:
REM
REM   col x heading "Conversion" format a20
REM   select baseconv(255,10,2) x from dual;
REM   Conversion
REM   ----------
REM   11111111
REM
REM   select baseconv('Zebra',36,10) x from dual;
REM   Conversion
REM   ----------
REM   59454982
REM
REM   select baseconv('aaaaaaaaaaaaaaaa',16,2) x from dual;
REM   Conversion
REM   ----------------------------------------------------------------
REM   1010101010101010101010101010101010101010101010101010101010101010
REM **************************************************************
Create or replace function BaseConv
	( ValueIn	in varchar2	-- incoming value to convert
	, RadFrom	in number	-- source base
	, RadOut	in number	-- target base
	)
	return 		varchar2	-- outgoing value in target base
is
	ValIn		varchar2(1000);
	Sign		char;
	LenIn		number;
	Base10Value	number;
	DigitPool	varchar2(50)	:= '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DigitHold	varchar(1);
	HighPower	number;
	CurrValue	number;
	CurrDigit	number;
	ResultingValue	varchar(2000);
	function GetDigit10 (InDigit in varchar2, RadIn in number) return number
	is
		bad_digit	exception;
		pragma exception_init(bad_digit,-6502);
	begin
		if InDigit = '0' then
			return 0;
		end if;
		DigitHold := upper(InDigit);
		for i in 1..RadIn-1 loop
			if DigitHold = substr(DigitPool,i,1) then
				return i;
			end if;
		end loop;
		raise_application_error(-20000,'Illegal digit, "'||InDigit||'" for base "'||RadIn||'"');
	end;
begin
	ValIn	:=	ValueIn;
	if substr(ValIn,1,1) = '-' then
		Sign	:= '-';
		ValIn	:= substr(ValIn,2);
	else
		Sign	:= null;
	end if;
	LenIn := length(nvl(ValIn,'0'));
	Base10Value	:= 0;
	for i in 1..LenIn loop
		Base10Value	:= Base10Value +
			GetDigit10(substr(ValIn,i,1),RadFrom) * power(RadFrom,LenIn-i);
	end loop;
	for i in 1..1000 loop
		if power(RadOut,i) > Base10Value then
			HighPower := i-1;
			exit;
		end if;
	end loop;
	CurrValue	:= Base10Value;
	ResultingValue	:= null;
	for i in 0..HighPower loop
		CurrDigit := floor(Currvalue / power(RadOut,HighPower-i));
		CurrValue := Currvalue - (CurrDigit * power(RadOut,HighPower-i));
		if CurrDigit = 0 then
			ResultingValue := ResultingValue||'0';
		else
			ResultingValue := ResultingValue||substr(DigitPool,CurrDigit,1);
		end if;
	end loop;
	return sign||ResultingValue;
end;
/
************************************************************************************************

So, to implement all of this in your environment, do so in “bottom-up” order from the presentation above.

Let us know if this does what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:44 (01Oct04) UTC (aka "GMT" and "Zulu"), 18:44 (30Sep04) Mountain Time)

 

[edrossi]

Santa:

You rock......... It was perfect....

Ed

 

[Beilstwh]

I was just looking around for a base conversion routine to generate unique PO numbers using an Oracle sequence (too lazy to write one myself). To maximize the number of PO numbers allowed (6 characters max), I wanted to use base 36 conversion. I just wanted to say that your baseconv routine was exactly what I was looking for. Thanks very much for posting it.

 

[SantaMufasa]

My pleasure, Beilstwh. I'm pleased you found it useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.