Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » APEX Error ! (APEX)
|
|
|
|
|
|
| Re: APEX Error ! [message #559520 is a reply to message #559482] |
Tue, 03 July 2012 14:24  |
 |
Littlefoot
Messages: 21823
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 |
|
|
|
Goto Forum:
Current Time: Thu Dec 12 07:13:45 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
