Next
I'll be at APEX Meetup Munich: Thu 19 Jul 2018 - 15-Jul-2018 07:14 - Dimitri Gielis
Just a quick note I'll do two presentations at the APEX Meetup in Munich on Thursday, July 19th 2018.

In the first presentation I'll bring you to a virtual and augmented world, entirely build in Oracle Application Express (APEX). There are 30 Google Cardboards available to make the experience complete. Fun guaranteed! :)


At KScope I was also interviewed by Bob Rhubart on my talks over there, which the AR/VR presentation was one of them.


In my second presentation at Munich I'll show the upcoming version of APEX Office Print (AOP).
I'll show some features nobody has seen before :) With every major release of AOP I feel like this:


If you are in the Munich area I would love to meet you at the meetup.

My top 5 APEX 18.1 Plug-ins - 14-Jul-2018 06:46 - Dimitri Gielis
With every new version of Oracle Application Express (APEX) new features are added and the life of a developer is made even easier. If the feature set is not enough or you see you need to build the same functionality more often, you can always extend APEX with plug-ins.

There are six different types of plug-ins: dynamic action, item,  region, process, authentication scheme and authorization scheme.

Plug-ins are absolutely fantastic to extend the native functionalities of APEX in a declarative way. The APEX plug-in becomes a declarative option in the APEX Builder and has the [Plug-in] text next to it. In the next screenshot, you see the dynamic actions being extended by two Plug-ins.


If you are searching for an APEX plug-in, I typically go to APEX World > Plug-ins. The nice thing about that site is that the plug-ins seem to be maintained, so if a plug-in is not supported anymore it gets the status deprecated.

!! And here lays the catch with using plug-ins. When you decide to use a plug-in in your project, you become responsible for this and need to make sure it's compatible with every release of Oracle APEX. Many plug-ins are open source and many plug-in developers maintain their plug-ins, but it's really important you understand that at the end you are responsible for things you put in your application. If the plug-in is not secure or it breaks in the next release of APEX, you need to find a solution. So use plug-ins with care and see for example how many likes the plug-in has or what the comments are about the plug-in or author. Oracle is not reviewing or supporting the plug-ins !!

When I saw the tweet of Travis, I thought to do a blog post on my top 5 plug-ins I use in almost every project.


Here we go:

1. Built with love using Oracle APEX

I'm proud to built applications with Oracle Application Express, and this plug-in makes it very clear :) At the bottom of the app, you will see this text:


Note that in Oracle APEX 18.1 this text in included by default and you don't even need to add the plug-in. Nevertheless, I wanted to include it in this list as it should be there in every app, even the ones built before APEX 18.1 :)

2. Select2

When a select list (or drop-down) has many values, it takes too long to find the right value. Select2 makes it easy to search for values, it also supports lazy loading and multiple select.


3. APEX Office Print

APEX Office Print extends APEX so it becomes possible to export to native Excel files and generate documents in Word, Powerpoint, PDF, HTML and Text, all based on your own template. It has many more features, I blogged about some before.



4. Dropzone

APEX 18.1 has declarative multi-file upload, but still, I love the Dropzone plug-in developed by Daniel Hochleitner. You can drag multiple files from your desktop straight in your APEX app. Daniel is one of my favorite plug-in developers. When he releases something, you know it will be good.



5. Modal LOV

This is a newer plug-in and I haven't used it that much yet, but I'm sure I will do. The nice thing with this item type plug-in is that it also supports Interactive Grid. Where Select2 stays within the page, this Modal LOV comes with a modal list of values (pop-up) which is great if you want to show multiple columns or need more context for the record you need to select.


There are many more plug-ins out there, most of them work on APEX 5.x and upwards. For example, Pretius has some cool plug-ins too, the one to create nested reports I recently used in a project. Another site you can find plug-ins is APEX-Plugin.com.

Autonomous Blockchain, It’s Almost Here! - 13-Jul-2018 18:56 - Insum Solutions

ICYMI (in case you missed it), Oracle is having a special live webcast on July 16, 2018. After several false expectations from the media, Oracle’s finally (probably) announcing the general availability of the Autonomous Blockchain service.

I have been longing for the day!

