Showing posts with label Oracle Cloud. Show all posts
Showing posts with label Oracle Cloud. Show all posts

Monday, October 6, 2025

How to invoke a Web Service from BI Publisher report in Oracle Fusion Cloud

In Oracle Fusion Cloud, BI Publisher reports are widely used for generating formatted outputs and documents. One of the unique business requirement is to invoke a web service from a BI Publisher report, say to fetch real-time data from a particular web service provider. So how do we achieve this ?

Let's see the step by step process to achieve this.


- For a sample purpose, I'm going to use a Public Web Service that provides details about various Countries. Since this is a public web service, it doesn't need any authentication.

http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso

For example, this web service provides an operation named ListOfCountryNamesGroupedByContinent which is parameter-less and provides full list of Countries along with the Continents they belong to.



Output:



Another operation is FullCountryInfo which accepts a Parameter for Country Code and provides the full country information in return.



Output:


Now, let's see the steps for configuring and using this web service in our BI Publisher reports.

- Login to Oracle Fusion environment

- Add /xmlpserver at the end of URL and enter



- This will get us to the Analytics area

- Click on My Profile and navigate to Administration



- Navigate to Data Sources -> Web Service Connection



- Here, we are going to create a new Web Service Data Source

For this, we are going to refer to this WSDL provided by the web service.

http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL

WSDL typically provides details about all the operations offered by the given web service -




- Let's create a new Data Source as shown below:



Protocol: http in our case

Server: Use the base URL of the web service

Port: Default http port is 80 and default https port is 443

URL Suffix: the remaining part of the WSDL URL

Check the Complex Type box

Set WS-Security to 2002

Authentication Type: Set to HTTP

I'm leaving Username and Password empty since the web service I'm using is a Public service and doesn't need any authentication. If you are using any web-service that needs authentication, then the credentials can be entered here.

- Click Apply to save the Data Source


Let's create two Reports (and hence two Data Models) showcasing various options.


Report 1: No Parameters

List of Countries by Continent


- Let's create a new Data Model of type Web Service




- I'm going to name it CountryList_DataModel

- Here, I'm going to select Data Source as the Web Service Data Source (CountryWebService) we created in previous steps.

- It will automatically populate WSDL URL and Web Service name (CountryInfoService) from our data source.

- Under Method option, we need to choose which Operation we want to use. I'm going to select ListOfCountryNamesGroupedByContinent operation from the drop-down.





- Since this operation doesn't have any parameters, the Parameter section will show no options for configuration



- Let's save and navigate to View Data


- Click View to see the sample output data


- We can see the data provided by web service appears here




- Let's click Save As Sample Data and Create Report using this Data Model


- In the report layout builder, we will be able to see all the columns offered by our Web Service Data Model in left pane.


- Drag and arrange the desired columns in the layout area.




- Click Done and Save the Report


- Run the Report and we will be able to see the output based on the data provided by web service:






Report 2: Parametric Report

Country Info Report


- Let's create a new Data Model

- We are going to use FullCountryInfo operation from our Country Web Service.

Since this operation needs a Parameter for Country Code, let's first create a Parameter P_COUNTRY_CODE in our Data Model



- Now, let's create a Data Set of type Web Service




- I'm going to name it CountryInfo_DM


- Here, I'm going to select Data Source as the same Web Service Data Source (CountryWebService) we used in previous report.


- It will automatically populate WSDL URL and Web Service name (CountryInfoService) from our data source.


- This time, under Method option, I'm going to select FullCountryInfo operation from the drop-down.





- Since this operation needs a parameter, it will automatically show us the Name of the parameter and we can map it to our Data Model parameter P_COUNTRY_CODE as shown below:




- Let's Save it and navigate to View Data


- Here, we can enter a sample country code (e.g. US) in the parameter and click View


- We'll be able to see that the web service has returned the full country details for the given Country Code




- Let's click Save As Sample Data and Create Report using this Data Model


- In the report layout builder, we will be able to see all the columns offered by our Web Service Data Model in left pane.


- Drag and arrange the desired columns in the layout area.





- Click Done and Save the Report


- Run the Report and we will be able to see the web service output based on the Parameter we entered in report parameter:






Conclusion:

