Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » APEX Error ! (APEX)
icon11.gif  APEX Error ! [message #559324] Mon, 02 July 2012 10:44 Go to next message
callmechandan
Messages: 2
Registered: July 2012
Location: India
Junior Member
Hi,

We have a very simple APEX application, used to upload alignment data (basically an Excel datasheet).

The issue is:
Whenever we try to upload more than 400 records, a red STOP sign comes as an ERROR.

• We upload 400+ records once in a month
• We have to split them into several packets (of less than 400 each) to avoid the error

Does size matter ?
• We tried with 2 different data-sheets. One is having more than 20000 records and is of a size more than 6 MB. But the 2nd one is having approx 400 records and is of a size in few KBs.
• The first one triggers the error while the 2nd one uploads quite easily without any error.

Not sure what may be the cause. Any help/suggestion is appreciated !

Thanks..
Re: APEX Error ! [message #559326 is a reply to message #559324] Mon, 02 July 2012 10:55 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hello! It seems that you missed to read our Guide which contains some information that makes your questions easier to understand and, hopefully, provide an answer. Perhaps you should read it now and supply some additional info (such as versions of all software products involved into this operation).
Re: APEX Error ! [message #559482 is a reply to message #559326] Tue, 03 July 2012 09:14 Go to previous messageGo to next message
callmechandan
Messages: 2
Registered: July 2012
Location: India
Junior Member
Sorry for not mentioning the version. It is 3.1.2.00.02 and Oracle 10g.

The error displayed is : ORA-00001 unique constraint violated

Thanks for your support and useful advise.
Re: APEX Error ! [message #559520 is a reply to message #559482] Tue, 03 July 2012 14:24 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That ("unique constraint violated") makes things much simpler.

It is not the file size that is the problem. You didn't say anything about the target table, so here are some general objections.

Imagine that there's a TEST table, no constraints at all. Inserts work just fine (no matter that two persons can't have the same ID):
SQL> create table test (id number, ename varchar2(20));

Table created.

SQL> insert into test (id, ename) values (1, 'Little');

1 row created.

SQL> insert into test (id, ename) values (1, 'Foot');

1 row created.


OK, now let's make things smarter (trying to simulate what you have). A new table has a primary key constraint which won't let us enter the same ID twice:
SQL> create table test
  2   (id    number    constraint pk_t primary key,
  3    ename varchar2(20)
  4   );

Table created.

SQL> insert into test (id, ename) values (1, 'Little');

1 row created.

SQL> insert into test (id, ename) values (1, 'Foot');
insert into test (id, ename) values (1, 'Foot')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_T) violated

Ooops! Exactly the same error message you got!

Another example: unique key constraint. It won't allow duplicates, but will allow NULLs:
SQL> create table test
  2   (id    number    constraint uk_t unique,
  3    ename varchar2(20)
  4   );

Table created.

SQL> insert into test (id, ename) values (1, 'Little');

1 row created.

SQL> insert into test (id, ename) values (1, 'Foot');
insert into test (id, ename) values (1, 'Foot')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_T) violated


SQL> insert into test (id, ename) values (null, 'Foot');

1 row created.


The next example: no constraint, but with a unique index on the ID column:
SQL> create table test (id number, ename varchar2(20));

Table created.

SQL> create unique index ui_t_id on test (id);

Index created.

SQL> insert into test (id, ename) values (1, 'Little');

1 row created.

SQL> insert into test (id, ename) values (1, 'Foot');
insert into test (id, ename) values (1, 'Foot')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UI_T_ID) violated

The same error again.


Therefore, I suppose that your case falls into one of the above examples - your target table has a primary (or, possibly, a unique) key constraint, or a unique index created on a column (or several columns).

The fact that "400 records file" gets inserted correctly was pure luck - obviously, there were no duplicates that are inserted into the constrained column(s). There must be duplicates in your "20.000 records file". Basically, Apex (somewhere behind the scene) does the same thing - takes "input" and INSERTs INTO the table.

So, what can you do? Make sure that there are no duplicates in the input file is one option.

Another one is to remove (drop or disable) constraints that make insert impossible, but that's not really a good option. Constraint is there for a reason, I suppose.

Finally, handle the exception, if possible. I'm sorry, but I never did what you do (i.e. upload Excel file contents into the database using Apex) so I can't speak from that point of view, but here's one option - see if you can adjust it in your case.
SQL> create table test
  2    (id     number   constraint pk_t primary key,
  3     ename  varchar2(20)
  4    );

Table created.

SQL> declare
  2    cursor c1 is
  3      select 1 id, 'Little' ename from dual union    -- won't be loaded (see ORDER BY - 'Foot' is inserted before 'Little')
  4      select 1 id, 'Foot'   ename from dual union    -- will be loaded
  5      select 2 id, 'Big'    ename from dual          -- will be loaded
  6     order by 2;
  7  begin
  8    for cur_r in c1 loop
  9      -- starting the inner BEGIN-END block which makes it possible
 10      -- to continue loading, even though there's an error
 11      <<inner_block>>
 12      begin
 13        insert into test (id, ename) values (cur_r.id, cur_r.ename);
 14
 15      -- handling the ORA-00001 exception (DUP_VAL_ON_INDEX)
 16      exception
 17        when dup_val_on_index then
 18          -- do nothing; simply skip that record
 19          null;
 20      end inner_block;
 21    end loop;
 22  end;
 23  /

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID ENAME
---------- --------------------
         1 Foot
         2 Big


I hope you got the idea; the important part here is the inner BEGIN-EXCEPTION-END block.

If you can't make it work, try to create a test case (on a simple TEST table, just like I did that), create an Apex page (that "simple" loading processing) on http://apex.oracle.com (you'll need a free account, if you don't have it already) and - once you do that - post login credentials, provide input Excel file and we'll have a look.

[Updated on: Tue, 03 July 2012 14:45]

Report message to a moderator

Previous Topic: PDF attachment from Apex application
Next Topic: Computation
Goto Forum:
  


Current Time: Thu Mar 28 15:03:41 CDT 2024