K Cannell for ODTUG Board ~ Keep the Spark Alive

Vote for Karen Cannell for the ODTUG Board of Directors

I seek your vote for reelection to the ODTUG Board of Directors, so I may continue to actively establish and maintain ODTUG as the center of excellence for Oracle developers.

Vote Karen Cannell for the ODTUG Board

ODTUG has a spark, an indescribable vibe that you just don’t find at other user groups or conferences. We are technical, committed, passionate and fun. Something happens when top technical experts and Oracle product teams combine, elbow to elbow to share and talk tech.  As an ODTUG Director, my job is to make sure that spark continues.

There are many challenges in doing so – engaging members, conference attendance, leading (not bleeding) edge content, member recruitment, quality practical technical resources, marketing what-where-when-how – all these balanced correctly combine to keep that ODTUG difference alive. That quality, that vibe, is our distinction – ODTUG is the best resource for Oracle developers.  It takes work to keep it that way. And I love doing that unsung behind-the-scenes work.  I gained from ODTUG, I have the experience to give back. I look forward to serving ODTUG for the coming years..

How developers work and learn continues to evolve – ODTUG must keep pace by delivering practical, actionable advice and training on legacy and emerging technologies. For all experience levels – novice through expert.

I am a senior ODTUG member, developer and consultant. I bring a quiet, steady, long-term perspective to the Board that is needed to balance the enticements of the “next new thing” with the need to deliver practical, get-the-work-done knowledge to our members. Please support my reelection to the ODTUG Board of Directors.

Experience / Attributes

Experience and attributes I bring to the ODTUG Board include:

  • Insight on developer needs and challenges gained via 30+ years of development experience, across large and small companies, government and private, large to single-person development teams.
  • Insight on user group operations gained through 13 yrs volunteering at local, regional and national user groups as reviewer, monitor, conference committee member, presenter, attendee and editor. As a board member I will have greater ability to solicit more content in more diverse formats.
  • Eye for timely, quality material on the latest Oracle tools, gained through 8 yrs as Editor and writer, for ODTUG, IOUG and APress
  • Quiet steadfast persistence to get tasks done, even the less glorious ones.
  • First-hand experience with major Board committees and activities:
    • Leadership Program
    • Emerging Technology Committee
    • Marketing Liason
    • ODTUG Innovation Award
    • APEX Community Liason
    • Various other committees on future not-yet-to-be-disclosed projects. All good stuff that may or may not be going forward, depending on research.

Read more details about my involvement in these Board activities, below in this post (just scroll to the bottom)

Reflections

As the ODTUG Board of Director election for the 2019-2020 approached, I reflected on my term as an ODTUG Director, on ODTUG in general and why an running again. This started small, then grew.  There was lots of time and experience to cover!

ODTUG Board of Directors Election

Crazy

You want to what? my husband asks. Then he laughs – that kooky, you-are-certainly-crazy-girl laugh. The one that says, “I have no clue why you do this, but clearly you love it, so have at it”.

Why do I love ODTUG?

Why do I do this stuff?

ODTUG is where I learned to be a mature technical professional. Sure, I had technical Oracle skills before I got to ODTUG. I worked at General Electric writing automation engineering drafting applications – code that produced production drawings. At Computervision, I wrote CAD-CAM Architecture, Engineering and Drafting packages. I used bit of Oracle at CV, about when that Internet thing came to be. Then I worked in state and federal government (fisheries) agencies, on Oracle databases. Forms, Reports, Discoverer, Designer. Then came Portal, then HTML-DB.

I was privileged to attend an IOUG Live conference – what a great way to learn! I volunteered – reviewing conference submissions. Presented! On Oracle Forms. Helped out with the IOUG SELECT Journal.

Then someone suggested I try ODTUG. They thought it might be a better fit, since I was a developer. Thank you, Bob Reidman, for that not-so-subtle nudge.

About that ODTUG Spark

ODTUG has a spark, an indescribable vibe that you just don’t find at other user conferences. We are technical, committed, passionate and fun. Something happens when top technical experts and Oracle product teams combine, elbow to elbow to share and talk tech. That spark was evident in the first ODTUG I attended. And I don’t even remember which one it was. I got accepted to speak,on Oracle Forms and again, and JDeveloper for PL/SQL Developers (literally days before they split off SQL Developer as a separate product). I attended – still attend – both ODTUG and IOUG COLLABORATE conferences.  By speaking and volunteering, I gained – gain –  confidence in my skills, in myself as a professional.  Good stuff.

But something is different about ODTUG Kscope. The level of technology, a bit higher. The intensity of learning, a bit higher. The technical focus and quality of presentations – higher. Willingness of presenters  and attendees to sit down and talk shop – much higher. No one scatters at the end of the day – they hang out and gab, About Oracle and related technology. Geeky stuff. Non-stop learning. The connections made I use throughout the year – if I get stuck, I know who to go to. That was before things like the ODTUG listservs (now retired). And before the Oracle forums. And before apex.world.  Now when I go to the Oracle forum or apex.world, I know who is answering – and someone always answers. Cool!  Can’t buy that anywhere.

Getting Involved, My Story

ODTUG Technical Journal Corner

ODTUG Technical Journal – submit papers now!

Of course I soon volunteered for ODTUG.  Reviewing conference submissions, writing book reviews, then as Assistant Technical Editor.  Then as Technical Editor when Donna Richey-Winkelman retired. (Thank you for all I learned from you, Donna!)  At one point I was also Associate Editor for the IOUG SELECT Journal.

The beauty of being an editor is that one gets to read content from all over, and meet the experts and make connections from all areas. 

All communities, all topics, I got to learn more, every month,  I got to meet more people in other user groups, more people at Oracle.  I guarantee many persons got tired of seeing me, they knew I was asking for another column, or another article.  I wrote articles. Edited articles. Wrote book reviews on early versions of the JDeveloper Handbook, by Peter Koletzke and Paul Dorsey. What a privilege to learn editing from Peter Koletzke! (Many Thank You’s, Peter!)

Observations

As an editor I got to see a lot. Observe a lot. Experience a lot of ODTUG and Oracle technology in general. Traditional “Developer” roles changed – are changing. Traditional “DBA” roles changed, and are changing. With Dev-Ops, the roles are even continue to blur.  How do we change with the technology?  We turn to ODTUG for the latest sound technical resources.  As an Oracle developer, I must evolve with the technology – I cannot do that alone – I can do that with resources and connections like I find at ODTUG.

Back to ODTUG.

The connections I made through ODTUG convinced me to go into consulting. (Thank you Scott Spendolini and Doug Gault!).

 I would never have had  the courage to make the jump to consulting without the support of ODTUG and the connections I made therein.

With the friends I had made in ODTUG, I knew I had the knowledge, I knew I had the technical support I needed, and I had the consulting support I needed. Fortunately, TH Technology continues to work for me and my clients.

I keep up with the latest versions of the Oracle database, or APEX, of ORDS, SQL Developer, and all sorts of earlier tools – JDeveloper, Application Server, HTTP Server,  Discoverer, (I am dating myself …).  By presenting at, and attending ODTUG Kcope, and attending webinars, and meetups when they are local, I have a bounty of training that I cannot match anywhere.  I do not have the time nor the budget for formal weeks of training on one or two topics. As a consultant, it is my job to stay ahead of the curve – to advise my clients on the next steps for their installations. Fortunately I do best when I am learning – good thing in this field, as it never ends.

Important Board Activities

This term as a Board member, I am engaged in the following activities.  If/when elected to a new term, I wish to follow on with these activities, passing some on to newer Board members, and in turn rolling on to new committees as they may fit ODTUG’s strategic direction.

  • Leadership Program, Board Advisor – The ODTUG Leadership Program prepares selected ODTUG members for leadership positions within their current organizations, or  for that next step.  LP persons read a leadership textbook, attended a series of online presentations/discussions and execute a group project, the topic of which changes year to year. The LP is NOT “how to be an ODTUG leader”.  However, when we do our job right in the LP, the graduates want to stay on and give back to ODTUG.  It is hugely rewarding to watch younger (time-wise, not age-wise) workers grow as the program progresses.  It is fascinating how persons grow within this simple program. Due to certain opportunities, I initiated a post-LP mentoring program, underway now, and possibly ongoing.  I know I am getting a lot out of this – I hope the participants are gaining as well.
  • Emerging Technology Committee – Investigate emerging technologies such as Blockchain, Artificial Intelligence (AI), Machine Learning (ML), Internet-of-Things (IOT) and others to discern what our members need to know about, how much and when. There are lots of new cool IT things out there – which do you as an Oracle developer, EPM product manager related technologist need to learn?  Know about is one thing – need to learn to stay relevant and productive at your job is another.  ODTUG strives to deliver some of the former, more of the latter, the Need-To-Learn material.
  • Alternative Revenue Committee – Investigate alternative forms of revenue for ODTUG. As a non-profit organization, we watch expenses. We aim to give our members the best technical resources and the best conference for the best price possible.
  • Marketing Liaison – Review and recommend on ODTUG and ODTUG Kscope marketing materials – social media, email blasts, conference advertising.
  • APEX Community Liaison – The APEX community essentially runs itself, yet there are still needs for planning for ODTUG Kscope community activities, exchanging requests from the community, planning webinars, and assistance in planning meetups.

I am also involved in committees on future, yet-to-be-disclosed projects. All good stuff that may or may not be going forward, depending on initial research.

Volunteer Activities

  • Editor’s Choice – While serving as Editor of the Technical Journal, each year there was a corresponding Editor’s Choice Award for the best white paper – remember them – out of the ODTUG Kscope conference materials.  Presenters used to submit both a white paper and a presentation for each conference they attended.  The white papers served as materials for the ODTUG members, part of the overall ODTUG library. The Editor’s Choice reviews – an annual read-fest where a small group literally read and graded/ranked every paper submitted to the conference.  At one point there were 125 papers. The beauty of the Editor’s Choice reviews was that one got to read the conference material before the conference. Like attending the conference before the fact.  I learned more about Hyperion and Essbase through those papers than I ever knew existed.  By far, our Hyperion/EPM community is the most prolific!
  • Pants On the Ground – In 2011, as part of our Long Beach conference, I participated in the general session reality-TV vote-off.  I got to learn the American Idol Pants on the Ground song, and gave my husband (and no doubt many others) another good laugh.  For kicks and grins, check out those 2011 general session videos. Always practice your dancing before an ODTUG Kscope.
  • ODTUG Volunteer Award – In 2012, I was awarded the ODTUG Volunteer award – a surprise for sure. And a huge honor. And yes, they caught me off guard on that one.
  • ODTUG Innovation Award – When the Editor’s Choice was retired – mainly due to lack of papers (who writes White Papers?  The world writes Blogs now) – I initiated the ODTUG Innovation Award in 2016. The ODTUG Innovation Award honors technical excellence and innovation in the use of Oracle technology over the past year.  Innovative, Wow! stuff.  We have had great entries every year – and awesome winners:  Mia Urman, Vincent Morneau, and Stewart Bryson.   Any many more to come!

The ODTUG Innovation Award opens in April, closes before ODTUG Kcope in sufficient time for a panel of judges to evaluate the submission and award a winner, announced at ODTUG Kscope.  Best luck to all future submissions!

  • APEX Conference Submission Reviews – I am honored to review conference submissions for ODTUG Kscope and other major conferences.  This is a natural followup to reviewing Editor’s Choice papers.  I have learned through the years what works and what is new and what is not yet – and I have fun doing it.

Giving Back

In short, I originally joined ODTUG to learn. I originally volunteered to give back. As a senior ODTUG member, developer and consultant, I bring a quiet, steady, long-term perspective to the ODTUG Board that is needed to balance the enticements of the “next new thing” with the need to deliver practical, get-the-work-done knowledge to our members.

ODTUG Mission

ODTUG, as an organization, has a mission:

To establish members as leaders in their organizations through education, timely access to the latest information, communication, and networking within the community, and provide a united voice to influence the strategic direction of Oracle and other vendors.

As long as ODTUG follows that mission, I can keep working, keep learning, keep current technically, keep a great bunch of friends and connections.  And be a solid technical professional. Help me forward ODTUG’s mission, for all our members.

I ask for your vote for reelection to the ODTUG Board of Directors to ensure that the ODTUG spark, and the ODTUG flow of knowledge continues.

Happy Coding!

Karen

Vote Karen Cannell for hte ODTUG Board

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ODTUG 2018 Elections ~ Vote Now!

Elections for the 2018 ODTUG Board are underway – Vote Now!

Calling all ODTUG Members:  Exercise your right as an ODTUG member and vote for the Board of Directors.  This may be the most important thing you can do for ODTUG.

(Not an ODTUG Member? You are missing out – see why to join ODTUG and become a member here)

Vote for ODTUG Board

Why vote for the Board? Why is it important?

ODTUG Board members determine the current and future direction of ODTUG.  Board members set strategic goals for the organization at the beginning of the year, and execute on that plan throughout that year and into the next. We strive to stay on budget while launching webinars and other educational sessions, marketing campaigns to spread the word and attract new members, and of course there is our fabulous Kscope conference. We are also your voice to Oracle, bringing members in touch with key Product Managers at ODTUG events, online and in person.

For the Board to do our job, we need to hear from you, our members.  Starting with who you want as Directors determining ODTUG goals and direction.

Board members face tough questions in the coming years:

  • Do we need more (or fewer) Meetups? On what topics?
  • What is the direction of the Database Community?
  • Where do tools like Node.js and Docker belong within ODTUG, or do they belong at all?
  • How can ODTUG reach and engage more millennial members?
  • How do we provide our members with the latest and best in Oracle tools technology, as that technology is changing under out feet, or rather, above our heads into the Cloud.

It is your right – and your responsibility – as an ODTUG member to vote in the candidates you feel can best form our future in the direction you feel it should go.

By now you have received your voting email from Association Voting providing you with an email address and Member ID. Please use that information to log in,  learn about our candidates and vote.

( Can’t locate the email? Click here and then click on the help button to request the information be resent.)

Find that email, and please do two things:

  1. Read all the candidate’s statements carefully.  Then
  2. Vote for the candidates you feel are best able to guide ODTUG for the next few years.

Let’s keep ODTUG serving our members in the best way possible.

Thank you, and Good luck to all 2018 candidates!

APEX Interactive Grid: Customize Toolbar

APEX Interactive Grids allow customization of the Toolbar, the Grid Menu and the Column Heading options using JavaScript in the appropriate Grid or Column Attributes Advanced –> JavaScript Code element. This post discusses how to build a function to make simple changes to the Interactive Grid Toolbar.

widget.toolbar.js

The toolbar is implemented by a toolbar widget ,which is created by the interactive grid widget. To learn more about the toolbar widget, read library file widget.toolbar.js.  That file documents the properties of the toolbar widget,  the toolbarData array, control types and their properties.

toolbarData

toolbarData is an array that describes the toolbar – the buttons, fields and menus visible to the user, and their actions. Toolbar objects are grouped into control groups.  Each control group has one or more controls, of type Static, Text, Select, Button, Menu, Radio Group or Toggle.

By default, the toolbarData is based on the Grid default options and declarative settings.

Developers (you) can override the toolbarData property by adding a function to the Advanced–> JavaScript Code section of the Interactive Grid Attributes. Basically, you write a function that resets the toolbarData array to the configuration you want.

There are two general approaches to writing an override function:

  1. Totally replace
  2. Copy and adjust

That is, your custom configuration function can completely (re)define the toolbar, or, it can make adjustments to the default or current toolbarData.    Which approach is best depends on your requirements – the degree of customization required.

If you are totally replacing, you can fill the array as you want. You might want to examine the default toolbarData to see what is possible.

If you are adjusting, you need to understand the default toolbarData array to know where to add your customization.

Default toolbarData Array

By default, for an editable grid,  there are 7 control groups in the toolbar, as shown below:

toolbarDataArrayLabeled

Note that the reports, views, actions2 and actions3 toolbar groups are conditionally visible: the reports group becomes visible when there are multiple reports, the views group becomes visible when there are multiple views; the actions2 and actions3 groups become visible when edit mode is on.

To examine all of the elements in the toolbarData object (as well as all other objects of the interactiveGrid widget) use this command in the Console:

apex.region("emp_igrid").widget().interactiveGrid("option","config")

using the static id of your interactive Grid for “emp_igrid”. This command says, Show me the config options for the emp_igrid Interactive Grid region.

Examine the returned contents by clicking through all the objects.  Look at the toolbarData object, expanding the elements beneath it.  After a few clicks you will no doubt recognize menu and button elements of the interactive grid Actions menu and other toolbar features.

The default toolbarData array has 7 elements, for 7 control groups, shown below:

toolbarDataArray

If we look at just “actions1” object, you will see that it is a menu containing  the familiar elements of the Actions menu. This image shows the download dialog menu element expanded.

actions1Download

Let’s say our users do not like doing two clicks (Actions –> Download) to get to Download – they want a Download button on their toolbar.  In fact, they want it all the way over to the right.

To do this, we need to construct a JavaScript function to adjust the toolbarData to add a Download button in the actions4 (the rightmost) control group.

This function does the trick:

function(config) {
 var $ = apex.jQuery,
 toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(),
 toolbarGroup = toolbarData.toolbarFind("actions4"); // this IS the rightmost GROUP WITH the RESET button

// add a Download button next to the Reset button
 toolbarGroup.controls.push( {
 type: "BUTTON",
 action: "show-download-dialog",
 iconBeforeLabel: true,
 hide: false,
 disabled: false
 });
 
 config.toolbarData = toolbarData;
 return config;
}

The toolbar stuff adds the button, with the action show-download-dialog.  The show-download-dialog action is the same definition found while examining the actions menu.

If you are a PL/SQL developer, and relatively new to JavaScript, the above JavaScript is very readable:

  • Copy the default toolbarData – we will make changes to a copy
  • Get the actions4 toolbar group
  • Add (Push) the download dialog to the actions4 toolbar group
  • Copy the modified toolbar to the return config

Very easy to follow.  But this is a very basic example.  If you will be doing a lot of such customization, pursue additional JavaScript training.  Hint: Remember that JavaScript is case sensitive, where PL./SQL is not.  That alone will save some grief.

Note that Download is a declarative option –

DeclarativeDownload

So, if/when a (the next?) developer sets Download Off … your newly-placed Download button disappears.  The declarative nature of the show-download-dialog action is contained within the action – our moving the button around does not overwrite that declarative toggle.

One can also turn off Download (and other features) programmatically. To do so, we use this command:

config.features.download = false;

as part of you Advanced –> JavaScript Code function, or in a dynamic action.  This command will also turn off your newly-placed Download button.

This simple example show how to add one button – you can of course add more, or redefine the entire toolbarData area.  For additional, more complex toolbar customization examples, see the examples in the Sample Interactive Grid packaged application.  And read the Interactive Grid posts in the HardLikeSoftware blog by John Snyders of the Oracle APEX team.

Interactive Grid is a great new region type – and it is still evolving. This writing is for APEX 5.1.2 – APEX 5.2 may bring more declarative options, and/or may bring documented APIs for customizing the toolbar – who knows.  Stay tuned!

 

 

 

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.

 

APEX Interactive Grid: Column Widths and noStretch

Setting column widths in Interactive Grid is totally different than how you may have set column widths in Interactive Reports.  While it is nice to blindly let APEX and the browser handle it, there are times when you need to explicitly set a column width, or at least set the width of one column relative to another, and for that you need to know how Interactive Grid handles column widths.

Recall that Interactive Reports let the browser figure out the column width, using table-layout:auto;  We could do little tricks like making the column heading longer or shorter to trick the browser into making the column wider or narrower.  That won’t work with Interactive Grids.  Interactive Grids are Fixed Table Width – table-layout:fixed;.  APEX will, by default, try to fill the full table width with the displayed columns, adjusting  – stretching or shrinking – the width of columns to  fit the full width of the table.

So how to set Interactive Grid column widths?

First,  the Width property (visible in the Appearance property group) for an Interactive Grid column does not set the width of the column.

ColAppearWidth

Instead, run the Interactive Grid, then set the column widths interactively using Drag and Drop on the columns themselves.

Select the || divider to dynamically adjust column width:

ChangeWidth

Select the 4-way divider between columns to activate Drag and Drop column order:

DragDrop

Hover over the divider between column headings to grab either the column width adjustment icon or the drag-and-drop column order icon.

Adjust column widths and column order as desired.  (Notice that APEX will adjust column widths so the full width of the table is filled.  This auto-adjustment may not be what you want – more on “noStretch” later on in this post).  Then save the report: Action –> Report –> Save.   The column order and the relative column widths are saved with the report.   When a user adjusts column widths, or does any other actions, a Reset action will restore the column order and column widths to the default set by the Developer’s Save Report.  Much easier that messing with numbers!

reportSave

You can, in the Column action, set a minimum column width. This sets just that – sets a minimum column width for the column that APEX tries to honor.

ColMinWidth

What about Max width?  You may have a Yes/No column, or a single-digit column, and do not want that column to get auto-stretched to some absurd-looking width.

SillyWidth

 

This is where the noStretch option comes in.

Setting noStretch on a column will hopefully (Oracle Safe Harbor) be declarative as of  APEX 5.2.0.

Until then, to set noStretch, as of APEX 5.1.1, in the Advanced –> JavaScript for the column you do not want stretched, set the noStretch grid column option to true:

function(config) {
   config.defaultGridColumnOptions = {
       noStretch: true
   };
   return config;
}

If you want noStretch true for all columns, the solution is similar, but this time we adjust the configuration in the Advanced–> JavaScript region for the Grid:

function(config) {
   var cfg = cfg;
  cfg.stretchColumns = false;
  return config;
}]
    

With noStretch on, APEX will not resize your noStretch column(s) to fill the full table width:

noStretchOn

 

While this works perfectly if the current view is your Grid view, remember that there are also Chart, Group By, Icon and Detail views – the above code will fail if the current view is not Grid.  So one should make that code that code a bit smarter, so it only acts on – changes the configuration for – a Grid view:

function(config) {
   var cfg = cfg;
  "views.grid.features".split(".").forEach( function (p) {
      if ( !cfg[p] ) {           cfg[p] = {};      cfg = cfg[p];  });
  cfg.stretch.noStretch = true;
Columns = false;
  return config;
}

In version APEX 5.1.0, setting noStretch is a bit different.  If you can, upgrade to APEX 5.1.1 or higher.  There are significant updates to Interactive Grid widgets and APIs in APEX 5.1.1.  IF you cannot upgrade and need noStretch, it can be implemented per column, by adding JavaScript to the Page Execute When Page Loads element:

var igrid = apex.region ("my igrid").widget().interactiveGrid("getViews", "grid");igrid.modelColumns.COLUMN1_NAME.noStretch = true;igrid.modelColumns.COLUMN2_NAME.noStretch = true;
igrid.view$.grid("refreshColumns");igrid.view$.grid("refresh");

That’s it!

I highly recommend checking out the APEX Sample Interactive Grid packaged application. Your best source of Interactive Grid documented examples.

Good luck!
  

 

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.

Read XLXS Files from APEX 5.0 and Higher – One (or Two) Ways

In my work as a consultant I often have the task of getting data from MS Excel spreadsheets into tables in the database.

If this is a one-time load, the process is simple – use either SQL Developer or, easier yet, use the Data Load Workshop in APEX:  SQL Workshop –> Utilities –> Data Workshop.

If the requirement is for a repeated load or many files, I need to set up some automated process for reading in the data.  I do not want to ask my users to open the XLSX files, save the data to CSV, then upload the CSV files.  That makes for unhappy users, at best.

I am sure there are several options for load XSLX files into Oracle data tables.  I am going to focus on a two I have used (and continue to use) successfully across several clients, across many projects.  And another option that used to be a very good option, that goes away with APEX 5.0 and higher.

Let’s do Bad News First.

The Option That Goes Away:

With APEX Listener, now Oracle REST Data Services, ORDS, we gained a wonderful utility for loading XLS or XLSX files into the database.

I will not elaborate on that here, for reasons which will be obvious in a bit.  I did cover that option, and several other Data Load options, in this presentation, How Do I Load Data, Let Me Count the Ways..  Most of these options still apply.  Yes, it is a PowerPoint, but gives you enough detail to get going.

This ORDS-based utility works fabulous for APEX versions less than APEX 5.0.  It loaded data into a collection. You can process your data however you wish from there.  However, as of APEX 5.0, APEX uses JSON to move data, and the ORDS Excel file upload function no longer works.  Joel Kallman explains this clearly in his blog, Let’s Wreck This Together, here.  It will still work for APEX versions < 5.0 – that is, APEX 4.2, APEX 4.1.  But beware, if you are using this Excel-Upload option of ORDS in APEX 4.1 or APEX 4.2 applications, you will need to make some changes when you upgrade those applications to APEX 5.0 and above.

The XLSX Option That Still Works – READ_XLSX

Thanks to Anton Scheffer from AMIS, we have a PL/SQL package that reads .XSLX files from a file in a file server folder that has been mapped to an Oracle directory.  That sounds like a mouthful, but it is simple.

You have files in a folder.  You or your system administrators arrange for that file system folder to be available to the database – usually a mapping of the physical folder to a virtual folder on the database file server.  Then your DBA creates an Oracle directory that points to the folder, physical or virtual, on the database file system. And grants appropriate grants so you can see and read and/or write from /to that Oracle directory.

Now Anton’s package, READ_XLSX comes in.  download the package, read Anton’s blog post.  I am only going to summarize here.

READ_XLSX consists of two main functions, one to read the contents of an XLSX file into a BLOB (named file2blob), and another to read the contents of that BLOB into records (this one is named “read“).  Cast the results of the read function as a TABLE, and voila, you have the results of your XLSX file.

This statement is the guts of it:

SELECT * FROM TABLE( as_read_xlsx.read( as_read_xlsx.file2blob('MY_ORA_DIRECTORY',
 'MyExcelWorkbook.xlsx' ) ) );

Now if you are like me, you do not want ot have to keep typing that SELECT statement.  So I turn it into a view:

CREATE VIEW MY_SPREADSHEET_V AS SELECT * FROM TABLE( as_read_xlsx.read( as_read_xlsx.file2blob('MY_ORA_DIRECTORY',
 'MyExcelWorkbook.xlsx' ) ) );

Now I can query from the view the same as I query from any other table or view I have access.  And incorporate queries from that view into any packages, procedures and functions that I build to meet the requirements of the task at hand.

When I have a series of files in a folder, I use a version of GET_DIR_LIST to read files from the Oracle directory.  (GET_DIR_LIST is a java utility, wrapped in PL/SQL, that returns the list of file names in a folder in a one-column table). You probably have your own method of doing the same.  Then, in a loop, I use dynamic SQL to point my view to the next file in the loop, call my loading procedure to process the contents of that file, then move on to the next file.  OF course I have all kinds of validations and error checking in there, as much is needed depending on the task at hand.  Once configured, the process is clean, reusable, easily customizable and – best of all – works in APEX 5.0 and above.

I did not use Anton’s READ_XLSX directly.  Why?  At the time I downloaded it (years ago now), Anton’s package handled cells of up to 4000 characters.  The spreadsheets I had to load contains some cells with > 4000 characters.  So I made a slight adjustment to handle CLOBs.  I described those customizations here.  I suspect most persons will not need such a customization.

Another (Better?) Option – EXCEL2COLLECTION Plugin:

Is READ_XLSX the only way? No. Seems like too much work? Yup, it can be.  Anton apparently thought so too, because he came up with EXCEL2COLECTION, a utility APEX Plugin that, with less coding than the READ_XLSX package, will read data from an XLSX, XSL, XML 2003 or CSV file into a PL/SQL collection.  I have also used this plugin successfully across several customers and projects.

I have used the EXCEL2COLLECTION plugin in conjunction with a customized instance of the APEX Data Load Wizard to enable simpler (from a user perspective) upload of XLSX files.  I describe more of that process here.  The APEX Data Load Wizard does not out-of-the-box allow upload of XLSX files – one needs to cut/paste.  Integrating the EXCEL2COLLECTION plugin into a customized Data Load Wizard series of pages makes use of other features of the Data Load Wizard – like the column mapping page – possible.  Beware, that customizing the Data Load Wizard pages and process flow means that your work is subject to break upon any and every APEX upgrade.  This customization of the Data Load Wizard process is unsupported territory.  Note that the EXCEL2COLLECTION plugin itself is supported (by Anton). My use of it in customizing the Data Load Wizard is not.

Bottom line:

Anton’s READ_XLSX remains one of the best methods of reading XLSX data into an Oracle database, when the requirement is to read many XLSX files.  When using APEX, investigate using the EXCEL2COLLECTION plugin; that will make your XLSX load tasks easier.  For one-of data loads, I still use SQL Developer or the APEX SQL Workshop Data Workshop utility.  Need to script the load for Production?  Use SQL Developer in a DEV environment, then use SQL Developer to generate a load script, there are several options for that.

Happy Data Loading!

 

 

 

Last Day! ODTUG Innovation Award

June 2nd is the LAST DAY for ODTUG Innovation Award nominations.

Make your nomination for the latest, greatest piece of awe-inspiring work using Oracle development technologies today.

It’s simple, go to the ODTUG Innovation Award Nomination page. Tell us about your nominee – or nominate yourself!  Everyone is eligible – make your entry today.

Is is a Cloud hybrid?  An APEX plugin? An open-source project that leverages Oracle tools?  A real clever in-house application of  Oracle JET? A really neat product from Oracle?  Anything goes!

Today is the last chance  to make your 2017 nomination.

The ODTUG Innovation Award honors excellence in creative, effective, and innovative use of Oracle development tools within ODTUG’s supported communities (ADF, APEX, BI, Database, EPM and Career). The goal is to honor developers – individuals or teams – whose passion and creativity shine through in their application of Oracle technology to address real-world problems.  Note that this year we are not excluding anything or anyone, so projects and individuals from all organizations (Oracle, too!) are welcome.

Remember that ODTUG members get to vote this year.  Make your nomination, then vote for it too! Member voting will be combined with judges scoring to determine an overall Innovation Award winner.

Check out the full information on the ODTUG web site here:

ODTUG Innovation Award Nominations

Best wishes to all our nominees!

 

ODTUG Innovation Award ~ Make Your Nomination Now!

Nominations for the 2017 ODTUG Innovation Award are open now through June 2nd.  Make your nomination now, before your Memorial Day weekend.

We are looking for innovative, exceptional, ahead-of-the-curve applications of Oracle development tools that stand out as examples of “the next big thing”, or maybe the really neat next small thing,  in Oracle technology.   Can’t think of anything? Nah, I don’t believe that.  Consider:

  • Do  you have, or does your co-worker have an unusually creative application or combination of new and emerging Oracle tools?
  • Did you find a new open source project that makes your life easier?  Open source projects are eligible, as long as they integrate some Oracle technology.
  • Does your latest project enable faster, smoother, cleaner adoption of an Oracle tool?
  • Are you envying someone else’s work, saying “Wish I thought of that …”?
  • Have you seen a real neat learning tool that aids others in understanding a new Oracle tool?   All projects, large and small, are eligible.
  • Does your organization have an exception program for attracting or training persons new to Oracle technology?
  • Have you created, seen or adopted a new plugin or feature, large or small, that others may not have seen yet?

We are looking for the stuff that makes you say “Wow, great idea …”  or “Neat stuff …” or “wicked cool …” (you can tell where I am from).  We need your help to get as many innovation examples in before the June 2nd deadline.

Now is the time to make your nomination.

The ODTUG Innovation Award honors excellence in creative, effective, and innovative use of Oracle development tools within ODTUG’s supported communities (ADF, APEX, BI, Database, EPM and Career). The goal is to honor developers – individuals or teams – whose passion and creativity shine through in their application of Oracle technology to address real-world problems.  Note that this year we are not excluding anything or anyone, so projects and individuals from all organizations (Oracle, too!) are welcome.

Note that ODTUG members get to vote this year.  Make your nomination, then vote for it too! Member voting will be combined with judges scoring to determine an overall Innovation Award winner.

Check out the full information on the ODTUG web site here:

ODTUG Innovation Award Nominations

Nominations close June 2nd, so act soon!

ODTUG Innovation Award 2017 ~ Nominations are Open!

Nominations for the 2nd Annual ODTUG Innovation Award are now open!

The ODTUG Innovation Award honors excellence in creative, effective, and innovative use of Oracle development tools within ODTUG’s supported communities (ADF, APEX, BI, Database, EPM and Career). The goal is to honor developers – individuals or teams – whose passion and creativity shine through in their application of Oracle technology to address real-world problems.

We are looking for innovative, exceptional, ahead-of-the-curve applications of Oracle development tools that stand out as examples of “the next big thing” in Oracle technology or as unusually creative applications of new and emerging Oracle tools.  The stuff that makes you say “Wow, great idea …”  or “Neat stuff …” or whatever rad saying you use.

Now is the time to make your nomination.

What is it this year: A new Cloud implementation?  A clever APEX integration? A cross-platform collaboration?  The sky (cloud joke) is not the limit!

New this year is Member Voting – all ODTUG members are invited to vote for your favorite Innovation Award nomination. Yes,  we want YOU to help decide the 2017 Innovation Award winner. Member voting will be combined with judges scoring to determine an overall Innovation Award winner.

Check out the full information on the ODTUG web site here: ODTUG Innovation Award Nominations

Nominations close June 2nd, so act soon!

ODTUG

The 2017 ODTUG Innovation Award nominations will be reviewed by a team of judges according to these general topics:

  • Innovation – The “wow” factor: acknowledges the exceptional and innovative
  • Quality – Not only does it have to be “wow,” but it has to work
  • Applicability – Evaluates subject matter importance, completeness, effectiveness, and accuracy
  • Topic merit – Treatment of a cutting-edge topic, technical complexity, or new and unique approach to a common problem

Judges score will be combined with member voting results to determine the overall winner.

For guidance, a perfect example of innovation with Oracle tools is our 2016 Innovation Award Winner: Mia Urman, AuraPlayer 

AuraPlayer and Oracle joined forces to mobilize the Oracle Forms based collision reporting system for the New York MTA to a new Oracle Mobile Cloud based application without redevelopment. In the past, MTA’s officers had to manually fill out accident reports in the field and then deliver it to a data entry clerk who would then enter the information into MTA’s legacy system.  Using AuraPlayer and Oracle’s Mobile cloud they were able to create a “mobile-ready” version of the application without the need for redevelopment or migration of the underlying Oracle Forms system. To enable this modernization, REST services were automatically generated from the Oracle Forms business processes using AuraPlayer. The REST services communicate with the Oracle Mobile Cloud Service, enabling authentication and push notifications, as well as application monitoring and analytics.

Using this application, MTA employees are now able to gather collision information and in addition, now capture photos of the accidents and documents on their tablets.  This data is automatically updated by AuraPlayer into MTA’s Oracle Forms system.  The mobile app also works offline, even without cell or Wi-Fi coverage, this is especially important when an officer is working in a tunnel or on the middle of a bridge. With the real-time submission of traffic accident reports, MTA can assess damages much quicker, determine if a crew is needed to repair damage, and clear the scene in much shorter time—in some cases minutes, instead of hours. The need to manually type collision reports after the fact was eliminated, and the efficiency, accuracy, and timeliness of the data were improved.

See more about AuraPlayer here.

Congratulations, Mia and AuraPlayer!