At Kscope18, I had the honour of presenting on how Blockchain technology could be relevant to an Oracle Application Express (APEX) developer. With huge leaps in how APEX supports REST services, especially in the recent 18.1 release (read Carsten Czarski’s blog post on how easy it is to integrate REST services in APEX), the APEX developer community is strongly positioned to capitalize on the potential that Blockchain brings to the applications we write.

My session focused on two popular Blockchain technologies: Ethereum and Hyperledger Fabric (HF). They represent a “permissionless” and “permissioned” blockchain respectively, with the latter being more relevant to the industry and natively more secure.

Hyperledger Fabric is the Cornerstone

If you have been watching developments in the blockchain arena, you will know that HF is a front-runner and will be the cornerstone of their Autonomous Blockchain product offering. From the information I have gleaned, we can anticipate a comprehensive and easy to use user interface (UI) for managing a HF network. Aside from management tools, we can expect a UI for compiling and deploying chaincode, and REST APIs that developers can build against.

Unfortunately, there was so much to talk about during my presentation, that I ran out of time as anticipated. I was not able to demonstrate and explain how I created a proof-of-concept application using APEX and HF.

 

Autonomous Blockchain it's almost here!

I will be repeating my Kscope18 presentation later in August. If you are interested to listen to the extended version of my session, please register your interest using this link, so that we can inform you when registrations are open.

A quick Overview of Hyperledger Fabric

Meanwhile, in anticipation of a launch announcement, I’d like to share a quick overview on how you could start looking at integrating HF with your applications.

The Hyperledger is a huge and growing consortium of large and small companies interested in developing next-generation frameworks for creating usable blockchains. It comes under the umbrella of the Linux Foundation and works very similarly to the more matured Apache Foundation.

There are many frameworks that the community has already developed, but the most prominent is Hyperledger Fabric. To make it easier for developers to create blockchain applications, the consortium created the Hyperledger Composer (Composer) tool.

Four Key Concepts

With Composer, developers, in collaboration with business owners, create a business network. It contains four

key concepts:

  • Model – a file that defines the assets, participants and transactions that are involved
  • Script – a file that contains the transaction (business) logic and is written in JavaScript
  • Access Control – a single ACL file that determines what participants can do on the business network
  • Query – a file with query definitions.

These are packaged and deployed on a HF network using either command line interface (CLI) tools or the Composer Playground. The latter is an easy to deploy and use web application that helps developers create, test and deploy business networks. To get a feel of what it is, check out IBM’s demo playground. Note that using Playground, you can create blockchains that run entirely off your browser’s local storage. But, be sure to export and save your hard work!

You can simulate interactions with the blockchain using the Playground, but the real proof of the pudding is to have your APEX applications interact directly with the blockchain. And that’s where REST comes in. Along with Composer, a simple npm install supplies a utility to restify the business network. It even generates a Swagger document that provides all you need to know on making REST calls against the blockchain.

Autonomous Blockchain more Robust

A side note. At Kscope18, I attended Emmanuel Abiodun‘s session on an introduction to Hyperledger technologies. Emmanuel’s an expert in blockchains. I was grateful to have a brief discussion on how “production-ready” Composer is for prime time. It’s a useful POC tool, but I think we can expect Oracle to provide a more robust platform with their Autonomous Blockchain service.

APEX is future (and enterprise) ready! Be even more prepared by starting to look at emerging technologies like Blockchains. HF has gone through a few major releases now, and using Docker, you can stand up a HF network even on your laptop! I’ll share more on the development process in August. Or if you can’t wait, please look me up on Twitter or drop me a note below.

“See you” at Monday’s webcast!

Want to learn more about APEX applications? Contact us!

The post Autonomous Blockchain, It’s Almost Here! appeared first on Insum.

The City of Montreal Recognizes Insum with an Intercultural Award

On April 17th, 2018, the City of Montreal honored Insum with the Abe-Limonchik intercultural award.  This distinction recognizes “exceptional contribution to intercultural relations and the promotion of diversity”.

The evaluation criteria for this unique award are as follows:

  • The distinct, innovative and exemplary nature of the practices put in place
  • The impact of having created a greater awareness of the richness of ethnocultural diversity
  • The improvement of intercultural relations by bringing together members of various communities and origins.
  • Commitment to the integration of members of diverse origins and their participation in Montreal society
  • The actions aiming to stop discrimination and promote ethnocultural diversity

Furthermore, the award has three categories, which are Individual, Enterprise, and Organization.

