Rupatil's Oracle Apps Technical Hub

Thursday, November 28, 2019

Calling Fusion Soap Web Service in PL/SQL

Step 1 : Get Soap web service wsdl

Find the SOAP web service url i.e.wsdl from Oracle Enterprise Repository (OER). Below are the steps given to get the soap web service wsdl.

Go to OER and click on ‘Cloud Applications’

Select product offering. Here we are looking for journal import soap web service. Hence we have selected Financials product offering.

Select the cloud applications release from given dropdown. Here we selected release 19D. Click on ‘Integrate’

Click on ‘Review SOAP web services’ under Discover Web Services section.

Click on ‘Business Object Services’

Search for business object ‘Journal’ and click on Journal Interface

We get the soap web service wsdl here to import journal into interface table of oracle fusion application.

Modify the above Service WSDL URL to include the URL path of your oracle cloud/fusion instance. Here we are using fusion demo instance provided by Oracle.

Original WSDL:

https://(FinancialDomain,Ledger)/finGlDesktopEntry/JournalImportService?WSDL

After Modification:

https://ucf6-fap1522-fa-ext.oracledemos.com/finGlDesktopEntry/JournalImportService?WSDL

Step 2: Create SOAP Project

We need SoapUI tool installed. You can get it on google for free since it is a opensource Soap Client. Create new project in SoapUI.

Enter the project name. Provide the modified WSDL url and click on OK.

Find mandatory attributes for importing journal and create a request payload. Sample request payload is given below (Only mandatory fields are included in the request payload) -

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/generalLedger/journals/desktopEntry/journalImportService/types/" xmlns:jour="http://xmlns.oracle.com/apps/financials/generalLedger/journals/desktopEntry/journalImportService/" xmlns:acc="http://xmlns.oracle.com/apps/financials/generalLedger/accounts/codeCombinations/publicFlex/Account/">

<soapenv:Header/>

<soapenv:Body>

<typ:importJournals>

<typ:interfaceRows>

<jour:BatchName>RP_TestBatch</jour:BatchName>

<jour:BatchDescription>RP_TestBatch</jour:BatchDescription>

<jour:LedgerId>300000046975971</jour:LedgerId>

<jour:AccountingPeriodName>07-17</jour:AccountingPeriodName>

<jour:AccountingDate>2017-07-14</jour:AccountingDate>

<jour:UserSourceName>Manual</jour:UserSourceName>

<jour:UserCategoryName>Manual</jour:UserCategoryName>

<jour:ErrorToSuspenseFlag>True</jour:ErrorToSuspenseFlag>

<jour:SummaryFlag>True</jour:SummaryFlag>

<jour:ImportDescriptiveFlexField>N</jour:ImportDescriptiveFlexField>

<jour:GlInterface>

<jour:LedgerId>300000046975971</jour:LedgerId>

<jour:PeriodName>Jul-17</jour:PeriodName>

<jour:AccountingDate>2017-07-14</jour:AccountingDate>

<jour:UserJeSourceName>Manual</jour:UserJeSourceName>

<jour:UserJeCategoryName>Manual</jour:UserJeCategoryName>

<jour:GroupId></jour:GroupId>

<jour:ChartOfAccountsId></jour:ChartOfAccountsId>

<jour:BalanceType>A</jour:BalanceType>

<jour:CodeCombinationId></jour:CodeCombinationId>

<jour:Segment1>101</jour:Segment1>

<jour:Segment2>10</jour:Segment2>

<jour:Segment3>11504</jour:Segment3>

<jour:Segment4>000</jour:Segment4>

<jour:Segment5>000</jour:Segment5>

<jour:Segment6>000</jour:Segment6>

<jour:CurrencyCode>USD</jour:CurrencyCode>

<jour:EnteredCrAmount currencyCode="USD">777</jour:EnteredCrAmount>

<jour:ManagementSegmentValue>?</jour:ManagementSegmentValue>

