Home » SQL & PL/SQL » SQL & PL/SQL » FOR UPDATE NOWAIT and ORA-29285: file write error (Oracle 11g)
FOR UPDATE NOWAIT and ORA-29285: file write error [message #661009] Sat, 04 March 2017 13:01 Go to next message
Prabhu Hooli
Messages: 3
Registered: March 2017
Junior Member
Dear Experts,

I am having problem with a oracle proc inside package which writes named xxx.txt file on the linux server. cursor with FOR UPDATE NOWAIT clause fetch data from the tables and write data into the file using the UTL_FILE oracle functions.

A dbms job has been set to run the proc daily to check any new data and write into the file on server. this procedure throwing this error ORA-29285: file write error while running this function UTL_FILE.FREMOVE. problem is, same proc runs without any errors next day.

please help me to understand the problem here. is issue in rows locked in cursor? if that is the case, why proc runs successfully on next day?

Thanks
Prabhu
Re: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661010 is a reply to message #661009] Sat, 04 March 2017 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

ORA-29285: file write error
 *Cause:  Failed to write to, flush, or close a file.
 *Action: Verify that the file exists, that it is accessible, and that
          it is open in write or append mode.
The error comes from the write and only the write and nothing about how the data are retrieved.
Now if you want more help you MUST copy and paste the code and an execution that fails with the complete error stack.

Re: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661030 is a reply to message #661010] Sun, 05 March 2017 23:19 Go to previous messageGo to next message
Prabhu Hooli
Messages: 3
Registered: March 2017
Junior Member
My issue similar to the issue had been posted on the OTN https://community.oracle.com/thread/2452590 but, No solution in that post too.



[Edit MC: fix link]

[Updated on: Mon, 06 March 2017 00:53] by Moderator

Report message to a moderator

Re: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661036 is a reply to message #661030] Mon, 06 March 2017 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No solution because you did not investigate in what has been told to you.
This is indeed the same issue here.
Did you investigate what I said?

Re: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661207 is a reply to message #661036] Fri, 10 March 2017 08:03 Go to previous messageGo to next message
Prabhu Hooli
Messages: 3
Registered: March 2017
Junior Member
Hi Michel, attached code from my program. you may see some unwanted to variables and some comments. there is debug package which captures the program flow and inserts inserts into log table.

error, i am seeing here is ORA-29285: file write error and debug message captured is right before UTL_FILE.fclose (fileHandle1).

File has been created on the directory, so this is not permission issue.

-----
/* Formatted on 3/10/2017 7:26:15 PM (QP5 v5.256.13226.35538) */
DECLARE
c_location1 CONSTANT VARCHAR2 (100) := 'LAT_OUT';
c_fileName1 CONSTANT VARCHAR2 (100) := 'table3_test3.txt';
--:= db_utilities.get_parameter ('table3') ;
c_openmode1 CONSTANT VARCHAR2 (1) := 'W';
c_writetime CONSTANT VARCHAR (14)
:= TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') ;
c_linesize1 CONSTANT PLS_INTEGER := 303;
c_filename2 CONSTANT VARCHAR2 (100) := c_filename1 || '_' || c_writetime;
c_intfctype CONSTANT VARCHAR2 (4) := 'TRNS';
c_intfctyp2 CONSTANT VARCHAR2 (4) := 'AMRT';
c_intfctyp3 CONSTANT VARCHAR2 (4) := 'SRND';

v_line_cnt PLS_INTEGER := 0;
v_exists BOOLEAN := FALSE;
v_length PLS_INTEGER := 0;
v_block PLS_INTEGER := 0;

fileHandle1 UTL_FILE.file_type;
fileHandle2 UTL_FILE.file_type;
v_uid lat_interfaces.int_uid%TYPE;
v_path VARCHAR2 (4000); -- For err msgs.
v_api table1.api_well_code%TYPE; -- For err msgs.
v_well table1.well_name%TYPE; -- For err msgs.

i PLS_INTEGER; -- must match apiTabTyp index
v_unprocessed PLS_INTEGER;
v_amort_rec amortRecTyp;

file_exists EXCEPTION;
api_sap_mismatch EXCEPTION;
balance_posted EXCEPTION;
surr_sap_mismatch EXCEPTION;



CURSOR lock_cur2
IS
SELECT s.calc_uid, s.pl_calc_uid
FROM table3 s, lan_amort_onlys a
WHERE s.calc_uid = a.calc_uid
AND s.status = 'W'
AND s.intfc_uid IS NULL
AND s.intfc_line IS NULL
FOR UPDATE --OF s.status
NOWAIT;



CURSOR sap_cur2
IS
SELECT RPAD (s.intfc_type, 4) -- intfc_type
|| RPAD (s.co_code, 4) -- co_code
|| RPAD (NVL (TRIM (TO_CHAR (s.npl_asset)), ' '), 12) -- npl_asset
|| RPAD (NVL (TRIM (TO_CHAR (s.npl_subasset)), ' '), 4) -- npl_subasset
|| RPAD (' ', 18) -- npl_serial
|| RPAD (' ', 10) -- npl_cctr
|| REPLACE (
TRIM (
TO_CHAR (NVL (s.npl_amt, 0) * -100, 'S000000000000')),
'+',
'0') -- npl_amt
|| RPAD (s.npl_curr, 5) -- npl_curr
|| REPLACE (
TRIM (TO_CHAR (NVL (NULL, 0) * 1000, 'S000000000000')),
'+',
'0') -- npl_old_qty
|| REPLACE (
TRIM (TO_CHAR (NVL (NULL, 0) * 1000, 'S000000000000')),
'+',
'0') -- npl_new_qty
|| RPAD (' ', 3) -- npl_uom
|| RPAD (' ', 50) -- npl_asset_desc1
|| RPAD (' ', 50) -- npl_asset_desc2
|| RPAD (s.npl_cmpl_ind, 1) -- npl_cmpl_ind
|| LPAD (NVL (NULL, '0'), 12, '0') -- pl_asset
|| RPAD (' ', 4) -- pl_subasset
|| RPAD (' ', 18) -- pl_serial
|| RPAD (' ', 10) -- pl_cctr
|| TRIM (TO_CHAR (NVL (NULL, 0) * 100, '0000000000000')) -- pl_amt
|| RPAD (' ', 5) -- pl_curr
|| TRIM (TO_CHAR (NVL (NULL, 0) * 1000, '0000000000000')) -- pl_old_qty
|| TRIM (TO_CHAR (NVL (NULL, 0) * 1000, '0000000000000')) -- pl_new_qty
|| RPAD (' ', 3) -- pl_uom
|| RPAD (' ', 50) -- pl_asset_desc1
|| RPAD (' ', 50) -- pl_asset_desc2
|| '\n'
output_text,
a.acct_mo,
s.calc_uid,
s.pl_calc_uid
FROM table3 s, lan_amort_onlys a
WHERE s.calc_uid = a.calc_uid
AND s.pl_calc_uid = a.calc_uid
AND s.status = 'R'
AND s.intfc_uid IS NULL
AND s.intfc_line IS NULL
AND s.intfc_type = c_intfctyp2
ORDER BY s.calc_uid, s.pl_calc_uid
FOR UPDATE NOWAIT;


CURSOR sap_cur3
IS
SELECT RPAD (s1.intfc_type, 4) -- intfc_type
|| RPAD (s1.co_code, 4) -- co_code
|| RPAD (NVL (TRIM (TO_CHAR (s1.npl_asset)), ' '), 12) -- npl_asset
|| RPAD (NVL (TRIM (TO_CHAR (s1.npl_subasset)), ' '), 4) -- npl_subasset
|| RPAD (' ', 18) -- npl_serial
|| RPAD (' ', 10) -- npl_cctr
|| REPLACE (
TRIM (
TO_CHAR (NVL (ABS (s1.npl_amt), 0) * 100,
'S000000000000')),
'+',
'0') -- npl_amt
|| RPAD (s1.npl_curr, 5) -- npl_curr
|| TRIM (TO_CHAR (NVL (NULL, 0) * 1000, '0000000000000')) -- npl_old_qty
|| REPLACE (
TRIM (
TO_CHAR (NVL (ABS (s1.npl_new_qty), 0) * 1000,
'S000000000000')),
'+',
'0') -- npl_new_qty
|| RPAD (' ', 3) -- npl_uom
|| RPAD (
DECODE (s1.npl_cmpl_ind,
'Y', 'FULLY SURRENDERED ',
'PARTIAL SURRENDER ')
|| npl_asset_desc1,
50) -- npl_asset_desc1
|| RPAD (' ', 50) -- npl_asset_desc2
|| RPAD (s1.npl_cmpl_ind, 1) -- npl_cmpl_ind
|| LPAD (NVL (TRIM (TO_CHAR (s1.pl_asset)), '0'), 12, '0') -- pl_asset
|| RPAD (NVL (TRIM (TO_CHAR (s1.pl_subasset)), ' '), 4) -- pl_subasset
|| RPAD (' ', 18) -- pl_serial
|| RPAD (' ', 10) -- pl_cctr
|| TRIM (TO_CHAR (NVL (NULL, 0) * 100, '0000000000000')) -- pl_amt
|| RPAD (' ', 5) -- pl_curr
|| TRIM (TO_CHAR (NVL (NULL, 0) * 1000, '0000000000000')) -- pl_old_qty
|| TRIM (TO_CHAR (NVL (NULL, 0) * 1000, '0000000000000')) -- pl_new_qty
|| RPAD (' ', 3) -- pl_uom
|| RPAD (' ', 50) -- pl_asset_desc1
|| RPAD (' ', 50) -- pl_asset_desc2
|| '\n'
output_text,
a1.surr_uid,
s1.calc_uid,
s1.pl_calc_uid,
a1.lease
FROM table3 s1, table4 ac1, table2 a1
WHERE s1.calc_uid = ac1.calc_uid
AND ac1.surr_uid = a1.surr_uid
AND s1.status = 'R'
AND s1.intfc_uid IS NULL
AND s1.intfc_line IS NULL
AND s1.intfc_type = c_intfctyp3
AND a1.on_hold_yn = 'N';
BEGIN
debug_pkg.std_step ('LOCK RECORDS FOR PROCESSING');

FOR c00 IN lock_cur2
LOOP
UPDATE table3
SET status = 'R'
WHERE calc_uid = c00.calc_uid AND pl_calc_uid = c00.pl_calc_uid;
END LOOP;

FOR c000 IN lock_cur3
LOOP
UPDATE table3
SET status = 'R'
WHERE calc_uid = c000.calc_uid AND pl_calc_uid = c000.pl_calc_uid;
END LOOP;

-----------------------------
FOR c10 IN sap_cur2
LOOP
--------------------------------------------------------------------------
debug_pkg.std_step ('INCREMENT LINE COUNT B');
--------------------------------------------------------------------------

v_line_cnt := v_line_cnt + 1;

--------------------------------------------------------------------------
debug_pkg.std_step ('FIRST TIME THROUGH LOOP B?');

--------------------------------------------------------------------------

IF v_line_cnt = 1
THEN -- First time through
-----------------------------------------------------------------------
debug_pkg.std_step ('ENSURE INTERFACE NOT RUNNING B');
-----------------------------------------------------------------------

-- Because this cursor loop was opened with NO WAIT option then
-- this loop will err should this interface already be running.

-----------------------------------------------------------------------
debug_pkg.std_step ('REMOVE FILE THAT MAY EXIST B');
-----------------------------------------------------------------------

UTL_FILE.fgetattr (c_location1,
c_filename1,
v_exists,
v_length,
v_block);

IF v_exists
THEN
UTL_FILE.fremove (c_location1, c_fileName1);
END IF;

-----------------------------------------------------------------------
debug_pkg.std_step ('RETRIEVE BACKUP FILE ATTR B');
-----------------------------------------------------------------------

UTL_FILE.fgetattr (c_location1,
c_filename2,
v_exists,
v_length,
v_block);

-----------------------------------------------------------------------
debug_pkg.std_step ('ENSURE BACKUP NOT EXIST B');

-----------------------------------------------------------------------

IF v_exists
THEN
RAISE file_exists;
END IF;

-----------------------------------------------------------------------
debug_pkg.std_step ('OPEN NEW OUTBOUND FILE B');
-----------------------------------------------------------------------

fileHandle1 := UTL_FILE.fopen (c_location1, c_fileName1, c_openmode1);

-----------------------------------------------------------------------
debug_pkg.std_step ('OPEN NEW OUTBOUND BU B');
-----------------------------------------------------------------------

fileHandle2 := UTL_FILE.fopen (c_location1, c_fileName2, c_openmode1);
END IF; -- First time through?

--------------------------------------------------------------------------
debug_pkg.std_step ('PREPEND INTFC LINE B');
--------------------------------------------------------------------------

c10.output_text :=
RPAD (NVL (TRIM (TO_CHAR (111111)), ' '), 6)
|| RPAD (NVL (TRIM (TO_CHAR (v_line_cnt)), ' '), 6)
|| c10.output_text;

--------------------------------------------------------------------------
debug_pkg.std_step ('WRITE OUTBOUND FILE B');
--------------------------------------------------------------------------

UTL_FILE.putf (fileHandle1, c10.output_text);

--------------------------------------------------------------------------
debug_pkg.std_step ('WRITE OUTBOUND BU B');
--------------------------------------------------------------------------

UTL_FILE.putf (fileHandle2, c10.output_text);


debug_pkg.std_step ('UPDATE AS COMPLETED FOR AMRT');

UPDATE table3
SET status = 'C', intfc_uid = 111111, intfc_line = v_line_cnt
WHERE calc_uid = c10.calc_uid AND pl_calc_uid = c10.pl_calc_uid;
END LOOP;



FOR c100 IN sap_cur3
LOOP
--------------------------------------------------------------------------
debug_pkg.std_step ('KEEP TRACKOF APIs');
--------------------------------------------------------------------------


v_surr_tab (c100.surr_uid).lease := c100.lease;

--------------------------------------------------------------------------
debug_pkg.std_step ('INCREMENT LINE COUNT C');
--------------------------------------------------------------------------

v_line_cnt := v_line_cnt + 1;

--------------------------------------------------------------------------
debug_pkg.std_step ('FIRST TIME THROUGH LOOP B?');

--------------------------------------------------------------------------

IF v_line_cnt = 1
THEN -- First time through
-----------------------------------------------------------------------
debug_pkg.std_step ('ENSURE INTERFACE NOT RUNNING C');
-----------------------------------------------------------------------



-----------------------------------------------------------------------
debug_pkg.std_step ('REMOVE FILE THAT MAY EXIST C');
-----------------------------------------------------------------------

UTL_FILE.fgetattr (c_location1,
c_filename1,
v_exists,
v_length,
v_block);

IF v_exists
THEN
UTL_FILE.fremove (c_location1, c_fileName1);
END IF;

-----------------------------------------------------------------------
debug_pkg.std_step ('RETRIEVE BACKUP FILE ATTR C');
-----------------------------------------------------------------------

UTL_FILE.fgetattr (c_location1,
c_filename2,
v_exists,
v_length,
v_block);

-----------------------------------------------------------------------
debug_pkg.std_step ('ENSURE BACKUP NOT EXIST C');

-----------------------------------------------------------------------

IF v_exists
THEN
RAISE file_exists;
END IF;

-----------------------------------------------------------------------
debug_pkg.std_step ('OPEN NEW OUTBOUND FILE C');
-----------------------------------------------------------------------

fileHandle1 := UTL_FILE.fopen (c_location1, c_fileName1, c_openmode1);

-----------------------------------------------------------------------
debug_pkg.std_step ('OPEN NEW OUTBOUND BU C');
-----------------------------------------------------------------------

fileHandle2 := UTL_FILE.fopen (c_location1, c_fileName2, c_openmode1);
END IF; -- First time through?

--------------------------------------------------------------------------
debug_pkg.std_step ('PREPEND INTFC LINE C');
--------------------------------------------------------------------------

c100.output_text :=
RPAD (NVL (TRIM (TO_CHAR (111111)), ' '), 6)
|| RPAD (NVL (TRIM (TO_CHAR (v_line_cnt)), ' '), 6)
|| c100.output_text;

--------------------------------------------------------------------------
debug_pkg.std_step ('WRITE OUTBOUND FILE C');
--------------------------------------------------------------------------

UTL_FILE.putf (fileHandle1, c100.output_text);

--------------------------------------------------------------------------
debug_pkg.std_step ('WRITE OUTBOUND BU C');
--------------------------------------------------------------------------

UTL_FILE.putf (fileHandle2, c100.output_text);

--------------------------------------------------------------------------
debug_pkg.std_step ('MARK ROW AS BEING PROCESSED C');



UPDATE table3
SET status = 'C', intfc_uid = 111111, intfc_line = v_line_cnt
WHERE calc_uid = c100.calc_uid AND pl_calc_uid = c100.pl_calc_uid;
END LOOP; -- Surenders related to processed table3 rows.

-----------------------------------------------------------------------------
debug_pkg.std_step ('PROCESS ASSOCIATED SURR');
-----------------------------------------------------------------------------

i := v_surr_tab.FIRST;

WHILE i IS NOT NULL
LOOP
--------------------------------------------------------------------------
debug_pkg.std_step ('CHECK ALL SURR ROWS PROCESSED');
--------------------------------------------------------------------------

---Other business table validation are done here

END LOOP;



-----------------------------------------------------------------------------
debug_pkg.std_step ('CLOSE OUTBOUND FILE');
-----------------------------------------------------------------------------

UTL_FILE.fclose (fileHandle1);

-----------------------------------------------------------------------------
debug_pkg.std_step ('CLOSE OUTBOUND BU');
-----------------------------------------------------------------------------

UTL_FILE.fclose (fileHandle2);



COMMIT;
EXCEPTION
WHEN OTHERS
THEN


ROLLBACK;
END;
Re: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661208 is a reply to message #661207] Fri, 10 March 2017 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 13285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

Considering the exception handler at the end I don't see how you can be getting any error.
Re: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661209 is a reply to message #661208] Fri, 10 March 2017 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13285
Registered: September 2008
Location: Rainy Manchester
Senior Member
The code is also incomplete - there's no declaration of lock_cur3 for starters
Re: FOR UPDATE NOWAIT and ORA-29285: file write error [message #661210 is a reply to message #661209] Fri, 10 March 2017 08:16 Go to previous message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
>Considering the exception handler at the end I don't see how you can be getting any error.
+1
Previous Topic: How do I total each column of data with multiple queries?
Next Topic: Cant get rid of ORA-14155 after all googling (2 threads merged by bb)
Goto Forum:
  


Current Time: Thu Oct 18 17:00:29 CDT 2018