Category Archives: APEX 5.1

APEX 5.1 – Packaged Apps & JET Charts

I was looked around at the Packaged Apps in APEX 5.1 and noticed the Sample Charts application has been updated with the new JET charts.

If you’re not familiar with the Packaged Apps, they’re a great way to learn about the features and functionality in APEX, since if you see something you like in a packaged app you not only know that it must be possible to do that in APEX (since the packaged apps are APEX applications), but also you have access to the application so you can see exactly how the Oracle APEX development team accomplished it (note this will involve unlocking the sample app which has implications so make sure you’re happy with those implications before doing that).

So let’s go ahead and install the Sample Charts Packaged Application

after accepting the defaults in the wizard you’ll be able to run the application.

I like the look of this application and think it’ll be a great resource to refer back to, not only to see the different types of charts available but also how to implement them (remember I said you have full access to the packaged applications).

For example, looking at the new JET Bubble chart

I can dig into the SQL Query and see that it uses –

In other words, I need to define a query like

select country country,
       name,
       (employee/100) employee,
       (employer/100) employer,
       (total/100) total
 from eba_demo_chart_stats
 

and then you declaratively define which columns should be used for –

• Label
• X (position on the X axis)
• Y (position on the Y axis)
• Z (i.e. the size of the bubble)

So rather than trying to figure out from scratch how to produce a particular type of chart I can jump into the Packaged Application, find the example I like and then see how to implement it – that is a huge time saver.

There are quite a few of the chart types that still use either the old Anychart or D3 types and haven’t been updated to JET examples yet, but I’m sure that’s coming in APEX 5.2, but so far I’m loving the Oracle JET integration in APEX 5.1!

APEX 5.1 – Package Application – Quick SQL

You might have seen some demos of Live SQL from Oracle, however you might not know that there is a packaged application in APEX 5.1 called Quick SQL with very similar (if not identical) functionality that you can use locally.

Firstly – why the name difference between Live SQL and Quick SQL? Answer – I have no idea, perhaps to seperate the two applications in terms of one is a packaged application (Quick SQL) whilst the other (Live SQL) is a hosted application (by Oracle) that can be used by anyone with a web-browser regardless of if they have a local APEX installation or not. Also, since Live SQL is hosted publically and Quick SQL is shipped with APEX, it’s possible that Live SQL contains some newer features / bug fixes etc that may or may not be included in an updated packaged application in the future when APEX itself is patched (or 5.2 comes out).

For the rest of this post I’m going to refer to Quick SQL, however as far as I’m aware most of the functionality can equally be used on the public Live SQL site.

So what is Quick SQL? Well if you’ve spent any time with Oracle (or indeed any other RDBMS), you’ll have lost count of the number of times you’ve hand-typed DDL statements like –

create table foo (
  id number not null, 
  data varchar2(20) not null, 
  constraint foo_pk primary key (id) enable 
);

If you’ve used Oracle for a while, you might have some pre-defined templates you use to make this task less manual, however it is still a pretty manual task (unless you obviously use something like SQL Developer Data Modeller).

What Quick SQL does is allow you to write out SQL is shorthand notation and it transforms that shorthand into the full syntax.

So, let’s install the Quick SQL Packaged Application

The list of packaged applications can be quite long, so I just filtered for ‘sql’

Follow the wizard steps and you should have it installed in under a minute, once you run the application and login using your workspace credentials it will ask you a couple of questions about Access Control (I just went with the defaults).

After hitting “Complete Setup”, you get to the main application, which at first glance might look a bit underwhelming.

That’s it, just two panes, one pane you can type into, whilst the other pain is for output. So let’s go ahead and type this into the left hand pane –

foo
  id
  data

type it exactly like that, as soon as you hit return at the end (or press the ‘Generate’ button) you should see the right hand pane become populated with output.

in my case, the output was

-- create tables
create table FOO (
    ID                             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                                   constraint FOO_ID_PK primary key,
    DATA                           VARCHAR2(4000)
)
;

-- load data
 
-- Generated by Quick SQL Tuesday June 06, 2017  10:06:13
 
/*
foo
  id
  data

# settings = {  }
*/

You’ll notice a few things here –

• The ID column definition has some Oracle 12c syntax (the GENERATED BY DEFAULT ON NULL AS IDENTITY piece)

• It includes a comment on the input that was used to generate this output

• The DATA column was defined as a VARCHAR2(4000) by default

Let’s say instead of a VARCHAR2(4000) column, the data column should be a VARCHAR2(20) and also NOT NULL, just type this into the left hand pane –

foo
  id
  data vc20 /nn

which should give the following output (I’ve omitted the comments section for brevity)

create table FOO (
    ID                             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                                   constraint FOO_ID_PK primary key,
    DATA                           VARCHAR2(20) not null
)
;

so the VC20 was interpretted as VARCHAR2(20) and the /nn is shorthand for “NOT NULL”, we can also use any length datatype, e.g. VC5, VC10 etc.

