DBMS_CLOUD containes procedures to import data from the Cloud Object Store to an Oracle database. set_proxy | sets a proxy host/port for HTTP requests |
send_request | send HTTP request |
get_response_headers | get response headers |
get_response_text | get response as text |
get_response_raw | get response as raw |
get_response_status_code | get response status code |
create_credential | create a credential object to access Object Store |
enable_credential | enable credential object to access Object Store |
disable_credential | disable credential object to access Object Store |
update_credential | update attribute in credential object |
drop_credential | drop credential object to access Object Store |
create_external_table | Create External Table on Object Store files (used to accessd a file in the object store) |
create_external_part_table | Create External Partitioned Table on Object Store files |
create_hybrid_part_table | Create Hybrid Partitioned Table on Object Store files |
validate_external_table | Validate External Table on Object store file |
validate_external_part_table | Validate External Partitioned Table on Object Store files |
validate_hybrid_part_table | Validate Hybrid Partitioned Table on Object Store files |
copy_data | Copy data from Object Store files in to Oracle RDBMS table |
copy_collection | Load data from Object Store files in to Oracle RDBMS SODA collection |
export_data | Export Data from Oracle RDBMS Table to Object Store files |
delete_operation | Delete an operation for cloud data access |
delete_all_operations | Delete all operations for cloud data access |
get_object | Get the contents of an object in Cloud Store |
put_object | Put the contents in an object in Cloud Store |
get_metadata | Get the metadata for an object in Cloud Store |
list_objects | List objects at a given location in Cloud Store |
delete_object | Delete object in Cloud Store |
list_files | List files at a given directory object location |
delete_file | Delete file in a directory object |
set_api_result_cache_size | Set the max size of the rest api cache |
get_api_result_cache_size | Get the max size of the rest api cache |
dbms_cloud may be different if installed on Autonomous Database vis a vis being installed on prem. dbms_cloud is only supported on CDB architecture. begin
dbms_cloud.create_external_table(
table_name =>'TQ84_CLOUD_TABLE_TEST',
credential_name => null,
file_uri_list =>'https://www.web.statistik.zh.ch/ogd/daten/ressourcen/KTZH_00002063_00004104.csv',
format => json_object(
'type' value 'csv',
'delimiter' value ',' ,
'skipheaders' value '1' -- , 'header' value true),
),
column_list => 'JAHR VARCHAR2(200),' ||
'DATUM VARCHAR2(200), ' ||
'GEMEINDE_NUMMER VARCHAR2(200), ' ||
'GEMEINDE_NAME VARCHAR2(200), ' ||
'BEZIRK_NUMMER VARCHAR2(200), ' ||
'BEZIRK_NAME VARCHAR2(200), ' ||
'VERTEILZONE VARCHAR2(200), ' ||
'ANALYT VARCHAR2(200), ' ||
'KATEGORIE VARCHAR2(200), ' ||
'KATEGORIE_SORTIERUNG VARCHAR2(200), ' ||
'EINHEIT VARCHAR2(200), ' ||
'QUELLE VARCHAR2(200)'
);
end;
/
select
tab.owner,
tab.external,
tex.type_name,
tex.default_directory_name,
tex.access_parameters,
tex.access_type,
tex.property
from
dba_tables tab left join
dba_external_tables tex on tab.owner = tex.owner and
tab.table_name = tex.table_name
where
tab.table_name = 'TQ84_CLOUD_TABLE_TEST';
select * from tq84_cloud_table_test;
--
-- ORA-29913: error while processing ODCIEXTTABLEOPEN routine
-- ORA-20000: Database property SSL_WALLET not found