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

PL/SQL functions: Iterate and keys for associative arrays

XTended Oracle SQL - Sun, 2017-10-01 17:54

Unfortunately associative arrays still require more “coding”:
we still can’t use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don’t have convinient iterators and even function to get all keys…
That’s why I want to show my templates for such things like iterator and keys function. You can adopt these functions and create them on schema level.

declare 
   type numbers  is table of number;
   type anumbers is table of number index by pls_integer;
   a anumbers;
   i pls_integer;

   function iterate( idx in out nocopy pls_integer, arr in out nocopy anumbers) 
      return boolean
   as pragma inline;
   begin
      if idx is null 
         then idx:=arr.first; 
         else idx:=arr.next(idx);
      end if;
      return idx is not null;
   end;

  function keys(a in out nocopy anumbers) return numbers as
     res numbers:=numbers();
     idx number;
     pragma inline;
  begin
     while iterate(idx,a) loop
        res.extend;
        res(res.count):=idx;
     end loop;
     return res;
  end;

begin
   a(1):=10;
   a(3):=30;
   a(5):=50;
   a(8):=80;
   -- iterate:
   while iterate(i,a) loop
      dbms_output.put_line(a(i));
   end loop;

   -- keys:
   for i in 1..keys(a).count loop
      dbms_output.put_line(a(keys(a)(i)));
   end loop;

end;
Categories: Development

How to group connected elements (or pairs)

XTended Oracle SQL - Thu, 2017-09-28 22:03

I see quite often when developers ask questions about connected components:

Table “MESSAGES” contains fields “SENDER” and “RECIPIENT”, which store clients id.
How to quickly get all groups of clients who are connected even through other clients if the table has X million rows?
So for this table, there should be 4 groups:

  • (1, 2, 4, 8, 16)
  • (3, 6, 12)
  • (5, 10, 20)
  • (7, 14)
  • (9, 18)
SENDERRECIPIENT122436485106127148169181020

Of course, we can solve this problem using SQL only (model, recursive subquery factoring or connect by with nocycle), but such solutions will be too slow for huge tables.

Example of SQL solution

with 
   t(sender,recipient) as (select level,level*2 from dual connect by level<=10)
,  v1 as (select rownum id,t.* from t)
,  v2 as (select id, account
          from v1
           unpivot (
             account for x in (sender,recipient)
           ))
, v3 as (
           select
              id
             ,account
             ,dense_rank()over(order by account) account_n
             ,count(*)over() cnt
           from v2)
, v4 as (
           select distinct grp,account
           from v3
           model
                dimension by (id,account_n)
                measures(id grp,account,cnt)
                rules
                iterate(1e6)until(iteration_number>cnt[1,1])(
                   grp[any,any] = min(grp)[any,cv()]
                  ,grp[any,any] = min(grp)[cv(),any]
                )
)
select
   listagg(account,',')within group(order by account) s
from v4
group by grp

[collapse]

In such situations it’s much better to adopt standard algorithms like Quick-find or Weighted quick-union for PL/SQL.
The first time I wrote such solution about 5 years ago and I even posted here one of the latest solutions, but all of them were not universal, so I’ve created the package today with a couple of functions for most common problems: XT_CONNECTED_COMPONENTS

