Oracle RDS（AWS）：如何获取存储过程生成的文件？

Got it, let's walk through some practical alternatives for your Oracle RDS file transfer issue—since scp/sftp isn't an option and DBMS_FILE_TRANSFER.PUT_FILE is throwing errors, here are actionable workarounds tailored to your scenario:

1. Refactor Your Stored Procedure to Use a Table as an Intermediate #

Instead of writing directly to a file, tweak your stored procedure to save the text content into a CLOB column in a dedicated table. You can then move this data to RDS and regenerate the file there:

• Step 1 (On your original Oracle SE instance):
  Update your procedure to insert content into a table instead of writing to a file:

  CREATE TABLE file_content_store (
      file_id NUMBER PRIMARY KEY,
      file_name VARCHAR2(100) NOT NULL,
      content CLOB,
      created_at TIMESTAMP DEFAULT SYSTIMESTAMP
  );
  
  -- Modified stored procedure snippet
  DECLARE
      v_output_content CLOB;
  BEGIN
      -- Your existing logic to generate the TXT content into v_output_content
      INSERT INTO file_content_store (file_id, file_name, content)
      VALUES (file_id_seq.NEXTVAL, 'daily_report.txt', v_output_content);
      COMMIT;
  END;
  /
  

• Step 2 (Transfer to RDS):
  Use Oracle Data Pump (EXPDP/IMPDP) or a database link to export this table from your original instance and import it into RDS.
• Step 3 (On RDS):
  Use UTL_FILE to write the CLOB content to an RDS-allowed directory (like DATA_PUMP_DIR, or a custom directory you've configured via AWS RDS parameters):

  DECLARE
      v_file_handle UTL_FILE.FILE_TYPE;
      v_content CLOB;
      v_chunk VARCHAR2(32767);
      v_position NUMBER := 1;
      v_chunk_size CONSTANT NUMBER := 32767;
  BEGIN
      SELECT content INTO v_content 
      FROM file_content_store 
      WHERE file_name = 'daily_report.txt';
  
      v_file_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'daily_report.txt', 'W', v_chunk_size);
      WHILE v_position <= DBMS_LOB.GETLENGTH(v_content) LOOP
          v_chunk := DBMS_LOB.SUBSTR(v_content, v_chunk_size, v_position);
          UTL_FILE.PUT_LINE(v_file_handle, v_chunk);
          v_position := v_position + v_chunk_size;
      END LOOP;
      UTL_FILE.FCLOSE(v_file_handle);
  END;
  /
  

2. Use DBMS_CLOUD to Move Files to Amazon S3 #

If you're on AWS RDS Oracle, leverage the built-in DBMS_CLOUD package to transfer files to S3, then access them from there:

• On RDS:
  First, set up your S3 credentials (you can use AWS Secrets Manager for secure storage, or create a credential directly):

  BEGIN
      DBMS_CLOUD.CREATE_CREDENTIAL(
          credential_name => 'S3_ACCESS_CRED',
          username => 'your_aws_access_key_id',
          password => 'your_aws_secret_access_key'
      );
  END;
  /
  

  Then, upload the generated file (from the table method above, or directly from an RDS directory) to S3:

  BEGIN
      DBMS_CLOUD.PUT_OBJECT(
          credential_name => 'S3_ACCESS_CRED',
          object_uri => 's3://your-bucket-name/reports/daily_report.txt',
          directory_name => 'DATA_PUMP_DIR',
          file_name => 'daily_report.txt'
      );
  END;
  /
  

  You can then download the file from S3 via the AWS CLI, S3 console, or your preferred tool.

3. Use External Tables to Migrate File Data Directly #

If your TXT file is generated from database records, create an external table pointing to the file on your original instance, then sync the data to RDS:

• On original Oracle SE:
  Create a directory object for your file location, then define the external table:

  CREATE DIRECTORY source_txt_dir AS '/opt/oracle/txt_output';
  GRANT READ, WRITE ON DIRECTORY source_txt_dir TO your_db_user;
  
  CREATE TABLE external_report_data (
      employee_id NUMBER,
      report_date DATE,
      total_sales NUMBER
      -- Match the columns in your TXT file
  )
  ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY source_txt_dir
      ACCESS PARAMETERS (
          RECORDS DELIMITED BY NEWLINE
          FIELDS TERMINATED BY '|'
          -- Adjust format settings to match your file
      )
      LOCATION ('daily_report.txt')
  )
  REJECT LIMIT UNLIMITED;
  

• On RDS:
  Create a database link to your original instance, then copy the data to a local table and regenerate the file:

  CREATE DATABASE LINK original_oracle_db 
  CONNECT TO your_db_user IDENTIFIED BY your_password 
  USING 'original_db_tns_connection_string';
  
  CREATE TABLE local_report_data AS 
  SELECT * FROM external_report_data@original_oracle_db;
  
  -- Use UTL_FILE to write local_report_data back to a TXT file (same as Step 3 in Option 1)
  

4. Quick Troubleshoot for DBMS_FILE_TRANSFER (If You Want to Revisit It) #

If you still want to fix the original method, check these common pain points:

• Ensure both instances have valid directory objects with READ/WRITE permissions granted to the user executing the procedure.
• Verify the database link has EXECUTE privileges on DBMS_FILE_TRANSFER.
• Confirm the target directory on RDS is one of the allowed paths (RDS restricts direct access to most filesystem locations).

内容的提问来源于stack exchange，提问作者Rodrigo