How do I add unprintable ascii control characters to a VARCHAR2 in Oracle PL/SQL?

user_11605977 0
Viewed 157

I am supporting a label printing system which uses PL/SQL and an ORACLE database to fill in values that are then encoded in various barcodes (among other things). For a specific 2 dimensional data matrix barcode I'm attempting to create, I need to include some unprintable characters as control characters for the system that is scanning the barcode.

The problem is that I have no idea how to encode those characters.

Example:

v_string VARCHAR2(1000);
...
v_string := '[)><RS>06<GS>12SC<GS>16S2'||'<GS>V'||:(IN:-SPLR-)||'<GS>3S'||v_serial||'<GS>P'||:(IN:-CUSTITEM-)||'<GS>Q'||v_boxqty||'<GS>1T'||:(IN:-LOT-)||'<GS>15K123456789123'||:(IN:-PRODSEQ-)||'<RS><EOT>';

Then v_string is passed as a parameter to a function that actually populates the barcode on the printed label. The problem is that every <GS>, <RS>, and <EOT> in the string are supposed to be control characters. I have the ascii decimal and hex values for those control characters, but no idea how to add them into the above string instead of the placeholders.

Any help would be appreciated.

ascii plsql oracle
2 Answers

You can use the chr() function to supply individual ASCII characters:

CHR returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set.

(You can also use unistr() for Unicode characters, but that doesn't seem to be necessary in this case; but note the ASCII/EBCDIC message in the chr() document...)

For those control characters you can use chr(4), chr(29) and chr(30):

v_string := '[)>'||chr(30)||'06'||chr(29)||'12SC'||chr(29)||'16S2'||chr(29)||'V'||:(IN:-SPLR-)||chr(29)||'3S'||v_serial||chr(29)||'P'||:(IN:-CUSTITEM-)||chr(29)||'Q'||v_boxqty||chr(29)||'1T'||:(IN:-LOT-)||chr(29)||'15K123456789123'||:(IN:-PRODSEQ-)||chr(30)||chr(4);

db<>fiddle showing the generated string - the printable parts, anyway - and it's dumped value, so you can see the 4/29/30 characters are actually there.

You could also build your string as you have it, then pass it through replace() to replace the <GS> etc. placeholders with the chr() values.

user_2663040

If you have the ASCII value, you can use the CHR function to construct your string, as in:

v_string:='xx'||chr(10).....
user_73986530
Related