Development

The latest and greatest flavor of the Oracle Database in the cloud is Oracle Autonomous Transaction Processing (ATP). One of the Autonomous DB flavors that, it is optimized for OLTP (On-Line Transaction Processing) Applications - the type that you and I usually work on.

One new feature in the world of ATP is the way that you connect to the DB, connection is done leveraging wallets to make sure that your data is secured even though you are connecting over public internet. Here are instructions on how to get such a wallet file for your instance of ATP.

We introduced an enhancement to the latest version of Developer Cloud Service that allows you to connect to ATP from your CI/CD automation jobs. This can help you automate CI/CD for SQL scripts that you need to run against that DB.

As I mentioned in past blogs, DevCS has built in support for the SQLcl utility, allowing you to run SQL scripts against an Oracle database as part of your CI/CD chain. If you want to connect the SQLcl utility in DevCS to ATP, it will need to have access to your wallet.zip file. You can achieve this by uploading the file into your git repository.

Then in your SQLcl configuration you'll specify the user/pass like before, and then point the field titled Credentials File to the wallet.zip file location. (in the screenshot below, the zip file is at the top of the git repo connected to the build - so there is no need to add a path). In the next field, titled connection string, you specify the name used in the wallet's tnsnames.ora file to connect to the DB.

Now you can continue as usual and provide inline SQL or point to SQL files from your git repository.

 

This is a quick blog to show two techniques for adding calculated fields to an Oracle Visual Builder application.

Both techniques do the calculation on the client side (in the browser). Keep in mind that you might want to consider doing the calculation on the back-end of your application and get the calculated value delivered directly to your client - in some cases this results in better performance. But sometimes you don't have access to modify the backend, or you can't do calculations there, so here we go:

1. For simple calculation you can just use the value property of a field to do the calculation for you.

For example if you need to know the yearly salary you can take the value in a field and just add *12 to it.

You can also use this to calculate values from multiple fields for example [[$current.data.firstName + " " +$current.data.lastName]] - will get you a field with the full name.

2. For more complex calculation you might need to write some logic to arrive at your calculated value, for example if you have multiple if/then conditions. To do that you can create a client side JavaScript function in your page's JS section. Then you refer to the function from your UI component's value attribute using something like {{$functions.myFunc($current.data.salary)}}

As you'll see in the demo, if you switch to the code view of your application the code editor in Oracle VB will give you code insight into the functions you have for your page, helping you eliminate coding errors.

I posted in the past a basic introduction to filtering lists in Visual Builder - showing how to use the filterCriterion to filter the records shown in a ServiceDataProvider. Since I recorded this video, a few things changed, and I also saw several customers asking how can they use more complex conditions that involve more than one filter.

In the video below I show how to define a basic filter with the latest versions (note that in VB 18.4.1 you no longer need to surround value with quotes ""), and then I show how to create a more complex condition that involves two filter criteria and set them to work with either an or or an and operator.

When you are using business components in Visual Builder, the filterCriterion is translated into a "q" parameter that is passed to the GET method (more about this q query parameter here). If you find that you are not getting the records you are expecting, check out the browser's Network tab to see what query parameter was passed in your call to the REST service (intro to this debugging technique here).

As you'll see the filterCriterion contains an array of "criteria" so you can specify several of them. In the video I'm using an approach that Brian Fry showed me that gives you a more declarative way to populate the array dragging and dropping multiple "criteria type" variables into the same array.

Note however that the important thing is what is actually being populated in the json file that defines the action. You should go into this view and verify that you have the right structure there. You can also directly manipulate that source to achieve the filter you need.

As you'll see in the video there are some cases where the design time for this filterCriterion adds an entry into the JSON that might not match what you want (we are tracking this issue). So as mentioned - if things don't work as expected direct manipulation of the JSON might be required. 

At Oracle OpenWorld this year I did a session about the future of Oracle ADF, and one of the demos I did there was showing the powerful combination of Oracle ADF backend with a new Oracle JET UI layer and how Oracle Visual Builder makes this integration very simple.

