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

Question

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;
/

But the "FOR r_mview IN c_mviews LOOP" statement generates the following error:

Report error -
ORA-06502: PL/SQL: errore  di numero o valore
ORA-06512: a line 35
ORA-06512: a line 16
ORA-06512: a line 16
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

The error is thrown on a materialized view that has QUERY_LEN = 39000.

How can I solve the problem?

Many thanks in advance.

Answer 1

I don't think you are using UTL_FILE correctly. The putf() procedure is for writing and formatting (using printf() style). Try using just put() instead. I did run your script on my database and it executed fine with putf() and put(), but put() is more appropriate.

Also, beware that QUERY is a LONG. If you have a query over 32k I think you will get the error you are seeing. There are some great write-ups online about how awful LONGs are to work with.

I think easiest thing to do is convert the LONG to CLOB with a CREATE TABLE, read that table and then drop it. Here is a routine to do just that. It will create a copy of all_mviews and convert the query to a CLOB and write that in chunks to a file per view.

DECLARE
  v_out_dir_name      VARCHAR2(30) := 'MVIEW';
  v_count_object_elab NUMBER := 0;
  v_cursor            SYS_REFCURSOR;
  v_file              utl_file.file_type;
  v_start             INTEGER;
  v_buffer            VARCHAR2(1024);
  v_file_name         VARCHAR2(1024);
  v_query             CLOB;

  table_or_view_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_or_view_does_not_exist,
                        -00942);

BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE all_mviews_clob';
  EXCEPTION
    WHEN table_or_view_does_not_exist THEN
      NULL;
  END;

  EXECUTE IMMEDIATE 'CREATE TABLE all_mviews_clob AS SELECT mview_name, to_lob(query) AS query FROM all_mviews';

  OPEN v_cursor FOR q'[SELECT lower(mview_name) || '.sql' AS file_name,
           query AS script
      FROM all_mviews_clob]';

  LOOP
    FETCH v_cursor
      INTO v_file_name,
           v_query;
    EXIT WHEN v_cursor%NOTFOUND;

    v_file := utl_file.fopen(location  => v_out_dir_name,
                             filename  => v_file_name,
                             open_mode => 'w');

    v_start := 1;
    FOR i IN 1 .. ceil(dbms_lob.getlength(lob_loc => v_query) / 1024)
    LOOP
      v_buffer := dbms_lob.substr(lob_loc => v_query,
                                  amount  => 1024,
                                  offset  => v_start);

      IF v_buffer IS NOT NULL THEN
        utl_file.put(file   => v_file,
                     buffer => v_buffer);
        utl_file.fflush(file => v_file);
      END IF;

      v_start := v_start + 1024;
    END LOOP;

    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('no mviews');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('eror = ' || SQLERRM);

    IF utl_file.is_open(v_file) THEN
      utl_file.fclose(v_file);
    END IF;

    RAISE;
END;
/