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

ODTUG Innovation Award 2019

Posted on April 28, 2019 by TH Tech

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/

and
https://blog.redpillanalytics.com/checkmate-for-obi-free-ec48e30e0787

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.

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

APEX Interactive Grid: Customize Toolbar

Posted on July 21, 2017 by TH Tech

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

widget.toolbar.js

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

toolbarData

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

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

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

There are two general approaches to writing an override function:

Totally replace
Copy and adjust

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

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

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

Default toolbarData Array

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

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

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

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

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

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

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

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

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

This function does the trick:

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

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

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

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

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

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

Note that Download is a declarative option –

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

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

config.features.download = false;

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

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

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

APEX Interactive Grid: JavaScript Basics Cheat Sheet

Posted on July 3, 2017 by TH Tech

APEX Interactive Grid can be customized by JavaScript in the Advanced –> JavaScript Code attribute of the Grid or a Grid column. Yup – JavaScript.

JavaScript may be out of the comfort zone for PL/SQL developers, even those who implemented extensive tabular form customization working with PL/SQL collections. Time to say Goodbye to those collections … Welcome JavaScript!

The following is a collection of simple JavaScript lines most likely to be needed by a developer wanting to customize an Interactive Grid, or access data elements in the Grid.

This is not a comprehensive list or a complete function – just a simple collection of lines to give you an idea of the process and examples of – a reference for – the syntax.

Taken line by line:

var $te = $(this.triggeringElement);

This line gets the triggering element – the element that caused the dynamic action (DA) to fire. Usually we want to do this in a Grid to get the value of a particular cell – the one clicked on in this case. To get the value of the cell, we need to know which row id, then we can narrow things down to a column using the column static id.

To get the row id, we start with the triggering element.

Next, we find the closest row – a ‘tr’ – and get the “id” data from it. It helps to know that Interactive Grid has an “id” data element on each row:

var rowId = $te.closest('tr').data('id');

If our data element was data-thingamajig, we would access it via

 ... .data('thingamajig');

The next line gets the grid widget. The apex.region function is the preferred way to access region widgets. “grid_static_id” is the Static ID of the Interactive Grid, set by the developer in the Advanced –> Static Id attribute of the Grid. If you do not set one, a static id gets assigned, but it will be a long difficult-to-read identifier – it is much easier and better practice to set a meaningful static id, then use that meaningful static id in your code.

var ig$ = apex.region("grid_static_id").widget();

Given the Grid, we can now get the data model. The data set is referred to as a data model. There is a data model for every view: grid, chart, group by, icon, detail. The following line gets the grid data model.

var model = ig$.interactiveGrid("getViews", "grid").model;

Given the grid data model, which we know is a table, we can get the record of the model, using our rowId which we identified via properties of the triggering element.

var record = model.getRecord(rowId);

Once we have the record, we can access properties of any column in our Grid – any column in that record, using the column name – or the aliased name we assigned to the column. Here my column name is COMM for commission.

var comm = model.getValue( record, "COMM");

model.getValue gives us the value of a cell in a record. The corresponding model.setValue sets the value of a cell in a record.

if (comm < 100) { 
  model.setValue(record,"RIFF",'Y');
}

The above examples are easy on purpose. In fact, I bet any PL/SQL developer could follow these lines without headache.

Now you know how to access a row id, access the grid widget, access the data model of a grid, access rows – records – in that data model, and how to get and set values of the columns in that data model record. That covers most of the basics!

You will need to learn more if you plan on complex customization or perhaps on building plugins. For now, I recommend examining the grid in the Console, and reading the APEX widget js files. Looking forward to APEX 5.2 (Oracle Safe Harbor), there may be documentation for all the Interactive Grid widget APIs. Won’t that be nice!

Happy coding!

I highly recommend reviewing all of the examples in the Sample Interactive Grid packaged application. And, read John Snyders’ hardlikesoftware.com blog posts on How to Hack the Interactive Grid.

APEX Interactive Grid: Column Widths and noStretch

Posted on July 2, 2017 by TH Tech

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

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

So how to set Interactive Grid column widths?

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

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

Select the || divider to dynamically adjust column width:

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

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

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

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

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

This is where the noStretch option comes in.

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

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

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

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

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

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

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

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

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

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

That’s it!

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

Good luck!

Going to the Grid: What the APEX Interactive Grid Means to You and Your End Users

Posted on June 14, 2017 by TH Tech

I will be speaking at Kscope 17 in San Antonio, TX on APEX 5.1 Interactive Grid:

Going to the Grid: What the APEX 5.1 Interactive Grid Means for You and Your End Users

Karen Cannell , TH Technology

When: Jun 26, 2017, Monday Session 2 , 11:45 am – 12:45 pm

Room: Cibolo Canyon 5

Topic: Application Express – Subtopic: APEX New Release

The long-awaited APEX 5.1 Interactive Grid region is here – but what does it mean to you and your end users? Need some practical guidance on when, why, and how to adopt the interactive grid region type? This session is for those who have seen the introductory demonstrations, are looking to upgrade to APEX 5.1, and need some advice. Does the interactive grid replace interactive reports? No more tabular forms? This session answers practical functional and technical questions raised by this new region type:

• When and why should I upgrade existing regions to interactive grid?
• Is there an upgrade wizard?
• What features will I gain; what features will I lose?
• What about customizations – will they upgrade?
• How does an editable interactive grid compare to my existing tabular form?
• Which features must still be manually written? Do I still need to write all that collection logic?
• How can I customize appearance?
• How can I add dynamic actions?
• Can I extend the interactive grid functionality?
• Will my end users like it? What about data entry users?

This session compares and contrasts interactive grids with the interactive reports and tabular forms we are familiar with. We will pay particular attention to maintaining or replacing features and common customizations in existing applications: checkboxes, 32K limits, cascading select lists, row-level validations, interactions between columns, and navigation between fields. How does the interactive grid region measure up? Should you upgrade all your interactive reports and tabular forms now?

How we do settings of which features are on or off for a particular Grid and for a particular column is now controlled by javascript. Sound scary? It is really not, you just need to learn where to put what piece of code when. How to access data in the Grid data model? We will show you that too.

If you cannot attend, don’t worry, I will be posting bits of that presentation here over the next few weeks. IF you can attend, I will see you there!

Rest up ~ our Kscope 17 schedule is packed, so don’t expect much sleep or downtime.

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

Posted on June 14, 2017 by TH Tech

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

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

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

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

Let’s do Bad News First.

The Option That Goes Away:

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

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

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

The XLSX Option That Still Works – READ_XLSX

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

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

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

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

This statement is the guts of it:

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

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

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

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

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

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

Another (Better?) Option – EXCEL2COLLECTION Plugin:

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

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

Bottom line:

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

Happy Data Loading!

ODTUG Innovation Award ~ Make Your Nomination Now!

Posted on May 25, 2017 by TH Tech

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

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

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

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

Now is the time to make your nomination.

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

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

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

ODTUG Innovation Award Nominations

Nominations close June 2nd, so act soon!

ODTUG Innovation Award 2017 ~ Nominations are Open!

Posted on May 7, 2017 by TH Tech

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

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

Now is the time to make your nomination.

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

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

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

Nominations close June 2nd, so act soon!

ODTUG

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

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

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

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

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

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

See more about AuraPlayer here.

Congratulations, Mia and AuraPlayer!

TH TECHNOLOGY

Oracle Consulting Services, specializing in Application Express (APEX) and legacy development tool conversions.

Tag Archives: TH Technology