While we have many happy Oracle ADF customers, we do hear from some of them about new UI requirements that might justify thinking about adopting a new UI architecture for some modules. These type of requirements align with an industry trend towards adopting a more client centric UI architecture that leverages the power of JavaScript on the client. While ADF (which is more of a server centric architecture) does let you leverage JavaScript on the client and provides hook points for that in ADF Faces, some customers prefer a more "puristic" approach for new user interfaces that they are planning to build. Oracle's solution for such a UI architecture is based on Oracle JET - an open source set of libraries we developed and share with the community at http://oraclejet.org.

Oracle Visual Builder provides developers with a simpler approach to building Oracle JET based UIs - for both web and on-device mobile applications. Focusing on a visual UI design approach it drastically reduce the amount of manual coding you need to do to create JET based UIs. 

UIs that you build in Visual Builder connect at the back to REST services, and this is where you can leverage Oracle ADF. In version 12 of JDeveloper we introduced the ability to publish ADF Business Components as REST services through a simple wizard. Note that out-of-the-box you get a very powerful set of services that support things like query by example, pagination, sorting and more. If you haven't explored this functionality already, check out the videos showing how to do it here, and this video covering cloud hosting these services.

Once you have this ADF based REST services layer - you'll be glad to hear that in Visual Builder we have specific support to simplify consuming these REST services. Specifically - we understand the meta-data descriptions that these REST services provide and then are able to create services and endpoints mapping for you.

You leverage our "Service from specification" dialog to add your ADF services to your Visual Builder app - and from that point on, it's quite simple to build new JET UIs accessing the data.

In the video below I show how simple it is to build a JET-based on-device mobile app that leverage a set of REST services that were created from Oracle JDeveloper 12. Check it out:

Snippets are a feature of Oracle Developer Cloud Service that gives you a place to store reusable pieces of code as part of your project. These are the type of code snippets that you don't want as part of your core Git repository, but that you still find useful. Snippets can be your own private ones or shared among your team.

One nice usage for code snippets is the ability to quickly include them in a wiki page. This allows you, for example, to create a template of a wiki page and then quickly apply it to a new page that you creates. Using the correct markup for your wiki page format (confluence in the example in the video), you can create a collection of templates. For example, a template for a feature page, a template for a meeting minutes page, etc.. then your team members can quickly create pages that conforms to these templates.

In the video below I show you how to leverage this combination step by step.

Off Canvas layout is a common UI pattern for modern applications, especially on mobile devices. The concept is aimed at saving space on your page, allowing you to pop out a "drawer" of additional information. This helps reduce clatter on the main page but still provide access to important data when needed without leaving the page context. You can see an example of the runtime behavior at the top of this post. 

Oracle JET provides this type of "off-canvas" behavior as a built in component, and they have a demo of it working as part of the cookbook here.

In the video below I show you how to add this to a Visual Builder application. As always - you can mostly just copy and paste code from the JET cookbook, but you need to handle some of the importing of resources a little different, and use the Visual Builder approach for adding your JavaScript function.

The code used in the video is:

Page source:

JavaScript Function in the page:

define(['ojs/ojcore'], function(oj) { 'use strict'; var PageModule = function PageModule() {}; PageModule.prototype.showSide = function() { var offcanvas = { "selector": "#startDrawer", "content": "#mainContent", "edge": "start", "displayMode": "push", "size": "200px" }; oj.OffcanvasUtils.open(offcanvas); } return PageModule; });

and in your page Json file add this import:

"oj-offCanvas": { "path": "ojs/ojoffcanvas" }

One of the strong features of Visual Builder Cloud Service is the ability to consume any REST service very easily. I have a video that shows you how to work with REST services in a completely declarative way, but that video doesn't show you what happens behind the scenes when you work with the quick starts. In addition, that video shows using the GET methods and several threads on our community's discussion forum asked for help working with other operations of REST.

The demo video aims to give you a better insight into working with REST operations showing how to:

• Add service endpoints for various REST operations
• Create a GET form manually for retrieving single records
• Create a POST form manually
  • Create type for the request and response parameters
  • Create variables based on the types
  • Call the POST operation passing a variable as body
• Get the returned values from the POST to show in a page or notifications

A couple of notes:

In the video I use the free REST testing platform at https://jsonplaceholder.typicode.com

While I do everything here manually - you should be able to use the quick starts for creating a "create" form and map them to the post operation - as long as you marked the specific entry as a "create" entry like I did in the demo.

