Category Archives: APEX

APEX 5 IR Guts and Performance

Posted on by
5

APEX 5 Interactive Reports (IR) are powerful out of the box, but one can significantly improve performance by strategic settings of certain key parameters.  The full presentation covers all the options.

When your data volumes are low to medium, there may not be much noticeable difference. But when data volumes are high, or the query is resource intensive, the following advice and settings can have a significant performance impact.

In brief:

  • Tune your query, then fine-tune your SQL query. Any slowness here will be magnified in APEX
  • Check MAXROWCOUNT.   The more rows, the longer it takes APEX to retrieve them.  do users really need ALL the rows? OR is it reasonable to ask users to filter first. (APEX 5.1 Interactive Grid will include features to FORCE users to filter first – nice!) Consider  supplying users who need to Download All a separate pag,e os not all users are handicapped by a high MAXROWCOUNT value.
  • Check Max Rows Per Page
  • Check your Pagination setting.  The X to Y of Z is popular, but the “of Z” part is expensive – APEX needs to retrieve all rows then set up pagination. That All Rows part takes time.  Consider using X to Y, and adding a page item that calculates the Z separately. (THere are various options to do this, a topic for another blog post, ranging from a straight COUNT(*) to a sophistcated use of APEX_IR to get the count of the as-filtered IR query)

These are your big-impact settings.

For more info, see the full presentation via the link below or Slideshare:

As you move to APEX 5 Interactive Grid, these three settings till apply – but you have better pagination options, and pagination works differently – heck, all of Interactive Grid works differently – better!  So watch here for upcoming information on Interactive Grid.

APEX5 IR Guts and Performance

Smart use of the IR settings makes for a better end user experience ~ and that’s the whole point!

Vote for the ODTUG Board

Posted on by
1

Vote for the ODTUG BoardI seek your vote for the ODTUG Board of Directors so I may actively establish and maintain ODTUG as the center of excellence for Oracle developers. After years of work behinds the scenes, I wish to take a more active role in steering ODTUG forward.

Through 30+ years of development experience,working with persons at all levels –  CEO/CIO/CTO, analysts, architects, developer, end users ,trainers – and performing these tasks myself, I have gained:

  • Insight on developer needs and challenges gained via hands-on experience across large and small companies, government and private, large to single-person development teams.
  • Insight on user group operations gained through 11 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, for IOUG and ODTUG
  • Quiet steadfast persistence to get tasks done, even the less glorious ones.

How we developers work and learn has evolved – open source, mobile, cloud, node, JET are now mandatory knowledge.  ODTUG is how and where we thrive – where many of us first learn about new technologies or new applications of them. ODTUG needs to lead members into evolving technologies and best practices by delivering more accessible learning, sharing and networking opportunities.

To achieve improved access and quality of learning opportunities we need to improve accessibility and improve participation:  Let’s create more local events for members to attend, contribute and promote ODTUG, year round, not just at KScope. We need to:

  • Reach more members in more countries through meetups and partnerships with local Oracle and related-technology groups
  • Deliver new-era technical materials by actively soliciting and delivering content from our members via social media, webinars, video, and regional conferences
  • Expand our ODTUG Communities to focus on trends in emerging tools and best practices

We do some of these now – but let’s increase participation – we need to get the word out to non-members to get them to join, and invite all to volunteer in any capacity, large or small.

I was honored as ODTUG Volunteer of the Year in 2012, and have served ODTUG in many capacities in the past 10 years:

  • Editor of the Technical Journal – 8 yrs
  • Leadership Program presenter
  • Established the Innovation Award in 2016
  • Member of 9 Editor’s Choice Award teams
  • Conference material reviewer

I am an Oracle ACE Associate, was a 2016  Oracle Developer’s Choice Award finalist.

I originally joined ODTUG to learn. I originally volunteered to give back. I ask for your vote for the ODTUG Board of Directors to ensure that the ODTUG flow of knowledge continues.

communites

One more time: Do we still need Data Modeling?

Posted on by
3

Yes, we need still need to do data modeling, and yes, data modeling is a skill that developers need to learn and practice.

Kent’s points are important:
— To get business value out of the data, you need to understand the data
— Even if you don’t need a model to store the data, you DO need a model to understand the data and use it properly
— Know your data!