At the ceremony, the city recognized Insum’s involvement in several programs encouraging intercultural relations. These commitments lead to the hiring of several highly qualified employees. In addition, Insum makes a point of hiring new immigrants. As a result, they represent 35% of the company’s workforce.

 

Insum’s take on this Intercultural Award

We spoke to Michel St-Amour, President of Insum solutions and to Marie Cadoret, Administrative Assistant, and participant in one of Insum’s immigration programs.

A President’s perspective

“For us, this distinction recognizes our corporate environment or corporate ecosystem, if you will. It nice to know that we stand out in the industry and among our peers, and it also validates what comes naturally to us through our inclusive values. Furthermore, it tells us that there is a quality to what we are doing that you don’t find elsewhere. It’s not a cliché to say we accept people as they are, it’s for real. That’s because we make no distinctions between countries of origin or religions and we try our best to make everyone feel at home. Everyone has the same importance.

So our “over and above” effort in this area is recognized for all to see, and that’s gratifying. Of course, we are looking for people who have a strong desire to integrate into their new communities. But then I would say most people have this desire, anyway. And, when you give them the right conditions, well, they flourish. It is certainly a win-win for all involved.

This isn’t just a characteristic of our Montreal office either. For example, I just got back from our Peruvian office and it has the same culture of acceptance. A significant part of their workforce comes from other  South American countries, and the work dynamic is great. I think this is really good.”

An administrative assistant’s perspective

“I see this as an outside recognition of something that, internally, we knew we already had.  Our internal policies are great in respect of multiculturality. And, both management and employees respect differences. Also, everyone helps sustain this harmonious environment. That’s especially motivating.  Some companies’ values aren’t fully accepted by the employees,which of course, doesn’t work.  We (the employees) actually chose our values.  (ed. Note see Insum’s values here) and there are about 12 nationalities represented here!

The Abe-Limonchik award intercultural award

The Abe-Limonchik Intercultural Award was created in 2009. It is in honor of Abe Limonchik, a former city councilor of the highly diverse Montreal neighborhood of Côte-des-Neiges. Most notably, he worked in the promotion of ethnocultural diversity and the reduction of discrimination.

See also: Women in Tech

The post City of Montreal Recognizes Insum with Intercultural Award appeared first on Insum.

TypeScript and APEX - 12-Jul-2018 13:58 - Trent Schafer
TypeScript is a typed superset of JavaScript. That is, you can use the familiar JavaScript syntax you are used to, but where it gets enhanced is the fact it is can be strongly typed to give you compile time warnings.

The most example is the sum of two numbers. A typical JavaScript function would looks like:

function add(num1, num2){
return num1 + num2;
}


By looking at this code, by the function name and the return statement, we can see that the idea would be to add two numbers together. In JavaScript, depending what the user passed in, it could do string concatenation, implicit type casting.

So if you do:

add(1,2)

This will return 3

If you do:

add ("1", 2)

This will return 12 (string concatenation).

Since we want this function to always add two numbers, using TypeScript, we can change the definition to be:

function add(num1: number, num2: number): number {
return num1 + num2;
}

Now the TypeScript engine is always going to expect the inputs to be numbers. So in our TypeScript file, if we issue the same statement:

add("1", 2)

The compiler is going to complain and you won't be able to transpile into JavaScript code.



What's really neat, a colleague of mine Adrian Png, has gone ahead and created a TypeScript definition of the APEX JavaScript API reference (work in progress). Check out his project on GitHub. This project provides the documentation to start leveraging that good work that he has done, but for completeness, the basic steps are:

1. Ensure your project has been initialized with npm. Run:

npm init

..and follow the on screen prompts.

2. Install the library to your project folder by using npm:

npm install --save-dev https://github.com/fuzziebrain/orclapex-js.git.

What this does is installs the relevant node modules and updates your package json with the relevant dev dependencies.

3. Add a tsconfig.json file to your project:

{
"compilerOptions": {
"module": "commonjs",
"lib": [
"es6",
"dom"
]
}
}

4. Finally, add the following line to your TypeScript file:

/// <reference types="orclapex-js" />
 
Now, one of the JavaScript API's that has been done is the `apex.da` namespace, which has a function named `resume`. I'm using VSCode, so after adding that reference, there is also some code completion. Typping apex.da.resume give me this hint:



So not only do we get parameter names, we get to know the expected types. Its worth noting, if you don't add the reference or there is parts of the API that haven't been compiled yet, the TypeScript engine will complain about not knowing about that object.

OK, so now with that covered - how can we use TypeScript in our APEX projects.

It's not immediately clear from the apex-nitro documentation (credit to Adrian Png for adding TypeScript support into Nitro), but if you navigate into the examples folder you will find a demo-typescript folder - great, we can leverage apex-nitro to develop TypeScript libraries for our next APEX project. If you don't have apex-nitro installed, install with npm by running:
 
npm install apex-nitro -g
 
After you install, run apex-nitro config to set up your project. It should end up looking like this:



They key thing is that the JavaScript processor will be set to be TypeScript. Save that. Now, following the set up guide for apex-nitro for your application, you just need to do a couple of tasks.

1. Create a new before header application process with a negative sequence so it's the first thing that runs. Set conditions to owa_util.get_cgi_env('APEX-Nitro') is not null and source to apex_application.g_flow_images := owa_util.get_cgi_env('APEX-Nitro');

2. Now, you will want to add a reference to your compiled script. Despite the fact we developed in TypeScript, the output will be JavaScript. So go ahead and somewhere in your application, add the following reference:

wwv_flow_file_mgr.get_file?p_security_group_id=2246505718948672&p_flow_id=110&p_fname=js/app.min.js

Now, this was a really quick and dirty example of using apex-nitro. For for full options and usage, you should read the docs.

Additional resources:

TypeScript website: https://www.typescriptlang.org

There is a good article on FreeCodeCamp that is worth having a read of: https://medium.freecodecamp.org/when-should-i-use-typescript-311cb5fe801b
Upgrade to SQL Developer 18.2 ? - 12-Jul-2018 10:06 - Sven Weller

Why upgrade to SQL Developer 18?

This post intents to help you with the decision, if it is about time to upgrade. And if you are on the new version already, then you might find out about some new features that you might not have noticed so far.

I recently upgraded from version 17.3 to 18.2.

Here is a listing of my favourite changes. This listing includes new features, changed behaviours but also fixed bugs in either version 18.1 or 18.2. It is totally subjective. The focus is on SQL Developer only, I didn’t analyze changes regarding SQL Data Modeler, sqlcl or ORDS.

TL;DR;
Upgrade Now? => Yes!

Links

If you want you can go through the various documents of new features and bugfixes yourself.

Top new features or bugfixes

New Welcome Page

It is very likely you disabled the welcome page in a previous version. Go to Help/Start Page to show it. Then you can decide to keep it around by checking the appropriate check box in the lower left corner.

In the past this welcome screen was essentially useless. Now it has a section “RECENT” connections. It holds the list of the last five recently opend connections which I find myself using a lot.

The welcome screen still has a lot that can be improved (in red is what I dislike). But now it is a working and useful part of the tool.
sqldev_182_welcome_screen_b

Also interesting is the DEMO tab in the “Getting Started” section. Among others it links to videos about “Debugging PLSQL”, “Importing Data from Excel” and “Introduction to Oracle SQLcl”.

Improved Editor / Syntax Highlighting

Syntax Highlighting is arguably one of the most crucial features of any IDE.
A lot of bugfixes have been done for that.
Here is a quick list of changes/bugfixes with regards to the editor or syntax highlighting. The list is not complete.

  • Improved Arbori Editor:
    • syntax error highlighting
    • catch syntax errors before Arbori execution
  • Change Case as You Type was removed from the Code Insight Preferences page. Use the Code Formatter to set your identifier and keyword preferences, and format your code on demand instead.
  • SQL Injection vulnerability warnings for your PL/SQL
  • 27678178 PARSER DOESN’T LIKE NEW FOR CALLING A USERDEFINED CONSTRUCTOR IN A SQLBLOCK
  • 27718434 FORUM: SOME KEYWORDS ARE HIGHLIGHTED ONLY WHILE TYPING
  • 27803006 FORUM: BRACES () COLORING IS INTERMITTENT AND DIFFERENT FROM PREFERENCES
  • 27939025 FORUMS: BETTER PL/SQL SYNTAX HIGHLIGHTING/FORMATTER FOR CONDITIONAL COMPILATION SYNTAX

Since I use conditional compiling a lot in PLSQL the last fix is an essential one for me.

