UTL_FILE Generating Empty file

Question

I have a requirement to generate .txt files in a particular path, i tried in 11i instance, its worked fine. but the issue is when i try to add more columns(80 columns only) its generating empty file only. when i remove some columns again its worked fine.

I tried in R12 instance there its working fine and its generating more than 120 columns as a single line. same code is not working in 11i instance, is there any set up required from DBA side or any limitation in 11i instance. Can any one please help in this.

Answer 1

PROCEDURE xxx_test (    RETCODE IN VARCHAR2,    ERRBUFF IN VARCHAR2) IS
FILENAME  UTL_FILE.FILE_TYPE;
v_file_name varchar2(250);
v_count NUMBER;
CURSOR C1 IS
SELECT  *
FROM
GRC.GR_PO_EDI_TEMP_ARCHIVE GPET
,OE_ORDER_HEADERS_ALL       OOHA
,OE_ORDER_LINES_ALL         OOLA
WHERE GPET.ORDERNUMBER              = OOHA.CUST_PO_NUMBER
AND   OOHA.HEADER_ID                = OOLA.HEADER_ID
AND   OOHA.ORG_ID                   = OOLA.ORG_ID
AND     nvl(GPET.PO_ACK_STATUS,'N') <> 'Y'  ;
i C1%ROWTYPE;   
BEGIN
BEGIN
SELECT 'orders_1.1_'||to_char(sysdate,'yyyymmddHH24MMSS')||'.txt' INTO v_file_name FROM dual;
exception
WHEN others THEN
v_file_name := NULL;
RAISE_APPLICATION_ERROR(-20201,'File Name Generation failed.!!');
END;
FILENAME:= UTL_FILE.FOPEN('/home/outbox',v_file_name,'w',32767);
/* THIS WILL CREATE THE HEADING IN the FILE */
UTL_FILE.PUT_LINE(FILENAME,
'orderNumber' ||'    '||
'orderDate' ||'    '||
'orderType' ||'    '||
'currency' ||'    '||
'paymentTerm' ||'    '||
'paymentTermText' ||'    '||
'customer' ||'    '||
'supplier' ||'    '||
'shipToSite' ||'    '||
'shipFromSite' ||'    '||
'freightTerm' ||'    '||
'freightTermMeaning' ||'    '||
'freightCarrier' ||'    '||
'buyerNotesHeader' ||'    '||
'supplierNotesHeader' ||'    '||
'buyerName' ||'    '||
'buyerEmail' ||'    '||
'buyerContact' ||'    '||
'lineNumber' ||'    '||
'item' ||'    '||
'itemDescriptionFreeForm' ||'    '||
'UOM' ||'    '||
'price' ||'    '||
'quantity' ||'    '||
'reqDeliveryDate' ||'    '||
'supplierSuggestedPrice' ||'    '||
'buyerNotesLines' ||'    '||
'supplierNotesLines' ||'    '||
'lineShippedQuantity' ||'    '||
'lineReceivedQuantity' ||'    '||
'lineBilledQuantity' ||'    '||
'scheduleNum' ||'    '||
'scheduleQuantity' ||'    '||
'scheduleDeliveryDate' ||'    '||
'promisedDeliveryDate' ||'    '||
'quantityPromised' ||'    '||
'buyerNotesSchedule' ||'    '||
'supplierNotesSchdule' ||'    '||
'quantityReceived' ||'    '||
'quantityShipped' ||'    '||
'Action' ||'    '||
'Id' ||'    '||
'shipToSiteName' ||'    '||
'shipToSiteaddress1' ||'    '||
'shipToSiteaddress2' ||'    '||
'shipToSitecity' ||'    '||
'shipToSitestate' ||'    '||
'shipToSitepostalcode' ||'    '||
'shipToSitecountry' ||'    '||
'totallines' ||'    '||
'orderlinesquantitysum' ||'    '||
'scheduleStatus' ||'    '||
'erpHeaderId' ||'    '||
'erpLineId' ||'    '||
'erpScheduleId' ||'    '||
'billToSiteName' ||'    '||
'billToSiteaddress1' ||'    '||
'billToSiteaddress2' ||'    '||                         
'billToSitecity' ||'    '||
'billToSitestate' ||'    '||
'billToSitepostalcode' ||'    '||
'billToSitecountry' ||'    '||
'supplieritemnumber' ||'    '||
'manufacturer' ||'    '||
'flexField1' ||'    '||
'flexField2' ||'    '||
'flexField3' ||'    '||
'flexField4' ||'    '||
'flexField5' ||'    '||
'flexField6' ||'    '||
'flexField7' ||'    '||
'flexField8' ||'    '||
'flexField9' ||'    '||
'flexField10' ||'    '||
'SupplierName' ||'    '||
'LineValue' ||'    '||
'OrderValue' ||'    '||
'customererpitemid' ||'    '||
'suppliererpitemid' ||'    '||
'salesOrder' ||'    '
);
OPEN c1;
loop
fetch c1 INTO i;
exit WHEN c1%notfound;

