ODTUG and the ODTUG Board – Why I Care, Why Vote

It’s that time of year, Nominations for the ODTUG Board are open (til Sept 23rd, get your nominations in here). I am up for reelection, and this time I really really care. More than in any other years, so much has changed, and so much is at stake.

Vote Karen Cannell for the ODTUG Board

VOTE.

ODTUG needs leaders who know what is going behind the scenes and can lead this organization through the minefield of challenges posed by COVID-19 restrictions. I am one of those leaders. I ask for your vote for reelection to the ODTUG Board of Directors for the 2021 term.

Read through to learn why you should vote, and why I ask for your vote.

Why I Care

This year COVID-19 brought drastic changes to the world. While we in IT are *relatively* unaffected by COVID-19 closings and restrictions – we can work, learn and network from home – there is no doubt that how we work, learn and network has changed drastically. ODTUG is actively – and needs to continue – adapting to our new mostly-virtual-hybrid world.

More than ever we need user groups to keep us connected, learning, and progressing. And those user groups – ODTUG in particular – must rise to the challenge of delivering not just content (the learning part) but the connections and camaraderie (networking) that is essential to build skills, confidence and leadership in our members. We need to keep our members engaged.

2020 Challenges

Most businesses – large corporations to sole proprietors, across all business sectors – have incurred significant setbacks, and have to adjust to conduct business virtually instead on on-site (or “on-prem”, if you will), all due to COVID-19. Cancellation of in-person events has touched us all. ODTUG was hit hard too, canceling Kscope20 in Boston.

ODTUG is a global not-for-profit organization (business) whose sole purpose is to keep you on up-to-speed on Oracle technology.. We have goals and budgets, and planning and tasks to meet those goals throughout the year. The ODTUG Board works behind the scenes to keep ODTUG operating. This year, like every other business in the world, our master plan was turned upside down by COVID-19.

ODTUG Has Adapted.

Forced to cancel KScope20, we launched our virtual Learn From Home series. Our webinars continue. In-person Meetups are on hold, virtual meetups proceed. Community activities, now all virtual. Kscope21 planning is underway (I am truly hoping to see all of you in Nashville) yet planning is by necessity flexible and cautious, as we need to adapt to what is allowed and required to host events by June of 2021.

On the surface, it seems not much has changed. Live events to virtual. Behind the scenes, your Board is doing everything possible to keep ODTUG operating at the same level of excellence as always. We will restore live events, starting with Kscope21 in Nashville, as safe as possible and as allowed, following all required guidelines.

Keep The Spark

Most important, we need to keep the ODTUG spark alive. ODTUG has a different feel from other user groups – more roll-up-your-sleeves technical, a great sense of community, networking and sharing (as opposed to consume-and-go). We are actively working to maintain this spark across all ODTUG events, live or virtual or hybrid, whatever we need to evolve to. With so many virtual events, ODTUG needs to bring the camaraderie and connections that ODTUG fosters to our virtual events as well as our live events, across all communities.

ODTUG Open Mic for Charity September 28 -30  Join us!

Our ODTUG Open Mic for Charity is one such event, Sept 28 to Sept 30 – join us to see the latest new stuff in your community, and to raise money for Cradles to Crayons our Kscope20 Boston Kathleen MacCasland Community Service Day charity.

Activities

Over the past terms as an ODTUG Director, I have served in many capacities, and essentially done anything I can behind the scenes to assist where needed in ODTUG operations:

  • Vice President, 2020
  • Secretary, 2019
  • Leadership Program Board Liaison
  • APEX Community Board Liaison
  • Marketing Director/Liaison
  • Governance Subcommittee
  • Conference Structure Subcommittee
  • Strategic Planning Subcommittee
  • Volunteer Engagement Subcommittee
  • Conference Committee Board Liaison
  • Board meetings and unscheduled discussions to handle assorted planned and unexpected ODTUG business, confidential and non-confidential.

As you can see I have experience across just about all that ODTUG is involved in. Plus the maturity and perspective of a senior ODTUG member and long-time developer/consultant, having observed and participated in ODTUG and other Oracle user group growth and changes since I first started volunteering in 2005.

Goals for 2021 Term