SQL Injection detection is a very promising feature. Especially when working with not so experienced developers. They easily might overlook some SQL injection issue and now the editor is able to warn about such scenarios. Jeff explains more about it here.

Btw: “Arbori” refers to the language used to describe parse trees during syntax analysis. Want to know more?

DBA Users Editor – SQL page now generates complete DDL for user including GRANTS

Way overdue. Happens a lot that you want a script to create the same schema that you have in some DEV environment and now you want to script the user creation for TEST and potentially PROD environments.

In the past the grants had been missing. A quick test of mine showed that all the grants that this schema needs seem to be there now. Granted roles, object privs, granted access to directory objects, granted system privileges. However grants that are given to other users are not included! So it is still not complete.

And remember, there also is a “create like” feature in the DBA panel.

sqldev_182_create_like

This “create like” has not all the grants that are on the DBA Users Editor SQL Page.

27169586 ALTER SCRIPT SCROLLS OFF PAGE, IMPOSSIBLE TO READ WITHOUT MUCH SCROLLING/RESIZE

I’m not sure, but I think I hit this bug very often in the past. And then it was annoying as hell. However we can’t access the bug numbers, so I’m not sure if that fix covers the problematic behaviour that I remember. At least it looks as if it is fixed now. Will monitor it closely.

Improved Code Formatter

I’m not a big fan of code formatters. But I know those matters to many developers. The bugfix lists are full of minor changes to it.

Two things I would like to point out:

  • Formatter: New Basic Preference: Convert Case Only

    This new setting means, the code will not be reorganized. Only the UPPER/lower case logic is applied. I personally like to have everything in lowercase (apart from some plsql method names where I use lowerCamelCase). So this setting allows me to quickly format SQL statements in the worksheet.
    sqldev_182_format_case_only

  • FORUM – ADDED FORMATTER OPTION: RIGHT-ALIGN QUERY KEYWORDS

    I know that many developers like to format their SQL queries in such a way, that the keywords are right alined. So this is possible now.

    sqldev_182_formatter_right_align
    Example Result

    -- left alined keywords
    select containerid
          ,status
          ,reason
          ,additional_info
    from table1 t1
    left join table2 t2 on t1.id   = t2.id
    where barcode   = :barcode;
    
     -- right alined keywords 
    select containerid
          ,status
          ,reason
          ,additional_info
      from table1 t1
      left join table2 t2 on t1.id   = t2.id
     where barcode   = :barcode;
    

Interesting new features or bugfixes

The following features are less deciding. So they might not be the sole reason why you want to upgrade. However they might be useful to know and to try out eventually.

  • Can now convert Oracle to ANSI Joins with context menu

    This feature sounds very promising. Unfortunatly I didn’t find the context menu yet, that allows to do so. So far I tried it only on a 11.2.0.4 db. Maybe the option uses functionality that requires a higher db version.

  • DBMS_XPLAN Added to Explain Drop Down toolbar menus
    sqldev_182_DBMS_XPLAN
    The new option does not create a plan immediatly. Instead it finds the sql_id and then produces a statement that uses the sql_id and then can read the execution plan using DBMS_XPLAN.

    select * 
    from table(dbms_xplan.display_cursor(
          sql_id=>'d7yr3jw0rj963', 
          format=>'ALLSTATS LAST'));
    
  • DBMS_XPLAN is the best way to get a nicely formatted execution plan.

  • Enhanced DB Search for dependencies and selected object types

    It is not clear what was enhanced. The search menu certainly looks the same. Also the search findings didn’t really seem to change.

    In case you don’t know, but search does cover view sources. This was already implemented a few versions ago.

    I use the DB Search a lot. And I welcome any improvements in that area, even if I don’t see them at first glance.

  • ORDS 18.2 bundled

    Tools/REST Data Services

    sqldev_182_ords_version

  • Extract variable/function prototype

    Refactoring code is something that SQL Developer supports since many versions now. It is surprising that almost no developers know about it or even use that frequently.

    This new option allows to quickly change expressions into local functions. This can save an aweful lot of codeing time!

    Example

    Somewhere inside my plsql code I have a concatenation.

    ...
    begin
    ...
        if v_json is not null then
          p_plate.additional_info := '{'||v_json||'}';
        end if;  
    ...
    

    I mark the '{'||v_json||'}' part, open the right context menu, choose Refactoring / Extract Variable/Function/CTE and give the function a new name, for example make_json_object.

    sqldev_182_refactor_expression

    The result is that my procedure got a local function. And the marked expression is replaced with a call to that function.

    ...
      --refactored function
      function make_json_object(v_json clob) return varchar2 is
      begin
        return '{'||v_json||'}';
      end make_json_object;
    begin
    ...
        if v_json is not null then
          p_plate.additional_info := make_json_object(v_json);
        end if;  
    ...
    

    The code would still require some rework, but it is a nice quick starting point.
    As always you can undo that change using CTRL-Z.

  • Allow explain/autotrace on selected text

    This I like a lot. You can mark a part of a bigger sql statement and get the explain plan for that part only. The marked code needs to be a valid SQL statement of cause.

    Highly useful for with clause statements. But also for expanded SQL queries.

  • 27962558 DRAG AND DROP FROM GRID TO EDITOR SHOULD QUOTE STRINGS

    The behaviour for drag and drop changed. I’m not so convinced about it, but I rarely use drag and drop from the grid.

    If we drag from a single varchar2 column, then all the values are copied, put in single quotes and separated by comma.
    However if we copy from multiple columns then the strings are not quoted.
    The quoteing only happens during drag and drop. Not when copying with CTRL+C, CTRL+V.

    I think this feature can be highly helpful, but needs some getting used to it.

