Preface
Reference resources
https://oracle-base.com/articles/21c/dbms_cloud-package
Pressed for a long time, ready to start writing, found that someone has sent similar,
https://blog.csdn.net/stevensxiao/article/details/118578785
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
https://support.oracle.com/epmos/faces/DocContentDisplay?id=2748362.1
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
https://oracle-base.com/articles/21c/dbms_cloud-installation
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
https://oracle-base.com/articles/21c/dbms_cloud-installation
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 @$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql @$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql @$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql @$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql REM Create the Package Spec @$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb @$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql @$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb @$ORACLE_HOME/rdbms/admin/dbms_cloud.sql @$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb REM Create the Package Body @$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb -- Create the metadata @$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql alter session set current_schema=sys; @$ORACLE_HOME/rdbms/admin/sqlsessend.sql
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
begin
dbms_cloud.create_credential (
credential_name => 'obj_store_cred',
username => 'AKID**********************7JGzU',
password => 'MTK*************************DVYNxNf'
);
end;
/
upload
After setup, test upload
begin
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');
end;
/
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
begin
dbms_cloud.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')
);
end;
/
Create error-free and query directly,
Tips
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;
CREATE TABLE "SYS"."EMP_EXT"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "DATA_PUMP_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY DETECTED NEWLINE NOLOGFILE NOBADFILE NODISCARDFILE READSIZE=10000000 CREDENTIAL 'obj_store_cred'
FIELDS TERMINATED BY '|' MISSING FIELD VALUES ARE NULL REMOVE_QUOTES NOTRIM
)
LOCATION
( 'https://first-1307489902.cos.ap-shanghai.myqcloud.com/test_dir/test_upload.csv'
)
)
REJECT LIMIT 0
PARALLEL
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
declare l number; begin select count(1) into l from EMP_EXT; end; /
Sure enough, go to dbms_cloud and its associated packages are inside, and the first step in debugging is directly there
declare
metadata clob;
m_obj json_object_t;
begin
metadata := dbms_cloud_internal.get_metadata(dbms_assert.enquote_name(:1,
FALSE),
:2,
:3);
m_obj := json_object_t.parse(metadata);
:4 := m_obj.get_Number('Content-Length');
end;
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.
Last
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.
To-do+1
"He digs a line"