Oracle Apex Guide Pdf
Tosha Dickason
APEX_WEB_SERVICE is by far my favorite APEX Pl/SQL API. It dramatically reduces the effort of calling external Web Services from the Oracle Database. In this post, I will first cover the key concepts you need to know and then review several use cases where I have used APEX_WEB_SERVICE to make my life easier.
You typically use HTTP Headers to convey information to a REST API. In the example below, I am requesting version 2.1 of the API be used. Note: HTTP Headers are also used to pass Authorization tokens, but APEX_WEB_SERVICE takes care of this for you (more on that later).
Query string parameters are typically used to pass parameters to the REST API you call. When viewed in a REST Request URL, they are seen as a '?' mark followed by a '&' delimited list of 'parameter=value' pairs.
APEX_WEB_SERVICE simplifies passing parameters by allowing you to populate two PL/SQL arrays, one with a list of the parameter names and one with a list of the parameter values. The above query string can be represented as follows.
You will not get far until you create an ACL to allow APEX to make outbound requests to the Web Service you are trying to call. ACLs are the Oracle Database's way of limiting which users can make calls to which end-points. When creating the ACL, you need to specify the current APEX schema as the principal_name, e.g., the principal_name would be APEX_230200 for APEX 23.2.
Nearly all web services have HTTPS end-points. This means that APEX_WEB_SERVICE must pass along a public certificate and the request to the Web Service. The Oracle Database handles this by storing these certificates in a database TLS wallet. TLS Wallets are created on the file system of the Database Server.
You need to get the certificate before creating the DB TLS Wallet. The easiest way to get the certificate for a web service is to call it from your browser, view it, and then export the Root certificate to a '.cer' file.
APEX_WEB_SERVICE will use the TLS wallet configured here unless you tell it otherwise. You can override the default by passing the parameter p_wallet_path to APEX_WEB_SERVICE. If the wallet has a password (which it need not), then you can pass this with the parameter p_wallet_pwd.
Most web services are secured and require you to pass some credentials to authenticate your request. APEX_WEB_SERVICE makes dealing with credentials easy. At the heart of this ease of use are APEX Web Credentials. APEX Web Credentials offer a way to store your credentials securely, making it seamless for APEX services like APEX_WEB_SERVICE to consume them. APEX Web Credentials support the following credential types:
OAuth2 Client Credentials - Oracle APEX exchanges the client ID and client secret for an Access Token using a token server URL. The access token is then used to perform the actual request. If the access token is expired, Oracle APEX will transparently request a new one.
Once you have called a web service using APEX_WEB_SERVICE, you need a way to confirm that it worked. In the world of REST APIs, this is accomplished by verifying the HTTP response status code. While there are guidelines on what each response code means, you need to understand the response codes for each API you call. A response code in itself does not tell you everything. Some APIs may return a 500 response and provide details of the error in the response payload.
When calling web services, you must specify an HTTP method. Possible HTTP methods are HTTP GET, POST, PUT, DELETE, and PATCH. When using APEX_WEB_SERVICE, you select the method using the p_http_method parameter. APEX_WEB_SERVICE defaults to the GET method if you do not explicitly provide it.
After configuring a new APEX Environment and you start making many calls to APEX_WEB_SERVICE, many people get the error message "ORA-20001: You have exceeded the maximum number of web service requests per workspace".
You can change this at the Workspace Level or from the INTERNAL (Administration Services) Workspace to change it for your whole instance. Setting the value at the Workspace level overrides the value set at the INTERNAL (Administration Services) level.
The final concept we need to cover is response headers. Web services often include HTTP Headers in the response, providing additional information such as eTag and Content-Type information. After calling APEX_WEB_SERVICE, APEX populates a PL/SQL array called apex_web_service.g_headers containing a list of the Response HTTP Headers.
Now that we have covered the concepts, let's examine the examples. This section will review several examples of calling REST APIs using APEX_WEB_SERVICE. These examples will focus on apex_web_service.make_rest_request and apex_web_service.make_rest_requestb.
We can achieve great things if we combine APEX_WEB_SERVICE with other capabilities of the Oracle database. Let's say we want to query the last five posts from Jeff Smith's blog feed. Jeff's blog posts are available via an XML-based RSS feed accessible using the URL There is no security; you can copy and paste the above URL into your browser and see the feed.
Looking deep within the FROM clause, you can see we are calling apex_web_service.make_rest_request. This returns the XML from the feed into the XMLTYPE function, which converts the XML text into an XML object.
Even though I billed this post as the 'definitive guide', I am sure there are many use cases for APEX_WEB_SERVICE that I have missed. This speaks to the power and flexibility of this API. I strongly encourage you to get familiar with this, my favorite Oracle APEX PL/SQL API!
In order to connect to the AOP Cloud over HTTPS, you may need to install the SSL certificate in your Oracle Wallet.The Oracle Cloud has our certificate loaded by default. Here are the commands to install our certificate in your own Oracle Wallet.First make sure to copy the file /db/AmazonRootCA.cer to your database server e.g. in the directory /opt/oracle/admin/XE/.
Connection issues to the AOP Server or AOP Cloud are most of the time due to an invalid configuration of the TNS, ACL or Wallet.If a call to apex_web_service works in SQL, but still not in your Oracle APEX application, it's most likely that the wallet path and password are not specified in the INTERNAL workspace of your Oracle APEX environment.
There might be multiple reasons why you get this. There might be a permission issue, most likely on UTL_FILE.Another reason might be that during uploading of the script in SQL Workshop > SQL Scripts the File Character Set was not set to Unicode UTF-8.
This error means the AOP Server has difficulties full-filling your request. In this case it's best to debug the request to the AOP Server or AOP Cloud. You can get the debug JSON by inserting the following PL/SQL code in the Init PL/SQL section of the Dynamic Action or before you call the AOP PL/SQL API:
64591212e2