Oracle Fusion Cloud offers a robust and secure methods to invoke web services from within BI Publisher reports and consume the data for reporting purposes. This supports reporting based on a parameter-less or parameter-based web service calls which gives us a liberty of making a variety of HTTP or HTTPS web service calls to fetch real-time data for our reporting needs.


Share:

Monday, August 11, 2025

How to enable OCI Object Storage Buckets over SFTP using S3 Compatibility API and SFTPGo

Managing file transfers securely and efficiently is a critical requirement for many organizations, especially when integrating cloud storage into existing workflows. When it comes to Oracle Cloud Infrastructure (OCI), the Object Storage service is widely used for its durability, scalability. It's a internet-scale, high-performance storage platform offered by OCI which is scalable, flexible and offers greater data durability and resiliency.

However, many applications and legacy systems still rely on SFTP for file transfers. In such cases it gets tricky to get the data files transferred between such legacy systems and the OCI cloud platform.

By combining OCI Object Storage with S3-compatible endpoints and a lightweight, open-source tool like SFTPGo, we can seamlessly enable SFTP access to our OCI Object Storage buckets without restructuring existing processes. 

This article will explain step by step process on how to configure OCI Object Storage over SFTP using the S3 Compatibility API and SFTPGo, enabling secure, familiar file transfer workflows in a modern cloud environment.


High level steps
:

1. Create Bucket and Enable S3 compatible API

2. Generate Client Secret keys

3. Provision SFTPGo on OCI instance

4. SFTPGo configurations

5. Test the OCI bucket connectivity over SFTP


Let's dive in.


1. Create Bucket and Enable S3 compatible API

- Navigate to the Tenancy details 



- Once on Tenancy details page, note down the Compartment listed under Amazon S3 Compatibility API designated compartment as well as the Namespace of the Object Storage



If you wish to change the Amazon S3 compatible compartment, then you can do so by clicking 'Edit object storage settings' button and changing the S3 compatible compartment





- Let's create a new bucket in the above mentioned Compartment, as shown below. Let's name it sftpbucket



- Now that we've created a bucket under S3 compatible compartment, let's move on to step 2.


2. Generate Client Secret keys

- Navigate to profile and go to the User details page



- Go to Token and Keys section



- Scroll down to Customer Secret Keys section and click Generate secret key



- Give a relevant name to the key like sftogo and click Generate

- At this stage, it will generate the key and also show you the Secret.

Make sure to note the Secret down as it won't be displayed again after this stage.

- Also, note down the newly generated Key 




3. Provision SFTPGo on OCI instance


- Navigate to Compute -> Instances



- Create a new instance (or you can use your existing instance)

- If you are creating a new instance then below references may help:


Placement: AD 1

Image: Oracle Linux 9

Shape: VM.Standard.A1.Flex

Shape build: Virtual machine, 1 core OCPU, 6 GB memory, 1 Gbps network bandwidth

- While provisioning new instance, you'll be given an option to generate Private-Public key pair. Download the Private key and store it on your machine.

- Once you enter the instance, navigate to Instance access section and note down the Public IP Address and username (usually opc)



- Open command prompt on your machine

- Run below command

ssh -i <private key file path on your machine> opc@<public IP address)


- This will let us in the new instance, and you should see the shell prompt similar to this



- Now let's run below commands in the given sequence:


Create the SFTPGo repository:

ARCH=`uname -m`

curl -sS https://ftp.osuosl.org/pub/sftpgo/yum/${ARCH}/sftpgo.repo | sudo tee /etc/yum.repos.d/sftpgo.repo


Reload the package database and install SFTPGo:

sudo yum update

sudo yum install sftpgo


- At this stage we are done installing SFTPGo on our instance.

- Now, let's start the SFTPGo service and enable it to start at system boot:


sudo systemctl start sftpgo

sudo systemctl enable sftpgo

At this stage, we've started the SFTPGo server on our OCI instance.


SFTPGo server runs on the Public IP address of our instance and at the port 8080


Let's make sure we open this port in our firewall config.


Run this command to see the current firewall config

sudo firewall-cmd --list-all


Run below commands to add the port 8080 to firewall config and reload it

sudo firewall-cmd --add-port=<port_number>/tcp --permanent

