Null Values in CSV file

Question

I am trying to create CSV file to generate data where I want the null value to be kept as a string in the column but it's not fitting properly as per the attached image screenshot

Screenshot

My procedure:

CREATE OR REPLACE procedure ABLEA_NEW.DATA_BAH
as
fHandle UTL_FILE.FILE_TYPE;
err  varchar2(200);
rec  varchar2(300);
cursor data1 is SELECT A."CUSTOMERNAME", A."CUST_ID_N",A."NATIONALID",R."NATIONALITY",A."LOS_EXISTING_CUST_C",A."CIF_NO",A."PASSPORTNO",A."MONTHLY_INCOME",
    A."DOB",A."SEX",A.PAN_NO,C.ADDRESSID,C.PHONE1,C.ADDRESS1,X.AGREEMENTNO,A.FNAME, R.COMP_NAME,R.ACCOTYPE
    from NBFC_CUSTOMER_M R,ABCAS_NEW.NBFC_CUSTOMER_M A,LEA_GUARANTOR_HIRER_DTL B ,ABCAS_NEW.NBFC_ADDRESS_M c,LEA_AGREEMENT_DTL X WHERE A.CUST_ID_N = B.GUARANTOR_HIRER_ID and  C.BPID = B.GUARANTOR_HIRER_ID AND X.AGREEMENTID=B.APPLID AND A.CUST_ID_N=R.CUSTOMERID;


all_data1 data1%rowtype;
begin
fHandle := UTL_FILE.FOPEN('BAHDB', 'DATA_BAH', 'W');
open data1;
loop
fetch data1 into all_data1;
exit when data1%notfound;
rec:=nvl(rpad(all_data1.CUSTOMERNAME,30,' '),'  NotFound  ')||nvl(rpad(all_data1.NATIONALITY,20,' '),'  NOT FOUND  ')||nvl(rpad(all_data1.CUST_ID_N,10,' '),'  NotFound  ')||nvl(rpad(all_data1.NATIONALID,3,' '),'  NotFound  ')||nvl(rpad(all_data1.NATIONALITY,10,''),'  NotFound  ')||
nvl(rpad(all_data1.LOS_EXISTING_CUST_C,7,' '),'  NotFound  ')||nvl(rpad(all_data1.CIF_NO,15,' '),'  NotFound  ')||nvl(rpad(all_data1.PASSPORTNO,15,''),'  NotFound  ')||nvl(rpad(all_data1.MONTHLY_INCOME,8,' '),'  NotFound  ')||nvl(rpad(all_data1.DOB,15,''),'  NotFound  ')||
nvl(rpad(all_data1.DOB,15,' '),'  NotFound  ')|| nvl(rpad(all_data1.ADDRESSID,5,' '),'  NotFound  ')||nvl(rpad(all_data1.AGREEMENTNO,25,' '),'  NotFound  ')||nvl(rpad(all_data1.FNAME,7,' '),'  NotFound  ')||nvl(rpad(all_data1.COMP_NAME,15,' '),'  NotFound  ') ||nvl(rpad(all_data1.ACCOTYPE,15,' '),'  NotFound  ');
UTL_FILE.PUTF(fHandle,rec,' ');
UTL_FILE.PUTF(fHandle, '\n');

end loop;
UTL_FILE.FCLOSE(fHandle);
close data1;
end;
/

CSV is a data exchange protocol (albeit a crude one). Why do you care what the raw file looks like ? Anybody who needs to look at the data could use Excel (other spreadsheet tools are available). Also you should consider using Oracle SQL\*Plus or SQL Developer to export CSV, as these have built-in features which make it easier to generate such files. [Find out more](https://stackoverflow.com/q/643137/146325) — APC, Apr 11 '18 at 06:30
Becouse I need to generate the file and send it to our backend team, in order to read from it , this well be daily generated — mosa, Apr 11 '18 at 06:34

score 2 · Answer 1 · answered Apr 11 '18 at 06:51

Ah, you use the term CSV but you're not generating comma-separated values. Instead you are trying to produce a file where the attributes are aligned by position.

So your problem is that your replacement value for NULL doesn't fit the space allocated for actual values. Consider this one:

nvl(rpad(all_data1.CUST_ID_N,10,' '),'  NotFound  ')

When all_data1.CUST_ID_N is populated you pad the result to ten characters but if it's null you substitute a string which is twelve characters long. That will knock out the offset of the subsequent values by two characters. A safer approach would be:

rpad(nvl(all_data1.CUST_ID_N,'NotFound'),10,' ')

That way all the attributes are aligned, regardless of the source data.

Incidentally, I think this is a terrible approach to signalling null values. Data exporting tools have built-in functionality to handle actual null values, whereas loading this file will require bespoke handling for NotFound. This will be especially annoying for columns which ought to be numeric (such as MONTHLY_INCOME) but which must be loaded as a string and cast to a number datatype (after handling the NotFound).

I replaced the with this rpad(nvl(all_data1.CUST_ID_N,'NotFound'),10,' ') but exception appeared it keep Running with no result — mosa, Apr 11 '18 at 07:13
What exception? Please remember we are not telepathic: we cannot see your screen so we only know what you tell us. — APC, Apr 11 '18 at 07:31
an oracle exception has occurred in your code if the code contains an exception handler, its didn't showing any thing keep running — mosa, Apr 11 '18 at 07:46
That sounds like something "special" in your client or environment is swallowing the exception. There's nothing we can do to help if you can't tell what the actual error is. Try running the procedure in SQL\*Plus or some other vanilla client. — APC, Apr 11 '18 at 07:50

Null Values in CSV file

1 Answers1