Conclusion

There are several more changes, but those are the ones I am most interested in. As with any new toy we should check what is possible and how we can use it to improve our playing codeing experience.

Several of the new features are so convincing to me that I installed SQL Developer 18.2 now in all projects that I supervise.

Feel free to share what new features or bugfixes you like most!

Advertisements
Cross Row Interactive Grid Cascading LOVs - 12-Jul-2018 07:45 - Explorer

One of my main gripes about the Interactive Grid is that it performing cross row validations is quite a painful process. By this I mean, the user can modify various records and add their own; but I want to check if the entries are valid in relation to each other before submitting to the DB. For example: upon completing user edits, I want to check if at least one Admin exists, or check if all entered values add up to 100, or check if there are an equal amount of records of each type been entered, or check if there are no duplicated records… that kind of thing.

The challenges comes when you need to cascade an LOV across records:  A select list showing a custom set of records based on other rows in the grid. The problem is that the select list is based on a SQL statement and just cannot be based on the Interactive Grid Model (which holds the modified record values) or indeed the IG column value – even with the PLSQL bind syntax.

One trick I have used here to create a hidden item and then use an on change event to populate that item and then refresh the select list – however you have to reset that hidden item when clicking through records (apexendrecordedit or interactivegridselectionchange should do it) and it can be tricky.

How do I do it? Let’s take the “check there are no duplicated records scenario” from my example list above. I want to prevent the entry of duplicate records by disallowing them to enter duplicates in the first place rather than validate on save (more on this later). In my example I want them to enter Regions (e.g. Europe, Americas, etc.) and Notes – but they can’t add more than one Region of the same name to the Grid. You can follow the example here and see my code.

So the way I do it is to create the grid as normal – my columns are

  • REGION_ID column is a select list based on a query
  • NOTES column is a text item

SELECT region_name d,
region_id r
FROM regions
ORDER BY region_name

…standard stuff so far.

I have also created a new Select List Page Item on the page called P1_REGION_SELECT_LIST which is based on the following “PL/SQL Function Body returning SQL”


DECLARE

lv_lov_source APEX_APPL_PAGE_IG_COLUMNS.<em>LOV_SOURCE</em>%TYPE;

BEGIN
SELECT LOV_SOURCE
INTO lv_lov_source
FROM APEX_APPL_PAGE_IG_COLUMNS
WHERE application_id = :APP_ID
AND page_id = :APP_PAGE_ID
AND name ='REGION_ID';


RETURN lv_lov_source;

END;

I’m going to use this item as my template item – i.e. this item contains all the possible options. Rather than repeat the same SQL from the REGION_ID item, I’m going to reuse it by pulling the same SQL out from the meta-data from the REGION_ID IG Column. This way, if the item SQL requires a change… I just change it in one place. Also since I do not want to show this item to my users, I will add some CSS to hide it (I could’ve used a dynamic action – but it’s faster this way).