sudo firewall-cmd --reload


At this stage we have completed all the configurations in installting and enabling SFTPGo on our OCI instance.



4. SFTPGo configurations


- SFTPGo WebUI is accessible at this URL: http://<Public IP of your Instance>:8080/web/admin


- Let's go to this URL in Chrome browser


- We'll be presented to configure Admin account upon accessing this URL first time


- Setup the Admin user name and Password and Save.


- Let's login to WebAdmin as the admin





- Once we login as Admin, we should see below page:





- Let's create a sftp user that will be used to access our Object Storage bucket


- Click Add


- Let's name our user sftpgo_user and provide a strong password



- Scroll down to File system section and fill-in below details:


Storage: S3 (Compatible)


Bucket: Bucket name from OCI Object Storage obtained from Step 1


Region: Your OCI Region


Access Key and Access Secret: Obtained from Step 2





Optionally, you can mention Key Prefix if you want to restrict access to a particular folder in the bucket and not provide access to the whole bucket.

For example if your bucket has a folder named SFTPFiles and you want SFTP User to have access only to this folder then you can mention SFTPFiles/ in the Key Prefix.


Now, we need to enter S3 compatible endpoint of our namespace in which the bucket resides.

The syntax for compatible endpoint URL is:


https://{object-storage-namespace}.compat.objectstorage.{region}.oraclecloud.com



So your endpoint URL will look similar to this -


- Click Save to create the SFTP user


- Once created, the Admin can see the user and it's status on the Users page




- At this stage we've completed all the SFTPGO configurations



5. Test the OCI bucket connectivity over SFTP


- Let's login to WebClient as the sftpgo_user




-
On the Files page, we can either create a New Folder or Upload a new File





- Create a new folder names test





- Let's upload a test file as well



We will be able to see the new folder and the newly uploaded file on SFTPGo Web Client






- Now, let's navigate back to the OCI Object Storage bucket and see if these new objects are available there





As we can see, both the folder and the file are available on our bucket -







Conclusion:


By combining the power of OCI Object Storage with the flexibility of SFTPGo and the S3 Compatibility API, organizations can modernize their file transfer infrastructure without disrupting existing processes. This solution offers a secure, cost-effective, and scalable way to support SFTP workflows while taking full advantage of cloud storage. Whether you're migrating legacy systems or building hybrid environments, this approach bridges the gap between traditional file transfer needs and modern cloud capabilities.


Share:

Monday, July 14, 2025

Understanding Email security and Implementing Custom Domain Emails in Oracle Fusion Cloud using SPF and DKIM configurations

By default, any email notification sent from an Oracle Fusion Cloud environment will usually have From email address as <your pod>.fa.sender@workflow.mail.<your data center>.cloud.oracle.com

There are several use cases where Oracle Fusion subscribers would want to modify the 'From' email address in emails that get sent from Fusion Cloud applications.

Specifically, one would normally want to change the sending address in these emails to one that reflects their organization’s identity, instead of sticking to the default Oracle managed 'From' address that may expose the use of Oracle’s cloud infrastructure to their customers and contacts.


Although this change may seem simple. it comes with the need of implementing correct email authentication measures to avoid any possibility of phishing attacks and email spam.

Email authentication comprises a variety of measures that are designed to help recipients validate whether an email has really been sent from a particular sender. The right use of email authentication methods provides an increased level of security for both senders as well as recipients.


Sender Policy Framework (SPF) and Domain Key Identified Mail (DKIM) are two common forms of authentication. By adding this to our DNS entries, we're telling the recipients that we have authorized Oracle to send emails on behalf of our company's domain.



Sender Policy Framework (SPF):


SPF makes it possible to differentiate between the justified use of emails sent from an alternate domain versus using spoofing for malicious purposes. SPF utilizes the Domain Name System (DNS) infrastructure to register external servers that are authorized to send email on behalf of our company's domain. Once this configuration is in place, the email relay servers anywhere on the web can check emails sent with a custom From address and check DNS to see if the source servers are on the SPF list and therefore valid.
In short, SPF lets custom domain owners identify the servers they have approved to send emails on behalf of their domain.



DomainKeys Identified Mail (DKIM):


