rdsadmin.rds_file_util.read_text_file function throws ORA-22813

Question

I am having a problem when calling function **rdsadmin.rds_file_util.read_text_file** at **Oracle database 12.1.0.2.v18**. For example this code (taken from official doc \[1]):

```
select * from table
    (rdsadmin.rds_file_util.read_text_file(
        p_directory => 'DATA_PUMP_DIR',
        p_filename  => 'import.log'));
```

throws this error:

```
ORA-22813: operand value exceeds system limits
22813. 00000 -  "operand value exceeds system limits"
*Cause:    Object or Collection value was too large. The size of the value
           might have exceeded 30k in a SORT context, or the size might be
           too big for available memory.
*Action:   Choose another value and retry the operation.
```

It does not matter which parameters are used. Even this call throws the same error:

```
select * from table
    (rdsadmin.rds_file_util.read_text_file(
        p_directory => 'NONEXISTENT_DIR_NAME',
        p_filename  => 'nonexistent-file-name.log'));
```

Other function from the same package **RDSADMIN.RDS_FILE_UTIL.LISTDIR** works fine. I tried to restart the whole DB instance but it did not help.  
  
I am quite sure the **READ_TEXT_FILE** procedure used to work at **12.1.0.2.v12** database engine.  
  
**Links:**  
\[1]: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Misc.html  
  
Edited by: mjiricka on Nov 28, 2019 5:18 AM

Answer

Fortunately it is possible to reimplement the **rdsadmin.rds_file_util.read_text_file** util like this:

```
function read_file(p_directory varchar2, p_filename varchar2) return varchar2 as
  v_file utl_file.file_type;
  v_buffer_size pls_integer := 32767;
  v_buffer varchar2(32767);
  v_output varchar2(32767) := '';
begin
    v_file := utl_file.fopen(p_directory, p_filename, 'r', v_buffer_size);
    loop
      begin
        utl_file.get_line(v_file, v_buffer);
        v_output := v_output || CHR(10) || v_buffer;
        
      exception when no_data_found then exit;
      end;
    end loop;
    utl_file.fclose(v_file);
    return v_output;
end;
```

It works for shorter logs up to 32767 characters in length. Otherwise it should be possible to use CLOB instead of VARCHAR2, but VARCHAR2 is enough for reading e.g. task logs from **rdsadmin.rdsadmin_s3_tasks.upload_to_s3**.

rdsadmin.rds_file_util.read_text_file function throws ORA-22813

관련 콘텐츠