APEX

Hi All,

Every week I synchronize data from the internal Oracle RMS server to our application on the internal Oracle APEX server by way of a CSV spreadsheet upload (in Data WorkShop> Load Data) to a new table in my workspace. Initially, this worked fine, if a bit slowly.


Recently, however, I find that the upload progressively takes longer to the point where, now, I consistently time out when uploading the CSV file. Please note that this is not a large file (<3000 records, no BLOB/CLOB). I have checked my tablespace and storage capacity for my workspace and have more than enough to load the table. The table and related index are, indeed, created in the database, but the data is either not completely loaded or not loaded at all.

The table is defined as:

Column NameᅟᅟᅟᅟᅟᅟᅟᅟᅟData Type
------------------ᅟᅟᅟᅟᅟᅟᅟᅟᅟ-----------------
IDᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟNUMBER
RELEASE_MILESTONEᅟᅟᅟᅟᅟVARCHAR2(30)
RELEASE_VERSIONᅟᅟᅟᅟᅟᅟVARCHAR2(30)
FAMILYᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(30)
DEV_AREAᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(255)
PRODUCTᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(30)
DELIVERABLE_IDᅟᅟᅟᅟᅟᅟᅟVARCHAR2(30)
DELIVERABLE_TYPEᅟᅟᅟᅟᅟVARCHAR2(30)
TASK_IDᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟNUMBER
TASK_NAMEᅟᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(255)
TASK_NAME_LINKᅟᅟᅟᅟᅟᅟVARCHAR2(255)
START_DATEᅟᅟᅟᅟᅟᅟᅟᅟDATE
FINISH_DATEᅟᅟᅟᅟᅟᅟᅟᅟDATE
COMPLETEᅟᅟᅟᅟᅟᅟᅟᅟᅟNUMBER
RISK1ᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(30)
ACTIVITYᅟᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(1)
RPS_IDᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(255)
UNIQUE_IDᅟᅟᅟᅟᅟᅟᅟᅟNUMBER
PROJECT_IDᅟᅟᅟᅟᅟᅟᅟᅟNUMBER
AUTHORᅟᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(40)
FILE_LAST_UPDATED_ONᅟVARCHAR2(30)
FILE_NAMEᅟᅟᅟᅟᅟᅟᅟᅟVARCHAR2(255)

I am not sure what the cause is, but I have tried the following workarounds to no avail:

WorkaroundᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟResult
-----------------ᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟ-----------------
Load CSV, let APEX create Index and Sequence ᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟTimes out, Most of Data loaded in table
Modify CSV to include PK (so upload does not have to create one)ᅟᅟᅟStill times out, Little or no Data in table
Perform upload without creating sequenceᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟStill times out, Little or no Data in table
Combine both aboveᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟStill times out, Little or no Data in table
Perform upload as Cut&Paste from ExcelᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟFormat and column headers incorrect,table not viable.

When it does load data, it fails with an error similar to the following:
ErrorᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟᅟData
ORA-00040: active time limit exceeded - call abortedᅟᅟRow 1112:[string of comma delimited values]

In the most recent cases, this happens just one record shy of the full upload, which implies that the problem may lie in "closing out" the upload process, rather than with any data problems per se.

It should be noted that I am performing the upload from a PC using VPN to connect in to the internal Oracle APEX server, but I do not think that upload or connection speeds would impact the upload of a file as small as the ones I am trying to upload. However, I am wondering if the VPN connection is preventing the finalization of the upload in some way.

Has anyone encountered this or have some idea as to what/where the problem may lie?

Hoping for a quick resolution as the synchronization of data is waiting on this.

Thanks,

Edited by: mi**** on Mar 10, 2011 10:54 AM

Edited by: mi**** on Mar 10, 2011 11:08 AM