zaterdag 31 oktober 2015

Script to find unique values of all columns in a table (data profiling)

Script to find unique values of all columns in a table (data profiling)

Someone in the community was looking for a script to subtract all unique values for columns in a table. There are several ways to do this, for example use a DISTINCT on each column and gather your lists that way.
You could also build your query using dynamic sql and all_tab_columns so you don't have to program the columns.
Next to that there are more advanced ways using Java and Hashing, but a very simple script that may be useful in some cases could be something like this. Ofcourse note that I use varchar as index here, where your columns could be dates, numbers, etc.
If data analysis however is a major thing, you may also like to check out the Big Data Preparation Cloud ( Pretty cool stuff which does profiling, but also provides mapping rules and detects data that might need obscuring like SSN, credit card numbers, etc.

  CURSOR C_All_Rows
  ,      Country
  l_cities   T_Values;
  l_countries T_Values;
  i           VARCHAR2(240);
  FOR R IN C_All_Rows
    IF l_cities.exists (R.City) THEN NULL; ELSE l_cities (R.City) := R.City; END IF;
 IF l_countries.exists (R.Country) THEN NULL; ELSE l_countries (R.Country) := R.Country; END IF;
  END LOOP Rows;
  dbms_output.put_line ('Number of countries: ' || l_countries.count);
  dbms_output.put_line ('Number of cities   : ' || l_cities.count);
  i := l_countries.first;
    dbms_output.put_line (l_countries (i));
    i :=;
    i := l_cities.first;
    dbms_output.put_line (l_cities (i));
    i :=;

woensdag 28 oktober 2015

Oracle Application Builder Cloud Service (ABCS)

Oracle Application Builder Cloud Service (ABCS)

This week at Open World the Application Builder Cloud Service was announced, A simple “development” tool to create your own custom apps for web or mobile based on SAAS REST services. Oracle Sales Cloud already provides you some REST services to use, but ERP Cloud has none to date.

The end-user can create his own page in a few clicks, for example to show opportunities with or without child related objects. But it’s also possible that you want to add additional information, not available in Sales Cloud, like tickets related to your opportunities. You can easily define custom objects, which are stored in the 2-4GB database that you get with ABCS.

Oracle is also working on a connection to your own custom local database or the Database Cloud Service though. At this moment your custom objects would only be available in your local app and not cross apps.

ABCS provides you the choice between standard Cloud Application layout or Alta UI. ABCS is based on Javascript and the Javascript Extensibility Toolkit (JET),

Oracle JET
Oracle JET is a Javascript Development Framework which was used by Oracle internally in the last 2 years to build the Cloud Service solutions and contains stuff like JQuery for the UI Components and other available popular components.
Of course other popular frameworks are already out there like AngularJS, Backbone, Ember, Meteor, etc.

Oracle JET is also available to Oracle customers using Cloud Services under Restricted Use License mainly to connect to existing SAAS REST Services.
Next step is that JET will be released as open source.

So how do these products compare to ADF?

ABCS is aimed at the Citizen Developer, the business user, who can easily create his own app based on the SAAS services, without help of IT. It allows for very simple validations, but any complex validations and navigation should be handled by Javascript, which can be provided for example with JET.
You can export your ABCS application and customize it further in JET (but you cannot import it back again).

So ABCS is a very nice intuitive tool to create custom pages quickly, also for mobile, but it’s not a development tool as such.

ABCS is dus een hele mooie intu├»tieve tool waar je snel maatwerk pagina’s mee kunt maken, ook voor mobiel, maar het is niet een development tool voor developers en het is niet bedoeld voor complexe applicaties.

Oracle JET is aimed at the Javascript Developer, while Oracle ADF aims at the Java Developer. JET is also code centric, while ADF works more in a declarative way using Metadata Services (MDS), where you build your UI with some kind of composer.

Oracle JET is a client-side toolkit and ADF is aserver framework. Oracle JET only allows for REST webservices, where ADF supports much more type of connections including SOAP, REST services and direct binding.

In Oracle JET you can copy code snippets (HTML, Javascript) from the JET Cookbook (for example from the Knockout Framework) and paste it in your HTML template in any IDE (Netbeans for example) and build your page quickly using code only.

Oracle ADF or JET?
Extensible UI through in-place editing or some form of composer
Oracle ADF
Declarative programming model based on Metadata Services (MDS)
Upgrade safety for an enterprise application
Oracle ADF
Metadata and API upgradability for base code and extensions
Declarative navigation and/or reusable task flows
Oracle ADF
Task Flows
Multiple Fusion Middleware (FMW) Suite products
Oracle ADF
Federated regions and task flows
Integrate with Fusion Applications (FA)
Oracle ADF
Services for complying with FA navigation, state sharing, and so on
Desktop integration with Microsoft Excel
Oracle ADF
Microsoft Excel front ends
Insulation from technology shifts
Oracle ADF
Guaranteed support for upgrades
Embed UI in a page built with third-party technology
Oracle JET
Fully client-side rendered component set and framework that doesn't assume page ownership
Single page application
Oracle JET
URL routing and browser history management for navigating within a single page
Standalone component set that can be integrated in a third-party framework
Oracle JET
Wrapped as independent client-side rendered jQuery UI components

Below yoou see some examples of what was shown on Open World for the Work Better app as promotion of Alta UI. The first is developed using ADF Faces, where the second was with JET. As you can see you can’t see much difference in layout, but that’s because they are both based on the same UI components, which come from the JET framework underneath.

So in short, Application Builder Cloud Service might be a nice way for the Citizen Developer to create his own app, Oracle JET for smaller cloud based applications based on REST webservices and where your development team focuses on Javascript. If your development team focuses on Java and you need a server side application with much data access, you may focus on ADF.

dinsdag 27 oktober 2015

eBS integration versus Fusion integration

Integratie EBS versus Fusion

Traditionally a lot of customization was developed around Oracle Applications, although the idea has Always been ofcourse to use flexfields and later form personalization wherever possible.
However, there was Always the need for additional logic using PL/SQL packages and in some cases database triggers (try to avoid it!), additional tables to store data which would be exposed through custom build forms using Oracle Forms and all kinds of integration to external systems.

In this blog I like to elaborate on custom inbound and outbound integration with eBS and also how this would work in Fusion.
Note, my purpose is not to describe ALL integration option that Oracle may offer through XML Gateway,EDI, Standard webservices BPEL, etc, but I aim for the traditional eBS custom developer and functional consultant who like to get an overview idea on the differences between integration of eBS and Fusion.
Inbound integration eBS

For inbound integration we use the schema as shown below.

The external application delivers a file, for example in CSV, which we load with SQL Loader in a custom (maatwerk) table. Then we enrich, validate using a PL/SQL package and fill the Open Interface or call an API directly. And if you are really in to it, you also directly call the import program in your PL/SQL package and use a wait for request to see the result. The import will eventutally pick up the Open Interface records and fill the actual Oracle tables.

Around this you can build a host script that ties the components together and call them sequentially or you use a request set (Load, Validate, Import).
In newer releases webservice options were added and through Integrated SOA Gateway you could easily build your own APIs in PL/SQL and expose this as a webservice. In R11 you could also do this without SOA Gateway, but you simply exposed your package as an URL.
Ofcourse you have much more advanced integration techniques using middleware, messaging services, etc.
So the image of above can be adapted to

Our SQL Loader program, the host script run on the application server of our eBS installation and the custom table, the validation program and the SOAP services are running on the database server.
All inside eBS.
So in a cloud or Fusion solution this will not work anymore ..

Outbound integration eBS

For outbound integration we use the schema as depicted below. Data can be made available through views, which can be queried using database links or we export the data through either custom programs (PL/SQL) or BI Publisher to support multiple export formats like XML, Excel, CSV, etc.

Here also the custom components (views, export programs, BI Publisher reports, your own custom webservices) are in eBS.
So what changes with Fusion?
Now we have Fusion this is no longer possible. We cannot develop in the cloud/fusion application itself (fusion on premise is slightly different of course). We can integrate with it of course. So our schema would look more like this ..

Inbound integration Fusion

From the external application you can either directly call webservices or you can use File Based Data Import and upload a program to UCM (Universal Content Manager) so it can be imported into Fusion.
In OER (Oracle Enterprise Repository you can download excel templates where you can place data and generate a zip file which can be uploaded directly to UCM or through a webservice.

For a detailed description download the document Using External Data Integration Services for Oracle Cloud in OER (

Problem with this approach is that all pre-processing has to take place between the extracted data from the client and what Oracle accepts. This is the proces we usually build using SQL Loader, custom tables and custom enrichment and validation.
You can still follow this scenario, but now it has to be done outside the Fusion application. So either in an external application or in an environment in between. This could be a PAAS environment. For
example use the Java Cloud or SOA Cloud to do your enrichment and transformation in BPEL.
So you would need the Database Cloud to define your custom tables where you load the data from the external system (like our staging tables), build validation logic in PL/SQL and add a BPEL process to fetch additional information from Fusion Cloud to do your enrichment. You may use BI Publisher reports to do your own custom queries and fetch internal IDs or do validation in your process.
Then when you have finished validation and enrichment, you create the files to be uploaded and send them to Fusion using your BPEL process and place them in UCM. Now submit the import process.

From a BPEL like environment you can also directly call the FinancialUtilService webservice (see section Flow Automation Using Web Service in above mentioned document, operation UploadFiletoUCM).

Outbound integration Fusion

Then finally outbound integration with Fusion. In a lot of custom solution we fetch data from eBS and use that either in processing or custom screens and processing. That's not possible this way, but we can fetch the information from Fusion using existing webservices or custom written queries in BI Publisher or OTBI.

Running a BI Publisher report can be invoked through the FinancialUtilService webservice (operation submitEssJobRequest), after which the output is available in UCM and can be fetched with operation getEssJobStatus to see the status of the request and downloadESSJobExecutionDetails to fetch the output as a zipfile.

This method of downloading can also be used to fetch data and store it locally for further processing. See for a more detailed description Hakan Biroglu's blog on this subject (