About TH Tech

Karen Cannell is Director of TH Technology, a small consulting firm providing Oracle technology services, lately focused on Application Express. A mechanical engineer by degree (one of them), she has analyzed, designed, developed, converted, upgraded, enhanced, and otherwise improved legacy and commercial database applications for over 25 years, concentrating on Oracle technologies since 1994. She has worked with Application Express since its Web DB and HTMLDB beginnings. TH Technology leverages the Oracle suite of tools to build quality web applications for clients in government, medical, and engineering industries. Karen is editor of the ODTUG Technical Journal and co-author of Agile Oracle Application Express, Expert Oracle Application Express and Beginning Oracle Application Express 4.

ODTUG Board of Directors ~ Keep the Spark Alive

As the ODTUG Board of Director election for the 2019-2020 term approaches, I get to reflect on my term as an ODTUG Director, on ODTUG in general and why I want to run 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 had worked at General Electric writing automation engineering drafting applications – code that produced production drawings. At Computervision, writing CAD-CAM Architecture, Engineering and Drafting packages. Using a bit of Oracle there, about when that internet thing came to be. Then I worked in state and federal government (fisheries) agencies, on Oracle database. 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.

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

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!)  When I took the ODTUG Technical Editor job, I had to resign as Associate Editor for the IOUG SELECT Journal.  A bit much to do both, they said.  The beauty of being an editor is that one gets to read content from all over, and meet the experts from all areas.  All communities, all topics, I got to learn more, every month,  I got to meet more people in the 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. Got to review 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!)

I will say about this time, there was the unexplained, unspoken division between ODTUG and IOUG.  I was told, “One is for DBAs, one is for Developers, and the two do not mix”. I did not believe that then, and I do certainly not believe that now.  A smart developer knows what a DBA does, at least sufficient to ensure all applications run securely and efficiently. A smart DBA knows what developers are doing and how best to support them so the applications run securely and efficiently. Now, the roles are more blurred – with Dev Ops, even more so.  My hope is that mythical IOUG-ODTUG split, which seems to be getting smaller, gets totally forgotten soon.

Back to ODTUG.

The connections I made through ODTUG convinced me to go into consulting. (Thank you Scott Spendolini and Doug Gault!). Who knew, I still work for that same client, though now directly, owing to a few buyouts and an odd set of circumstances.              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 technical support I needed, I had the consulting support I needed, and if all failed, I knew where to go to look for work.  Fortunately, I have not been out of work since ( knock on wood).

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 KScope, 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. Plus, as a consultant, it is my job to stay ahead of the curve – to be able 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.

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.

Board Activities

This term as a Board member, I was engaged in – am still 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 text, attended a series of online presentations and 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, if we do our job right in the LP, the graduates will want to stay on and give back to ODTUG.  It is totally rewarding to watch younger (time-wise, not age-wise) workers grow as the series progresses.  The project usually separates the leaders from the maybe-not-so-much leaders. 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
  • Alternative Revenue Committee – Investigate alternative forms of revenue for ODTUG – non-conference revenue.  Options include smaller conferences, alternate membership models, various forms of outreach and training options.
  • 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 occasional need for planning for Kscope community activities, exchanging requests from the community, planning webinars, assistance in planning meetups.

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

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.  Yes, 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 award 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. Then papers became optional, then we went to 76, then 65, the eventually 25, then lower.  The beauty of the Editor’s Choice reviews was that one got to read most of 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!

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.

General Session Skits

Long Beach marked the first full video intermissions general session.  Prior to that, the Board members did skits of varying topics and craziness.  Some persons question why we traditionally have fun, even silly intermissions for our general sessions.  There are no hard set reasons.  But I like them. They are part of ODTUG.

  • They are fun.
  • They show a sense of community.  The Board works hard all year. This lets us goof off a bit, and hopefully get our members to take a breather and laugh a bit as well.
  • Technology can be dull.  Comic relief is just that.
  • We need intros for awards.
  • They demonstrate that ODTUG is just a bit different. Not stuffy, not dry, not dark suit serious.
  • They send the message, “Prepare to have fun here, too. As you learn.”

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.

Innovation Award

When the Editor’s Choice was retired – mainly due to lack of papers – I initiated the ODTUG Innovation Award in 2016. The Innovation Award honrs 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 KScope in sufficient time for a panel of judges to evaluate the submission and award a winner, announced at KScope.  Best luck to all future submissions!

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.

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

APEX 5 Event: theme42ready

A while aback I had a puzzle with setting the visibility of certain tabs on a carousel region.  Whether a tab displays or not depends on user selections on another page; based on those selections, I can hide or show a particular tab of the carousel.  The problem was, firing my hide/show dynamic action after Page Load was not doing it – the carousel seemed to complete its rendering after Page Load.  What?

I went to apex.world for suggestions. The solution was to fire my hide/show DA on the theme42ready event.
This event fires after page load … which is the event I needed.

How would I know such an event exists?

Luckily a friend in apex.word – Thank You Jeff Eberhard – clued me in.

theme42ready is triggered after the initialization of Universal Theme:

jQuery(window).on('theme42ready', function() {
log('Do something after UI elements are rendered on the page.');
});

I was then able to implement a dynamic action that first on event theme42ready – and got the result I expected.

There are two other Universal Theme events one should be aware of that may help in similar situations:

theme42layoutchanged  is triggered when left navigation menu is expanded or collapsed.

apex.jQuery("#t_TreeNav").on('theme42layoutchanged', function(event, obj) {
console.log('Left menu action: ' + obj.action); // obj.action will return expand or collapse
});

and
apexwindowresized is triggered when the APEX window is resized

apex.jQuery(window).on('apexwindowresized', function() {
console.log('Window resized.');
});

For more information on these events and other aspects of the Universal Theme, see the Universal Theme reference application. https://apex.oracle.com/pls/apex/f?p=42:6200:::NO:::

APEX Data Load Wizard Customizations ~ Revisited

dataLoadWizard
I was revisiting our APEX Data Load application, which uses a highly customized version of the APEX Data Load Wizard – several of them. Key words here: Highly Customized.

Customizing the Data Load Wizard – or modifying any set feature of APEX – leaves one open to the possibility that things might not work in the following versions. This has been the case with every major APEX upgrade since we made the Data Load Wizard customization in APEX 4.2.  Each time we have had to plan time/resources to revisit the customizations and make corrections – all on our own sleuthing.  No big deal, right?

This year an upgrade to APEX 5.1,  left the last step, the load into our defined Data Load table, not working. I had two choices, rebuild the Data Load Wizard pages ( 3 sets), and re-apply the customizations.  Or, write my own load into the final table.

This time I chose the latter.
As a quick reminder, the Data Load Wizard loads uploaded data into a series of collections.  To learn more about the Data Load Wizard and why we customized, read the previous Data Load Wizard posts. The data from those collections – after transformations are applied – gets loaded into the Data Load target table.  In our case, we needed complex validations and transformations, and logging of each change. The logging of each change is what really drove us to customizing the process. So we pulled data from the uploaded collection (SPREADSHEET CONTENT) into a temporary table, performed all our validations, transformations and logging, then moved the data back into the load collection (LOAD_CONTENT).  And continued with the Data Load process.  That’s a simplification, there are a lot of other pieces involved, but that’s enough for here. (Read previous blog posts for more details).

By choosing to write my own load into the final table, and bypass the APEX processes, I lose some other features that I now have to code on my own.  What I lose:

  • Automatic insert/updates
  • Automatics insert/update failure counts and logging.

I am OK with handling these in my own bulk process insert with the LOG ERRORS clause. We only get inserts, never updates, which simplifies things.  Your mileage may vary. Note that if you need to process updates and inserts, and report which records are inserted vs updated, the solution will be more complicated.

Next time we upgrade, I expect another set of minor Data Load Wizard changes … to which I will adjust somehow.  Now, IF the Data Load Wizard were to allow greater flexibility in logging transformations for each row – then I may be able to abandon the customized code altogether and revert to the mainstream Data Load Wizard.  Until then I am resigned to maintenance with each update.

P.S.
I just had someone ask about customizing the Data Load Wizard – breaking into the collections as I did. My recommendation was, use the Data Load Wizard to load into a staging table, then write PL/SQL processes to perform the validations needed – in his case to only do inserts no updates – filter out existing rows. Such a solution will be in-the-sandbox (no customizations) and will readily upgrade.
I strongly advise against breaking into the Data Load Wizard unless there is a solid business requirement to do so. Unless …

    • You have plenty of time to go back and fix things upon upgrade, and
    • You have a finding Fairy to pay for all those upgrades.

Happy Coding, whichever approach you choose.

Bulk Bind Reminder: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

Bulk Bind: Truncated bind

I recently had a good reminder on one of the subtleties of PL/SQL bulk fetches and inserts. I kept getting this error on the FETCH:

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind  

The procedure purpose is to bulk insert data from a temporary staging table to the real, properly-sized column Load table.  Simple, right?  Except when I use the wrong table types.

My problem?

The elements I was selecting in my cursor did not logically fit into the table type target of my cursor fetch.

I defined my cursor using a query on the temporary staging table (larger VARCHAR2 columns), not the real table (exact-fit VARCHAR2, NUMBER and DATE columns).
But, I defined my table type to receive the cursor results on my real table:


CURSOR c_dl IS
SELECT ...
...
...
FROM my_staging_table stg
WHERE action IN ( 'INSERT','UPDATE');
TYPE my_load_table_tt IS TABLE OF my_load_table%ROWTYPE
INDEX BY PLS_INTEGER;
load_data_arr my_load_table_tt;


BEGIN
OPEN c_dl;
LOOP
FETCH c_dl BULK COLLECT INTO load_data_arr LIMIT 1000; ----- Error  happens here!
FORALL ii IN 1 .. load_data_arr.COUNT
INSERT INTO my_load_table
...
...

The Bulk Bind: Truncated Bind error is because the cursor – with bigger elements, cannot logically fit in the defined array/table type – the columns are too big.

To correct this, I changed the definition of the table type to be a table of the staging table. Then the FETCH proceeds fine, as does the INSERT.

But wait, why didn’t the INSERT fail, since I am Inserting from a (potentially) large-column cursor into the exact-fit table?

The INSERT proceeds because the elements being inserted fit  – the transforms and validations (processed previous to this point in the code) have ensured that all elements are indeed the size they should be in the exact-fit table.  So the INSERT does not complain.

LOG ERRORS REJECT LIMIT UNLIMITED

Now, IF one column in one row was too large, or the wrong format for a date, the entire INSERT would have failed. Unless I used the LOG ERRORS clause, which I did.

IF one or more records is/are bad, I want to load all the good records, and single out the bad. To do this, I used the LOG ERRORS REJECT LIMIT UNLIMITED clause at the end of the INSERT statement:

INSERT INTO my_load_table
VALUES ( ..load_data_arr(ii).col1,
load_data_arr(ii).col2,
load_data_arr(ii).col3,
...
load_data_arr(ii).coln)
LOG ERRORS REJECT LIMIT UNLIMITED;

This LOG ERRORS REJECT LIMIT UNLIMITED clause ensures that all clean rows will insert, and all “bad” rows get logged to an error table, in my case called ERR$MY_LOAD_TABLE.

I created the error log table previously, using the command:
BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => 'MY_LOAD_TABLE');
END;

We define the staging tables with generous VARCHAR2s on purpose. We want to allow leeway in the incoming data, knowing we can strip punctuation, perform lookups, format dates, and perform other transformations in the validation stage. That means doing all that validation is on us, to make sure the data is in shape for the final insert. Just one way of doing things – there are many other approaches.

My reminder is – especially when writing things quickly – to pay attention to the nuances of the features in use.  I won’t make that mistake for a while now :).

For a full treatment of bulk processing with BULK COLLECT and FORALL, see Steve Feuerstein’s article PL/SQL 101: Bulk Processing with BULK COLLECT and FORALL.

 

Submit to ODTUG Kscope18 – Last Days!

Hurry up! Abstract submissions for ODTUG Kscope18 are due January 5th.

No more procrastinating, no more wavering, Just Do It! Submit your abstract here.

Presenting at ODTUG KScope is an awesome experience – share your knowledge, share your technical success stories, share you case study of geez-it-was-hard-but-we-figured-it-out.  We are looking for presenters in these areas:

Application Express
Database
Big Data & Data Warehousing
EPM Infrastructure
EPM Data Integration
EPM Reporting, BI Analytics, and Visualization
Essbase
Financial Close
Multi-Product EPM Solutions
Planning
Real World EPM

Not sure how to write an abstract, or what to share?  Check out these blogs for more information and ideas:

http://spendolini.blogspot.com/2013/11/presentation-advice.html
http://www.odtug.com/p/bl/et/blogid=1&blogaid=497
https://www.linkedin.com/pulse/20140630141618-4832037-how-to-get-your-conference-abstract-accepted
http://www.pythian.com/blog/concrete-advice-for-abstract-writers/
https://chadthompson.me/2012/12/how-to-submit-winning-presentations/

We need topics at all levels of expertise, beginner to advanced.  No excuses – write that abstract now  Join us in Orlando, June 1-14, 2018!

 

Submit to ODTUG Kscope18 – Last Days!

Hurry up! Abstract submissions for ODTUG Kscope18 are due January 5th.

No more procrastinating, no more wavering, Just Do It! Submit your abstract here.

Presenting at ODTUG KScope is an awesome experience – share your knowledge, share your technical success stories, share you case study of geez-it-was-hard-but-we-figured-it-out.  We are looking for presenters in these areas:

Application Express
Database
Big Data & Data Warehousing
EPM Infrastructure
EPM Data Integration
EPM Reporting, BI Analytics, and Visualization
Essbase
Financial Close
Multi-Product EPM Solutions
Planning
Real World EPM

Not sure how to write an abstract, or what to share?  Check out these blogs for more information and ideas:

http://spendolini.blogspot.com/2013/11/presentation-advice.html
http://www.odtug.com/p/bl/et/blogid=1&blogaid=497
https://www.linkedin.com/pulse/20140630141618-4832037-how-to-get-your-conference-abstract-accepted
http://www.pythian.com/blog/concrete-advice-for-abstract-writers/
https://chadthompson.me/2012/12/how-to-submit-winning-presentations/

We need topics at all levels of expertise, beginner to advanced.  No excuses – write that abstract now  Join us in Orlando, June 1-14, 2018!

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!