Development

Scaling Oracle using NVMe flash

Gerger Consulting - Tue, 2017-11-21 07:37
Attend the free webinar by storage expert Venky Nagapudi and learn how to improve the performance of your Oracle Database using new storage technologies such as NVMe flash. 

About the Webinar
Growth in users and data put an ever-increasing strain on transactional and analytics platforms. With many options available to scale platforms, what are the considerations and what are others choosing? Vexata’s VP of Product Management, Venky Nagapudi covers how the latest in storage side technologies, like NVMe flash, can deliver both vast improvements in performance as well as drive down costs and complexity of platforms. He will also cover key use cases where storage-side solutions delivered amazing results for Vexata’s customers.
In this webinar, you will:
  • Hear real-world performance scaling use cases.
  • Review the pros & cons of common scaling options.
  • See specific results of choosing a storage-side solution.


About the Presenter


Venky Nagapudi has 20 years experience in engineering and product management in the storage, networking and computer industries. Venky led product management at EMC and Applied Microsystems. Venky also held engineering leadership roles at Intel, Brocade and holds 10 patents with an MBA from Haas business school at UC Berkeley, an MSEE from North Carolina State University, and a BSEE from IIT Madras.

Sign up now.

Categories: Development

Date Calculations and Queries with Oracle Visual Builder Cloud Service

Shay Shmeltzer - Fri, 2017-11-17 12:10

It's very easy to define a field in a custom object in Oracle Visual Builder Cloud Service to store a date, but when it comes to doing calculations and queries based on this date you'll find that you need to resort to a little bit of JavaScript calculations.

Here are a couple of useful things to know if you are trying to do that.

Calculating Age (or time passed from a date in years)

Let's assume you are storing information about employees and one of the pieces of information you have is their date of birth - the Birthday field in the image below.

How do you show their actual age in years on a page?

You can define a calculated field in your business object - and have VBCS use the "calculate value with formula" as the source for this field.

Your formula would be something like:

(new Date() -new Date($birthdate) )/ (60*60*24*1000*365)

You are calculating the difference between today's date and the birthday field and since the answer is in milliseconds you convert it to years by dividing by the number of milliseconds in a year.

Note that as you type in your formula the dialog shows you the results of the formula below the formula field - quite useful to verify that you are doing it right.

Now your page can show the age of your employees:

Filtering Based on Date

What if you wanted to limit the records shown in the table above to only show employees of a specific age?

The tricky part is that you'll need to do the calculation against the birthday field and not against the age field. The age field is not actually stored anywhere - rather it is calculated on the fly.

Let's take the table shown above and assume we want to limit it to show employees who are younger than 9 years. To do that we'll add a query condition to our table to check that the birthday is larger than the date of (today - 9 years).

The calculation of the date 9 years ago will be with a formula like this:

new Date($current_date-9*365*24*60*60*1000)

Now your table only shows older employees.

Want to have a more dynamic way to define the query criteria - you can adopt the approach I showed in the blog about Creating Custom Search/Query Pages with Visual Builder along with the techniques shown here.

One last note - since not every year has 365 days - the calculation for milliseconds conversion is not completely accurate - but it is quite close.

Categories: Development

Conditional Navigation based on Queries in Oracle Visual Builder Cloud Service

Shay Shmeltzer - Wed, 2017-11-15 13:21

A couple of threads on the Oracle Visual Builder Cloud Service forum asked about writing code in buttons in VBCS that compares values entered in a page to data in business objects and perform conditional navigation based on the values. In a past blog I showed the code needed for querying VBCS objects from the UI, but another sample never hurts, so here is another demo...

For this demo I'm going to show how to do it in a login flow - assuming you have a business object that keeps usernames and passwords, and you want to develop a page where a user types a user/pass combination and you need to verify that this is indeed a valid combination that exist in the business object.