I see fewer and fewer data modeling session at conferences these days – dull and boring perhaps, but essential for efficient development and data retrieval based on your data.  I see data modeling as a fundamental skill for all IT professionals – at least those working with databases.

APEX 5 Data Load Wizard ~ Slight Updates Mean Changes to Customized Load Pages

Posted on by
16

Slight changes in the APEX 5 Data Load Wizard meant our customized Data Load pages needed some attention to work properly in APEX 5.

Ever customize the APEX Data Load Wizard pages?  We did so in APEX 4.2,  to

  • Accomodate data change logging requirements. We needed to log every change made to incoming data.  The APEX Data Load Wizard will make those changes for you, but not log them.
  • Allow users to upload XLSX, XLS or CSV files.

So we customized. And all worked well, until we upgraded to APEX 5.

In APEX 5, all our data uploads all broke, giving an abrupt “no data found” error during the file upload.  Geez, it hadn’t even got to all our custom stuff yet!

Well, a little digging into the APEX 5 Data Load Wizard-generated pages and collections gave me the answer. And we are back in business.

This is good reminder that any time you play outside the APEX sandbox – use anything other than the standard wizards and documented APIs –  you are subject to things breaking upon upgrade. We know this and accept it … and we really should PLAN for it. Even point releases.

So what changed?

The Data Load Wizard uses a series of collections behind the scenes to store the data being uploaded and processed:

  • SPREADSHEET_CONTENT – Uploaded (or copied) data
  • PARSE_COL_HEAD – As-uploaded column headings
  • LOAD_CONTENT – Content to be loaded, plus Error and Action information in c047, c048 and c049.
  • NOT_LOAD_CONTENT – Content to NOT load, including the same Error and Action information in c047, c048 and c049.
  • LOAD_COL_HEAD – Mapped column headings; the columns to be loaded.

The collection names pretty much tell you what each collection does.

The APEX 5 DLW now also creates a new collection:

  • SPREADSHEET_SMTP – One member, the uploaded spreadsheet sheet name.  (Not sure what happens if there are multiple sheets … task for another day.)

Was this the problem?  No.  This new collection was getting created in APEX 5 – clearly not the problem.

What was? Behind the scenes, the APEX 5 DLW added a new element as  the last element in the PARSE_COL_HEAD collection, USE_APPLICATION_DATE_FORMAT.  As soon as I added this element to my collections, all worked happy again.

How did I find it?  Plain old triage. I knew from experience that upon upload the SPREADSHEET_CONTENT and PARSE_COL_HEAD columns got populated. Since we did not get beyond the upload- clearly something was happening  – missing – there.  I created some APEX 5 data load wizard pages based on the same table, and step by step examined what was there or not in session state.  Our customization populated PARSE_COL_HEAD (for a variety of reasons re saving the uploaded file, off point for this post).  But it was not adding the USE_APPLICATION_DATE_FORMAT element.  A simple PL/SQL change corrected that, and the no-data-found went away.

Note that this solved our APEX 5 data load issue.  I have seen other cases of the same no-data-found message on other posts in the APEX Community and stackoverflow.com – these may or may not be the same issue I describe here, and the pre-APEX 5 posts are definitely not. Suffice it to say if one customizes any part of the APEX Data Load Wizard, one adopts  the responsibility of keeping up with all of the nuances of the Data Load Wizard for the life of that application.

Is there something else different behind the scenes?  Yes, a few things off topic for this post.  Is there more? Could be, but it is not effecting my customized data load  pages right now.  Will it break on the next upgrade?  Maybe, and we will of course be watching, testing and prepared to adjust as needed.

Lessons reinforced:  Customize only when necessary.  Plan for, allocate time for, test, and adjust as necessary before every upgrade. Common sense stuff that sometimes gets back-seated in the haste to upgrade.

Migrate BI to APEX 5?

Posted on by
Reply

Just back from presenting Migrating BI to APEX 5 at RMOUG Training Days 2016.  Great conference!  Great experts on all Oracle technology. If you have to pick one short sweet conference a year, this is it.