My goals for ODTUG for the coming term:

  • Kscope21 Ensure a safe, practical, Kscope21 event, in whatever forms it needs to be to serve our members, as allowed by the latest regulations and as required to meet our member needs in all communities
  • Volunteers Engage more ODTUG members in active volunteer roles. Virtual events, webinars, online meetups, even Open Mic for Charity afford more opportunities for persons to jump in and help for a few hours or a few days. These smaller chunks of commitment mean more persons can join in more easily. More volunteers means lower costs, saving on resources that can be better spent elsewhere. For this task I will actively be asking for persons to help out – Watch for my contact … and join us!
  • Budget vs Tasks vs Programs Ensure a slim practical, flexible ODTUG budget for the coming years, that allows us to survive in virtual mode and readily expand to introduce more programs and live events as allowed.
  • Keep the Spark Ensure ODTUG maintains its differentiating positive fun learning vibe in coming months and years, whether in-person, hybrid or virtual events.

Why you Should Vote

Your vote matters. ODTUG matters. Oracle User Groups matter. If you have read this far, you understand that I just about eat-drink-sleep ODTUG (much to my husband’s puzzlement!)

I am passionate about seeing ODTUG not just survive but adapt to thrive and serve our members in new, engaging, exciting ways in the coming term. All ODTUG communities have their unique needs. While partial to the APEX community (of course, I’m an APEX gal) each of our communities contributes to ODTUG as a whole.

All ODTUG members should vote, that includes you! Sometimes our elections come down to a few votes one way or the other, so please vote! If anyone wishes to talk about ODTUG in general, ODTUG topics, or why I am running, or you have specific needs from ODTUG that you want considered, please contact me at kcannell@thtechnology.com or DM on Twitter @thtechnology

Happy Coding, Happy Voting, and please consider a vote for Karen Cannell.

Thank you ~ Karen

ODTUG Innovation Award 2019

Nominations are open for the ODTUG Innovation Award 2019 – again, I ask you, our ODTUG members – to show us, let us know about the WOW! things you discovered – or built yourself – in the past year. To nominate for this year, read more here:
https://www.odtug.com/p/bl/et/blogaid=882&source=1

ODTUG Innovation Award

To get you revved up, consider last year’s winner, Stewart Bryson of Red Pill Analytics for his product Checkmate. An awesome tool – full product, a full IDE really – to control and automate your Oracle Analytics development and lifecycle. Read more about Check Mate here –
http://redpillanalytics.com/checkmate/

and
https://blog.redpillanalytics.com/checkmate-for-obi-free-ec48e30e0787

Explore Checkmate to facilitate your Oracle Analytics development – and remember to give Stewart Bryson a hand when you next see him.



Now back to this year’s Innovation Award:

To make a nomination for the award, you must be an ODTUG member (Associate or Full). If you are not a member, join now.

Nomination Criteria

  • Nominees may be individuals or teams.
  • Nominees do not need to be ODTUG members, but they must work with a tool that supports at least one of our communities (Analytics, APEX, Database, and EPM).
  • Open-source and/or collaborative efforts are eligible.

Nomination Deadline

  • Nominations open: Tuesday, April 23, 2019.
  • Nominations close: Monday, May 13, 2019 11:59 p.m. EDT.

APEX 18.2 Data Load Wizard no data found OR Let’s Revisit the Data Load Wizard …

A recent upload to APEX 18.2 meant a need to revisit our Data Load application, as we expect because we have customized the out-of-the-box Data Upload pages to meet our requirements. (Our customization: Load XLS and XLSX files, do our own set of validations/transforms with logging – read more about our Data Load Customization here )

no data found

This upgrade the showstopper was a “no data found” error on Next from the column mapping page. This customized data load applicaiton that has been working for years – since the first version w the DL wizard – 4.2? With 5.1 we adjusted for a new element and all was fine. With the upgrade to APEX 18.2, the no data found error was a dead end. So I started digging …

I posted on the Oracle APEX forum to ask the APEX community – always a good source. You can also get to this forum vi apex.world.

Turns out it is indeed a bug – given a Data Load Definition with 3 unique columns, and, upload data set does not include the 3rd unique column OR one sets that 3rd unique column to Do Not Load, one gets a quick “no data found” error. I suspect there may be variations of this, but this was our case.

