Call Web Services from Autonomous Database
Describes options for calling Web Services from Autonomous Database.
There are a number of options for calling Web Services from Autonomous Database, including the following:
-
Use
DBMS_CLOUD
REST APIs: TheDBMS_CLOUD.SEND_REQUEST
function begins an HTTP request, gets the response, and ends the response. This function provides a workflow for sending a cloud REST API request with arguments and the function returns a response code and payload. See SEND_REQUEST Function and Procedure for more information. -
Use Oracle APEX: You can interact with both SOAP and RESTful style web services from APEX in your Autonomous Database instance. See Use Web Services with Oracle APEX for more information.
-
Use
UTL_HTTP
to submit a request to a public site: See Submit an HTTP Request to a Public Host for more information. -
Use
UTL_HTTP
to submit a request to a private site: See Submit an HTTP Request to a Private Host for more information.When your Autonomous Database instance is on a private endpoint you can use a customer-managed wallet with procedures in
UTL_HTTP
,DBMS_LDAP
,UTL_SMTP
, orUTL_TCP
. See Make External Calls Using a Customer-Managed Wallet for more information.
See PL/SQL Packages Notes for Autonomous Database for information on restrictions for UTL_HTTP
on Autonomous Database.
Topics
- Submit an HTTP Request to a Public Host
Provides details for usingUTL_HTTP
to submit an HTTP request on a public host. - Submit an HTTP Request to a Private Host
Describes the steps to useUTL_HTTP
to submit an HTTP request on a private host. - Submit an HTTP Request to Private Site with a Proxy
When your Autonomous Database instance is on a private endpoint you can use a proxy to submit HTTP requests withUTL_HTTP
. - Use Credential Objects to Set HTTP Authentication
Describes how to pass a credential objects toUTL_HTTP.SET_CREDENTIAL
procedure. - Notes for Submitting HTTP Requests with Oracle APEX or Database Actions
When you use Oracle APEX SQL Commands or Database Actions SQL worksheet to run multiple sequential SQL commands, the commands can run in different database sessions that do not save the state of a previous statement. This behavior differs from desktop SQL clients such as SQL*Plus and SQL Developer that maintain a persistent connection to the database.
Parent topic: Tasks
Submit an HTTP Request to a Public Host
Provides
details for using UTL_HTTP
to submit an HTTP request on a public
host.
For example, to submit an HTTP request for a public host www.example.com, create an Access Control List for the host:
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'www.example.com',
ace => xs$ace_type( privilege_list => xs$name_list('http'),
principal_name => 'ADMIN',
principal_type => xs_acl.ptype_db));
END;
Then submit the HTTP request:
SELECT UTL_HTTP.REQUEST(url => 'https://www.example.com/') FROM dual;
If your Autonomous Database instance is on a private endpoint and you want your
UTL_HTTP
calls to public hosts to be subject
to your private endpoint VCN's egress rules, set the
ROUTE_OUTBOUND_CONNECTIONS
database property to
PRIVATE_ENDPOINT
.
See Enhanced Security for Outbound Connections with Private Endpoints for more information.
See PL/SQL Packages Notes for Autonomous Database for information on restrictions for UTL_HTTP
on Autonomous Database.
Parent topic: Call Web Services from Autonomous Database
Submit an HTTP Request to a Private Host
Describes the steps to use UTL_HTTP
to submit an HTTP
request on a private host.
To submit a request to a target host on a private endpoint, the target host must be accessible from the source database's Oracle Cloud Infrastructure VCN. For example, you can connect to the target host when:
-
Both the source database and the target host are in the same Oracle Cloud Infrastructure VCN.
-
The source database and the target host are in different Oracle Cloud Infrastructure VCNs that are paired.
-
The target host is an on-premises network that is connected to the source database's Oracle Cloud Infrastructure VCN using FastConnect or VPN.
You can also make UTL_HTTP
calls with a customer-managed
wallet when your Autonomous Database is on a
private endpoint. See Make External Calls Using a Customer-Managed Wallet for more information.
To make a UTL_HTTP
request to a target on a private
endpoint:
-
Create an Access Control List for the host.
For example:
BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => 'www.example.com', ace => xs$ace_type( privilege_list => xs$name_list('http'), principal_name => 'ADMIN', principal_type => xs_acl.ptype_db), private_target => TRUE); END; /
As shown in this example, when you create an Access Control List for the host specify the
private_target
parameter with the valueTRUE
.Note
If you setROUTE_OUTBOUND_CONNECTIONS
toPRIVATE_ENDPOINT
, setting theprivate_target
parameter toTRUE
is not required in this API. See Enhanced Security for Outbound Connections with Private Endpoints for more information. -
Submit the HTTP request:
SELECT UTL_HTTP.REQUEST( url => 'https://www.example.com/', https_host => 'www.example.com') FROM dual;
See PL/SQL Packages Notes for Autonomous Database for information on restrictions for UTL_HTTP
on Autonomous Database.
Parent topic: Call Web Services from Autonomous Database
Submit an HTTP Request to Private Site with a Proxy
When
your Autonomous Database instance is on a private
endpoint you can use a proxy to submit HTTP requests with
UTL_HTTP
.
When your Autonomous Database
instance is on a private endpoint, to use UTL_HTTP
with a target
proxy the target proxy must be accessible from the source database's Oracle Cloud
Infrastructure VCN.
For example, you can connect using a proxy when:
-
Both the source database and the proxy server are in the same Oracle Cloud Infrastructure VCN.
-
The source database and the proxy server are in different Oracle Cloud Infrastructure VCNs that are paired.
-
The proxy server is an on-premises network that is connected to the source database's Oracle Cloud Infrastructure VCN using FastConnect or VPN.
You can also make UTL_HTTP
calls using a
customer-managed wallet. See Make External Calls Using a Customer-Managed Wallet for more information.
To use a proxy server with UTL_HTTP
:
Notes for setting a proxy server with
UTL_HTTP.SET_PROXY
:
-
DBMS_CLOUD
requests do not honor the proxy server you set withUTL_HTTP.SET_PROXY
. This includesDBMS_CLOUD.SEND_REQUEST
and all object storage access forDBMS_CLOUD
external tables that you define withDBMS_CLOUD.CREATE_EXTERNAL_TABLE
,DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
, orDBMS_CLOUD.CREATE_HYBRID_PART_TABLE
. -
APEX_WEB_SERVICE
requests do not honor the proxy server you set withUTL_HTTP.SET_PROXY
.
See PL/SQL Packages Notes for Autonomous Database for information on restrictions for UTL_HTTP
on
Autonomous Database.
Parent topic: Call Web Services from Autonomous Database
Use Credential Objects to Set HTTP Authentication
Describes how to pass a credential objects to UTL_HTTP.SET_CREDENTIAL
procedure.
The UTL_HTTP.SET_CREDENTIAL
procedure sets HTTP authentication information in the HTTP request header. The Web server needs this information to authorize the request.
The UTL_HTTP.SET_CREDENTIAL
procedure enables you to pass credential objects to set HTTP authentication. Credential objects are schema objects, hence they can be accessed only by privileged users and enable you to configure schema-level privileges to access control the credentials. Passing credential objects is an appropriate and secure way to store and manage username/password/keys to be used for authentication.
The UTL_HTTP.SET_CREDENTIAL
procedure is a secure and convenient alternative to UTL_HTTP.SET_AUTHENTICATION
procedure.
Example
...
UTL_HTTP.SET_AUTHENTICATION (l_http_request, 'web_app_user', 'xxxxxxxxxxxx');
...
As shown in the example above, when you invoke SET_AUTHENTICATION
procedure, you must pass the username/password in clear text as part of PL/SQL formal parameters. You might need to embed the username/password into various PL/SQL automation or cron scripts. Passing clear text passwords is a compliance issue that is addressed in UTL_HTTP.SET_CREDENTIAL
procedure.
See SET_AUTHENTICATION Procedure and SET_AUTHENTICATION_FROM_WALLET Procedure for more information.
UTL_HTTP.SET_CREDENTIAL
Syntax
UTL_HTTP.SET_CREDENTIAL (
r IN OUT NOCOPY req,
credential IN VARCHAR2,
scheme IN VARCHAR2 DEFAULT 'Basic',
for_proxy IN BOOLEAN DEFAULT FALSE);
Example to pass a credential object in the SET_CREDENTIAL
procedure:
-
Create a credential object:
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL ( credential_name => 'HTTP_CRED', username => 'web_app_user', password => '<password>' ); END;
This creates a credential object which creates a stored username/password pair.
See CREATE_CREDENTIAL Procedure for more information.
See Specifying Scheduler Job Credentials for more information.
-
Invoke
UTL_HTTP.SET_CREDENTIAL
procedure:DECLARE l_http_request UTL_HTTP.REQ; BEGIN l_http_request := UTL_HTTP.BEGIN_REQUEST('https://www.example.com/v1/dwcsdev/NAME/dwcs_small_xt1.csv'); UTL_HTTP.SET_CREDENTIAL (l_http_request, 'HTTP_CRED','BASIC'); ... END;
This example first creates a request by invoking the
BEGIN_REQUEST
procedure and sets HTTP authentication information in the HTTP request header by invoking theSET_CREDENTIAL
procedure. The Web server needs this information to authorize the request. The valuel_http_request
is the HTTP request,HTTP_CRED
is the credentials name andBASIC
is the HTTP authentication scheme.
See UTL_HTTP for more information.
See PL/SQL Packages Notes for Autonomous Database for information on restrictions for UTL_HTTP
on Autonomous Database.
Parent topic: Call Web Services from Autonomous Database
Notes for Submitting HTTP Requests with Oracle APEX or Database Actions
When you use Oracle APEX SQL Commands or Database Actions SQL worksheet to run multiple sequential SQL commands, the commands can run in different database sessions that do not save the state of a previous statement. This behavior differs from desktop SQL clients such as SQL*Plus and SQL Developer that maintain a persistent connection to the database.
Oracle APEX SQL Commands and Database Actions SQL worksheet submissions to an Autonomous Database instance are stateless. This means running individual SQL and PL/SQL statements may save state in database memory, for example when you submit a command to use a wallet, but the state might be cleared before you run the next statement.
View the following table for the steps to keep the database memory state between statement executions for SQL commands you submit to Autonomous Database.
SQL Command Tool | Submit Statements as a Block |
---|---|
Database Actions SQL Worksheet |
|
Oracle APEX SQL Commands |
APEX SQL Commands only supports running individual statements. When you want to run multiple statements you must wrap the statements in a single PL/SQL anonymous block. To run the block with APEX SQL Commands, click Run. |
For example, use the following code block to run a
utl_http.request()
command that makes use of a
Customer-Managed Wallet:
SELECT utl_http.request(url => 'https://api.example.com/', wallet_path => 'file:path_to_wallet', wallet_password => 'password' ) FROM DUAL";
Compare this to running with two consecutive statements that might
fail if the utl_http.set_wallet()
command and the
utl_http.request()
statement run individually,
rather than as a single code block:
EXEC utl_http.set_wallet('file:WALLET_DIR/wallet.sso', 'password');
SELECT utl_http.request('https://api.example.com/') FROM DUAL;
Parent topic: Call Web Services from Autonomous Database