exp_data ORA-29285: file write error

Hi,
I am using the function EXP_DATA created by Sloba
-- Version 1.0
It works perfect for 13 of my 14 files.
One of the files fails with ORA-29285: file write error

It does write 8 of the records to the file.
The first record that it fails on is 2113 in length - and the max length could potentially be around 10000 so I modified the fopen as follows:
l_output := utl_file.fopen( p_dir, p_filename, 'w',32767 ) - and the max individual column could be 1500 chars.
It still fails.  I also removed all of the CHR(10) and CHR(13) from the fields.  Any idea what could be wrong?

Thanks - cathie
KathysFriendDBAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Did you modify the function?
Does the table you are trying to use have a CLOB or VARCHAR2 with more than 2000 characters?

If you are using the function as-is from the link in your previous question, it appears a column value can only be 2000 characters.  Anything over that, it might have problems.
KathysFriendDBAAuthor Commented:
No - the max column size is varchar2(1500)
slightwv (䄆 Netminder) Commented:
I cannot reproduce the problem you describe using the function from the previous post after making the 32767 change on fopen.

Here is my complete test:
drop table tab1 purge;
create table tab1(
	col1 varchar2(1500),
	col2 varchar2(1500),
	col3 varchar2(1500),
	col4 varchar2(1500),
	col5 varchar2(1500),
	col6 varchar2(1500),
	col7 varchar2(1500),
	col8 varchar2(1500),
	col9 varchar2(1500),
	col10 varchar2(1500)
);

insert into tab1 values(
lpad('a',1500,'a'),
lpad('a',1500,'a'),
lpad('a',1500,'a'),
lpad('a',1500,'a'),
lpad('a',1500,'a'),
lpad('a',1500,'a'),
lpad('a',1500,'a'),
lpad('a',1500,'a'),
lpad('a',1500,'a'),
lpad('a',1500,'a')
);
commit;

select length(col1) + length(col2) + length(col3) + length(col4) + length(col5) + length(col6) + length(col7) + length(col8) + length(col9) + length(col10) from tab1;

declare
	l_rows number;
begin
	l_rows := exp_data('select * from tab1',chr(9),'MYDIR','q.txt');
end;
/

Open in new window


Can you run the above example?

If so, there is something about your table or data.  Post your table definition and I'll revise my test to simulate your table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

KathysFriendDBAAuthor Commented:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148
ORA-06512: at "SYS.UTL_FILE", line 403
ORA-06512: at "SYS.UTL_FILE", line 860
ORA-06512: at "ASSIST.EXP_DATA", line 69
ORA-06512: at line 4

q.txt contains:
COL1      COL2      COL3      COL4      COL5      COL6      COL7      COL8      COL9      COL10
slightwv (䄆 Netminder) Commented:
I didn't see where you answered my first question:
Aside from the change to the fopen call, did you change anything else in the function?

Can you post the function code you are using?

Here is what I used.

Original code from:
https://slobaexpert.wordpress.com/2012/08/21/exporting-tab-delimited-or-csv-from-oracle/

I only modified this line:
 l_output := utl_file.fopen( p_dir, p_filename, 'w', 32767 );

/***************************************************************************
-- FUNCTION EXP_DATA
-- PURPOSE: This Function exports the data to a tab delimited file.
-- This is a generic function which pass any SQL query which will
-- be executed and the rows along with the header will be exported
-- to the directory where we have already defined on our oracle
-- server. Read and Write access should be there on directory.
-- PARAMETERS:
-- P_QUERY
-- Pass the SQL query for which the export has to be done.
--
-- P_SEPARATOR
-- Pass the separator i.e. "|" for tab delimited file
-- or for CSV file use ",".
--
-- P_DIR
-- Name of the directory where the file is been placed.
-- Note: The grant has to be given for the user to the directory
-- before executing the function
-- P_FILENAME
-- The name of the flat file(a text file)
--
-- AUTHOR:
-- Sloba
-- Version 1.0


**************************************************************************/
 
CREATE OR REPLACE FUNCTION exp_data(
 p_query IN VARCHAR2,
 p_separator IN VARCHAR2 DEFAULT ',',
 p_dir IN VARCHAR2 ,
 p_filename IN VARCHAR2 )
 RETURN NUMBER
 IS
 l_output utl_file.file_type;
 l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;
 l_columnValue VARCHAR2(2000);
 l_status INTEGER;
 l_colCnt NUMBER DEFAULT 0;
 l_separator VARCHAR2(10) DEFAULT '';
 l_cnt NUMBER DEFAULT 0;
 l_descTbl dbms_sql.desc_tab;
 BEGIN
 l_output := utl_file.fopen( p_dir, p_filename, 'w', 32767 );
 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 FOR i IN 1 .. 255
 LOOP
 BEGIN
 dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
 l_colCnt := i;
 utl_file.put( l_output, l_separator || l_descTbl(i).col_name || '|' );
 EXCEPTION
 WHEN OTHERS THEN
 IF ( SQLCODE = -1007 ) THEN
 EXIT;
 ELSE
 raise;
 END IF;
 END;
 END LOOP;
 utl_file.new_line( l_output );
 dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
 l_status := dbms_sql.execute(l_theCursor);
 LOOP
 EXIT
 WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
 l_separator := '';
 FOR i IN 1 .. l_colCnt
 LOOP
 dbms_sql.column_value( l_theCursor, i, l_columnValue );
 utl_file.put( l_output, l_separator || l_columnValue);
 l_separator := p_separator;
 END LOOP;
 utl_file.new_line( l_output );
 l_cnt := l_cnt+1;
 END LOOP;
 dbms_sql.close_cursor(l_theCursor);
 utl_file.fclose( l_output );
 RETURN l_cnt;
 END exp_data;
/

show errors

Open in new window

KathysFriendDBAAuthor Commented:
This is embarrassing but when I went to get the code for you - I realised that I had two copies of the function.  When I made the change to add the 32767 - it was still calling the function without the change.
Thanks for the really good example - of course you are right - that is all that is required.

Thanks
-cathie
KathysFriendDBAAuthor Commented:
This is an excellent example of how this works.
slightwv (䄆 Netminder) Commented:
>> I realised that I had two copies of the function.

That happens to all of us!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.