The idea is, when the user clicks a row in edit mode, a Dynamic Action on Row Initialisation fires which resets the select list entries within the IG column.

So in pseudocode, when I initialise my IG Row…

  1. I remove all select list entries from my REGION_ID IG select list column
  2. I set my REGION_ID IG select list column to have exactly the same entries from the template item P1_REGION_SELECT_LIST
  3. Then I loop around the modal and remove any entries that already exist as non-deleted records.
  4. If I get to the row I’m on; well I’ve just wiped out all the select list entries so I need to set the selected value back to the one held in the modal.

I have to do this for every click of a different record. This is because the select list REGION_ID is static for all rows; if I change the select list entries once, it the same changed-state for all record. That is why I have to destroy & rebuild the list every time. The good thing is that the model just holds values – so once the value is changed, regardless of how many entries are in the select list, it persists in the model.

You can see a working example, together with code examples here:
https://apex.oracle.com/pls/apex/f?p=116686

The deleted records cause great problems. Why? Because it’s possible the user can delete an entry, then add a duplicate non-deleted one, then Refresh Rows of the deleted record and press save… causing a duplicate. Furthermore, Duplicating Records and adding New Rows when the IG is at capacity requires custom built solutions.

Rather than explain the ins and outs of duplication preventing client side; here are a few things to consider.

  1. Add a server side validation to prevent duplicates
  2. At the very least a unique index protecting your table
  3. Use a PL/SQL based DML process with duplicate detection
  4. Add a before-submit dynamic action to check the modal for duplicates
  5. Use the Save “button swap” technique, combined with a model check (see above)
  6. Remove IG actions like duplicate from the IG in the advanced JS configuration area to reduce chances of duplicate creation (I have used this in my demo application)
  7. Consider how the Add Button can be enabled/disabled to prevent new rows been added when the grid is at capacity (i.e. when the maximum number of distinct rows can be added)

As explained earlier; this is my gripe that cross model validations are not out-of-the-box, that I can’t easily compare user entered/modified records and I have to plumb all the routes that the user has available to them to cause problems. It would be really great if this sort of thing was available out of the box.

The post Cross Row Interactive Grid Cascading LOVs appeared first on Explorer | Award Winning UK Oracle Partner.

Hola en esta oportunidad solo quiero compartir contigo algunas experiencias que he tenido con el uso de nombres de columnas de tipo formato título, es decir manteniendo las mayúsculas y mínúsculas.

Para ponernos en contexto, supongamos que creamos una tabla en el Taller de SQL de la siguiente forma:

Tabla Nombre: CMO_CUSTOMER
Columnas:
- Cust_ID
- Cust_FirstName
- Cust_LastName

Y seleccionamos el checkbook Mantener Mayúsculas/Minúsculas


Al finalizar el asistente tenemos creada la tabla con los nombres de columnas de esta forma:


Que inconvenientes me trajo el usar "Mantener Mayúsculas/Minúsculas" en Apex?


En primer lugar cuando creamos una consulta SQL en nuestra aplicación y queremos mostrar ciertas columnas de la tabla, es necesario encerrar cada nombre de columna entre comillas dobles.

select "Cust_FirstName", "Cust_LastName"
from cmo_customer

Si no lo encerramos entre comillas nos  muestra el error: Invalid indentifier


Entonces siempre debemos recordar de colocar las comillas dobles.

Eso no pasa si creamos la tabla con las columnas sin especificar el checkbox  Mantener Mayúsculas/Minúsculas. Ya que las columnas se van a crear todo en mayúsculas en la tabla, no importando si lo escribimos en formato minúscula y mayúscula.


Otro problema es a la hora de querer filtrar un reporte por medio de la URL.

Supongamos que tenemos un botón el cual filtra nuestro reporte pasando el parámetro por la URL.

A modo de ejemplo he creado un botón en una página y la acción es Redirigir a URL:

http://localhost:8080/apex/f?p=&APP_ID.:2:&SESSION.::NO:RP,2,CIR:IR[IDENTIFICADOR_ESTATICO_REGION_INFORME_INTERACTIVO]
_<NOMBRE_COLUMNA>:<VALOR> 

http://localhost:8080/apex/f?p=&APP_ID.:2:&SESSION.::NO:RP,2,CIR:IR[CUST]_Cust_ID:4 