Ever thought of replacing your under-utilized, expensive or outdated BI tool with a suite of APEX Interactive Reports?  In *SOME* cases this makes sense:

  • Your Data Display requirements are reasonable – i.e. users are happy with seeing and working with a few thousand rows or less at a time.
  • Your BI feature requirements include APEX IR Actions (select, sort, filter, chart, pivot, group by aggregate, save, download), and
  • Your BI feature requirements do NOT include BI analysis, unlimited drill-downs, automatic or integrated analysis (such as percents, rank, etc), drag and drop report creation/analysis, and/or MS-Excel-like features. IF you want these, you have to build them.
  • You have APEX resources to plan, design, build and maintain the APEX app
  • You have Database Architects/Developers to plan, design, build and manage the data structures in the database required to consolidate and serve your data ( materialized views, indexes, or other rollups as needed as licensed for).
  • You are prepared to design, build and tune all for performance (as you always should anyways!)
  • You do NOT require out-of-the-box PDF Printing for the as-displayed data set.  Those who own and extensively use BI Publisher may not want to give up this printing luxury IF moving to APEX means doing so. There are many many printing uses cases for which there are many solutions – ensure you have a solution that fits (and is reasonable to implement) before you jump.

Given all this, it just might work for you  It has for many customers, successfully.  The caveats are:

  • If you need a feature that APEX does not natively supply, you must build it.
  • Dynamic Actions are great -but need to be implemented wisely. They may not always perform well with volumes of data.
    • ex: Dynamic parameter selection => Good.
    • Dynamic parameter selection AND dynamic refresh of the IR with the new parameters (a new query against the source data structure) may/will not perform so nicely, depending on the volume of data.
  • Some features are just not reasonable to build in APEX:
    • ex: True Excel-Like behavior
    •        Dynamic Aggregate adjustments

So – want to replace that aging Discoverer installation?  Consider APEX 5.

In planning pages, menus and features, consider these tips:

  • Plan and meticulously tune all structures – materialized views, CUBEs or ROLLUPS, or whatever works for your data. Anything slow here is magnified slow in APEX.
  • Give users mandatory parameters.  This forces up-front filtering, to reduce the result set to a reasonable size AND gives you a ready-made drill path.  We want reasonable size for reasonable performance of all interactive features.
  • Try 3 to 5 Parameters – More gets tedious for end users if they have to select every time.
    • A Temporal (Year. Quarter, Month), and Spatial ( Country, State, City ) and one or few others specific to your data.
    • Consider using Page Zero or building  a Plugin for displaying the same parameter set on all or several pages. (Yes, Plug-Ins can be used locally!)
  • Use a flag to control when data displays. This allows users to filter (using your parmeters Plugin) first, then wait for data to display.  Plan so users do not incur a big wait right up front.
  • Consider multiple IRs on one page, each containing different aggregates of you data depending on the parameters.  The parameters chosen control which ONE of thes IRs displays at any given time.
  • Consider multiple IRs on one page, each containing different Action Item features.  Authorizations for authenticated users control which ONE of thes IRs displays at any given time.
  • Plan and build drill paths wisely.  Intelligently build useful drill paths.  A drill on every column is not necessary. Drills on key columns are nice to have.

Know Your Users, and know what they really do with the data.  That helps you to design and build truly useful data sets and features.

Still not convinced? On the Fence ?

Consider waiting for APEX 5.1 Interactive Grid.  Previews of this new region type show some promising features that BI folks may find interesting:

  • Drag and drop headings
  • Lazy loading – an option to display the frame then the rest of the data
  • Loading data in pages, as opposed to one big result set.

Doesn’t seem like much, but these are big useability improvements , especially when we are considering paging through volumes of data.  Were I to be starting a BI migration project now,  I would investigate the APEX 5.1 Interactive Grid previews and plan my project accordingly.

In the meantime, see my Migrating tBI to APEX 5 slides here, and  if you have specific questions, reach out:

Honored to be an Oracle Developer Choice Awards – Application Express Finalist

Posted on by
1

I am honored to be a finalist in the Oracle Database Developer Choice Awards, in the Oracle Application Express category.

I am most impressed with my fellow finalists – amazing devotion to helping the APEX community – and humbled to be included in such a crowd.  Congratulations to all!

