I needed to create a page on my existing APEX application that would allow the user to upload a file, I followed an online tutorial where the user had created a dummy table and inserted CSV File inserted through APEX into the table.Why? With APEX 4.2 the ability to create applications with data loading capabilities is built-in.
Icing on the cake is that APEX does not produce any error, this lack of debugging feature (expecially line by line debuggine) is such a pain.APEX has many debugging features, most importantly Debug Mode. In properly designed applications based on PL/SQL program units, full debugging using SQL Developer is available.
</tt> tags as described in the FAQ.
DO NOT use quoted identifiers unnecessarily.
CREATE TABLE "TABLE"."STG" ( "DUMMY01" NUMBER, "DUMMY02" NUMBER, ...
DO NOT use dynamic SQL unnecessarily.
EXECUTE IMMEDIATE 'insert into TABLE.STG (DUMMY01, DUMMY02 ..... DUMMY65) ...
Certainly a major part of the problem is the use of double quotes in this dynamic SQL. As described in the link above, in Oracle SQL double quotes (") are used to delimit identifiers, and single quotes (') to delimit string values. When a single quote is required to appear inside a text literal like this dynamic SQL statement, +two single quotes+ are used to represent it:
VALUES ( 60040, 5, TO_DATE("14-Dec-12","DD-MON-RRRR HH:MI:SS"), "ABC", "ABC", ...