UTL_FILE.PUT_LINE(FILENAME,
i.ordernumber||'    '||
i.orderdate||'    '||
i.ordertype||'    '||
i.currency||'    '||
i.paymentterm||'    '||
i.paymenttermtext||'    '||
i.customer||'    '||
i.supplier||'    '||
i.shiptosite||'    '||
i.shipfromsite||'    '||
i.freightterm||'    '||
i.freighttermmeaning||'    '||
i.freightcarrier||'    '||
i.buyernotesheader||'    '||
i.suppliernotesheader||'    '||
i.buyername||'    '||
i.buyeremail||'    '||
i.buyercontact||'    '||
i.linenumber||'    '||
i.item||'    '||
i.itemdescriptionfreeform||'    '||
i.uom||'    '||
i.price||'    '||
i.quantity||'    '||
i.reqdeliverydate||'    '||
i.suppliersuggestedprice||'    '||
i.buyernoteslines||'    '||
i.suppliernoteslines||'    '||
i.lineshippedquantity||'    '||
i.linereceivedquantity||'    '||
i.linebilledquantity||'    '||
i.schedulenum||'    '||
i.schedulequantity||'    '||
i.scheduledeliverydate||'    '||
i.promiseddeliverydate||'    '||
i.quantitypromised||'    '||
i.buyernotesschedules||'    '||
i.suppliernotesschdules||'    '||
i.quantityreceived||'    '||
i.quantityshipped||'    '||
i.action||'    '||
i.id||'    '||
i.shiptositename||'    '||
i.shiptositeaddress1||'    '||
i.shiptositeaddress2||'    '||
i.shiptositecity||'    '||
i.shiptositestate||'    '||
i.shiptositepostalcode||'    '||
i.shiptositecountry||'    '||
i.totallines||'    '||
i.orderlinesquantitysum||'    '||
i.schedulestatus||'    '||
i.erpheaderid||'    '||
i.erplineid||'    '||
i.erpscheduleid||'    '||
i.billtositename||'    '||
i.billtositeaddress1||'    '||
i.billtositeaddress2||'    '||
i.billtositecity||'    '||
i.billtositestate||'    '||
i.billtositepostalcode||'    '||
i.billtositecountry||'    '||
i.supplieritemnumber||'    '||
i.manufacturer||'    '||
i.flexfield1||'    '||
i.flexfield2||'    '||
i.flexfield3||'    '||
i.flexfield4||'    '||
i.flexfield5||'    '||
i.flexfield6||'    '||
i.flexfield7||'    '||
i.flexfield8||'    '||
i.flexfield9||'    '||
i.flexfield10||'    '||
i.suppliername||'    '||
i.linevalue||'    '||
i.ordervalue||'    '||
i.customererpitemid||'    '||
i.suppliererpitemid||'    '||
i.salesordernumber||'    '
);
END LOOP;   
UTL_FILE.FCLOSE(FILENAME);
fnd_file.put_line(fnd_file.output,'File created & File Name:  '||v_file_name      );
dbms_output.put_line(v_file_name||' '||'file created '||'for Order Number:   '||i.ordernumber);
exception  
WHEN UTL_FILE.INVALID_PATH THEN
null;
END xxx_test;

Answer 2

I have had the same problem.

It turned out that it was a file size issue, less than 70k and the file would copy ok, but 70k or over and it would end up as 0 bytes.

It was not an oracle error, but how the shared drives had been mounted.

If you can try the fcopy to a local drive, you should find that it works ok.

Get whoever is responsible for mounting the shared drives to fix the problem.