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 |
|
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 |
|
Michel Cadot
Messages: 68693 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 #661207 is a reply to message #661036] |
Fri, 10 March 2017 08:03 |
|
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;
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:40:58 CDT 2024
|