(In reality, if you want to do user authentication in VBCS - you should use the built in security frameworks and not code it this way. I'm just using this as an example.)

Here is a quick video of the working app - with pointers to the components detailed below.

The first thing you'll do is create the business object that hosts the user/pass combination - note that in the video since "user" is a reserved word - the ID for the field is actually "user_" - which is what we'll use in our code later on.

 

Next you'll want to create a new page where people can insert a user/pass combination - to do that create a new page of type "Create" - this page will require you to associate it with a business object, so create a new business object. We won't actually keep data in this new business object. In the video and the code - this business object is called "query".

Now design your page and add the user and pass fields - creating parallel fields in the query business object (quser and qpass in the video). You can then remove the "Save" button that won't be use, and instead add a "validate" button.

For this new button we'll define a new custom action that will contain custom JavaScript code. Custom code should return either a success state - using resolve(); - or failure - using reject();

Based on the success or failure you can define the next action in the flow - in our case we are showing either a success or error message:

success flow

Now lets look at the custom JavaScript code:

require(['operation/js/api/Conditions', 'operation/js/api/Operator'], function (Conditions, Operator) { var eo = Abcs.Entities().findById('Users'); var passid = eo.getProperty('pass'); var userid = eo.getProperty('user_'); var condition = Conditions.AND( Conditions.SIMPLE(passid, Operator.EQUALS,$QueryEntityDetailArchetypeRecord.getValue('qpass') ), Conditions.SIMPLE(userid, Operator.EQUALS, $QueryEntityDetailArchetypeRecord.getValue('quser')) ); var operation = Abcs.Operations().read( { entity : eo, condition : condition }); operation.perform().then(function (operationResult) { if (operationResult.isSuccess()) { operationResult.getData().forEach(function (oneRecord) { resolve("ok"); }); } reject("none"); } ). catch (function (operationResult) { if (operationResult.isFailure()) { // Insert code you want to perform if fetching of records failed alert('didnt worked'); reject("error"); } }); });

Explaining the code:

  • Lines 2-4 - getting the pointers to the business object and the fields in it using their field id.
  • Lines 5-8 - defining a condition with AND - referencing the values of the fields on the page
  • Lins 9-11 - defining the operation to read data with the condition from the business object
  • Line 12 - executing the read operation
  • Line 14-18 - checking if a record has been returned and if it has then we are ok to return success - there was a user/pass combination matching the condition.
  • Line 19 - otherwise we return with a failure.

One recommendation, while coding JavaScript - use a good code editor that will help highlight open/close brackets matches - it would save you a lot of time.

For more on the VBCS JavaScript API that you can use for accessing business components see the doc.

Categories: Development

Meet me in Australia and New Zealand at the OTN Days 2017

Dimitri Gielis - Tue, 2017-11-14 08:32
Tonight I'll start my trip from Belgium to Australia and New Zealand. Although we have a company in New Zealand and Australia, which Lino is managing, I've never been there myself. It has always been my dream to visit the other side of the earth, so I look forward to it :)

I'll present on how I build Oracle APEX apps today (and in the future) and how to make them available for others (cloud and others).

My schedule of the OTN Days 2017 (APAC Tour) looks like this:
If you are in one of those places, I would love to meet you and hear how you use Oracle APEX.
And I'm always up for showing you a live demo of APEX Office Print, you'll see our upcoming AOP 3.2 version as first! Just grab me by my arm and ask :)

In Perth there will also be a Q&A slot - so any Oracle APEX question can be asked there.
Categories: Development

Karamozov

Greg Pavlik - Fri, 2017-11-10 12:02
"Brothers, have no fear of men's sin. Love a man even in his sin, for that is the semblance of Divine Love and is the highest love on earth. Love all God's creation, the whole and every grain of sand in it. Love every leaf, every ray of God's light. Love the animals, love the plants, love everything. If you love everything, you will perceive the divine mystery in things. Once you perceive it, you will begin to comprehend it better every day. And you will come at last to love the whole world with an all-embracing love. Love the animals: God has given them the rudiments of thought and joy untroubled. Do not trouble it, don't harass them, don't deprive them of their happiness, don't work against God's intent. Man, do not pride yourself on superiority to the animals; they are without sin, and you, with your greatness, defile the earth by your appearance on it, and leave the traces of your foulness after you -- alas, it is true of almost every one of us! Love children especially, for they too are sinless like the angels; they live to soften and purify our hearts and, as it were, to guide us. Woe to him who offends a child! Father Anfim taught me to love children. The kind, silent man used often on our wanderings to spend the farthings given us on sweets and cakes for the children. He could not pass by a child without emotion. That's the nature of the man.

At some thoughts one stands perplexed, especially at the sight of men's sin, and wonders whether one should use force or humble love. Always decide to use humble love. If you resolve on that once for all, you may subdue the whole world. Loving humility is marvellously strong, the strongest of all things, and there is nothing else like it....

Brothers, love is a teacher; but one must know how to acquire it, for it is hard to acquire, it is dearly bought, it is won slowly by long labour. For we must love not only occasionally, for a moment, but for ever. Everyone can love occasionally, even the wicked can.

My brother asked the birds to forgive him; that sounds senseless, but it is right; for all is like an ocean, all is flowing and blending; a touch in one place sets up movement at the other end of the earth. It may be senseless to beg forgiveness of the birds, but birds would be happier at your side -- a little happier, anyway -- and children and all animals, if you were nobler than you are now. It's all like an ocean, I tell you. Then you would pray to the birds too, consumed by an all-embracing love, in a sort of transport, and pray that they too will forgive you your sin. Treasure this ecstasy, however senseless it may seem to men."

Introduction to Oracle Developer Cloud Service Issue Tracking REST Interfaces

Shay Shmeltzer - Mon, 2017-11-06 13:38

The task tracking system in Oracle Developer Cloud Service (DevCS) helps your team manage your development priorities and process. DevCS offers a simple web interface for working with the system. However, in some cases you might want to build your own interfaces to interact with the issues. For example, you might want to build a system for end-users to report bugs in your app and you don't want to give them direct access to the DevCS web insterface. In the August 17 update of DevCS  we introduced a set of REST services that will let you build a custom interface that will interact with our issues repository.

The official documentation for the DevCS REST services is here.

I wanted to share some tips to help you get this going in your project. The results are in this short video demo, and the details are below.

Figuring Out The End Points

The documentation gives you the basic end-points you should be calling, but it took me a little bit of time to figure out the full URL to the end point. Turns out the URL is composed in the following way:

https://server/org-id/rest/org-id+project-id/issues/v2/issues

The first parts (server/org-id) are quite easy to get - just copy it from the URL of your project when you look at it in your browser.

The org-id+project-id part is something you can get by looking at the details of your maven repository URL - see the image below - what you are looking for is the part before the /maven/ at the end:

Note that in some projects this will also include a numeric value appended to the project name. Something like developer-oracletemplates_db-oss-devops_20266.

In the video sample below the result URL for the REST that returns the list of issues currently in the system ended up being:

https://myserver/developer-oracletemplates/rest/developer-oracletemplates_adf1221/issues/v2/issues

Creating New Issues

One of the useful services is the /issues/v2/issues/create-form service. It returns a json file that you can edit to specify information about a new task that you want to create.

Note that the file start with : {"createIssue":{"links":.... Before you use the file to insert a new issue, you'll need to remove the  {"createIssue": at the start and the corresponding } at the end of the file. Only then can you use it to submit the POST operation to create an issue.

In the video I used the following command to create the issue in the DevCS:

curl -X POST -u shay@oracle.com https://myserver/developer-oracletemplates/rest/developer-oracletemplates_adf1221/issues/v2/issues/ -d@issue.json -H 'Content-type:application/json'

(the -d allows you to specify the name of the file with the new issue, and the -H specifies the content format).

Now that you have access to the information you can create new systems on top of it using your favorite development tool. At the end of the video you can see a simple issue system I built with Oracle Visual Builder Cloud Service - more on that in a future blog entry.

 

Categories: Development

Exporting and Importing Data from Visual Builder Cloud Service - with REST Calls

Shay Shmeltzer - Thu, 2017-11-02 16:37

Visual Builder Cloud Service (VBCS) makes it very easy to create custom objects to store your data. A frequent request we get is for a way to load and export data from these business objects. As John blogged, we added a feature to support doing this through the command line - John's blog shows you the basic options for the command line.

I recently needed to do this for a customer, and thought I'll share some tips that helped me get the functionality working properly - in case others need some help skipping bumps in the road.

Here is a demo showing both import and export and how to get them to work.

Exporting Data

Export is quite simple - you use a GET operation on a REST service, the command line for calling this using curl will look like this:

curl -u user:password https://yourserver/design/ExpImp/1.0/resources/datamgr/export > exp.zip

The result is a streaming of a zip file, so I just added a > exp.zip file to the command's end. The zip file will contain CSV files for each object in your application.

Don't forget to replace the bold things with your values for username and password, your VBCS server name and the name of the app you are using (ExpImp in my case).

Importing Data

Having the exported CSV file makes it easy to build a CSV file for upload - in the demo I just replaced and added values in that file. Next you'll use a similar curl command to call a POST method. It will look like this:

curl -X POST -u user:password https://yourserver/design/ExpImp/1.0/resources/datamgr/import/Employee?filename=Employee.csv -H "Origin:https://yourserver" -H "Content-Type:text/csv" -T Employee.csv -v

A few things to note.

You need to specify which object you want to import into (Employee after the /import/ in the command above), and you also need to provide a filename parameter that tell VBCS which file to import.

In the current release you need to work around a CORS security limitation - this is why we are adding a header (with the -H option) that indicate that we are sending this from the same server as the one we are running on. In an upcoming version this won't be needed.

We use the -T option to attach the csv file to our call.

Note that you should enable the "Enable basic authentication for business object REST APIs" security option for the application (Under Application Settings->Security). 

Using Import in Production Apps

In the samples above we imported and exported into an application that is still being developed - this is why we used the /design/ in our REST path.

If you want to execute things on an application that you published then replace the /design/ with /deployment/ 

One special note about live applications, before you import data into them you'll need to lock them. You can do this from the home page of VBCS and the drop down menu on the application.

 

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 14.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff} span.s1 {font-variant-ligatures: no-common-ligatures}
Categories: Development

Using Flyway to Manage Oracle DB Versions in the Cloud

Shay Shmeltzer - Thu, 2017-10-19 13:11

This is another entry in my series about managing database scripts/schema as part of agile development. In the past I showed how to use simple SQL and Liquibase to manage schema creation/population scripts, and today I'll show you how to use Flyway.

Flyway log

Flyway is a free open source solution for managing "database migrations" - or basically helping you keep multiple database in synch by tracking and applying changes to the schema structure and data.

Flyway uses simple SQL scripts - which means you can use DB specific syntax - and tracks their execution in the database through a table it maintains. It is very easy to get started with and only has 6 commands that you need to be familiar with.

The main command is "migrate" which will check your database status, and then run all the newer scripts that have yet to be run on that instance.

Flyway uses a directory structure that contains a sql folder where you'll host all your SQL scripts. It uses a naming convention (that can be adjusted) where you start the file name with a Version number (V1, V1.1, V2.1) and then two "_" followed by a description - so something like V1__Create_Emp_Table - will show up as "Create Emp Table" when you issue the "info" command to find out what is the status of a database and which scripts have already run. By the way, the info command will also show you which new scripts are pending to be run on a specific database instance.

In the video below I show how to configure and use Flyway, and how to integrate it into an automatic DevOps process leveraging Oracle Developer Cloud Service. (including task tracking, Git version management of the source, and build execution of the scripts).

Flyway can integrate with various build framework (ant, maven, gradle etc), but since many DB folks are not familiar with those, I chose to use simple command lines in my demo to invoke Flyway. On my laptop and local MySQL DB I just used the Flyway command line utility. However Flyway is not installed by default in the DevCS servers, so I did a little trick:

Flyway is a Java program, so into my DevCS Git repository I uploaded the Flyway directory along with needed jars for flyway and the JDBC driver. Then I looked at the script for invoking the command line and found out the Java command they used and copied it into a regular shell command in my build:

java -cp lib/flyway-commandline-4.2.0.jar:lib/flyway-core-4.2.0.jar org.flywaydb.commandline.Main info -user=fw -password=$Password -url=jdbc:oracle:thin:@ipaddress:1521/servicename

The $Password refers to a build parameter which is encrypted.

The directory structure and files in my Git are shown in this image:

directory structure

 

Categories: Development

Your DBA Career in the Age of Oracle Cloud

Gerger Consulting - Wed, 2017-10-18 11:48
Attend the free webinar by Oracle ACE Director Craig Shallahamer and learn how the Oracle Cloud and the Oracle 18c autonomous database changes your role as an Oracle DBA.
About the Webinar
The cloud is a change that all Oracle DBAs must face. The cloud is here stay, and that means Oracle DBAs need to adapt or get out of the game. It makes no difference if you are a new Oracle DBA or retiring in five years, before us is one of the most significant changes you will ever face.

In this webinar, you'll learn what has happened, what is happening, what you can expect, and what you can do today to ensure you are positioned to thrive in a cloud world full of surprising and exciting opportunities.

Register at this link.
Categories: Development

Introduction to Liquibase and Managing Your Database Source Code

Shay Shmeltzer - Mon, 2017-10-16 10:35

In previous posts I showed how you can manage SQL scripts lifecycle with the help of Oracle Developer Cloud Service (DevCS) as part of an overall Oracle DB DevOps solution. I wanted to add one more utility that might act as an alternative or addition to the SQL script managing - Liquibase.

Liquibase logo

Liquibase is an open source solution for managing revisions of your databse schema scripts. It works across various types of databases, and supports various file formats for defining the DB structure. The feature that is probably most attractive in Liquibase is its ability to roll changes back and forward from a specific point - saving you from the need to know what was the last change/script you ran on a specific DB instance.

Liquibase uses scripts - referred to as "changesets" - to manage the changes you do to your DB. The changesets files can be in various formats including XML, JSON, YAML, and SQL. In the examples below I'm using the XML format.

As you continue to change an enhance your DB structure through the development lifecycle you'll add more changesets. A master file lists all the changeset files (or the directories where they are). In parallel Liquibase tracks in your database which changesets have already run. 

When you issue a liquibase update command, liquibase looks at the current state of your DB, and identifies which changes have already happened. Then it run the rest of the changes - getting you to the latest revision of the structure you are defining.

By integrating Liquibase into your overall code version management system and continuous integration platform you can synch up your database versions with your app version. In my case this would of course mean integration with Oracle Developer Cloud Service (DevCS) - which you get for free with the Oracle Database Cloud Service. In the video below I show a flow that covers:

  • Tracking my DBA tasks in the issue system
  • Modifying a local MySQL DB with Liquibase (doing forward and backward rolls)
  • Adding a change set defining a new table
  • Committing to Git
  • Automatic build implementing the changes in Oracle Database Cloud Service
  • Automatic testing with UT/PLSQL

Here is a quick 10 minute demo:

For those who want to try and replicate this, here are some resources:

A changeset that creates a "department" table with three columns:

A changeset that creates PL/SQL function, package and procedure. Note that in line 3 the dbms="oracle" means this script will only run when we are connected to an Oracle DB:

create or replace function betwnstr( a_string varchar2, a_start_pos integer, a_end_pos integer ) return varchar2 is begin return substr( a_string, a_start_pos, a_end_pos - a_start_pos+1 ); end; create or replace package test_betwnstr as -- %suite(Between string function) -- %test(Returns substring from start position to end position) procedure basic_usage; end; create or replace package body test_betwnstr as procedure basic_usage is begin ut.expect( betwnstr( '1234567', 2, 5 ) ).to_equal('2345'); end; end; A changeset that adds a record to a table. Line 8 has the rollback tag that defines how to do a rollback for this insert: delete from department where id=20

 

A few tips about my DevCS project and build setup.

1. For the sake of simplicity, I loaded the liquibase and JDBC jar files into my git repository - this makes it easy for my build steps to find the files and execute them. I'm guessing you could also use Maven to host those.

2. I use a password parameter for my build so I don't need to hardcode the password adding a bit of security to my build. Reference teh parameter in your build with a $ sign - $password

3. Want to learn more about test automation with ut/PLSQL - check out this blog entry.

 

 

Categories: Development

APEX Office Print 3.1 released - support for Docker

Dimitri Gielis - Mon, 2017-10-16 04:18
Last week we release APEX Office Print (AOP) 3.1, our best release ever :)

AOP was already the easiest and most fully integrated printing and exporting solution for Oracle APEX, but with every new release we allow you to customise the way you use AOP a bit more and add more advanced functionalities.

As more and more bigger companies are using AOP, we focussed in this release more on enterprise features, for example, native HTTPS support, end-to-end and customisable debugging, a new queuing system for large amounts of prints and overall performance enhancements and general improvements.
You can read more about this release in our release history.

One other addition I want to highlight is the ability to run AOP in a Docker configuration.
The Docker image is available for our Gold and Enterprise license.

Docker is the world’s leading software container platform. If this concept is new for you, you can read more at What is Docker?



In the previous days Martin Giffy D'Souza blogged about How to Setup Oracle DB 12.2 Docker Container and Docker Oracle and APEX and Roel Hartman talked about Dockerize your APEX development environment. Those are some excellent posts how to get started with Docker in an Oracle Database and APEX context.

The most important reason for us to make an APEX Office Print docker image available was to ease the installation of multiple AOP instances even more and give the possibility to scale AOP in an enterprise way.

Here's a video how you are up and running with our AOP docker image in less than a minute:



You also find the detailed steps in the AOP documentation.

Juergen Schuster and Martin Giffy D'Souza did a podcast with me end of August, where I talk a bit about AOP and our development too.

If you are not yet on APEX Office Print 3.1, go and download the latest version, even when you are not enterprise, it's worthwhile the upgrade. We updated our AOP Sample Application with some new examples too.


Happy printing and exporting from Oracle APEX with AOP :)

Categories: Development

Talking about APEX Reporting and AOP @ Montreal Oracle Dev Day 2017

Dimitri Gielis - Wed, 2017-10-11 01:00
For those in Montreal and the surrounding area I encourage you to come out to the Montreal Oracle Dev Day on October 25th (8:30-4:30 at Centre for Sustainable Development).

Here’s a summary agenda of the presentations with the full agenda here:
Aside from the presentations you will have plenty of opportunity to network and share your Oracle development experiences. All speakers will be available all day so feel free to bring your APEX questions!

You can register now online.

As I'm not that much in this part of the world it would be great to meet in person. I would love to hear your thoughts on APEX Office Print (AOP) too.  If you have any questions, feedback or just want to talk how to use AOP in your environment, don't hesitate to come up to me. I'm more than happy to talk to you :)

Categories: Development

JavaScript, Node.js, JET and APEX day, 7-NOV-2017, Belgium

Dimitri Gielis - Mon, 2017-10-09 04:07
With great pleasure I can announce we have a special APEX Meetup in Belgium on Tuesday, November 7, 2017.


Dan McGhan is our special guest and will present on different technologies that will enrich your life as an Oracle APEX Developer too.

Unlike our other APEX Meetups, we decided to do a full day event, with food and drinks :) The event is sponsored by APEX R&D, iAdvise, ODTUG and Oracle.

Agenda:
• 8.30 am - 9 am: welcome coffee and registration
• 9 am - 10 am: Setup help for those that couldn't do it before (VM with Oracle & Node.js - optional)
• 10 am -11 am: Intro to JavaScript for PL/SQL and APEX Developers (language mechanics and DOM basics)
• 11 am -12 pm: Intermediate JavaScript for PL/SQL and APEX Developers (Ajax, promises, etc.)
• 12 pm - 1 pm: Lunch
• 1 pm - 2 pm: Intro to Node.js
• 2 pm - 3 pm: RESTful APIs with Node.js
• Coffee Break
• 3.30 pm - 4.30 pm: Intro to Oracle JET
• 4.30 pm - 5.30 pm: Using JET and Node.js with APEX
• Closing Drink

We have a capacity of 75 people, so if you didn't register yet, you find the link here.
Categories: Development

Choosing the right template and print to PDF from your Oracle APEX application

Dimitri Gielis - Fri, 2017-10-06 07:19
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

To motivate players to exercise more, I thought it would be a good idea to give the players a diploma (certificate) when they complete the multiplication table. They will be able to download or print this certificate if they have a specific score. I designed it that we have one official diploma of mtable.online, but there's also the ability so teachers or people at home can print their own certificate in the template or colours they want.

Creating the diploma

Creating a certificate in Microsoft Office is not that hard, in fact online you find many certificate templates you can just download. Mostly are in Word or Powerpoint format.



In our multiplication table project we have the concept of teams. One or more players can be part of one or more teams. A team could be for example a classroom and the students are the players within that team. I want the team manager be able to upload a template for the certificate (diploma) of the team.

How integrating those certificates in Oracle Application Express (APEX)?

With APEX Office Print, this becomes very easy!


APEX Office Print (AOP) is a print server for Oracle Application Express (APEX) which allows you to define your template in Word, Excel, Powerpoint, HTML or Markdown and merges it with your data in the Oracle database. As output you can select PDF, Word, Excel, Powerpoint, HTML or Markdown. APEX Office Print comes with an APEX Plug-in and PL/SQL API that makes it very easy to select the template and the data in any Oracle APEX application. AOP is also smart and understands Oracle APEX meta-data, so for example printing one or more Interactive Reports or Grids to Excel or PDF is done in a breeze. Till date, APEX Office Print (AOP) is the most integrated, easiest and flexible printing and data exporting solution for Oracle Application Express (APEX).
Full disclosure, my company APEX R&D is the maker of AOP, so I might be a bit biased, but you can check this youtube video where different APEX printing solutions are discussed.

Installation of the AOP plugin

When you go to the APEX Office Print website, you can download the cloud or on-premise version and try it out for 100 days. You just have to click sign up, provide an email go to Downloads and click the Cloud package.


 Extract the zip file that was downloaded and locate the db folder and plugin folder.



Import the APEX Plug-in by going into your APEX app, to Shared Components, Plug-ins, hit the import button and choose the dynamic_action_plugin_be_apexrnd_aop_da_51.sql file. Depending the version of APEX, you might need to choose the _50.sql file (in case of APEX 5.0). Follow the wizard to import the plug-in.



The plug-in calls a package, so there's one more step to do in order to make the plug-in work. Go to SQL Workshop > SQL Scripts and hit the Upload button and select the file aop_db_pkg.sql from the db folder.



Next, click on the Run button to run the script, which will install the AOP_API3_PKG PL/SQL package.

That's it - you successfully installed the AOP plug-in and are now ready to use it in your application.

Note: in the above example we installed the Dynamic Action plug-in. AOP also comes with a process type plugin, in case you prefer a process over a dynamic action.


Calling the AOP plugin from your page

Create a new dynamic action, for example Click on a button and as Action select the APEX Office Print (AOP) Plug-in:



The plug-in is very flexible and has many options, yet it's so easy to use. You first tell the plugin where your template is; in Static Application Files (Shared Components), in a table (define your SQL), a url, the filesystem, ... you just select where and tell it which one.

Next you have to tell which data you want to use, you can define SQL, PL/SQL, URL or even the static id of the region. AOP is so smart it will understand if you put the static id of an Interactive Report or Grid, a Classic Report or even a JET or other chart. Behind the scenes AOP is reading the meta-data, so it will use whatever is behind that region as source. This is one of the most liked features by our customers and something no other printing solution offers.



The plug-in has build-in help and examples, so it's more easy to know how to use the plugin.

You can define some other settings (which items to submit in session state, some special report settings etc.) and finally the output you want; PDF, Word, Excel, you name it.

Print the diploma

When you go to mtable.online, you can view the highscores for all players, or the highscores within a team.



Based on some rules you might be able to print a diploma. The logic which defines if you are allowed to get a diploma I wrapped in a view:


Now the interesting thing is that we can define the selection of the template dynamically in the AOP plugin. So I've some PL/SQL code that returns the correct template. If a team is selected, we will use that template of the team, if it doesn't have one, or if you are looking at all players, we will use the default template defined by us. As described in the first paragraph, the owner of the team can define his own template, they don't need to contact us (the developers) anymore. The just create their template in Word, Excel or Powerpoint and upload it.

From the very start when developing AOP, this was a main goal; we want the business users be able to create their own template. As a developer we just provide them the data, how it looks like the business people can define. With other printing solutions, I had to spent hours and hours redefining the template in XSL-FO, iReport, or other tools, but with AOP, as a developer my job is done, the moment I write my query :)

Back to the diploma in mtable.online; as default the template I used was a Powerpoint I downloaded from the certificate templates site. The only thing I changed was adding the substitution variables that AOP would understand: {player_name}, {mi_ss}, {play_date} (those are the columns in my query - see further)


 For my team, I went with a Word Template:


 And my plug-in looks like this:


Whenever the diploma link is clicked in the report, the AOP Dynamic Actions kicks in and generates the PDF, based on the template from the team (Word) or the default (Powerpoint). That's it :)

APEX Office Print is even more used in the Multiplication Table project; namely when you go to the  details of an exercise, which are shown in an Interactive Grid, AOP exports this Interactive Grid to our own Excel template. I'll do a dedicated post on that as there're more interesting steps to talk about with adding buttons and custom dynamic actions to an Interactive Grid.

If you didn't try APEX Office Print 3.0 yet, you can sign up for a trial for free. In a few days we ar also releasing AOP 3.1, our best version ever :)
Categories: Development

Multiple APEX Workspaces in one Oracle Exadata Express Cloud account now possible

Dimitri Gielis - Sat, 2017-09-23 05:18
Last night my Oracle Exadata Express Cloud account was updated to APEX 5.1.2, but there was more updated once I looked closer into the dashboard.


Before we could already create multiple Oracle schemas, but from now on within one Oracle Exadata Express account you can create multiple APEX Workspaces.

Go to Instance Administration under the Application Express setting in Manage:


Clicking on Manage Workspaces > Create Workspace


Just follow the wizard; you can attach the workspace to an Oracle database schema and once finished you will see.


Now when you login again in your Oracle Exadata Express account and go to the APEX App Builder it will ask you which workspace you want to login to:


The first time it will give you a welcome message and asks to set a password


As it's an Oracle cloud account with identity management, I don't really need to set a password, I just need to confirm my profile


That's it! You are now in your new Oracle Application Express (APEX) Workspace.

It's very nice to see Oracle Exadata Express getting better with every maintenance release. Thanks Oracle :)

Update 23-SEP-2017: another new feature is that you can export your Oracle schemas and data to the Oracle Storage Cloud Service.

Go to Export to Cloud Storage:


Add your Oracle Cloud storage details and select the schemas to be exported:


Categories: Development

Webinar: Version Control for the Oracle Database

Gerger Consulting - Wed, 2017-09-20 14:36

Version control of tables and PL/SQL code in the Oracle Database has always been challenging. Database programmers lack the tools that make version control easy for developers who code with file based programming languages such as Java and JavaScript. In fact, this is one of the primary reasons why large software teams avoid schemas and writing code in the database.

In this presentation you will learn how Gitora 3 can help you use Git, the prominent open source version control software, to manage every database object including tables and PL/SQL source code.

Sign up now.


Every attendee will receive a discount offer to purchase Gitora 3 developer licenses. :-)