Please check out the Oracle Database Developer Choice Awards main page, and check out the finalist in all categories:

Most important – Visit the Developer Choice Awards pages and Vote!

If you have comments to share, there is space to do so here, or on the page for each finalist.

Many thanks to the Developer Choice Awards team ~ I am most grateful and honored by the nomination and finalist status.

My Favorite APEX Applications

Posted on by
Reply

I have been fortunate to have clients that allow me to work with Oracle Application Express. Fortunate for them, because the tool enables me to produce production applications faster and lighter-weight (pure HTML, no download, less system-administration overhead, less IT maintenance). Fortunate for me, because I enjoy working with the tool.
In general, I enjoy learning; I enjoy the challenge of a new puzzle, of a new tool to learn the ropes of. I believe APEX is one of those tools where one can do just about whatever one needs with it, by getting a bit clever in telling APEX how to produce a page. The cleverness comes in designing the overall application flow, in designing the APEX page flow, in figuring which components ot use where, and in figuring how best to implement business rules. Yes, in general this is the challenge with any web application written in any tool. With APEX, it’s just a bit more fun.

~ My Favorite Applications

I am happy to have written many APEX applications through the years, but some stand out for various reasons of technical challenge, large or small, and personal interest. The names are modified to protect client interests.

Days at Sea

– A fairly simple APEX app on the surface, this application got DAS calculations correct after several high-visibility failed attempts by other more expensive contractors) and included a flexible, extensible, system to allow the application to declaratively grow and adapt to changing regulations. The PL/SQL behind the scenes and APEX to pull all into simple, maintainable interfaces made for delivery on time. Key factor in this project was the team – 3 persons put their heads together and made it work with APEX. A big win.

Interlab Quality Control

– Consolidation of laboratory statistics into an enterprise database,plus a series of complex quality control statistics, presented in a series of customized reports and charts. Moving this medical corporation’s quality statistics from MS Access/Excel – and awesome app in itself – is a big win for the client. Additional phases in progress and more reports and charts are developed. Hmm. D3 or FOT in the future?
Key fun features: XSL-FO templates for color-coded PDF output that matches the color-coded online interactive reports, some neat data loading challenges, and an automated scatter-plot slide show to replicate (improve on) an in-house quality display.

Proficiency Management

– A system for collection, review, tiered approvals and notification of proficiency management information. Automated data loads, automated notifications and a complex set of business rules made development fun and deployment satisfying.

Critter Information Database

– A true migration from a fully-licensed Discoverer installation to BI in APEX. Still in use today, with another migration of a sister Discoverer installation in progress this year. APEX 5 makes this migration so much easer, and the resulting app will have more BI features at less development cost. Non-confidential and confidential users and authorizations schemes in APEX and in the database structures contorl who sees what. Key fun features: PL.SQL Pivot queries and interface customizations to get a BI “look” to the tool.

Permitting and Landings Applications

– This series of applications, completed over several years, marked migration for Oracle Forms and Reports to APEX, enabling the agencies to retire costly Forms and Reports licenses, streamline deployment (no more Java Applet!) and reduce resource costs for development and maintenace. Now these same agencies are upgrading to mobile application and adding more complex features – a positive example of long-term planning and a successful complete migration to APEX and mobile technologies.

Inspection Database

– A set of two applications, one for automated loading of standards data from various regulating agencies, one for collection, consolidation and edit of inspection reports. Key fun features Online editable “report” to replace MS Word document edits, and XSL-FO templates for PDF version of the final report. Fun in progress on this project!

~ It Takes a Team

It’s not all me ~ every great application has a great team behind it. Along the way I have learned immeasurably from:
– The APEX community – I feel the most open and sharing user community there is,
– The fabulous clients I have worked with (rather than for) through the years, and
– The magnificent managers I have been lucky to have who guided me, challenged me to grow, and had faith in me when I did not have it in myself.

APEX Presentations ~ Through the Years

Posted on by
1

I was recently asked to summarize my APEX Presentations. I realized I had them in several places, but not in one consolidated space. So here it is – a summary of my APEX Presentations through the years:

APEX 5 Interactive Reports

Updated for the APEX 5 release, these links contain the presentations from KScope 15, and white papers as published in RMOUG’s SQL>UPDATE magazine.

