Hello everybody,
i wrote a procedure that deals with writing the contents of the QUERY column of ALL_MVIEWS to a file:
DECLARE v_out_dir_name VARCHAR2(30) := 'DIR_TEST'; v_out_dir_path VARCHAR2(60); v_count_object_elab NUMBER := 0; CURSOR c_mviews IS SELECT LOWER(MVIEW_NAME) || '.sql' AS FILE_NAME , QUERY AS SCRIPT FROM ALL_MVIEWS ; v_file UTL_FILE.file_type;BEGIN FOR r_mview IN c_mviews LOOP v_file := UTL_FILE.fopen (v_out_dir_name, r_mview.FILE_NAME, 'w'); UTL_FILE.putf (v_file, r_mview.SCRIPT); UTL_FILE.fclose (v_file); v_count_object_elab := v_count_object_elab + 1; END LOOP; IF v_count_object_elab = 0 THEN DBMS_OUTPUT.PUT_LINE('NESSUN FILE ELABORATO'); END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERRORE = ' || SQLERRM); IF UTL_FILE.IS_OPEN (v_file) THEN UTL_FILE.FCLOSE (v_file); END IF; RAISE;END;/
DECLARE v_out_dir_name VARCHAR2(30) := 'DIR_TEST'; v_out_dir_path VARCHAR2(60); v_count_object_elab NUMBER := 0; CURSOR c_mviews IS SELECT LOWER(MVIEW_NAME) || '.sql' AS FILE_NAME , QUERY AS SCRIPT FROM ALL_MVIEWS ; v_file UTL_FILE.file_type;BEGIN FOR r_mview IN c_mviews LOOP v_file := UTL_FILE.fopen (v_out_dir_name, r_mview.FILE_NAME, 'w'); UTL_FILE.putf (v_file, r_mview.SCRIPT); UTL_FILE.
This post has been answered by Solomon Yakobson on Apr 6 2020
Jump to Answer