Mi region del reporte interactivo de la página 2 tiene un identificador estático denominado CUST.

Por mas que pasamos el nombre de columna y su valor el informe interactivo no se filtrará:



 Lo que necesitamos hacer es colocar un alias de columna en la consulta SQL del Informe Interactivo

select "Cust_ID" "CUST_ID",
"Cust_FirstName",
"Cust_LastName"
from CMO_CUSTOMER


De esa forma se filtra el reporte sin problemas.

En el caso que usemos las columnas de la tabla sin especificar Mantener Mayúsculas/Minúsculas, no tendríamos ese problema de tener que colocar un alias de columnas si no quisieramos.

Cuando se crea el reporte por medio del asistente, éste coloca las comillas dobles a cada columna:

select "CUST_ID",
"CUST_FIRSTNAME",
"CUST_LASTNAME"
from CMO_CUSTOMER

 
Pero si nosotros ingresamos la consulta SQL manualmente, de igual forma funcionaría el filtro del reporte sin ningún problema.

select cust_id, cust_firstname,cust_lastname
from cmo_customer



Pasando esta URL en el botón:


http://localhost:8080/apex/f?p=&APP_ID.:2:&SESSION.::NO:RP,2,CIR:IR[CUST]_Cust_ID:4 

o esta: 

http://localhost:8080/apex/f?p=&APP_ID.:2:&SESSION.::NO:RP,2,CIR:IR[CUST]_CUST_ID:4

En conclusión a mi me gusta mas trabajar con los nombres de columna en mayúsuclas, que especificar si es mayúsculas o minúsculas.

Te dejo a ti que decidas la forma que más te gustaría trabajar en esta situación.

I’ve moved! - 11-Jul-2018 11:34 - Christoph Ruepprich

Please come by my new site at ruepprich.com.

Oracle Forms Migration - 11-Jul-2018 08:32 - Scott Wesley
This post is one of a series on what I learned while not at Kscope18.

I realise that's a month ago now, but the mind still ponders, and I've had these lined up for a while. Just got busy with a deployment, as you do. Went nice and smooth though, APEX sure does make staggered deployments easier with build options. Underrated feature. /tangent

Anyhoo - every now and then someone on the forum asks about the Oracle Forms migration tool. I've noticed it a bit recently, I guess that's a good thing.


Oracle Forms Migration. I guess what I'm learning here is that sometimes I'm a little shocked they keep at it, but I guess you've got to play the odds. And I haven't heard what was said with this slide.

I'd like to start and alternate list:

1) Setup APEX. Of course. OracleRAD will make this a breeze.

2) Create a schema that will serve as your conduit to your existing data. Privileges become additive, adding another layer to protect your data, which may include information not for the relevant APEX application.

3) Analyse what needs to change, what's broken, what's required.

Important note - workflow in a browser will be different from Oracle Forms.

While the components, capabilities, and IDE are astonishingly similar, I would never bother converting Form->Page. You'll spend more time cleaning when you could be doing more productive building.

The web universe just works different to Forms. It offers different opportunities. For instance, it's easier to build walk-through wizards for data entry. This can be important in touch device world, offering interaction with buttons instead of virtual keyboard is a plus.

Our applications now tend to generally require less keystrokes for information to be added, it's more of an interaction of buttons.

4) Import your Forms code if you must, but only for the ability to annotate existing code while you build the fresh interface. You may get better mileage by analysing straight from Oracle Forms.

The spectrum of code you'll need to ditch/refactor/keep will vary depending on the quality of the legacy application.

Continue analysis of business rules. Are they fully known? stale? Does workflow need improvement? Maybe concessions were made during the Forms development.

5) Enhance - not always necessary, but always good to add polish. But this notion of RAD? Yep, you get pretty darn good apps without even trying to add polish. And polish with a few Dynamic Actions go a long way.

And with APEX plugins, polish comes in big, free tubs. And yes, you should embrace plugins.

6) Test. Of course. And with clicky people. People who click about for the sake of clicking stuff. They're the people that are going to find those left field holes.

7) Train. While you can never replace face-to-face explanations, hopefully your application will explain itself.

8) Rollout, and enjoy applications that aren't easily obstructed as new versions of APEX arrive.
Applications that run fast, derived directly from business data, data delivered to the right people, with a natural history of who visited what and how long it took them.

We've got a keeper.
Next