Skip Validations

The workaround, for me, is to use the new (perhaps it was there before and I missed it, or did not need it? ) Skip Validations setting on the Data Load Definition, By default this is No. Setting Skip Validations to Yes, all loads OK.

I do not have any transforms or checks on this data load definition – those happen as part of our customization to the DL pages – so Skip Validations works just fine for me.

Skip Validations causes the DL Wizard to NOT perform any defined transforms or lookups. So clearly this workaround is not for everyone. Your mileage may vary. Try it to find out.

Another option, if Skip Validations needs to be No for you – that is, you have defined transforms and/or lookups that need to be done on data load – is to use the DL Wizard to load data to a staging table, then add a custom PL/SQL process to load data from the staging table into the real table. Not ideal, but gets the data in, the main goal.

A request to the Oracle APEX team : Please update the DL Wizard to accept true XLS or XLSX files, much like SQL Developer allows. Maybe the SQL Dev team will share their packages :). This would be a nice addition. When you have free spare time … Thank you!

Show Advanced Option

Another new option in the Data Load pages – new to me, at least, is the Show Advanced Options checkbox. The shows/hides the Use Application Date Format and Use Custom Date Formats options. If you do not have any date columns, or all incoming data is already formatted properly, you can use the default of Use Application Date Format (and make sure one is set!). If you have no date columns, no worries

ADD PICTURES

APEX Interactive Grid: JavaScript Basics Cheat Sheet

APEX Interactive Grid can be customized by JavaScript in the Advanced –> JavaScript Code attribute of the Grid or a Grid column.  Yup – JavaScript.

JavaScript may be out of the comfort zone for PL/SQL developers, even those who implemented extensive tabular form customization working with PL/SQL collections.  Time to say Goodbye to those collections … Welcome JavaScript!

The following is a collection of simple JavaScript lines most likely to be needed by a developer wanting to customize an Interactive Grid, or access data elements in the Grid.

This is not a comprehensive list or a complete function – just a simple collection of lines to give you an idea of the process and examples of  – a reference for – the syntax.

JSRefLines

Taken line by line:

var $te = $(this.triggeringElement);

This line gets the triggering element – the element that caused the dynamic action (DA) to fire. Usually we want to do this in a Grid to get the value of a particular cell – the one clicked on in this case. To get the value of the cell, we need to know which row id,  then we can narrow things down to a column using the column static id.

To get the row id, we start with the triggering element.

Next, we find the closest row – a ‘tr’ – and get the “id” data from it.  It helps to know that Interactive Grid has an “id” data element on each row:

closestTrDataId

var rowId = $te.closest('tr').data('id');

If our data element was data-thingamajig, we would access it via

 ... .data('thingamajig');

The next line gets the grid widget. The apex.region function is the preferred way to access region widgets.  “grid_static_id” is the Static ID of the Interactive Grid, set by the developer in the Advanced –> Static Id attribute of the Grid.  If you do not set one, a static id gets assigned, but it will be a long difficult-to-read identifier – it is much easier and better practice to set a meaningful static id, then use that meaningful static id in your code.

var ig$ = apex.region("grid_static_id").widget();

Given the Grid, we can now get the data model.  The data set is referred to as a data model.  There is a data model for every view:  grid,  chart, group by, icon, detail.  The following line gets the grid data model.

var model = ig$.interactiveGrid("getViews", "grid").model;

Given the grid data model, which we know is a table, we can get the record of the model, using our rowId which we identified via properties of the triggering element.

var record = model.getRecord(rowId);

Once we have the record,  we can access properties of any column in our Grid – any column in that record, using the column name – or the aliased name we assigned to the column.  Here my column name is COMM for commission.

var comm = model.getValue( record, "COMM");

model.getValue gives us the value of a cell in a record.  The corresponding model.setValue sets the value of a cell in a record.

if (comm < 100) { 
  model.setValue(record,"RIFF",'Y');
}

The above examples are easy on purpose. In fact, I bet any PL/SQL developer could follow these lines without headache.

Now you know how to access a row id, access the grid widget, access the data model of a grid, access rows – records – in that data model, and how to get and set values of the columns in that data model record.  That covers most of the basics!