About the Webinar:

In this presentation you’ll learn how to:

  • Manage any database object including tables and packages in Git.
  • Implement agile development best practices for database development
  • Execute commonly used version control commands in the Oracle Database and how executing these commands can change the source code in the Oracle Database automatically.
  • Get the differential script between two Oracle Database source code repositories automatically.
  • Minimize any errors such as accidental overwrites of packages, procedures and functions.
  • Prevent anyone from editing source code in the database outside of a version control system
  • Have multiple teams change the same logical packages, procedures and functions and merge their changes automatically.

About the Presenter:

Yalim Gerger is the founder of Gerger, the company behind Formspider, the application development framework for PL/SQL Developers. Gerger’s new product Gitora helps Oracle developers manage their source code with Git just like any developer using a file based programming language.

Yalim is one of the founding members of Turkey Oracle User Group and a frequent speaker at conferences and webinars.





Categories: Development

Creating Custom Search/Query Pages with Visual Builder

Shay Shmeltzer - Fri, 2017-09-15 10:39

There is built in functionality in Visual Builder Cloud Service (VBCS) that will let you attach an advanced filter to any table. This will give you the ability to define complex searches.

However, sometime people would want to create their own custom query pages. For example the default filter for a field searches for a letter in any place in the field - and maybe you just want to search for the first letter. Or maybe you want to display a nice selection list for filtering. There is a little trick that will let you achieve this, and I show it in the video:

The basic steps involve creating a new edit page with a new dummy business object. Then you add a table that is based on the real business object (the one with the data), and hook up the query of this table to the values of fields from the dummy business object. Then you add a button to do a "fetch all" on the table, and you are done.

At the end of the video you'll also see how to hook this page into your main menu.

Categories: Development

A Guide to Attending Oracle OpenWorld and JavaOne for Free

Shay Shmeltzer - Thu, 2017-09-14 11:32

This title seems like a click bait, but I'll try and show you that this is actually possible. (Especially if you act fast).

A pass to both conferences is not a cheap item, and with training budgets shrinking you might be running into problems getting approval to expense this to your company. But here are a couple of tricks you can use to get free access to OOW/JavaOne. Some would get you a full pass and some will get you into specific areas and sessions.

Full Pass

Be a Speaker

Well we are a bit late to the call for papers at this point, but you might still be able to become a co-speaker if you have a good customer story to tell that shows how you are using the product/technology that a talk is about. Oracle is always looking to feature customer stories in our sessions - it is always better when a customer shares their real live success. Look up the content catalog and locate sessions on products that you are successfully implementing - ping the speaker and they might want to add you as a co-speaker.

Apply for the Oracle Excellence Awards

We are late for getting in this year, but keep an eye for this for next year. Winners get a free pass to OOW. Details here.