<jour:ObjectVersionNumber>1</jour:ObjectVersionNumber>

</jour:GlInterface>

</typ:interfaceRows>

</typ:importJournals>

</soapenv:Body>

</soapenv:Envelope>

Step 3: Create ACL in oracle database

To call Soap web service from pl/sql, we first need to create ACL (Access Control List) to allow access to external netwrok.

There are two steps: First create an ACL, and add a host and port range to the ACL.

Here in our example, we need to allow access to www.oracle.com, you could use the following code block -

--Create an ACL

BEGIN

DBMS_NETWORK_ACL_ADMIN.create_acl (

acl => 'acl_oracle.xml',

description => 'Allow access to oracle.com for testing',

principal => 'SCOTT',

is_grant => TRUE,

privilege => 'connect',

start_date => SYSTIMESTAMP,

end_date => NULL);

--Assign Port

DBMS_NETWORK_ACL_ADMIN.assign_acl (

acl => 'acl_oracle.xml',

host => 'www.oracle.com',

lower_port => 80,

upper_port => NULL);

COMMIT;

END;

Step 4 : Create wallet on application server

We also need to create a secure wallet on oracle application server. For this we first need to download trusted certificates for web service. To download the certificates follow belwo steps –

1. Open the WSDL in web browser. Click on ‘lock’ icon in address bar.

2. Click on certificate.

3. Under Certification Path tab, all the certificates are listed. We need to download all these certificates.

4. Under Details tab, click on button Copy to File… and then click on Next.

5. Select file type and click on next. Click on Browse button to browse the file location where we want to save the certificate.

6. Keep filenames same as certificate names as listed in Certification Path tab.

7. Click on Save button. Click on Finish.

8. Download all other certificates in similar manner.

Step 5 : Create wallet on application server

After downloading .CER certificates for target web service, create wallet on application’s server (unix) using following commands –

First of all, place the downloaded certificates on application server at your preferred location.

i. Create Wallet (Syntax):

$ orapki wallet create -wallet <wallet directory> -auto_login

For eg :- $ orapki wallet create -wallet $ORACLE_HOME/owm/wallets/oracle -auto_login

ii. Add certificate to your wallet (Syntax):

$ orapki wallet add -wallet <wallet directory> -trusted_cert -cert <certificate file path> -pwd <wallet password>

For eg :- $ orapki wallet add -wallet $ORACLE_HOME/owm/wallets/oracle -trusted_cert -cert /home/oracle/oracledemos.cer -pwd Welcome123

Step 6 : Write a PL/SQL code to call Fusion journal import soap web service

After creating ACL and wallet in the database, we can call soap web service using UTIL_HTTP package. Sample code for above request payload is attached below.

DECLARE

-- soap requests/response

soap_req_msg VARCHAR2 (32767);

soap_resp_msg VARCHAR2 (32767);

-- http request/response

http_req UTL_HTTP.req;

http_resp UTL_HTTP.resp;

x_clob CLOB;

l_buffer VARCHAR2 (8000);

BEGIN

--set wallet directory

UTL_HTTP.set_wallet ('file:/u02/oracle/VIS/db/tech_st/11.2.0/appsutil/wallet', 'welcome123');

-- create soap request via http

soap_req_msg := 'Place the sample request payload given above';

--WSDL url for fusion journal import web service is placed in UTL_HTTP.begin_request, web service handler as 'POST' and protocol version HTTP/1.1

http_req := UTL_HTTP.begin_request ('https://ucf6-fap1522-fa-ext.oracledemos.com/finGlDesktopEntry/JournalImportService?WSDL', 'POST', 'HTTP/1.1');

UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml;charset=UTF-8');--type of input data i.e. request payload

UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (soap_req_msg));

UTL_HTTP.set_header (http_req, 'SOAPAction', 'https://ucf6-fap1522-fa-ext.oracledemos.com/finGlDesktopEntry/JournalImportService');

UTL_HTTP.set_header (http_req, 'Connection', 'Keep-Alive');

