Problems with Convert

I have been working to to put the Oracle convert function in the output of my files. I have a couple problems using the OWB. In Warehouse builder, when you try to output to a fixed length file it uses RPAD to make sure the length is as defined in your file. The problem is no matter how you define the file it outputs it as ASCII even though I have it defined as EBCDIC. I have all the fields already padded with EBCDIC spaces but since it is RPAD inside the generated code it addes ASCII spaces because it doesn't translate some of my EBCDIC characters. Another problem I have is the generated code always adds a utls_file.new_line command after each output line. I have found no where in the OWB to stop this finctionality. So again an ASCII LF is added to everyline. I have created a procedure to do what I want it to do but can't get the OWB to replicate. This is the procedure.

CREATE OR REPLACE PROCEDURE "EXTRACTS"."TEST" AS
cursor sll_cur is
SELECT CONVERT(RPAD(BENE_ID,20,' '), 'UTFE') BENE, CONVERT(RPAD(TOTAL_BILLED_AMT,8,' '), 'UTFE') billed from MSIS_claimlt;
sll_rec sll_cur%ROWTYPE;
dat_file utl_file.file_type;
BEGIN
dat_file := utl_file.fopen('JSURS_CLAIMS', 'test_convert.txt', 'w',32767);
utl_file.put(dat_file,'');
utl_file.fclose(dat_file);
OPEN sll_cur;
LOOP
dat_file := utl_file.fopen('JSURS_CLAIMS', 'test_convert.txt', 'A');
FETCH sll_cur INTO sll_rec;
EXIT WHEN sll_cur%NOTFOUND;

utl_file.put(dat_file, sll_rec.BENE || sll_rec.billed);
--utl_file.fflush(dat_file);
utl_file.fclose(dat_file);
END LOOP;
END TEST;

You will notice I have to open the file as write to intialize the file and then open the file each time to output a line. This is because of the potential size of one line where it is not broken by a line feed. It keeps each line in the buffer until a new line is recieved.

Eric