If the concepts above such as types, variables, action chains are new to you - I would highly recommend watching this video on the VBCS Architecture and Building Blocks, it will help you better understand what VBCS is all about.

 

 

 

 

The business objects that you create in Visual Builder Cloud Service (VBCS) are quite powerful. Not only can they store data, manage relationships, and give you a rich REST interface for interacting with them, they can also execute dedicated business logic that deals with the data.

If you click on the Business Rules section of a business object you'll see that you can create:

• Triggers - allow you to react to data events such as insert, update, and delete on records.
• Object and field Validators - allowing you to make sure that data at the field or record level is correct.
• Object Functions - A way to define "service methods" that encapsulate logic related to a business object. These functions can be invoked from various points in your application, and also from outside your app.

To code logic in any of these location you will leverage the Groovy language.

I wanted to show the power of some of the functionality you can achieve with these hook points for logic. The demo scenario below is based on a requirement we got from a customer to be able to send an email with the details of all the children records that belong to a specific master record. Imagine a scenario where we have travel requests associated with specific airlines. When we go to delete an airline we want to send an email that will notify someoe about the travel requests that are going to be impacted by this change.

To achieve this I used an accessor - an object that helps you traverse relationships between the two objects - to loop over the records and collect them.

In the video below you'll see a couple of important points:

• Business object relationship and how to locate the name of an accessor
• Using a Trigger Event to send an email
• Passing an object function as a parameter to an email template
• Coding groovy in a business object

For those interested the specific Groovy code I used is:

def children = TravelRequests; // Accessor name to child collection def ret_val = "List of travel requests "; if (!children.hasNext()) { return "no impact"; } while (children.hasNext()) { def emprec = children.next(); def name = emprec.name; ret_val=ret_val+" " +name; } return ret_val;

 

By the way - if, like me, you come from a background of using Oracle ADF Business Components you might find many of the things we did here quite familiar. That's because we are leveraging Oracle ADF Business Components in this layer of Visual Builder Cloud Service. So looking up old Groovy tutorial and blogs about ADF BC might prove to be useful here too :-)

 

 

EnterpriseDB Postgres is the only open source database in Gartner's Magic Quadrant and has virtually full feature parity with Oracle. Yet, very few of us know about it or use it. Let's change this.

Attend the webinar by two of the best people in EnterpriseDB world and learn how EDB Postgres can help you.

Register at this link.


About the Webinar:

• - The EDB Postgres Platform supports Oracle’s PL/SQL and comes with enterprise-level management and diagnostic tools;
• 
  
• - EDB’s Oracle migration expertise and technology coupled with Destiny’s application migration expertise helps organizations migrate to Postgres;
• 
  
• - The EDB Postgres Platform significantly reduces an organizations TCO costs while still providing enterprise-level capabilities for business needs and applications.
• 
  

While running our final tests of APEX Office Print (AOP) 18.1 we hit "ORA-20000: Issue calling Main AOP Service (REST call: ): ORA-20001: You have exceeded the maximum number of web service requests per workspace."


When you login into the Internal Workspace and navigate to a workspace, there's a setting for  Maximum Web Service Requests. The default value is 1000 requests per 24h (rolling window).

If you know that AOP has next to hundreds of server tests, also around 500 automated tests through APEX, we hit this limit after the second full run. After setting the value to 20000, we are able to continue our final testing :)

I guess the chances are small you will hit the limit in a normal APEX app, but if you do, it's easy to fix by setting a higher value for your workspace.

In recent releases Developer Cloud Service (DevCS) has expanded to allow you to manage the full life cycle of Infrastructure in addition to software. One area where we made things simpler is in the management of CI/CD for Docker containers.

In this blog entry I'll take you through the basics of setting up a CI/CD chain that publishes Docker containers into the Oracle Cloud Infrastructure Registry (OCIR) - Oracle's cloud hosted docker registry. If you need a little tutorial on getting started with OCIR and docker using command lines - you can use this one.

Here is a demo video showing you how to leverage DevCS to automate the publishing process of Docker images and hook it into the Git repository in DevCS:

A few notes to help you replicate the steps I take in the video:

You will need to configure a DevCS build server that will have the docker software on it to run your builds. You do this from the "organization" menu under your user name (or get your org admin to do this for you). If you need tips, Abhinav shows you how to do it in this blog entry.

In order to work with OCIR from DevCS you'll need to have an Oracle Compute user created and have an auth token generated for it - make sure you have this token available for you as you'll need it when working from DevCS. (Note that this is separate from the password the user uses to login into the Oracle compute dashboard).

Now that you have a user, it's time to start your DevCS work. The first thing you might want to do is upload the code for a docker image into your DevCS git repository. In the video below you'll see that I'm using a very simple application and that my docker file is part of the 3 files I upload into the git repository in DevCS. (need some sample code pick up this docker getting started tutorial).

Once your code is in DevCS - the next step is to create a build jobs that pushes your code into the OCI Registry. Our job has three steps that leverage the following docker commands:

• Login - you'll need to use your tenantName/user as the username and the auth token as the password. Your registry URL will depend on the center you are in iad - Ashburn, phx -Phoenix etc. In my case it was iad.ocir.io.
• Build - you'll want to specify a complete image name again using your tenant/user/imageName. Also make sure to indicate where your docker file is (or provide a docker file in that step).
• Push - here everything should already be filled out for you and ready to go.

Here is how the completed job looks like:

Now run your build - and it will build your image and if it succeed it will push it to the registry. Monitor the build's log in case you run into errors.

You can hook up the docker registry section of DevCS to point to the OCIR registry - and then you'll be able to see all the images you pushed there. Again provide your tenant/user and auth token as the login info to the repository.

Have fun automating your docker publishing on the Oracle Cloud!

 

Sometimes the Oracle APEX documentation announces some packages will become deprecated in a release. It's not that those packages are suddenly gone, but you should not use them anymore. Your code will run fine still, but in the future, APEX might take it out completely, so it's best to replace them with the new package.

One of those packages announced in Oracle APEX 5.1 that are deprecated, and which I used a lot, was apex_util.string_to_table.

For example, in APEX Office Print (AOP) we read the session state of page items and we have some code like this:

declare
  l_string          varchar2(4000) := 'P1_X:P1_Y';
  l_page_items_arr  apex_application_global.vc_arr2;
begin  
  l_page_items_arr := apex_util.string_to_table(p_string => l_string, p_separator => ':');
  for i in 1..l_page_items_arr.count
  loop
    sys.htp.p(l_page_items_arr(i)||':'||apex_util.get_session_state(l_page_items_arr(i)));
  end loop;
end;    

As the function is deprecated and APEX is already on release 18.1, it's good to start replacing those calls. The new function you can use is apex_string.split.

The above code becomes then:

declare
  l_string          varchar2(4000) := 'P1_X:P1_Y';
  l_page_items_arr  apex_t_varchar2;
begin  
  l_page_items_arr := apex_string.split(p_str => l_string, p_sep => ':');
  for i in 1..l_page_items_arr.count
  loop
    sys.htp.p(l_page_items_arr(i)||':'||apex_util.get_session_state(l_page_items_arr(i)));
  end loop;
end;

Depending on your application, you might need to be careful. For example with AOP, we support customers with versions of Oracle APEX 5.0, 5.1 and 18.1. We can't really force customers to move to higher APEX versions, so the way we solve it is by using conditional compilation of our code. If we see you are on APEX 5.1 or above we will use apex_string.split if not, and you are still on an earlier version, we will use apex_util.string_to_table.

Here's an example of what the code with conditional compilation looks like:

  $if wwv_flow_api.c_current >= 20160824
  $then
    l_page_items_arr := apex_string.split(p_str=>l_string, p_sep=>':');
  $else
    l_page_items_arr := apex_util.string_to_table(p_string=>l_string, p_separator=>':');
  $end

Note the conditional compilation you also need to do on the variable if they are different, or you can choose to conditional compile on the entire function.

To conclude, I recommend with every new release of Oracle APEX to look for deprecated components and search for those and make notes to change those when needed.

Downtime during upgrades can cause a lot of headaches to E-Business Suite customers. No more!

Attend the free webinar by Oracle ACE Associate and Chicago OUG President Alfredo Abate and learn how you can patch your EBS installations without any downtime.

Register at this link.