Loading Data from Oracle Field Service Cloud into Oracle BI Cloud Service using REST

Introduction This post details a method of extracting and loading data from Oracle Field Service Cloud (OFSC) into the Oracle Business Intelligence Cloud Service (BICS) using RESTful services. It is a companion to the A-Team post Loading Data from Oracle Field Service Cloud into Oracle BI Cloud Service using SOAP . Both this post and […]

Loading Data from Oracle Identity Cloud Service into Oracle BI Cloud Service using REST

Introduction This post details a method of extracting and loading data from Oracle Identity Cloud Service (IDCS) into the Oracle Business Intelligence Cloud Service (BICS). It builds upon the A-team post IDCS Audit Event REST API which details the REST API calls used. One use case for this method is for analyzing trends regarding audit events. […]

Loading Data from Oracle Field Service Cloud into Oracle BI Cloud Service using SOAP

Introduction This post details a method of extracting and loading data from Oracle Field Service Cloud (OFSC) into the Oracle Business Intelligence Cloud Service (BICS). A compelling reason to use such a method is when data is required that is not in the standard daily extract. Such data might be planning (future) data or data […]

Loading Data into Oracle BI Cloud Service using BI Publisher Reports and REST Web Services

Introduction This post details a method of loading data that has been extracted from Oracle Business Intelligence Publisher (BIP) into the Oracle Business Intelligence Cloud Service (BICS). The BIP instance may either be Cloud-Based or On-Premise. It builds upon the A-Team post Extracting Data from Oracle Business Intelligence 12c Using the BI Publisher REST API. […]

Loading Data into Oracle BI Cloud Service using BI Publisher Reports and SOAP Web Services

Introduction This post details a method of loading data that has been extracted from Oracle Business Intelligence Publisher (BIP) into the Oracle Business Intelligence Cloud Service (BICS). The BIP instance may either be Cloud-Based or On-Premise. It builds upon the A-Team post Using Oracle BI Publisher to Extract Data from Oracle Sales and ERP Clouds. […]

Creating Event Based E-Mail and Text Notifications for BICS using Apex

Introduction Event Based notification functionality for Oracle’s BI Cloud Service (BICS) will be available later this year, but until that time some of the same functionality can be created through Apex. This article will walk through a process for creating notifications, both e-mail and SMS text message, based on a data event within BICS.  The article […]

Extracting Data from BICS / Apex via RESTful Webservice

Introduction BI Cloud Service (BICS) has an API to load data into the underlying database (more details here), but reports created in BICS can not be made available and called as web-services to extract the data.  This article will document a method by which RESTful web-services can be created in Apex and exposed to replicate […]

Externalizing Authorization in PL/SQL? Inconceivable!

Ok, so it’s not a land war in Asia.But for a recent engagement, I received requirements for replacing a home-grown authorization system and doing a proof-of-concept on APEX.Eek, unsupported platform.Alright, let’s roll up our sleeves.

If there’s one thing you can find at Oracle, it’s PL/SQL expertise.I was referred to one of our rock stars, Tyler Muth, for guidance in approaching this requirement.Tyler directed me to the flex-ws-api which allows you to make PL/SQL call to a SOAP interface.OES provides a SOAP interface for authorization, so could this be love at first sight?

Flex-ws-api is pretty straightforward.You put the SOAP envelope right into the body of a CLOB data type.OES Web Service Security Module, hereto forth referred to as the Policy Decision Point or PDP, authenticates using a username-only Identity Asserter.See the OES documentation on setting up Sharepoint for instructions on this.

set serveroutput on

declare

l_BLOB BLOB;

l_CLOB CLOB;

l_envelope CLOB;

l_response_msg varchar2(32767);

l_response_xmlXMLType;

procedure show_xml (p_xml in xmltype)isl_str long;beginl_str := p_xml.extract(‘/*’).getstringval();loopexit when l_str is null;dbms_output.put_line (substr (l_str, 1, instr (l_str, chr(10)) – 1));l_str := substr (l_str, instr (l_str, chr(10)) + 1);end loop;end show_xml;

BEGIN

l_envelope := q’! <?xml version=’1.0′ encoding=’UTF-8′?>!’;

l_envelope := l_envelope || ‘ <soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>

<soap:Body>

<isAccessAllowed xmlns=”http://security.bea.com/ssmws/ssm-soap-types-1.0.xsd”>

<IdentityAssertion>

<sampletoken>matt.carter </sampletoken>

</IdentityAssertion>

<RuntimeResource>

<ResourceString>app/claim </ResourceString>

<AuthorityName>ARME_RESOURCE_AUTHORITY </AuthorityName>

</RuntimeResource>

<RuntimeAction>

<ActionString>view </ActionString>

<AuthorityName>ARME_ACTION_AUTHORITY </AuthorityName>

</RuntimeAction>

<RequestedCredentialType>sampletoken </RequestedCredentialType>

<AppContext>

<Record>

<q1:RecordName xmlns=”” xmlns:q1=”http://security.bea.com/ssmws/ssm-soap-types-1.0.xsd”>attrname </q1:RecordName>

<StringValue>attrvalue </StringValue>

</Record>

</AppContext>

<AtzDirection>ALES_ONCE </AtzDirection>

</isAccessAllowed>

</soap:Body>

</soap:Envelope>’;

l_response_xml := flex_ws_api.make_request(

p_url=> ‘http://oes-pdp:9000/Authorization’,

p_action=> ‘isAccessAllowed’,

p_envelope=> l_envelope);

 

show_xml(l_response_xml);

END;

/

 

The OES response would look something like:

 

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>

<soapenv:Body>

<isAccessAllowedResponse xmlns=”http://security.bea.com/ssmws/ssm-soap-types-1.0.xsd”>

<AccessAllowed>true </AccessAllowed>

<AtzDecisionData>

<AtzTtlAdvice>300 </AtzTtlAdvice>

</AtzDecisionData>

<ContextRequests xsi:nil=”true”/>

</isAccessAllowedResponse>

</soapenv:Body>

</soapenv:Envelope>

From a database permissions side, the 10g database on the client site connected right to the PDP.In our 11g testing we found you had to set up some Access Control Lists on the database to have the database make a network connection.See this article for instructions on setting up this ACL.

The client was happy to PL/SQL and felt they could go the next step to externalizing this authorization in APEX.I haven’t accomplished this myself, so I would welcome readers to take this the next step.APEX has the capability for very fine-grained authorization and the ability to externalize this to a “PL/SQL Function returning a Boolean” type of Authorization Scheme, so it would seem APEX could call to OES for a Permit or Deny.

Special thanks to Tyler Muth for his direction on the PL/SQL side and Sri and the OES team for their guidance. Thanks to the A-Team for helping me get the word out.

mkc

Calling BPEL web services from Application Express

Just a quick post, on an issue that seems to come up a lot – how to call a BPEL web service from Application Express.  This used to be quite challenging, but now, with APEX 3.1.2, APEX can understand a … Continue reading