DKIM is used to verify the authenticity of email messages sent from Oracle Fusion cloud applications. DKIM authenticates emails through a pair of cryptographic keys. Email senders generate public and private key pairs. The public key is published to DNS records, and the matching private keys are stored in a sender's outbound email servers. The private keys generate message-specific signatures that are added to the embedded email headers. ISPs that authenticate using DKIM look up the public key in the public DNS record. This way they verify that the signature in the email header was actually generated by the matching private key. DKIM ensures that an authorized sender actually sent the message, and that the message headers and content were not altered during transit.



Process to configure SPF and DKIM in Oracle Fusion Cloud:


- Create a service request on the Oracle Support portal asking for SPF and DKIM configurations on the specific Pod of your choice. Please note that you will need to create a separate SR for each Pod in order to configure SPF and DKIM.

- Oracle will respond with SPF configuration details.

- Add a new SPF record to the domain of the from address to include the Oracle Cloud email delivery domain.

The SPF record statement is: include:spf_c.oraclecloud.com

- Validate the SPF record by using an SPF record checker tool. e.g. we can use the SPF Surveyor tool to authenticate our domain.

How to use the SPF Surveyor tool:

Navigate to https://dmarcian.com/spf-survey/

Here, we enter the domain we are using for the email, e.g. ourcompany.com

Click Survey domain.

A message is displayed indicating the validation results as shown below:



If there's a problem with the SPF configuration or if it's not been configured properly then we will see message as shown below:






- For DKIM configuration, we need to provide the below information in the SR:


Oracle Fusion environment Pod Name

The new custom domain based 'From' email address e.g. noreply@ourcompany.com

Key size: Default is 1024. One can change this to 2048, if desired.

DNS Selector name: Oracle generates this by default but one can specify a custom value, if desired. The default generated DNS selector uses this format: <env-name>-<region-code>-<date>



- Once this information is provided, Oracle registers the DKIM and responds to the service request with a DKIM-enabled DNS record.


Sample DKIM-enabled DNS record:

"key": "ORACLEABC1._domainkey.ourcompany.com",
"type": "TXT", 
"value":"v=DKIM1;k=rsa;p=MIIGIjEQWgkdkjgwdkjbnksjbnkwjnHnEGZXcJAHDBAB"


- We need to add this DNS record to our domain configuration and then update the service request confirming the changes we've made.


- At this stage, we have to wait for some time because Oracle has a process that runs every fifteen minutes to detect the DKIM stored in the customer DNS. After this step, Oracle (CNS) will start signing the emails. It usually takes somewhere between fifteen minutes to 24 hours for the DNS information to be detected by Oracle and for the DKIM process to therefore be successfully enabled. Once this process is completed, Oracle support engineer will respond on the SR.


- Once Oracle support responds, we need to verify that the signed email messages are delivered successfully. Once confirmed, we update the support request with confirmation.


- At this stage, Oracle support will change the 'From' email address in your Fusion Cloud environment to the new custom domain based DKIM-enabled address e.g noreply@ourcompany.com


Note: Please note that one needs to repeat these steps for each Pod by creating a separate SR for each Oracle Fusion Pod where the custom From email is required to be configured using SPF and DKIM.



Conclusion:

SPF and DKIM are critical components of email authentication and security in Oracle Fusion cloud. They prevent email spoofing and enhance the deliverability of enterprise emails.
These methods ensure that business communications remain trustworthy and secure. Organizations leveraging Oracle Fusion cloud and wishing to use custom domain based From email addresses, need to implement SPF and DKIM to safeguard their email ecosystem and enhance overall enterprise security.


Share:

Wednesday, July 2, 2025

How to dynamically extract metadata definitions in Oracle APEX

I recently came across a requirement where I needed to dynamically extract metadata definitions (such as column names, data types, etc.) of a variety of objects such as Tables, Views etc.


I achieved this using apex_data_export API in Oracle APEX. It’s important to note that apex_data_export is primarily used to export data and not the metadata. So to extract metadata definitions dynamically, we must first query metadata from data dictionary sources, and then pass that query result to the API call.


Let's see it in action.


Here's a sample PL/SQL block using apex_data_export to get Metadata:

DECLARE
    l_context apex_exec.t_context; 
    l_export  apex_data_export.t_export;