For APEX 5, Interactive Reports have been reengineered from the inside out. Any developer who uses IRs, has made customizations for appearance or features or performance outside of the standard API’s, even pagination plugins, needs to be aware of the new CSS and JavaScript engine.  APEX 4 and earlier customizations will likely not work in APEX 5 without some refactoring. Be prepared!

Presentations: APEX 5 (Released) Interactive Report Deep Dive, KScope 2015 Presentation
   APEX5 Interactive Reports New Features and Upgrade Cheat Sheet – Presentation RMOUG 2015
Papers:     APEX 5 Interactive Reports Part I: New Features and Enhancements
     APEX 5 Interactive Reports Part II: Structural CSS and JS Widget Code Changes
 

Migrating Critical Business Application to APEX – Successfully

Presentation: Migrate Business Applications to APEX, Successfully – Presentation 

I had a blast at COLLABORATE14 in Las Vegas, NV, April 6-11, 2014.  I had a chance to catch up on Oracle 12c skills, learn more about ADF Mobile with APEX for hybrid mobile applications, XQuery and XML DB and of course refresh friendships with all those at IOUG.  My Friday presentation was on Migrating Critical Business Applications to APEX, covering tips and tools for migrating any legacy app to APEX.

APEX Data Load Options, or, How Do I Load Data, Let Me Count the Ways …

Presentations: APEX Data Loading Options

There are many many ways to load data into an Oracle application. This presentation discusses data load methods specific to APEX – the data load wizard, external files, custom methods and using the APEX Listener for a true MS Excel file upload. This is practical what works, what doesn’t information to help one decide the best approach for data loading problems.

Meaningful Maps, Gantts and Charts

Presentations: Meaningful Maps, Gantts and Charts

APEX 4.0 introduces improved charts and maps, but how to make sense of them? This presentation covers all the basics for understanding APEX 4.0 Charts, Maps and Gantt Charts.  Plenty of information to get one building meaningful charts and maps in APEX applications.

Tight Tabular Forms

Presentations: Tight Tabular Forms

APEX tabular forms are handy, but kind of loose, if you use them out of the box. This presentation covers the improvements in APEX 4.0 for tabular form validations, plus some tips on adding validations using the APEX_APPLICATION arrays, and building manual tabular forms using the APEX_ITEM API.

Migrate BI to APEX: What Works, What Doesn’t, and Lessons Learned Along the Way

Presentation: Migrate BI to APEX – What Works, What Doesn’t, and Lessons Learned Along the Way

This presentation covers a real migration from a major-vendor BI tool to APEX Interactive Reports for end-user information needs. Covers feature considerations such as drills, pivots, output; dynamic crosstab options and construction, including use of 11g PIVOT and AMIS PIVOT object type solution.  Careful crosstab query construction makes APEX Interactive Reports more powerful than one may think. APEX 4.0 new features address the needs for user-defined grouping, shared reports, scheduled report delivery, multiple views of the same data set and improved charting.  Check it out!

Note:  I am working on an APEX 5 version of this presentation – improvements in APEX 5 interactive reports, including the PIVOT feature, may tip the scales and make a switch from an expensive, underutilized BI tool to APEX a better use of resources.

Oracle Application Express: Tactics to Tackle APEX Bugs

Paper:               Tactics to Tackle_APEX Bugs,  Paper

Presentation: Tactics to Tackle_APEX Bugs, Powerpoint

Know your application, know your tools, have a plan, and always remember that you know some stuff. You can debug Application Express apps and stay sane.

APEX development is a breeze, until something goes wrong and there’s no obvious clue of what happened or how to fix it, and there’s no UNDO. Or is there? This session presents tactics to tackle common APEX problems such as MRU internal error, Browser errors, SQL and PL/SQL issues, HTML, and JavaScript problems. We cover triage, debug tool options, and debug tactics, and then demonstrate applying those strategies to tackle common APEX problems.

This paper and presentation were written for APEX 4.0 (or earilier?) ~ APEX debugging techniques are much improved now! However, the strategies and tactics for addressing bugs: knowing your app, knowing your tool, instrumenting, researching first and using the forums still apply.

Oracle Application Express Interactive Reports: The Good, The Bad, The Ugly