JavaOne Special Discount

Right now you can get 50% off your JavaOne ticket price if you use this code DJFS2017. Register here https://www.oracle.com/javaone/register.html

Other Passes

So full pass might be hard to get for free, but you can get in on a big chunk of the action for free using 

The Discoverer pass

A Discoverer pass lets you into the exhibit halls and various keynotes. Getting into the exhibit hall is great if you want to meet product managers and learn about the latest versions (and upcoming versions too). Most of the PMs are going to do shifts at the Oracle Demoground in their product pods - so hang out there and you'll be able to chat with us.

Usually a Discoverer pass is $75 - but since you are reading this blog, you can get one for free - use the code CDFR2017. Register here.

Oracle Code

Want to get into some technical sessions - this year we are bringing Oracle Code back to San Francisco during OOW - and you can get into this part of the conference for free. Reg here:

https://developer.oracle.com/code/sanfrancisco-oct-2017

See you at the conference in a couple of weeks.

 

 

 

Categories: Development

Sending Emails from Visual Builder Cloud Service

Shay Shmeltzer - Mon, 2017-09-11 12:23

Sending emails as a result of some changes to data is a common requirement we've been hearing from customers of Oracle Visual Builder Cloud Service (VBCS). In previous versions we already added a function that allows you to fire up the client email software from your app. In the new version we rolled out a couple of weeks ago we added a new function that can manage the whole email processing inside VBCS - on the backend/server/cloud side.

When you define a new trigger on a business object you now have the option to add "send email notification" step to your logic flow. 

When you are defining this, you'll be able to pick or define a template for your email. The email template can have parameters to increase reusability. These parameters can then have their value set from expressions that can include values of fields from your objects.

Here is a complete demo video that shows you how to add an email notification to an event on your business object:

Categories: Development

Version Control for Oracle Database

Gerger Consulting - Thu, 2017-09-07 12:05
Today, we’re happy to announce the general availability of Gitora 3.
Gitora 2 enabled developers to manage database objects that support the CREATE OR REPLACE command, namely PACKAGES, PROCEDURES, FUNCTIONS, TRIGGERS, VIEWS, SYNONYMS and TYPES in a Git repository. (In Gitora terminology, we call these “Soft Objects”).
Gitora 3 goes a step further and enables developers to manage every database object in an Oracle Database with Git even if the object does not support the CREATE OR REPLACE command. (In Gitora terminology, we call these “Hard Objects”.)
Specifically, Gitora 3 allows developers and DBA's  to manage TABLES, INDEXES, SEQUENCES, CONSTRAINTS, TABLESPACES, MATERIALIZED VIEWS and every other schema or non-schema objects with Git.

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development