[ORACLE] About dbms_ Some research on cloud package mechanism

Posted by mort on Thu, 21 Oct 2021 16:14:37 +0200


Reference resources

Pressed for a long time, ready to start writing, found that someone has sent similar,

But it's a bit different from what I do, so I'll write one.

The title of this article was meant to be called
[ORACLE] Use dbms_ The cloud package reads files in the Object Storage Service as external tables

Then take Tencent Cloud cos for example, using ORACLE's own functions, create an external table with the files in cos.
However, the process is a bit tortuous, this article has not been completed yet, so the title of the article can only be changed first, then if there is a plan, write another one.

Install dbms_cloud

Official Documents
I don't have a mos account. I can't see it. I can only read TIM articles.

First, my local Oracle version is 19.3, and there is no dbms_in the database or rdbms/admin Cloud package, according to this article
There is in the 19c version, so I think maybe my version is lower, but after upgrading to the latest version 19.12, there is dbms_under rdbms/admin Cloud.sql file
[ORACLE] Record a window environment ORACLE19.3 patch rise to version 19.12

Then there's the installation, but there must be more than one package, there should be some dependent objects, or read this article
TIM has given a very long setup script to install PDB and all CDB s, but I'm a single-node, single-instance, so it's not necessary to be so complex, just copy the following section

REM Create the Catalog objects

REM Create the Package Spec

REM Create the Package Body

-- Create the metadata

alter session set current_schema=sys;


Log in to sqlplus directly with sys and paste

The command rolled down without a mistake, perfect

Create wallet

Since I don't have visa and MasterCard on hand, I can't request a free Orace or aws object store. Looking for domestic, now almost all the download traffic charges for personal-oriented object storage external network, or compulsory advertising, can only use relatively inexpensive Tencent Cloud cos first, and may not be successful, after all, there are no articles on the Internet that ORACLE accesses cos.
So the next step is to make a wallet based on Tencent's Cloud cos certificate

After downloading the certificate, open the wallet manager and click New.
Enter password, need 8 digits, test here, my password is set to asdf1234, click OK

No Point

Operation, import trust certificate

Select the file containing the certificate

Note that I only imported two certificates here, and I used to [AIO] Send encrypted email (SSL) using ORACLE database stored procedures This article states that at versions 12c and above, do not import the last level certificate, otherwise errors will occur

Save as, where I saved in H:\wallet_cos

Then, to save later operations, you can check the automatic login.

Save it again and close the program. We can see that there are four files in this folder at this time (sometimes only two, two lck s are temporarily locked files, they are usually left unattended).

Test the validity of the certificate

select utl_http.request(url             => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com',
                        wallet_path     => 'file:H:\wallet_cos',
                        wallet_password => 'asdf1234')
  from dual;

Find something out

If you start uploading files directly, you will be prompted

This is a bit different from the utl_http.set_wallet mentioned earlier. This time dbms_cloud sets the certificate path as a parameter of the database and needs to be set with the alter command

alter database property set ssl_wallet='H:\wallet_cos'

That's because oracle did this

This means that all you have to do in the future is to create a wallet and add any certificates to it? Don't comment on it here.

Create Authentication

Then you create a login authentication, which is provided by the Object Storage Service and corresponds to an encrypted user name and password

  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => 'AKID**********************7JGzU',
    password        => 'MTK*************************DVYNxNf'


After setup, test upload

  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_upload.txt',
    directory_name  => 'PY_FILE',
    file_name       => 'test_upload.txt');

Upload failed, start research

Tip, unsupported objects store URI s, emmm, debug, see what's wrong, and trace all the way here

NO_DATA_FOUND has been entered because there is no data in the dbms_cloud_store table (when dbms_cloud_metadata.sql is hit, CON_NAME must be CDB$ROOT for the insert to be validated and can be refilled manually) However, according to this logic, even if Tencent Cloud cos is added, it does not support it, because the three must be ORACLE/AMAZON/AZURE. I really don't know that oracle official also has this kind of case when writing without considering expansion, or even wrote several uri conversion processes directly according to the manufacturer. Out of line, it also uses mandatory domain name format matching to solve the problem. Analysis. . .

So if I change the official code directly, I don't know if I can?
First, let's take a look at what these codes are doing. Is it necessary?
Put dbms_ Cloud_ The request package has gone through the general code and found nothing special, the common utl_http operation, for each manufacturer's required api request format, various transformations assemble the request package, and then post the package to get a return value. That's it?

If that's the case with oracle, what else do you want to do?

Wait a moment,

Are there no external tables?

Let's skip dbms_first Cloud_ Request this package, because if you want to support storage of objects not from these three manufacturers, you have to rewrite the request yourself, because there are some differences in api (as if the oss in Jingdong is exactly the same as Amazon s3 at present, others have not been studied for the time being), or rather, write an oracle version of sdk to Tencent Cloud cos for free...

Next, let's see how the external table works.
Invoke procedure to create external table

    table_name      => 'emp_ext',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_dir/test_upload.csv',
    column_list     => 'empno     number(4),
                        ename     varchar2(10),
                        job       varchar2(9)',
    format          => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')

Create error-free and query directly,

As above, Tencent Cloud cos is not supported, so let's look at the ddl of this table

select dbms_metadata.get_ddl(object_type => 'TABLE',name =>'EMP_EXT' ) FROM DUAL;

   (	"EMPNO" NUMBER(4,0), 
       ( 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_dir/test_upload.csv'

It seems that there is little difference from the normal external table, there is more CREDENTIAL. Wouldn't it walk dbms_automatically if the file name was changed to a remote file name? Cloud went to read the data?

Write a query Drop debug window down to see

l number;
select count(1) into l from EMP_EXT;

Sure enough, go to dbms_cloud and its associated packages are inside, and the first step in debugging is directly there

  metadata clob;
  m_obj    json_object_t;
  metadata := dbms_cloud_internal.get_metadata(dbms_assert.enquote_name(:1,
  m_obj    := json_object_t.parse(metadata);
  :4       := m_obj.get_Number('Content-Length');

There are no triggers on this table, it already involves the feature that ORACLE is not natively supported in PLSQL, and there is an external C Library in between, if you do not change dbms_cloud packages, Tencent Cloud cos files cannot be used as external tables. If you write your own package, you can upload and download files at most. Even if you make a turn, you can also make external tables, such as triggering a request when querying, writing to your local location when you get the content, or parsing and converting it into a table type directly in memory, but this is not the native mechanism of this package after all. As long as you build the external surface of this url, It forces dbms_away Cloud. It seems that Oracle has made something.

But change dbms_ The cloud package is risky because once ORACLE upgrades the package, the version overrides, and if Oracle changes the mechanism, it cannot fit the code.


However, I'm still ready to give it a try, which is useful in case a path is opened. Imagine:

oracle periodically transfers a large amount of data to the object storage in the cloud, no other servers are needed in the middle, a large amount of network bandwidth and storage is saved, and then large data calculations are made in the cloud to get a result, which can be queried directly in oracle in the form of external tables. If oracle is also deployed in the cloud, it does not involve downstream traffic from the external network at all.


"He digs a line"

Topics: Database Oracle