Presentation: APEX Interactive Reports: The Good, The Bad & The Ugly

Paper:               APEX Interactive Reports: The Good, The Bad, The Ugly

I had fun writing this paper, mainly because the movie (old western, Clint, of course) theme song played in my head, and our house is defnitely a Clint-John Wayne-Sam Elliot-Any Old Western neighborhood.

Oracle APEX Interactive Reports have many Good points, but one-size does not fit all, so we developers still have some cleverness to apply to meet end user requirements in all cases. This paper outlines the Good, the Bad, the limitations, and shows some easy CSS edits to address the Ugly, the default appearance. Have fun!

APEX Interactive Reports offer amazing functionality out-of-the box, but this wealth of features comes with limitations. This session presents the Good, the Bad and the Ugly of APEX IR, then details and demonstrates workarounds to address the Bad (the limitations) and the Ugly (the appearance). Learn how to limit Search and Activity features for different user roles, how to eliminate part or all of the Search and Activity Bars and how to customize the look-and-feel to fit your corporate template.

(As with all my papers, I will keep this post up to date with the latest version of the paper.)

APEX Cheat Sheet

Download the APEX Cheat Sheet paper from here: APEX Cheat Sheet

When starting out with Application Express, it can be a bit confusing in terms of what syntax to use where, how to effect the item alignment needed, how to customize the look-and-feel… in general, how to get productive. Tha APEX Cheat Sheet paper is a collection of tips, how-to’s, syntax conventions and tricks to accelerate a developer’s APEX learning curve. The APEX Cheat Sheet paper has grown to be much longer than a “cheat sheet” but it does provide a solid quick-start to APEX development.

Note: While written for erly APEX versions – APEX 3! – some of the basics still apply, like the f?p syntax.  Someday I will do another Cheat Shet- the problem is there are so many new features, I will have to do multipe cheat sheets for each major feature.

APEX Under the Covers

Paper: APEX Under the Covers

In working with Oracle Application Express I have found the best way to learn “how they did that” is to look “under the covers” at the APEX code … The way to do that is to download an application, install it in my builder, and view how the author implemented the features of interest.

The white paper and presentation APEX Under the Covers walks the reader / viewer through the learning process of looking under the covers at some not-so-standard features implemented in APEX. The examples are not rocket science; they are intended only to get the brain thinking along lines where the reader may not yet have ventured, suggesting a few different approaches to achieving an end within APEX.

Note: This presentation was written for APEX 4, but the approach is the same with APEX 5 and beyond – learn from how they wrote APEX. For APEX 5, use and LOOK AT how they wrote the packaged applications. This is free training at its best!

APEX Debug Options

Paper: APEX Debug Options

Debugging Oracle Application Express can be a conundrum.   It’s PL/SQL, but I cannot see it, and I cannot step through it. PL/SQL generates the page, but I do not have access to the APEX page generator. There is JavaScript and CSS and HTML … Where to start?  The truth is there are many avenues for debugging APEX; some APEX-supplied, some external to APEX. The paper and presentation APEX Debug Options describes both APEX-supplied utilities and tools external to APEX for debugging APEX applications.

~ Keeping it Fresh: Cost-Effective Training

Layoffs, economizing, more for less, and oh-by-the-way keep up with the latest in Web development, languages, data modeling, coding techniques, testing, quality, the latest App Servers and find time to relax a bit and network with your peers. The temptation is to postpone or skip all training. I suggest however that this is the time we need training all the more. I maintain the most cost-effective training can be found at our user group conferences. Attend! Volunteer – get involved! IT is the most efficient, cost-effective way to stay current and learn new technologies, and you might have some fun along the way.

READ_XLSX_for CLOBs ~ Extending Anton’s AS_READ_XLSX for CLOBs

Posted on by
7

I had a need to read a series of MS Excel XLSX files from a file server over a period of time – usually chunks of 20-50 files, several times a year. That’s too many files to make an upload interface practical (without annoying the users), and the files come as is from an external agency so there is no control over the contents. That is, this is NOT a case of “save to CSV format” – the requirement is to read MS Excel .XLSX format files as they come.

