ORA-06502: When loading data in the APEX data-loader

Published by Richard Martens on

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.

Happy APEXing,
Richard

Categories: APEXDatabase

Richard Martens

Richard Martens has been involved in information technology for more than 15 years. He started as a web developer using the Oracle database as no more than data storage. Richard has been responsible for major European multilingual websites and has been working with the Oracle database since 2000. During those years, he developed himself using a multitude of technologies and specialized in PL/SQL and Oracle APEX. With APEX, he combines the things he loves most: the Oracle database and web technologies.