You will need to learn more if you plan on complex customization or perhaps on building plugins.  For now, I recommend examining the grid in the Console, and reading the APEX widget js files.  Looking forward to APEX 5.2 (Oracle Safe Harbor), there may be documentation for all the Interactive Grid widget APIs.  Won’t that be nice!

Happy coding!

I highly recommend reviewing all of the examples in the Sample Interactive Grid packaged application.  And, read John Snyders’ hardlikesoftware.com blog posts on How to Hack the Interactive Grid.

 

Going to the Grid: What the APEX Interactive Grid Means to You and Your End Users

I will be speaking at Kscope 17 in San Antonio, TX on APEX 5.1 Interactive Grid:

Going to the Grid: What the APEX 5.1 Interactive Grid Means for You and Your End Users

Karen Cannell , TH Technology
When: Jun 26, 2017, Monday Session 2 , 11:45 am – 12:45 pm
Room: Cibolo Canyon 5
KScope_Pic
Topic: Application Express – Subtopic: APEX New Release

The long-awaited APEX 5.1 Interactive Grid region is here – but what does it mean to you and your end users? Need some practical guidance on when, why, and how to adopt the interactive grid region type? This session is for those who have seen the introductory demonstrations, are looking to upgrade to APEX 5.1, and need some advice. Does the interactive grid replace interactive reports? No more tabular forms? This session answers practical functional and technical questions raised by this new region type:

• When and why should I upgrade existing regions to interactive grid?
• Is there an upgrade wizard?
• What features will I gain; what features will I lose?
• What about customizations – will they upgrade?
• How does an editable interactive grid compare to my existing tabular form?
• Which features must still be manually written? Do I still need to write all that collection logic?
• How can I customize appearance?
• How can I add dynamic actions?
• Can I extend the interactive grid functionality?
• Will my end users like it? What about data entry users?

This session compares and contrasts interactive grids with the interactive reports and tabular forms we are familiar with. We will pay particular attention to maintaining or replacing features and common customizations in existing applications: checkboxes, 32K limits, cascading select lists, row-level validations, interactions between columns, and navigation between fields. How does the interactive grid region measure up? Should you upgrade all your interactive reports and tabular forms now?

How we do settings of which features are on or off for a particular Grid and for a particular column is now controlled by javascript. Sound scary? It is really not, you just need to learn where to put what piece of code when.  How to access data in the Grid data model?  We will show you that too.

If you cannot attend, don’t worry, I will be posting bits of that presentation here over the next few weeks.  IF you can attend, I will see you there!

Rest up ~ our Kscope 17 schedule is packed, so don’t expect much sleep or downtime.

APEX 5 IR Guts and Performance

APEX 5 Interactive Reports (IR) are powerful out of the box, but one can significantly improve performance by strategic settings of certain key parameters.  The full presentation covers all the options.

When your data volumes are low to medium, there may not be much noticeable difference. But when data volumes are high, or the query is resource intensive, the following advice and settings can have a significant performance impact.

In brief:

  • Tune your query, then fine-tune your SQL query. Any slowness here will be magnified in APEX
  • Check MAXROWCOUNT.   The more rows, the longer it takes APEX to retrieve them.  do users really need ALL the rows? OR is it reasonable to ask users to filter first. (APEX 5.1 Interactive Grid will include features to FORCE users to filter first – nice!) Consider  supplying users who need to Download All a separate pag,e os not all users are handicapped by a high MAXROWCOUNT value.
  • Check Max Rows Per Page
  • Check your Pagination setting.  The X to Y of Z is popular, but the “of Z” part is expensive – APEX needs to retrieve all rows then set up pagination. That All Rows part takes time.  Consider using X to Y, and adding a page item that calculates the Z separately. (THere are various options to do this, a topic for another blog post, ranging from a straight COUNT(*) to a sophistcated use of APEX_IR to get the count of the as-filtered IR query)

These are your big-impact settings.

For more info, see the full presentation via the link below or Slideshare:

As you move to APEX 5 Interactive Grid, these three settings till apply – but you have better pagination options, and pagination works differently – heck, all of Interactive Grid works differently – better!  So watch here for upcoming information on Interactive Grid.

APEX5 IR Guts and Performance

Smart use of the IR settings makes for a better end user experience ~ and that’s the whole point!