BEGIN

    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select column_name, data_type, data_length from all_tab_cols where table_name=' || '''' || 'AJ_AP_INV' || '''');

    l_export := apex_data_export.export (
        p_context   => l_context,
        p_format    => apex_data_export.c_format_csv,
        p_file_name => 'AJ_AP_INV.csv' );

    apex_data_export.download( p_export => l_export );

    apex_exec.close( l_context );

EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;
END;


Now, let's incorporate this in an APEX page and invoke it upon a Button press.

- Let's create a Process in After Submit section

- Set above code in PLSQL section -



- Let's set the Server-side condition When Button Pressed to our export button



- That's it. Now when we run the app and click the Export button, it will automatically download the Metadata definition of our sample table AJ_AP_INV






- Now, if we open the downloaded file, we will be able to see the metadata of this table -




How it was used in my use case:


- My requirement was to obtain metadata definitions of Public View Objects (PVOs) and their underlying tables.

- In this case, the data lineage information for PVOs was ingested into Autonomous database which was accessed by APEX. The table holding all the PVO information was XXCUST_FSCM_DATA_LINEAGE

- To provide more perspective, let's take an example of FscmTopModelAM.AnalyticsServiceAM.TerritoriesPVO.

This PVO essentially contains columns from FND_TERRITORIES_B and FND_TERRITORIES_TL.

Now we want our solution to extract Metadata definition of this PVO as well as metadata definitions of both the underlying tables.

Let's see how this was achieved.

- I created a new page with a text box accepting the PVO name and three buttons to export metadata of PVO and the underlying tables.



- I created a new process to export PVO metadata by using below PL/SQL block and set it to trigger upon click of PVO button:

DECLARE
    l_context apex_exec.t_context; 
    l_export  apex_data_export.t_export;
BEGIN

    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'SELECT LISTAGG(VIEW_OBJECT_ATTRIBUTE, '''|| ',' || ''') WITHIN GROUP (ORDER BY NULL) "Col"
FROM   XXCUST_FSCM_DATA_LINEAGE
WHERE  VIEW_OBJECT = :P_PVO_NAME' );

    l_export := apex_data_export.export (
        p_context   => l_context,
        p_format    => apex_data_export.c_format_csv,
        p_file_name => :P_PVO_NAME );

    apex_data_export.download( p_export => l_export );

    apex_exec.close( l_context );

EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;
END;



- Similarly, I created two more processes, one for each underlying table using below code and set it to trigger upon click of each table button:

DECLARE
    l_context apex_exec.t_context; 
    l_export  apex_data_export.t_export;
    CURSOR c1 IS
        SELECT DATABASE_TABLE TABLE_NAME
        FROM
        (
        SELECT DATABASE_TABLE
        ,row_number() over (order by NULL) rnum
        FROM(
        SELECT DISTINCT DATABASE_TABLE
        FROM   XXCGI_FSCM_DATA_LINEAGE
        WHERE  VIEW_OBJECT = :P_PVO_NAME
        ORDER BY 1
        )
        )
        WHERE rnum=1;
BEGIN

    FOR I IN c1
    LOOP
        
        l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'SELECT LISTAGG(DATABASE_COLUMN, '''|| ',' || ''') WITHIN GROUP (ORDER BY NULL) "Col"
FROM   XXCUST_FSCM_DATA_LINEAGE
WHERE  VIEW_OBJECT = :P_PVO_NAME
AND    DATABASE_TABLE='|| ''''||I.TABLE_NAME|| '''');

    l_export := apex_data_export.export (
        p_context   => l_context,
        p_format    => apex_data_export.c_format_csv,
        p_file_name => I.TABLE_NAME );

    apex_data_export.download( p_export => l_export );

    apex_exec.close( l_context );

    END LOOP;

EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;
END;



- Let's run the app, provide the PVO name and click the three buttons to see the result



- As we can see, it downloaded three files, one for PVO meta definition, one for table #1 and another for table #2.





Possible Use cases:

This mechanism would certainly come handy in below use cases and many more:

• Dynamically inspecting a table/view

• Building data dictionaries

• Exporting data structure to Excel

• Validating report configuration dynamically

Share: