APEX

I’m replacing an Oracle Forms application that prints barcode labels on a barcode printer that’s attached to the user’s PC with an APEX version of the application. The barcode printer will be attached to the network. The printer requires line feeds in the text file that it receives (carriage returns are not acceptable). The text file is merged with a label format that’s in the printer’s memory. I’m having difficulty outputting the text file with line feeds from APEX. Here’s the 2 methods that I currently use to output the file, as well as the method that I've tried in APEX. I’m new to APEX. Any advice would be greatly appreciated.

Method used to create barcode text file via Oracle Forms:

PROCEDURE Print_labels IS

CURSOR reagent_label_cur IS
SELECT reagent_name,
reagent_concentration,
reagent_conc_units,
reagent_prep_date,
reagent_preparer,
reagent_expire_date,
reagent_storage,
reagent_lot_number,
aliquot_number
FROM lab_reagent_aliquot q,
lab_reagent r
WHERE r.collection_number = :lab_reagent.collection_number
AND q.collection_number = r.collection_number
ORDER BY to_number(substr(q.aliquot_number,
instr(q.aliquot_number,'-')+1,3),'999');

label_type VARCHAR2(8);
file_name VARCHAR2(15);
tlabel_type VARCHAR2(12);
f_desc lab_reagent.reagent_name%TYPE;
f_conc lab_reagent.reagent_concentration%TYPE;
f_conc_unit lab_reagent.reagent_conc_units%TYPE;
f_prepdate lab_reagent.reagent_prep_date%TYPE;
f_prepinit lab_reagent.reagent_preparer%TYPE;
f_expire lab_reagent.reagent_expire_date%TYPE;
f_storage lab_reagent.reagent_storage%TYPE;
f_lotnum lab_reagent.reagent_lot_number%TYPE;
f_aliquot lab_reagent_aliquot.aliquot_number%TYPE;
f_colnum VARCHAR2(10);
f_qty VARCHAR2(2) := '1';
num VARCHAR2(5);
ret_val BOOLEAN;
f_conc_w_unit VARCHAR2(20);
f_exp VARCHAR2(17);

BEGIN
label_type := :lab_reagent.label_format;
f_colnum := :lab_reagent.collection_number;
file_name := 'C:\temp\' || f_colnum || '.txt';
HOST('del '||file_name, NO_SCREEN);

FOR reagent_label_rec IN reagent_label_cur LOOP
f_desc := reagent_label_rec.reagent_name;
f_conc := reagent_label_rec.reagent_concentration;
f_conc_unit:= reagent_label_rec.reagent_conc_units;
f_prepdate := reagent_label_rec.reagent_prep_date;
f_prepinit := reagent_label_rec.reagent_preparer;
f_expire := reagent_label_rec.reagent_expire_date;
f_storage := reagent_label_rec.reagent_storage;
f_lotnum := reagent_label_rec.reagent_lot_number;
f_aliquot := reagent_label_rec.aliquot_number;
f_conc_w_unit := TO_CHAR(f_conc) || f_conc_unit;
tlabel_type := 'FR"'||label_type||'"';
IF f_expire IS NULL THEN
f_exp := 'Periodic Analysis';
ELSE
f_exp := TO_CHAR(f_expire,'DD-MON-YYYY');
END IF;
num := 'P'|| f_qty;
ret_val := WRITE_FILE(file_name, tlabel_type, '?', f_desc, f_conc_w_unit,
f_exp, f_storage, f_lotnum, f_aliquot, num);
IF ret_val <> true THEN
BRING_ALERT('E','Error writing to the file');
END IF;
END LOOP;

HOST('copy '|| file_name || ' lpt1');
END;

FUNCTION WRITE_FILE(f_name VARCHAR2, label_type VARCHAR2,
wild_char VARCHAR2, f_desc VARCHAR2, f_conc VARCHAR2,
f_expire VARCHAR2, f_storage VARCHAR2, f_lotnum VARCHAR2,
f_aliquot VARCHAR2, num VARCHAR2) RETURN BOOLEAN IS
out_f Text_IO.File_Type;

BEGIN
out_f := Text_IO.Fopen(f_name,'a');
Text_IO.Put_Line(out_f,label_type);
Text_IO.Put_Line(out_f,wild_char);
Text_IO.Put_Line(out_f,f_aliquot);
Text_IO.Put_Line(out_f,f_conc);
Text_IO.Put_Line(out_f,f_desc);
Text_IO.Put_Line(out_f,f_storage);
Text_IO.Put_Line(out_f,f_lotnum);
Text_IO.Put_Line(out_f,f_expire);
Text_IO.Put_Line(out_f,num);
Text_IO.New_Line(out_f,1);
Text_IO.Fclose(out_f);
RETURN true;

EXCEPTION
WHEN OTHERS THEN
RETURN false;
END;
***********************************************************************
Method used to create barcode text file from SQLPlus:

set pagesize 0
set recsep off
set trimspool on
column X newline
SPOOL c:\temp\R313_barcodes.txt

SELECT 'FR"REAGENT"' X,
'?' X,
q.aliquot_number X,
r.reagent_concentration || ‘ ‘ || r.reagent_conc_units X,
r.reagent_description X,
r.reagent_storage X,
r.reagent_lot_number X,
NVL(to_char(r.reagent_expire_date,’DD-MON-YYYY’),
’Periodic Analysis’) X,
‘P1’ X
FROM lab_reagent_aliquot q,
lab_reagent r
WHERE r.collection_number = ‘R313’
AND q.collection_number = r.collection_number
ORDER BY to_number(substr(q.aliquot_number,
instr(q.aliquot_number,'-')+1,3),'999');
SPOOL OFF;
*************************************************************************************
This is what I’ve tried in APEX in an "On Load-Before Header" process; however, the text file contains streaming records that don’t contain line feeds (the file contains carriage returns).

begin
owa_util.mime_header('application/octet', FALSE);
htp.p('Content-Disposition: attachment; filename="test.txt"');
owa_util.http_header_close;

for x in (SELECT 'FR"' || 'REAGENT'|| '"' label,
'?' quest,
r.reagent_name,
r.reagent_concentration || ' ' || r.reagent_conc_units amt,
NVL(TO_CHAR(r.reagent_expire_date,'DD-MON-YYYY'),
'Periodic Analysis') periodic,
r.reagent_storage,
r.reagent_lot_number,
q.aliquot_number,
'P1'
FROM lab_reagent_aliquot q,
lab_reagent r
WHERE r.reagent_db_id = :P11_REAGENT_DB_ID
AND q.reagent_db_id = r.reagent_db_id
AND to_number(substr(q.aliquot_number,
instr(q.aliquot_number, '-') +1, 3), '999')
>= :P11_BEGIN_ALIQUOT
AND to_number(substr(q.aliquot_number,
instr(q.aliquot_number, '-') +1, 3), '999')
<= :P11_END_ALIQUOT
ORDER BY to_number(substr(q.aliquot_number,
instr(q.aliquot_number, '-') +1, 3), '999'))

loop
-- print out a portion of a row, separated by ASCII character for LF
-- (barcode printer needs line feeds and not carriage returns)
htp.prn(x.label || chr(10));
htp.prn(x.quest || chr(10));
htp.prn(x.reagent_name || chr(10));
htp.prn(x.amt || chr(10));
htp.prn(x.periodic || chr(10));
htp.prn(x.reagent_storage || chr(10));
htp.prn(x.reagent_lot_number || chr(10));
htp.prn(x.aliquot_number || chr(10));
htp.prn('P1' || chr(10));
end loop;

htmldb_application.g_unrecoverable_error := true;
owa_util.redirect_url('10');
end;