--Specify fusion user's username and password

UTL_HTTP.set_authentication (http_req, 'casey.brown', 'XBp58877');

UTL_HTTP.write_text (http_req, soap_req_msg);

-- invoke request and get response.

http_resp := UTL_HTTP.get_response (http_req);

DBMS_LOB.createtemporary (x_clob, FALSE);

DBMS_LOB.OPEN (x_clob, DBMS_LOB.lob_readwrite);

LOOP

DBMS_OUTPUT.put_line ('inside');

UTL_HTTP.read_text (http_resp, l_buffer);

DBMS_LOB.writeappend (x_clob, LENGTH (l_buffer), l_buffer);

END LOOP;

DBMS_OUTPUT.put_line ('Response Status: ' || http_resp.status_code || 'Reason : ' || http_resp.reason_phrase);

DBMS_OUTPUT.put_line (x_clob);

dbms_lob.freetemporary (x_clob);

UTL_HTTP.end_response (http_resp);

EXCEPTION

WHEN UTL_HTTP.end_of_body

THEN

UTL_HTTP.end_response (http_resp);

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Exception Occurred : ' || SQLERRM);

END;

After pl/sql code execution completes successfully, Journal entry information is imported in gl_interface table.

Step 7: Schedule Import Journals ESS Job

Finally, we have to schedule “Import Journals” ESS job (concurrent program) in fusion applications.

After “Import Journals” ESS job completes successfully, Journal entry is created in gl_je_headers table. We can check this by creating a data model in ‘BI Reports and Analytics’ section.

Monday, September 16, 2019

Supplemental Logging in Oracle Database

Supplemental Logging

Supplemental logging is a pre-requisite for redo log based database replication or recovery after instance crash.
By default, there is not enough information maintained in redo logs by oracle to reconstruct SQL statements so that they can be executed in multiple databases (Replica of some database table or entire database can be maintained at different, unrelated databases).

For example - If any update happens on database table which is replicated on another database, and all the changes are replicated in real time (as and when they happen at original database) redo logs having only ROWID (by default) are not sufficient to update the same record on replicated database table because ROWID of one database will be different on another database.
Supplemental logging allows us to add extra information in redo logs.

Supplemental logging can be enabled at database level or at table level.

Supplemental logging at table level -
1. Supplemental Log Groups :
Allows to specify list of columns to be logged in redo logs.
'ALWAYS' keyword is used for unconditional log groups. All specified columns are logged into redo log file every time a row is updated whether or not any of the specified column is updated.
Ex. ALTER TABLE table_name ADD SUPPLEMENTAL LOG GROUP GROUP_NAME (COL1, COL2, COL3,..) ALWAYS

2. Supplemental Key Column Logging :
Allows to specify specific columns i.e. primary key/ unique/ foreign key or even ALL columns to be logged every time table row is updated.
Ex. i) ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS
ii) ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS
iii) ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS
iv) ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

Supplemental logging is enabled at both the levels mentioned above in below table -

CREATE TABLE XXRVBD.XXRB_ORCL_INV_ONHAND_DTLS
(
INV_ORG_CODE VARCHAR2(20 BYTE),
SUBINV_ORG_CODE VARCHAR2(20 BYTE),
PART_NUMBER VARCHAR2(100 BYTE),
SERIAL_NUMBER VARCHAR2(100 BYTE),
QUANTITY NUMBER,
CREATION_DATE DATE,
CREATED_BY VARCHAR2(100 BYTE),
PICK_FLAG VARCHAR2(1 BYTE),
SPLUS_ACCOUNT VARCHAR2(50 BYTE),
UPS_WAREHOUSE VARCHAR2(50 BYTE),
SUPPLEMENTAL LOG GROUP GGS_11869075_2 (INV_ORG_CODE,SUBINV_ORG_CODE,PART_NUMBER,SERIAL_NUMBER,QUANTITY,CREATION_DATE,CREATED_BY,PICK_FLAG,SPLUS_ACCOUNT,UPS_WAREHOUSE) ALWAYS,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS,
SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
)

