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!

Loop cursor of ALL_MVIEWS generate PL/SQL Error ORA-06502

CharlesMMApr 6 2020 — edited Apr 7 2020

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


  
    
    
      Comments


        
  
    
    
      Post Details
      
    
    
  
  
      
    
          Added on Apr 6 2020
      
      
      
    
  
      
    
           #cursor
      
      
      
    
  
      
    
                                    19 comments
      
      
      
    
  
      
    
                                   768 views