APEX Collapsible Region – Expand/Collapse

Posted on August 23, 2023 by TH Tech

Was updating some long-time apps to APEX 23.1 and found that the old jQuery way ot/collapse an APEX Collapsible region no longer works – so I had to adapt.

Now, I use this to collapse or exand an APEX region with a Collapsible template:

//collapse the easy intuitive way
$('#MY_REGION').collapsible("collapse");

//expand the easy intuitive way
$('#MY_REGION').collapsible("expand");

Much easier to remember, makes more sense. Documents itself.

For back reference, I used to be able to use this syntax:

// collapse the old way
$('#MY_REGION button.t-Button--hideShow[aria-expanded="true"]').click();

//expand the old way
$('#MY_REGION button.t-Button--hideShow[aria-expanded="false"]').click();

With APEX 23.1 the Collapsible region template was updated. Good news, I longer had to use custom Collapsible templates – thank you APEX team – as the spacing and declarative options were so much better. Bad news, that jQuery .click method no longer worked. The HTML. classes and CSS are different. The old jQuery .click() method mimiced a click on the expand/collapse button and effected a colllapse or expand. Researched more, that jQuery .click() was deprecated w JQuery 3. Researched more for a solution, finally found the now-preferred way, above. Thank you John Synders, again.

By “APEX Collapsible region” I mean an APEX region with the Collapsible region template.

Always an adventures on an upgrade. Moving forward. Happy coding!

Oracle APEX Datepicker Dynamic Min/Max Change

Posted on December 29, 2021 by TH Tech

I had a recent requirement to dynamically change the Minimum date on the datepicker, without a full page refresh (which I could have done … but, ick). Turns out its pretty easy.

The key to dynamically change an APEX datepicker item settings is this piece of JavaScript:

$('#P1_DATEPICK').datepicker("option","minDate",$("#P1_DATEPICKER_MIN_VAL").val());

where P1_DATEPICK is your datepicker item, and P1_DATEPICKER_MIN_VAL is an item – hidden or not – that holds your minimum date for the datepicker.

Use those Minimum Date and Maximum Date settings (this is what they are for):

Oracle APEX Datepicker Settings — Use the Datepicker item Settings

Assuming your hidden item name is P1_DATEPICKER_MIN_VAL, Set the Minimum Date to &P1_DATEPICKER_MIN_VAL. Similar for Maximum Date. Note the & … . syntax.

Create a dynamic action on whatever event it is that triggers a change in your datepicker minimum value. In that dynamic action, create whatever True actions are necessary to set the datepicker minimum value item value – Set Value, Execute PL/SQL Code, whatever works for your needs.

Next, in the same dynamic action, add a True action of type Execute JavaScript Code:

$('#P1_DATEPICK').datepicker("option","minDate",$("#P1_DATEPICKER_MIN_VAL").val());

That’s the piece that does the dynamic datepicker setting change.

In this post I purposely left out the details of creating and setting items. Those details are all specific to your specific requirements. If anyone wants assistance for your particular case simply contact me, I am glad to help.

Happy Coding!

APEX 21.1 Most Subtle New Feature: Client-Side Validations

Posted on July 21, 2021 by TH Tech

APEX 21.1 has a several awesome new features that steal the spotlight: Native Map Region Type, improved application Data Loading, and Faceted Search enhancements to list a few. But don’t overlook the subtle but important change in client side validations:

New Look

Client-Side validations now LOOK LIKE server side validations:

Client-Side Validations APEX 21.1 and Higher – Same as Server-Side

Where they used to look like this, with the familiar Correct errors before saving popup:

Client-side validations, up to & including APEX 20.2

Good News

This is *great* for your end users – all validation now look the same, you never have to explain to end users why they may get that “Correct errors …” popup. This is great for developers, you never have to do the extra coding to make the client-side validations look like the server-side validations – if you chose to go that extra step (I suspect many of us picky developers did).

UNLESS you have end users who are used to seeing that “correct errors before ..” popup. Now they will not see that popup, they will see the perhaps more alarming golden error popup, the same error popup as for server side validation errors.

Tell Your Users

This is different. Your end users should be told to expect this difference.

If you are upgrading to 21.1 from earlier APEX versions, and if your end users are used to seeing the client side validations the old way, please let them know of this upcoming change!

What If You Did This Already?

While all told this is a good thing – if you are a developer like me who has (already) done the apex.message coding to make your client side validation messages look like your server side validation messages, no worries:

a) You don’t need to do this anymore, and

b) Your existing code will work fine – you will not see double error messages.

While you may eventually want to take that apex.message code out, it does not hurt anything to leave it there.

Hope this saves someone a call or two,

Happy coding!

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

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

Posted on April 28, 2019 by TH Tech

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

no data found

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

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

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

Skip Validations

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

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

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

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

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

Show Advanced Option

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

ADD PICTURES

Thank You ODTUG ~ Serving Another Term

Posted on January 26, 2019 by TH Tech

Thank you ODTUG for my re-election to the ODTUG Board of Directors. I cannot do it without you! I will do my best to serve ODTUG through the coming term.

Congratulations to those elected with me this year:

Tim German, director at Qubix
Teal Sexton, director of finance and BI systems at UC Berkeley
Neviana Zhgaba, finance IT technical project manager at GE Digital

We join the rest of the ODTUG Board:

Danny Bryant, senior solutions architect at Snowflake Computing
Gary Crisci, principal architect of EPM at GE Digital
Natalie Delemar, senior manager at Ernst & Young
Roel Hartman, director at APEX Consulting
Eric Helmer, global vice president at Rimini Street

and Director Emeritus

Jake Turrell

Please let me – us – know if there is something we can do to make your ODTUG experience better, or if you wish to volunteer in any capacity to help us.

Contact me directly – kcannell – at – thtech.com or use our suggestions email, suggestions@odtug.com

Happy Coding! and again many thanks for your vote of confidence!

We have an exciting year ahead – hipe to see you all at Kscope19 in Seattle, WA.

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

APEX 5 Event: theme42ready

Posted on September 11, 2018 by TH Tech

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

Posted on September 11, 2018 by TH Tech

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

Posted on February 16, 2018 by TH Tech

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.