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

Posted on September 18, 2020 by TH Tech

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.

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

K Cannell for ODTUG Board ~ Keep the Spark Alive

Posted on September 19, 2018 by TH Tech

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.

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!

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 – 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

ODTUG 2018 Elections ~ Vote Now!

Posted on October 17, 2017 by TH Tech

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)

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:

Read all the candidate’s statements carefully. Then
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

Posted on July 21, 2017 by TH Tech

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:

Totally replace
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:

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.

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:

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.

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 –

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

Posted on July 3, 2017 by TH Tech

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.

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:

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

Posted on July 2, 2017 by TH Tech

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.

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:

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

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!

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.

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.

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:

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

Posted on June 14, 2017 by TH Tech

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

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

Posted on June 14, 2017 by TH Tech

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

Posted on June 1, 2017 by TH Tech

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!

Posted on May 25, 2017 by TH Tech

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.

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.