Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-29285: file write error

948384Nov 29 2012 — edited Nov 29 2012
Hi,

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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 27 2012
Added on Nov 29 2012
1 comment
685 views