It contains 2 functions based on Weighted quick-find quick-union algorithm:

  • function get_strings(cur in sys_refcursor, delim varchar2:=’,’) return strings_array pipelined;
    It accepts a cursor and returns found connected components as table of varchar2(v_size). You can change v_size in the package definition.
    Input cursor should contain one Varchar2 column with linked strings, for example: ‘a,b,c’.
    You can also specify list delimiter, by default it is comma.
    Examples:

    select * from table(xt_connected_components.get_strings( cursor(select ELEM1||','||ELEM2 from TEST));
    select * 
    from
     table(
       xt_connected_components.get_strings( 
         cursor(select 'a,b,c' from dual union all
                select 'd,e,f' from dual union all
                select 'e,c'   from dual union all
                select 'z'     from dual union all
                select 'X,Y'   from dual union all
                select 'Y,Z'   from dual)));
    COLUMN_VALUE
    -----------------------------------------
    STRINGS('X', 'Y', 'Z')
    STRINGS('a', 'b', 'c', 'd', 'e', 'f')
    STRINGS('z')
    
    
  • function get_numbers(cur in sys_refcursor) return numbers_array pipelined;
    This function also returns connected components, but for numbers.
    Input cursor should contain two columns with linked numbers.
    Examples:

    select * 
    from table(
            xt_connected_components.get_numbers( 
              cursor(
                select sender_id, recipient_id from messages
            )));
    select * 
    from
      table(
        xt_connected_components.get_numbers( 
           cursor(
              select level   account1
                   , level*2 account2 
              from dual 
              connect by level<=10
        )));
    SQL> select *
      2  from
      3    table(
      4      xt_connected_components.get_numbers(
      5         cursor(
      6            select level   account1
      7                 , level*2 account2
      8            from dual
      9            connect by level<=10
     10*     )))
    SQL> /
    
    COLUMN_VALUE
    ------------------------
    NUMBERS(1, 2, 4, 8, 16)
    NUMBERS(3, 6, 12)
    NUMBERS(5, 10, 20)
    NUMBERS(7, 14)
    NUMBERS(9, 18)
    

How to install:
Download all files from Github and execute “@install” in SQL*Plus or execute them in another tool in the following order:
xt_connected_components_types.sql
xt_connected_components_pkg.sql
xt_connected_components_bdy.sql

Download URL: https://github.com/xtender/xt_scripts/tree/master/extra/xt_connected_components

Categories: Development

Ampersand instead of colon for bind variables

XTended Oracle SQL - Wed, 2017-09-27 10:22

I’ve troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ampersand instead of :colon! I have never seen this before and I couldn’t find anything about this in documentation…
Unfortunately SQL*Plus doesn’t support ampersand yet, even if you disable define (“set define off”),
so I’ve tested such behaviour with this code:

set def off serverout on
exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;

And it really works! //at least on 11.2.0.2 and 12.2.0.1

SQL> set def off serverout on
SQL> exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
1

PL/SQL procedure successfully completed.

SQL> select substr(sql_text,1,40) stext,sql_id,executions,rows_processed from v$sqlarea a where sql_text like '%dual%&var';

STEXT                                 SQL_ID        EXECUTIONS ROWS_PROCESSED
------------------------------------- ------------- ---------- --------------
select 1 from dual where dummy=&var   ckkw4u3atxz02          3              3

SQL> select * from table(dbms_xplan.display_cursor('ckkw4u3atxz02'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  ckkw4u3atxz02, child number 0
-------------------------------------
select 1 from dual where dummy=&var

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DUMMY"=:VAR)


18 rows selected.

Update: Btw, it works for SQL only, not for PL/SQL:

SQL> var v varchar2(1);
SQL> begin &v = 'Z'; end;
  2  /
begin &v = 'Z'; end;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "&" when expecting one of the following:

SQL> exec &v := 'X';
BEGIN &v := 'X'; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "&" when expecting one of the following:
The symbol "&" was ignored.
SQL> exec :v := 'X';

PL/SQL procedure successfully completed.

SQL> select * from dual where dummy=&v
  2  ;

D
-
X

And we can can use mixed placeholders:

SQL> select * from dual where dummy=&v and &v=:v;

D
-
X
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

Simple regexp to check that string contains word1 and word2 and doesn’t contain word3

XTended Oracle SQL - Wed, 2017-09-13 17:15
with tst as (
  select 'qwe word1 asd ...............' s from dual
  union all
  select 'qwe word1 asd word2 .........' s from dual
  union all
  select 'qwe word1 asd word2 zxc word3' s from dual
  union all
  select 'qwe word2 asd word1 zxc word4' s from dual
)
select
  s
  ,regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') subst
  ,case when regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$') then 'matched' end tst2
from tst
where 1=1
--and regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$')
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

Adding the game, the importance of the APEX Advisor, a lot of JavaScript and adapting for mobile

Dimitri Gielis - Wed, 2017-09-06 13:48
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

Over a year ago I developed the game to practice the multiplications. I quickly created an Oracle APEX page, added a bunch of JavaScript and that was about it. The last weeks you could read I've redeveloped the entire app from the ground up but I still have to add the game to it.

I thought to just copy the page I developed before with all the JavaScript in the new app.

How do you copy a page from one Oracle APEX environment to another? 

Export the app from the first environment and import the app in the second environment. Once both apps are in the same workspace, you can copy a page from another app by going to the Create button:


Click the Page as Copy option and follow the wizard:



So I followed the wizard to copy the page to my new app.

Now the tricky bit was that I changed page numbers, so all my references (in JavaScript, Processes etc.) to page items were wrong. To quickly identify the issues I used the APEX Advisor.

The APEX Advisor you find in App Builder > Utilities : Advisor


The Advisor checks for best practices, errors, security issues etc. I really like the Advisor as it will give your application some kind of quality control. And it's very fast to identify issues and navigate to them. Here's a screenshot what the Advisor found:


I would always run the APEX Advisor, even when you start from a Blueprint application as you will most likely modify the app, add pages, make changes etc. and it's easy to forget for example to put an authorization scheme.

A few years ago I also wrote an extension to the APEX Advisor that would check for our own standards. You can query an Oracle APEX application with SQL, so it's easy to check if naming conventions where followed, if deployments went fine or anything else you find important. Here's a screenshot of some of the checks in one of the projects:


In the community some other people did something similar, for example Oliver Lemm wrote a Quality Assurance plugin.

So back to my application - I fixed those issues and was able to play the game :)
(screenshot from the browser)


To build the table/grid of the game I programmatically generate the APEX items by using the API apex_item.


All the rest is build in JavaScript. I'm not going to put all the source code in this blog post, but if you right click on the page where the game is, and you look at the source, you see all the JavaScript that is used.

Whenever the Start button is clicked, a Dynamic Action is fired which calls some other JavaScript functions. The fireworks and stopwatch are libraries I found on the internet which I integrated in the APEX app. If you have any question on the JavaScript side, feel free to ask in the comments section.

A few weeks ago I released the app to test. Some people sent me an email with their feedback, but many also used the build-in feedback mechanism that came with our Blueprint app.


I typically leave a feedback mechanism in every app I build (yes, even in production), it's a great way to communicate with your users.

The result of the feedback was, it didn't run great yet on mobile devices. Except from using Universal Theme, I didn't really pay attention to mobile devices, as I thought it would be too small anyway, but apparently people were trying it on there. So I decided to make sure it would work on at least a tablet, for example I saw a use case for myself to let my son play on my iPad.

In the next section I will go in a bit of detail of things that I had to adapt to make it more user friendly on a mobile device and some other things that might be interesting to know in other applications.

So here's how the app looks like on an iPad:


Compare this image with the image before taking in the browser and you will see some differences.

I added some JavaScript library to check if we run on a mobile device and when so, we add some extra CSS to make the table fit on the screen. The breadcrumb will always be shrunk and the padding is less. Here's the code:


When starting the game the keyboard comes out. One of the first things I had to do, was to make the number keyboard the default and not the normal keyboard with the letters. The way you do that is in your input text item, you define a pattern and give it a type of numeric (see the PL/SQL code where we do the call to apex_item).


The keyboard that slides out hides a part of the table, so it's not ideal. I've an external keyboard for my iPad, so hooking that up, makes it a really nice experience:


Another issue on the iDevice was that the fireworks at the end of the game didn't work well. So I decided to add a setting, so you can choose what you want to see at the end when you finish the game and when you run on an iDevice, the Fireworks is hidden.


The other settings are based on feedback. Some people don't want to play all numbers yet, so you can now pick the numbers. Some others wanted to see the time or countdown, and for some others they got stressed by it. So I decided to make it customizable per player.

Those preferences are all normal APEX items, but the difference is the way they get stored. I only use one field (preferences column in the mtl_player table) to store all preferences. The way I do that was inspired by the APEX Multi-language translate plugin (JTL Item) from my friend Jorge Rimblas, who stored all translations in one field by using JSON. So, all the settings of the game are stored in one JSON object.


On page load I use following JavaScript to set the items:


There's also a save button in the settings section. There's a straight forward dynamic action to do the update. But once saved I show a notification which is done by adding a call to the APEX JavaScript API:

apex.message.showPageSuccess("Player settings saved.")

This results in:


While you play the game on every switch of the number, the timing is saved by calling an AJAX Callback process by apex.server.process (see the source on the page when you play the game). For now I've done it this way, but maybe in the future I might cache the results and only do a callback at the end.



The last thing I want to cover is the overlay you get when you end the game. You have the fireworks, an image or a message as on the below screenshot.


The overlay is done by adding a div on the page and some CSS.



By default it has display set to none, but once the game is complete it's set by some JavaScript.


Hopefully this gives you more insight how the game was created and some things I cover are also useful in your own project.

If you want to play the game, surf to http://mtable.online.

Happy playing!

Categories: Development

How Does Oracle's Core RDBMS work?

Gerger Consulting - Tue, 2017-09-05 13:19

About the Webinar:
Despite the complexity and capability of the Oracle database, at it's heart there are a relatively small number of key activities and processes. If you understand them, you will get a lot more out of the database and advanced topics make a lot more sense. In this presentation I explain the key things the database does and how they work together. If you know what a SELECT statement is, you will understand this talk. And by the end of it, you will know some things that even many experienced developers and DBAs don't know.

In this webinar, you will learn:
  • The two ways Oracle reads data from disc and how it writes it back.
  • That REDO is actually the most important part of the database. 
  • What happens when you COMMIT.
  • Why Oracle data blocks are key to the buffer cache.
  • What a "Consistent Get" really is.
  • Why indexes are so powerful and what "balanced B-tree index" actually means.
  • What Oracle does when you give it a SQL statement to run.
  • How Oracle ensures you see the correct data, even as it is changing.

About the Presenter:


Martin Widlake is an independent Oracle consultant based in the UK. An Oracle ACE Director and member of the OakTable Network, he regularly presents internationally on Oracle performance, database design and PL/SQL. He co-wrote the book “Real World SQL and PL/SQL . Martin has provided consultancy to several large financial organisations and FTSE 500 companies as well as much smaller companies. For several years he was the database services manager for the UK side of the Human Genome Project, combining his I.T. skills with his background in Genetics.
Categories: Development

Create a Custom Authentication and Authorization Scheme in Oracle APEX

Dimitri Gielis - Tue, 2017-08-29 13:32
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

Before when creating the APEX application based on a Blueprint, we used the standard APEX Authentication, which means the management of users is done in Oracle Application Express itself. As we created our own user table, we want to manage our users there and have some custom authentication scheme for our multiplication application.

In this post I will show you exactly how I built the custom authentication scheme for my Oracle APEX application you see at mtable.online.

I first decided which kind of login page I wanted. A few years ago I blogged about pimping the logging page, which is what I use on the APEX Office Print login page. This type of page is a more traditional way of building a login page in Oracle APEX. For the registration page and forgot password page we used the same techniques.


For the multiplication table project I decided to do it a bit different. I started from the landing page and build the login mechanism into that page. But also the register and forgot password are on the same page, so not like what we did for APEX Office Print, using different pages for forgot password, register and login.

Here's how my page looks like in the Oracle APEX App Builder:


There are a few regions to help with the layout (Top, Left, Right). In the Right region, there are 3 sub-regions: Login, Register, Forgot Password, which will show one at a time. Dynamic Actions (Show/Hide) control which region is seen.

From the front-end this is what it looks like.
When clicking a button an APEX process is being fired, but all the logic is defined in a package.
The package to handle the authentication I typically call [project trigram]_AUTH_PKG. It doesn't only contain the authentication logic, but also the registration, reset password and authorization logic.

The specifications looks like this:

.gist .blob-wrapper.data { max-height:600px; overflow:auto; } And the body like this:

I typically use dbms_crypto to generate (hash) the passwords, but as that package is not supported on Oracle Exadata Express at the time of writing, I use another SHA256 PL/SQL implementation.

I'm not going into too much detail on the logic in the PL/SQL package. I hope it's kinda self explanatory, but if you have any question, feel free to ask in the comments field.

Now we will focus on creating a Custom Authentication Scheme in APEX.

Go to App Builder > Shared Components > Authentication Schemes and hit the Create button to add a new one:


Enter the custom_authenticate procedure from the package we created earlier:


By default the new authentication scheme will be current, so make sure you have some data in your tables, otherwise you won't be able to login.

Next I typically add some post authentication to fill some Application Items.
Edit the Custom Authentication and add the code and post_auth as in this picture:


We have now made our application accessible to people by defining our own custom authentication scheme.

Next, we want to define which rights you have in the application. To do this, we will create two Authorization Schemes, one for a normal user and one for an administrator.

In our package we already included a function with the logic. Every user has a role defined to him, and depending the role, it's a normal user or an administrator. An administrator can do everything a normal user can do, but can also access the administrator section where we maintain our application.

Blueprint actually already created our Authorization scheme for administrators, but we will adapt it to use our package. Go to Shared Components > Authorization Schemes and modify like this:


I hope it gives you all the components to build your own custom authentication and authorization schemes.

I also recorded a video which goes in more detail on the entire process of signing up, forgetting password and logging in and the different authorization schemes and code being used.

Categories: Development

Free Webinar by Craig Shallahamer on Oracle Performance

Gerger Consulting - Mon, 2017-08-21 14:28
Attend the free webinar presented by the Oracle ACE Director Craig Shallahamer and learn how to choose the best strategy when analyzing a Oracle Database performance problem.

About The Webinar. 
The Oracle Database provides amazing application performance opportunities. But sometimes there are still unhappy users. So, what is your best analysis strategy? As Oracle DBAs we have two fundamental strategies: time based and active session sample based (ASH). Knowing their differences and how to exploit them gives you the optimization edge. In this webinar, I introduce each strategy, highlight how they work, contrast their differences and demonstrate live each strategy. This webinar will teach you how to begin your analysis in the best direction.
Takeaways and value:
  • You will know the differences and advantages between an ASH and AWR (time-based) analysis strategy.
  • You will know which analysis is best in any given situation.
  • You will learn how to use an AWR report to analyze performance and derive multiple solutions.

Categories: Development

Build the Oracle APEX application: the framework

Dimitri Gielis - Thu, 2017-08-17 05:11
Over the years while developing Oracle APEX application, I noticed in every application I was following the same steps. It was a routine I followed over and over. For example: in every application I created a global page (page 0), enabled Feedback, ran the Advisor multiple times a week, used certain naming conventions etc.

A few years ago I wrote those steps in our APEX R&D development guide, so within our team we would be consistent. Just like the database best practices, I thought it would be a good idea to share the APEX best practices with you as well. There are about 9 pages, here's a screenshot of the first page:
I don't like too many rules, but a few are good to have, especially when you get new people on board or when the customer is working together with you. As I said in my previous post where I described our database guidelines, the guidelines in itself is not meant to be "this is best and you have to follow", it's more something to start from which you can adapt.

Based on those guidelines, we created a "starter application", so you get a head start in your project. The app in itself wasn't spectacular in itself, it contained the global page, feedback, some administrator pages which give statistics of your app, some application items, error handling package etc.

If you know what you have to build, it wouldn't even take that long to build it from scratch. I just don't like to do repetitive things, I far rather concentrate on the real solution, so anything that can help, I will embrace :) I also won't hesitate to take some components or solutions from other projects and reuse those. But just one piece of advice, always check if what you've been doing before, is still valid today and "the best" way of doing it -for your current problem-. For example if you developed something in APEX 4.2, maybe in APEX 5.x there's a build-in package to do that. Or the other way, that a feature of APEX became deprecated (e.g. apex_plsql_job) and you should use a database feature (dbms_scheduler).

In 2015 I also gave a presentation at some Oracle conferences how I developed in APEX 4.2 vs 5.0, which you find here.

So I could use my starter app for this project, but just as with Quick SQL (to create your database objects), there's a new feature in Oracle APEX 5.2 that will make the starter app irrelevant, so I will cover the new way of doing something in 2017 and further.

The new way of building an application, is to start from a "Blueprint". The concept is very similar to my workflow previously, but now it's built-in the APEX Builder and it's more powerful as it allows to customize the features you want to include in your app.

Blueprint is at the time of writing only available in Preview mode (since March 2017) through apex.oracle.com and will probably undergo some more changes. I see a lot of potential in this feature. It's not only the wizard you can use, rumors are you can also use JSON syntax to define your app.

Here's the video I recorded where I give a bit of background and you see me using Blueprint:



At the time of writing Blueprint isn't finished yet. Not sure what the end result will be, but although there's still a lot of room for improvement, it already looks spectacular. As you could see in the video, I will use it as my starting point for my multiplication table project. I've exported the app of apex.oracle.com and imported in my Oracle Exadata Express environment. Next I'll make changes to this app and customize further.

Blueprint is (or will become) the fastest and most low code way to build an Oracle APEX application.
Categories: Development

Set up domain and launch page

Dimitri Gielis - Mon, 2017-08-14 15:56
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017
Domain nameWhen you launch an app or service, you probably want a domain name. I register all my domain names with Godaddy. I'm a long time customer with them and I find them really reliable, not that expensive and they have good support. But I find it hard to come up with "the right" domain name. For example for our project I wrote on a piece of paper all different domain names: multiplicationtable.com, multiplicationstable.com, multiplications.edu, multiplicationtable.online, multiplicationtable.guru etc.

What domain would be perfect for our application? At one side you have to think about Google, a descriptive domain helps in your ranking, but on the other hand you want it to be easy to type and remember. Finally I decided to register mtable.online as my first choice multiplicationtable.com was taken. If you know a better domain that is still available, feel free to add in the comments field :)
Link domain name to Oracle APEX appOnce you have the domain name you want to link it to your Oracle APEX application.