Wednesday, August 14, 2019

ORDS : AutoREST Enabling Objects

ORDS : AutoREST Enabling Objects

AutoREST is a feature in ORDS that allows us to interact with oracle database without writing any code. It provides predefined REST handlers (GET, POST, PUT, DELETE) to access a database object.

Handlers are nothing but the set of action that needs to be performed when a particular REST operation is called on a resource. For example, if we call a REST web service with GET method, ORDS needs to know what action it is supposed to perform on the resource with GET method. This action is defined with the help of handlers.

At very high level, meaning of each REST method can be defined as (though it can vary depending on the resource) –

1. GET - Retrieve records with or without search criteria

2. POST - Insert new record

3. PUT - Update existing record

4. DELETE - Delete existing record

Let’s start AutoREST enabling a database table. But before that I am assuming below things –

You have access to oracle database.
You have ORDS installed on oracle database.

For this demonstration, I am using SQL Developer and I have installed ORDS on Oracle XE Database installed on my local machine (i.e. localhost).

Step 1 : REST enable a database schema.

When ORDS is installed on database, it enables a ‘REST Data Services’ menu option in connection tree as shown below. Expand this menu

Under the connection tree, right click on schema name, select REST Services -> Enable REST Services option.

Mark the Enable schema checkbox. Give alias to your database schema if you don’t want to reveal the schema name otherwise keep it same as schema name. Click on Next.

Click on Finish.

Step 2 : REST enable database table.

Right click on a table name that you want to REST enable and select Enable REST Services.

Make sure the Enable object checkbox is marked. Give object alias. This will be part of a URI that we will be using to access DEPARTMENTS table. Click on Next.

Click on Finish.

We can also perform above two steps using APIs provided by ORDS as shown below –

Step 1 : REST enable a schema

BEGIN

ORDS.ENABLE_SCHEMA(p_enabled => TRUE,

p_schema => 'HR',

p_url_mapping_type => 'BASE_PATH',

p_url_mapping_pattern => 'hr',

p_auto_rest_auth => FALSE);

commit;

END;

Step 2 : REST enable a database table

BEGIN

ORDS.ENABLE_OBJECT(p_enabled => TRUE,

p_schema => 'HR',

p_object => 'DEPARTMENTS',

p_object_type => 'TABLE',

p_object_alias => 'departments',

p_auto_rest_auth => FALSE);

commit;

END;

That’s it. We have REST enabled a table DEPARTMENTS. Now let’s try accessing it from REST client. Here we will be using Postman – addon by google chrome which acts as a REST client. A simple web browser like Mozilla Firefox also works as a REST client but it has limitation of being able to call only GET methods.

1) GET :- We call GET method on departments table (which is REST enabled) with URI below - http://localhost:9090/ords/hr/departments/

It tells the ORDS installed on database running at localhost:9090 to connect to the ‘hr’ schema to query the ‘departments’ table. Below is the output in JSON format we get in browser -

We can also query single record by just appending a primary key column value to the URI (as the one returned in above output as href link). It also includes additional links to capture metadata information.

2) POST :- We can insert new record in departments table with POST method. For this, we will use Postman. URI will remain same as the one in GET method. o Here we need to select POST method and give input values to the columns in departments table as key:value pair in body section.

Here we need to select POST method and give input values to the columns in departments table as key:value pair in body section.
Then Click on send. We will get 201 response code which means record is created successfully.

3) PUT :- We can update an existing record with PUT method. Follow the below steps –

Select PUT method.
URI will remain same here as well except one change that we need to give the primary key id of the record that we want to update in the URI itself.
Specify the values for fields that we intend to update as key:value pair in the body section.
Click on send. We will get response code 200 which means the operation is performed successfully.

4) DELETE :- We can delete an existing record using DELETE method with below steps -