I was happy to use Anton Scheffer’s AS_READ_XLSX code .. but wary of the VARCHAR2(4000) column limit. Sure enough, a few files in, I hit that limit.

What to do? Modify – adjust – Anton’s code to work with CLOBS, of course. The CLOB-ready package is here, PACKAGE_READ_XLSX_CLOB.

So now I have an AS_READ_XLSX_CLOB package that will read the contents of an XLSX file column into a CLOB.

Wh so much data in one MS Excel column? Not my concern – all I need is to be able to read it into my Oracle database.

The adjusted package is available here, PACKAGE_READ_XLSX_CLOB.

The change?  Simple – remove the VARCHAR2(4000) limiting piece – the DBMS_XLSPROCESSOR.VALUEOF function call.

With a little research I discovered the DMBS_XSLPROCESSOR.VALUEOF function was returning into a VARCHAR2(4000) variable  Understandable for a function call.

BUT the DMBS_XLSPROCESSOR.VALUEOF procedure does not have that limit – well, it would have  depending on how I have the variables that hold the VARCHAR2 OUT parameter.  Using the VALUEOF procedure made it easier to use a CLOB variable to accept the OUT parameter.  Of course there is implicit conversion from the procedure VARCHAR2 OUT parameter to the CLOB variable.

I used a CLOB, because that is how I need to store my data. And yes, a significant number of those spreadsheet table cells are indeed > 4000 characters, meriting the CLOB datatype for our purposes

The change:

Change the string_val declaration from VARCHAR2(4000) to CLOB

  type tp_one_cell is record
    ( sheet_nr number(2)
    , sheet_name varchar(4000)
    , row_nr number(10)
    , col_nr number(10)
    , cell varchar2(100)
    , cell_type VARCHAR2(1)
    , string_val clob        --KC: was --varchar2(4000)
    , number_val number
    , date_val date
    , formula varchar2(4000)
  );

Replace the function calls:

t_strings( t_c ) := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ), '.' );

with equivalent procedure calls:

dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ), '.', t_strings( t_c ) );

Then I use the AS_READ_XLSX_CLOB package the same as AS_READ_XLSX … for example:

select * from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ) ) );

or, for ease of reading data in a procedure:

CREATE VIEW MYSPREADHSEET_V AS select * from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ) ) );

and then reading data is as simple as:

SELECT string_val
  FROM MY_SPREADSHEET_V
 WHERE row_nr = 1
   AND col_nr = 2;

and its many variations.

Now I am reading in XLSX spreadsheets with ease, no matter what size.

Thank you Anton ~

APEX 5.0 Interactive Reports: The Good, the Bad and the Ugly, Revisited

Posted on by
Reply

This presentation covers the Good, the Bad and the Ugly of APEX 5.0 Interactive Reports. In short, the good news is APEX 5.0 brings us multiple IR’s on a single page, plus some other enhancements like improved Group By and Pivot. 

The Bad is, to do this, the APEX Team changed the guts – CSS and JavaScript / jQuery – so anything you may have built previously that does not use the standard APEX APIs will need to be refactored.  Fortunately, I found the mapping between the old and the new quite easy to follow.  While the changes are tedious to make and test, I was fairly quickly able to get my customized IRs back in working order on APEX 5.0.

The Ugly remains the default look and feel, and WYSIWYG printing options.  In APEX 5.0 Theme Option will make applying consistent styles easier  – all told less work to make you IRs match the rest of your application.  As for WYSIWYG PDF output, so far there are no advances here.  The best option I have found is to capture the as-is IR query (different APEX_IR_QUERY solutions for APEX 4.1 vs APEX 4.2, and again for APEX 5.0 – improvements each version) combined with a function that returns a table in the Report Query, combined with a custom Report Layout.  One could use the generic layout, but since this does not allow for varying column widths or highlighting and other conditional formatting, for my case I needed a customized XLS-FO template.  No worries, I found once I  built the first few the process gets easier. I also used a generator for the first pass – a time-saver – then customized. That is a topic for it’s own post on another day.  Any way you slice it, PDF output beyond the default generic for IRs is time-consuming and tedious.

See the full presentation here:

APEX 5 Interactive Reports: The Good, the Bad, and the Ugly:  kcannell.GoodBadAPEXIR.presentation

.