In a future post I will cover Reverse Proxy and SSL, but for now, as I want you to see what is happening, I just configured in Godaddy to redirect to my Oracle Exadata Express url.


I don't want to let the world wait to know about our project, so I want to setup a landing or launch page. This page serves as a home while we are building our application. People can already register, so once we are live, we can let them know about it.
How do you build a landing or launch pageEither you have an idea about how you want the page to look like, or what I do as well, is look at other sites or templates created by others. For example for the APEX Office Print website we bought a template as starting point and integrated it in our APEX app.

I get often inspiration from following sites:
If you know some graphic design people, they can obviously help too :) It's easy(ier) to recognize good design, but it's hard(er) to create it yourself.

I like minimalist design or "less is more". If you look at the multiplication table project launch page, only the bare minimum is on the page: a title, a graphic, a text item to leave your email and a button (and a text to let everybody know it was built with Oracle APEX.


You find some more examples of minimalism here or just Google for it and you find plenty. Typically you will see a background picture and a text. If you find a template you like, you can look at the HTML and CSS and copy this in your own APEX page.

The font you use is really important and can make a huge difference. Google Fonts are a good starting place to pick a font. In our project I used the Raleway font.

In the last month (that I know) two other people showed how to build a landing page in APEX: Stefanie used a background picture and Richard used a video as background.
How long to build a landing pageWhen I look again at our launch page, I would probably tell you it would take me less than 30 minutes to build it in Oracle APEX. If you know what you will build, yes, but if I told you it took me well over 6 hours to build this landing page, would you believe me? It's the truth!


So where did I spend all my time then?

I first started to search for a design. I didn't really want to copy something from a previous project, but rather wanted to get a fresh new design, so I started to search, and search and search more. Finally I gave up on the idea and followed my own thoughts to do it very minimal and as close as possible to universal theme that comes with APEX.

So I added the regions, items and a button on the page:


This goes fast (if you know a bit of APEX). As I wanted the image on the left of the items and the items going down a bit, I used two regions next to each other.

I looked at the result and didn't like it, so I added some CSS and searched for another font. This was a lot of trial and error till I was happy with the result.

Once that was done, I looked how responsive it was. On a smaller screen, I didn't find it looked good enough, so I added a media query and custom CSS, so it would look better.


Oh, before I forget, whenever you build an app in Oracle APEX, include the plugin built with love using Oracle APEX.

Next up, I had to include a validation (to check if the email already exists), a process and a branch. Now here's a story too... I first started with adding a dynamic action on the Subscription button that would insert a record, but during testing I found I lost the value required and is email validation that you get for free when you submit your page, so I changed it to be a normal process.

Finally I changed on the page that duplicate submissions are not allowed.

So the end result is we have one very simple page in APEX, using standard components (regions, items, button), 1 plugin (built with APEX), Universal Theme and a bit of custom CSS.
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development