Select DELETE method.
Here URI will have search criteria (query) included in it as shown below (Here I’ve want to delete the department with department_id 300)
Keep the Content-Type header attribute value as application/x-www-form-urlencoded

Oracle REST Data Services (ORDS)

Oracle REST Data Services (ORDS)

What is ORDS?

In this blog I will be introducing you to Oracle REST Data Services, a technology by oracle commonly referred to as ORDS. In most simple words, ORDS can be defined as ‘a java application which enables us to expose oracle database as a RESTful web service’. As its name implies, it uses REST architecture. Now the question is why specifically REST when we have many more web based protocols like SOAP? The answer is - REST is easy.

It comes with small and uniform set of operations : GET, PUT, POST, DELETE
Small and uniform set of status codes : 200(Success), 401(Unauthorized), 500(Server error) etc. There are few more, but I can only think of these at the time.
It exposes a resource rather than behaviour/action. For example, let say if I have two numbers a and b. What SOAP offers is – addition web service, web service for subtraction, division, multiplication and so on. But what REST will say is – hey, I have a calculator to offer with set of operation including add, subtract, divide, multiply. Tell me what operation you want to perform on these two numbers.
REST has become a de-facto standard on which most of the web/internet is built on. (There is no written rule, but it is commonly accepted architecture for building web applications.)

ORDS Architecture

As I said above, ORDS comes as a java application which is installed on top of oracle database. ORDS always runs within an application server which traverses requests/response to and from database. We can use ORDS either in standalone mode or with an application server like Apache tomcat, WebLogic or Glassfish server. When ran in standalone mode, it runs within its own embedded application server called Jetty.

NOTE : Standalone mode is recommended to be used only on Non-Prod/Dev/Test environments.

ORDS comes with .war configuration file and not as an executable (.exe). We need to place it somewhere on application server and install using command line interface. Alternately, we can also install ORDS through SQL Developer. Recent versions of SQL Developer like 4.1 and onwards, give us flexibility to install and use ORDS through wizards or in other words it provides us GUI so that we can enable our database objects as RESTFul web service with minimal or almost zero coding.

More on ORDS architecture –

Below picture depicts how ORDS works. Here client can be any application having some means to call HTTP protocol or it can be simply an end user accessing ORDS web service through a web browser.

Client requests an access to particular resource using URI i.e Universal Resource Identifier assigned to each REST enabled object in database by ORDS. URI is made up of –

HTTP : It tells ORDS that it has to use http protocol to transfer information over the network.
Hostname + Port : Hostname identifies a particular database server and a port at which it is listening for incoming requests/connections.
Basepath : HTTP://hostname:9090/ords/ this becomes our base path to which ORDS decrypts as – use http messaging protocol and connect to the database server identified by ‘hostname’ and the DB is running at port 9090.

Since ORDS is a java-based application, it uses JDBC connection pool to connect to a particular database. When a request comes to ORDS, it decomposes the URI as explained above to identify a database it is supposed to connect to. It then borrows one connection from its internal JDBC connection pool and connects to the database.

It then does mapping and binding of URI to database object and transforms the request into sql/plsql query. Database runs the query and sends result back to ORDS.

ORDS again transforms the result into JSON format before sending it to the client.

Uses

a. Exposing data restfully

The primary use case is to expose existing data as REST services. One area where I have seen this used extensively is exposing Oracle e-Business suite data as REST services. For ex. we can make our customer/master items data easily available for other applications like SFDC.

b. Data capture

ORDS is a fast and light weight method of allowing you to capture data. Think IOT (Internet of Things). There is a vast amount of data coming at us and we need a way to easily capture it and store it using fast industry standard protocols.

c. Integration middleware

Having provided the ability to capture and expose data, oracle database along with ORDS can function as low-cost integration middleware. (Purpose of integration middleware is to send the data back and forth between applications - exactly what ORDS enabled us to do with oracle DB)

That was all the theory about ORDS. We will see ORDS implementation with some practical examples in next blog à Link is here.