ORA-29285: file write error
948384Nov 29 2012 — edited Nov 29 2012Hi,
Requirement: Need to export Views . we have almost 2000 views
So i have used DBMS_METADATA.get_ddl to write these ddls in the file using PL/SQL . How ever i am able to write only 500 views. When it reaches 501 view create definition i am getting the below error
ORA-29285: file write error.
It seems that UTL_file has reached the maximum limit.
Code:
DECLARE
TYPE ref_cursor is REF CURSOR;
view_cur ref_cursor;
l_view_cur dba_views.text%TYPE;
l_view_file_Handle UTL_FILE.FILE_TYPE;
BEGIN
l_view_file_Handle := UTL_FILE.FOPEN('TEMP_DIR', 'utl_file_test', 'a');
OPEN view_cur for
select dbms_metadata.get_ddl ( 'VIEW', VIEW_NAME) from user_views;
Loop
FETCH view_cur INTO l_view_cur;
UTL_FILE.PUT_LINE(l_view_file_Handle,l_view_cur);
EXIT WHEN view_cur%NOTFOUND;
UTL_FILE.PUT_LINE(l_view_file_Handle,';');
END LOOP;
UTL_FILE.FCLOSE(l_view_file_Handle);
CLOSE view_cur;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || ' SQLERRM=' || SQLERRM);
END;
Please help me out.
Cheers