0

Currently I am doing this to upload cwallet.sso (ie a "normal" file, not an export, etc.) to an Oracle Autonomous Database...

BEGIN
 DBMS_CLOUD.GET_OBJECT(
    object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/Uasdfadsfasdf7Icmer6HMkv/n/sadf/b/paul/o/cwallet.sso',
    directory_name => 'DATA_PUMP_DIR');
END;
/

I would prefer to not rely on object store as I have the cwallet.sso locally and so it would seem an unnecessary additional step. Is there a straightforward PL/SQL command to just upload the file from local location to DATA_PUMP_DIR (or any dir really)? I couldn't quite tell from doc.

Paul Parkinson
  • 43
  • 5
  • Autonomous Database in OCI does not have access to the underlying operating system. There are _no_ directory objects you can access. – pmdba Dec 21 '21 at 00:17
  • thanks @pmdba I see DBMS_CLOUD (as I mentioned in my question) and DBMS_CLOUD_ADMIN operations that have directory_name => 'DATA_PUMP_DIR' suggesting 'DATA_PUMP_DIR' and other directories can be written to and read from. Do you know where these directories exist and more importantly if UTL_FILE or anything else can be used to write to them (the way DBMS_CLOUD.GET_OBJECT in my question is)? Thanks agian. – Paul Parkinson Dec 22 '21 at 01:33

1 Answers1

1

Autonomous Database does offer access to "directories" and "files." Under the covers, these are implemented as a virtual filesystem with the storage coming from your database, so it is charged to you as database quota. It's a little awkward, but you can get files into this filesystem with a PL/SQL procedure if you're able to load your input into a BLOB:

  PROCEDURE write_file(
        directory_name   IN  VARCHAR2,
        file_name        IN  VARCHAR2,
        contents         IN  BLOB
  )
  IS
    l_file      UTL_FILE.file_type;
    l_data_len  INTEGER;
    l_buffer    RAW(32000);
    l_pos       INTEGER := 1;
    l_amount    INTEGER := 32000;
  BEGIN
    -- Get the data length to write
    l_data_len := DBMS_LOB.getlength(contents);

    -- Write the contents to local file
    l_file := UTL_FILE.FOPEN(directory_name, file_name, 'wb', l_amount);
    WHILE l_pos < l_data_len
    LOOP
      DBMS_LOB.read(contents, l_amount, l_pos, l_buffer);
      UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
      l_pos := l_pos + l_amount;
    END LOOP;
    UTL_FILE.FCLOSE(l_file);
  EXCEPTION
    WHEN OTHERS THEN
      UTL_FILE.FCLOSE(l_file);
      RAISE;
  END write_file;

How you get your data into a BLOB depends on your client.

CChiappa
  • 11
  • 3