My contribution to this years contribution to #JoelKallmanDay
Recently I created a data-load within APEX to load about 3500 lines of excel-sheet into a temporary table.
I created the table for receiving the data using the Data Workshop, as shown in the image.
I now had a neat little table in which I could start building the data-load for use by the end-user:
All the columns were there, and they matched the columns as were in the first row in the Excel. Pretty straightforward and simple..
Creating the data-load wizard for the end-user now should be a tick-in-the-box. Follow the wizard for a new page and lets start testing.
What a disappointment.. Again and again my wizard showed me an ORA_6502 error:
How difficult could it have been? What dis I do wrong. I indeed made sure that regardless of all suggestions from APEX all columns in my load table actually were varchars..
In the end the problem was this.. The table created by the “Data Workshop” had indeed all the columns, but they also had the names that were in the Excel-sheet. Some of these columns were way longer than 30 characters.
Had I created that table myself, I would have created a table with less elaborate column-names. As you all know I’m a bit lazy 🙂
Once I had renamed all those columns into more Oracle-friendly names (no starting numbers for example and less than 30 characters in length) the Data-Load worked like a charm.
Please keep in mind to keep your column-name-lengths within the old style 30 characters max.