Original Task: Determine month in MM format given an ISO week. Simple, right? Well, it is not so straightforward, as one cannot use the WW or IW date formats in an Oracle TO_CHAR statement.
Trying to get the Month of a week – say, the Month of an ISO week (those are less obvious) in Oracle SQL or PL/SQL?
Imagine something like this:
select to_char( to_date( week, 'WW'),'Month') from table_with_week_column;
We all use TO_CHAR and date formats to turn date parts into character pieces, like YYYY for ‘2024’ and ‘DD’ for ’02’ and ‘MON’ for ‘JUN’ but did you know you cannot use ‘WW’ or ‘IW’ to get the week or ISO weeek?
Well, you can’t. You get this error:
ORA-01820: format code cannot appear in date input format
Which isn’t immediatley obvious.
One has to do things like this:
to_char(trunc(TO_DATE(b.year,'YYYY'),'IYYY')+ b.wk * 7,'Month') month,
to_char(trunc(TO_DATE(b.year,'YYYY'),'IYYY')+ b.wk * 7,'MM') mm,
I had to search a bit for an answer. When I first encountered this, I found help here – thank you @oraclenerd:
I ran into this griefer and want to share the solution, as a reminder to check the obvious-but-not-so-obvious options in situations of unexpected behavior. Particularly on legacy code.
Working in a ooooold app – born in HTML days and lovingly upgrading through the years, on a legacy Data Upload page, I found that a validation on the column mapping page stopped working this year.
By “stopped working” I mean that the validation failed, but control never returned to the page. The error was logged appropriately, then the processing icon appeared and kept turning … the page hung. What!? This worked fine last year. Has been working fine since 2009. In the meantime we had upgraded (since all worked fine last year) to APEX 23.1. Did tons of tesitng, all should still be OK. (But did we test a failed column mapping? Hmm… this is a small part of a very large app …did we?).
Of course I focused on the validation. The visible thing making the noise. Tried all kinds of things I will not list here but will admit too, because, that’s what we do, we focus on the thing that is failing. We think.
Long story short, the issue was not the validation – it was doing its job (as I confirmed several times over). It was the Page: Reload On Submit setting.
It was set to Only for Success – a setting we use for editable Interactive Grids, and on more modern pages, as opposed to Always – which it needs to be for a standard legacy transactional page with On-Submit validations, so those validation errors will display atop the page, and sit there, on validation failure.
I was so focused on the validation, I missed the real issue: The Page was not behaving properly after a validation failure. I had to take a step back and look at the real issue.
Hard to do when in a crunch, or overtired, or working on a production issue that holding up a deadline, or all of the above.
Lesson learned: Take a step back. Walk away, come back. Get another set of eyes. Look at the real issue. Again.
How or why this setting was changed, I do not know and do not (almost really) care. Indeed all 3 pages ( we have 3 legacy Data Loads) had to have the Page Reload On Submit reset to Always. Was this from the upgrade? Most likley. Failed column mappings added to the testing list.
All is now good. Back to normal operations, many brain cells released to focus on normal work and moving forward
Aside: Why a keep legacy Data Upload? For the luxury of colum mappings, a very handy feature when one needs to accept different format incoming data files that all map into a standard set of column names. So far, there is no updated declarative data load option that has the column mapping interface that our end users use once a year and are used to. Could we write one? Yes. Given time and resources and priority. So it stands, for now. Remember that “legacy” does not mean no longer valuable. It does mean we may need to rewrite this whole thing at some undetermined point in the future. It means we need to do a lot of testing on every upgrade, as usual. I do hope that the APEX team simply forgets to remove this piece of legacy code.
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!
The ODTUG Board elections open shortly – I encourage everyone to VOTE – because how you vote, or not, determines the future of ODTUG.
To vote, you must be a paid ODTUG member by September 30th. Simple. Not a paid member yet? Simply sign up here as an individual or corporate member: https://www.odtug.com/join-odtug
Join as an Individual or Corporate Member to Vote
Two Elected, One Appointed, 3-Year Terms. This is the first year our elections are completely on the new mode of elections: Top two candidates are elected to the Board, one person is appointed by the sitting Board, all to three-year terms. Board members are elected as Directors, not to any specific office. In the January meeting of the Board, the sitting Board elects officers for the year, based on experience and nominations from the Board itself.
Why did we make the change from two-year terms to three-year terms? We were losing too much knowledge in one year – half the Board was new in any given year. That made it impossible to work on longer-term strategic objectives, or anything that went beyond one year. Accounting, volunteer strategy, conference initiatives, and even webinars and meetups – the history was lost every year. By moving to staggered 3-year terms, there is less annual rollover, less knowledge lost, more knowledge carried on to the incoming Board members. This was not done lightly. The term max is still six years – two three-year terms instead of 3 two-year terms.
Why did we go to one appointed Board member? To ensure adequate coverage of all communities and all skill sets. Fact: Our voting membership is a small percentage of our membership. Fact: There is nothing that guarantees we have a Board member from each community, nor a Board member capable of being Treasurer, or Secretary, or one who is savvy with contracts
Allowing one Board member chosen by the sitting Board means we can back-fill to cover communities and skill sets. Two are elected, one is appointed – the control is still in the hands of voting members. Again, this was not done lightly.
Voting Members – Are You a Voting Member? – As I mentioned, this is a traditionally small percentage of our paid members. Why? Exercise your right to vote. When too few people vote, it is easy to skew an election toward a candidate whose company has a corporate membership, for example. Or skew to a candidate who is popular on social media, but perhaps not the best qualified otherwise. To prevent this – simple: VOTE. When you get that email, take a few minutes, read the candidates campaign statements, and VOTE. Take part in shaping ODTUG’s future.
Who YOU elect determines the future of ODTUG. Oracle User Groups – let’s face it ALL user groups – took a hit during COVID: budget, volunteers, connections and energy. The task at hand is to re-energize the Oracle technology community, to re-energize volunteers – ODTUG is a volunteer organization – and to continue provide quality educational events in whatever form they need to be. All this takes volunteers and planning and strategy. Your elected Board members drive this effort.
Bacon is Back for Kscope23! ODTUG KScope22 was a building conference – the first major Oracle developer conference post-COVID. We proved that the Kscope vibe is still alive – and that conference attendees want their hot breakfast and lunches. Rest assured we heard you, and agree, and barring any need for social distancing and grab-and-go meals – the reason for grab-and-go meals at Kscope 22- Bacon is Back for Kscope23! Watch for more information on ODTUG Kscope23, including abstract submissions opening on October 4th.
As an active ODTUG volunteer since 2004 I have seen a lot of ODTUG through the years, yet the core purpose remains the same – provide networking and quality, relevant knowledge to the Oracle community.
How people learn has changed. How people interact has changed. How people value education has changed, and what forms of education the prefer. Oracle technology has changed. Our careers have changed. ODTUG has adapted to meet new technologies and learning modes and must continue to do so. Who are the best candidates to carry us forward? You decide – VOTE for the ODTUG Board.
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:
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):
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:
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.
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.
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.
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.
Nominations are open for the ODTUG Innovation Award 2019 – again, I ask you, our ODTUG members – to show us, let us know about the WOW! things you discovered – or built yourself – in the past year. To nominate for this year, read more here: https://www.odtug.com/p/bl/et/blogaid=882&source=1
To get you revved up, consider last year’s winner, Stewart Bryson of Red Pill Analytics for his product Checkmate. An awesome tool – full product, a full IDE really – to control and automate your Oracle Analytics development and lifecycle. Read more about Check Mate here – http://redpillanalytics.com/checkmate/
Explore Checkmate to facilitate your Oracle Analytics development – and remember to give Stewart Bryson a hand when you next see him.
Now back to this year’s Innovation Award:
To make a nomination for the award, you must be an ODTUG member (Associate or Full). If you are not a member, join now.
Nomination Criteria
Nominees may be individuals or teams.
Nominees do not need to be ODTUG members, but they must work with a tool that supports at least one of our communities (Analytics, APEX, Database, and EPM).
Open-source and/or collaborative efforts are eligible.
Nomination Deadline
Nominations open: Tuesday, April 23, 2019.
Nominations close: Monday, May 13, 2019 11:59 p.m. EDT.
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 …
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
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.