Ok, so that’s not the worlds most impressive demo for saving time, but lets look at another use-case. Let’s say a typical Master Detail scenario, try typing this into the left hand pane –

dept
  deptno /nn
  dname /nn
  emp
    empno /nn
    deptno /nn
view emps_vw dept emp

this should produce the text

-- create tables
create table DEPT (
    ID                             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                                   constraint DEPT_ID_PK primary key,
    DEPTNO                         VARCHAR2(4000) not null,
    DNAME                          VARCHAR2(255) not null
)
;

create table EMP (
    ID                             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY  
                                   constraint EMP_ID_PK primary key,
    DEPT_ID                        NUMBER
                                   constraint EMP_DEPT_ID_FK
                                   references DEPT on delete cascade,
    EMPNO                          VARCHAR2(4000) not null,
    DEPTNO                         VARCHAR2(4000) not null
)
;

 

-- indexes
create index EMP_i1 on EMP (DEPT_ID);

-- create views
create or replace view EMPS_VW as 
select 
    DEPT.ID                                            DEPT_ID,
    DEPT.DEPTNO                                        DEPT_DEPTNO,
    DEPT.DNAME                                         DNAME,
    EMP.ID                                             EMP_ID,
    EMP.EMPNO                                          EMPNO,
    EMP.DEPTNO                                         EMP_DEPTNO
from 
    DEPT,
    EMP
where
    EMP.DEPT_ID(+) = DEPT.ID
/

-- load data
 
-- Generated by Quick SQL Tuesday June 06, 2017  10:15:57
 
/*
dept
  deptno /nn
  dname /nn
  emp
    empno /nn
    deptno /nn
view emps_vw dept emp

# settings = {  }
*/

notice here that simply by indentation, Quick SQL has figured out it needs to generate a foreign key relationship between the two tables.

I have also created a view joining the two tables, in only 7 lines of Quick SQL markup I’ve output close to 40 lines of DDL, saving myself a huge amount of typing.

There are a huge number of Table level directives you can use, for example –

  • /api     Generate PL/SQL package API to query, insert, update, and delete data within a table.
  • /audit     Adds Oracle auditing, by default audit all on table name.
  • /auditcols, /audit cols, /audit columns     Automatically adds an updated, updated_by, inserted, and inserted_by columns and the trigger logic to populate.
  • /compress, /compressed     Table will be created compressed.
  • /history     Generate code to log changes into a history table for this specific table.
  • /insert NN     Generate NN SQL INSERT statement(s) with random data.
  • /select     Generate SQL SELECT statement.
  • /rest     Generate REST enablement of the table using Oracle REST Data Services (ORDS).
  • /uncomment, /uncommented     If Yes will cause generated SELECT or INSERT SQL statements to not be commented. Reference /insert and /select.

and also column level directives –

  •  /idx, /index, /indexed     Will create a non unique index on the associated column.
  • /unique     Creates a unique constraint.
  • /check     Creates a check constraint with with comma or white space delimited values e.g. /cc Y N
  • /constant     When generating random data for a table set this column to a constant value, quotes will be added if needed.
  • /values     Comma separated list of values to use when generating random data. For example /values 1, 2, 3, 4 or /values Yes, No.
  • /upper     Forces column values to upper case.• /lower     Forces column values to lower case.
  • /nn, /not null     Adds a not null constraint on the column.
  • /between     Adds a between check constraint on the column.
  • /hidden, /invisible     Hidden columns are not displayed using select * from table.
  • /references, /reference, /fk     Foreign key references e.g. /references table_name. You can also reference tables that are not part of your model.
  • /pk     Identifies single column as table primary key. It is recommended not manually specify primary keys and let this app create them for you.

So you can a huge amount of control over the output DDL.

However, remember earlier when I said the DDL used some 12c syntax – what if you’re still on 11g? Well you can control that via the settings, either by including –

#db: 11g

in your markup in the left pane, or use the settings button to change it. It’s worth also looking in the Syntax and Examples section as it contains (funnily enough) lots of syntax and examples that you can learn from.

APEX 5.1 – Interactive Grid – Master Detail

For as long as I’ve been using APEX, a constant cry is “But what about Master Detail?”, well you’ve always been able to do that with Tabular Forms and for complex examples you’ve always been able to create your own editable reports using the APEX_ITEM API. However it’s always been just that little bit more difficult than it should be (not to mention long term maintainability of the code wasn’t always straight-forward – if for example the underlying table definitions changed).

This has been one of the promises of the Interactive Grid feature in APEX 5.1, so let’s take a look at how easy it is.

Once again, we’ll use the ubiquitous EMP and DEPT tables, so let’s create a Master Interactive Grid using the query –

select d.*
  from dept d

Let’s also add another Interactive Grid for our Employees, using the query –

select e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.sal,
       e.comm,
       e.deptno       
  from emp e   

Ok great, so we have 2 Interactive Grids, but they aren’t linked in any way.

To setup the relationship between the two Grids, we need to do a few steps –

• Configure the Employee Grid so that it “knows” the Departments region is the Master (hence the Employee region is a Detail region)
• Link the Master / Detail columns between the two regions

So firstly lets define the Master Detail relationship

Now we link the DEPTNO column in the Employees Interactive Grid with the DEPTNO column from the Master region (Departments)

If we run the page now, we’ll get an error –

this is quite self-explanatory but basically the DEPT table didn’t have a Primary Key defined (for whatever reason), so let’s go and define the DEPTNO column as the Primary Key.

Now when we run our page, if we select a row in the Departments Grid, we’ll see the corresponding Employees for that department in the Employees grid.

This might have seemed like a few steps to go through, but really it’s just a couple of additional steps to what you’d need to do to define your reprts anyway. Congratulations to the Oracle APEX team for making this so declarative and simple to implement.

APEX 5.1 – Interactive Grids – Controlling Allowed Operations

In the last post I showed how easily you can enable an Interactive Grid for editing, however what if you want to only allow the user to edit certain rows?

Lets imagine a scenario – given the standard EMP table, I should only be able to change the salary of Employees who report to me (i.e. I can’t change the salary of an employee if they report to someone else).

So, let’s create an Interactive Grid with the following query –

select e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.sal,
       e.comm,
       e.deptno
  from emp e

Now, for the purposes of this example, given the hierarchy of the EMP table –

I’m going to “pretend” to be BLAKE – since BLAKE is the manager of 5 people.

If you look at the properties of the Interactive Grid you’ll see the “Allowed Row Operations Column” setting, this allows us to define a column in the query which will be used to define whether I can Update and / or Delete that row.

The way you do this is simply by returning ‘U’, ‘D’, or ‘UD’ in that column, so for example we can adapt our query to say –


select e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.sal,
       e.comm,
       e.deptno,
       case
         when mgr = 7698 then 'UD'
         else null
       end allowed_operations
  from emp e

Notice the use of the CASE statement to return ‘UD’ if the Manager of the current record is 7698 (i.e. BLAKE).

We’ll also need to set the “Allowed Row Operations Column” attribute to use our new column.

Now if we try and edit a row of a record who doesn’t report to BLAKE then the row is read-only, otherwise if the employee does report to BLAKE then I can edit the row!

In the next post we’ll take a look at Master -> Detail (so we can finally replace those Forms Applications!)

APEX 5.1 – Interactive Grids

Now that APEX 5.1 has been released, I wanted to take a look at the Interactive Grid and see how easy it is to use.

So, let’s add a new Interactive Grid region to an application, using the standard EMP table as our query –

select e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.sal,
       e.comm,
       e.deptno
  from emp e

Now running the application gives us this

So by default it gives you many options similar to an Interactive Report (e.g. filtering, breaking, highlighting etc), but it doesn’t allow editting by default (hands up if this caught you out first time you used it?).

To enable editing pop into the Attributes section and click Enabled.

this will give you a few more options which we’ll explore later. For now running the application gives me what I want – An Editable Report!

By default all the columns are text fields, but we can easily change the DEPTNO column to be a select list based on the following query

select d.dname,
       d.deptno
  from dept d
 order by d.dname

You might also have noticed the little “hamburger menu” next to each row. This gives you some context specific control against each row.

The nice thing here is, as I mentioned earlier, we get all the capabilities of Interactive Reports, but with the power of easily being able to update the data. So for example I can do a break on Deptartmnent and can still update all the data easily.

I’m pretty sure I’ve heard that perhaps Interactive Reports will be ‘retired’ and Interactive Grids will become the replacement, there have however been a bunch of quirks I’ve noticed with the grid which should hopefully get patched soon, but in the meantime it’s an awesome productivity enhancement for developers to get this functionality out of the box (and declarative)!

In the next blog posts we’ll explore some more of the functionality of Interactive Grids.

 

APEX 5.1 Released

So, I just saw this tweet which we’ve all been waiting for!

This release has been very eagerly awaited, since it features-

  • Interactive Grid
    • Supports Fixed Headers, Frozen Columns, Sorting, Aggregation etc
    • Supports all Item types, including Plugins!
    • Master -> Detail -> Detail -> Detail
  • Oracle JET Charts Integration
    • Range of charts, Bar, Line, Area, Range, Radar, Pie etc
    • Declarative
    • HTML5 (no more Flash!)
  • Universal Theme Enhancements
    • Per-user Theme preferences
    • Modal Dialog Auto Resize (yay!)
    • Asynchronous Dynamic Actions
    • Live Template Options (this will be a huge time-saver!)
  • New and Improved Packaged Applications

I’m downloading this right now and can’t wait to test it out.

 

APEX 5.1 Early Adopter 1 Available

After a long (but I’m sure well worth it) wait, Oracle just announced APEX 5.1 Early Adopter is available.

Head over to https://apexea.oracle.com/ to sign up and request a Workspace.

The two big items everyone has been waiting for are

  • Interactive Grids
  • Jet Charts

but reading through the “Additional Features in Application Express 5.1” section on the EA homepage it looks like there are lots of other interesting improvements and enhancements.