1

I am trying to insert rows in a ErrorTable which has a few fields plus an idError which is supossed to be the primary key. I need idError to be autoincremented. However one requirement is we cannot use a trigger, so using O.AutoInc would not work for us.

We also tried to use plain sql using a sequence. However we have two blob fields which makes the query too long ( getting the string literal too long error).

Any idea about how to attack this problem? I am also considering to use UUID.

Note: we are using oracle-xe-11g

APC
  • 144,005
  • 19
  • 170
  • 281
Iván Portilla
  • 461
  • 1
  • 3
  • 13
  • What version of Oracle are you on? Have you seen [this question](http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle), specifically the points about identity columns in 12c. – Ben Mar 24 '17 at 14:43
  • We are using oracle-xe-11g – Iván Portilla Mar 24 '17 at 14:47

1 Answers1

2

In 11g you can have only implement an auto-incrementing identifier with a trigger. So it seems your requirements rule out anything except SYS_GUID. Find out more.

" it also represents another query to get that value "

Not necessarily. If you have the option to define the target table you can set a default values for the UUID column like this:

 create table t23 (
      id raw(16) default sys_guid() not null primary key
      , col1 varchar2(10)          
);

Then

SQL>  insert into t23 (col1) values ('ABC');

1 row created.

SQL> select * from t23;

ID                               COL1
-------------------------------- -----------
7DD7216E731C126537615FE1244B4B50 ABC

SQL>

Note: tested on 12C but should work in earlier versions.

APC
  • 144,005
  • 19
  • 170
  • 281
  • It seems a good option, but it also represents another query to get that value (as querying for the next val before inserting) or inserting it in a plain query (coming back to the "string literal too long" problem). But thanks for the answer :) – Iván Portilla Mar 24 '17 at 16:55
  • Again, your answer is awesome! But when i said that it represents another query, it was in my context, because i can't make a plain query because the "string literal too long problem" and i am not sure how to do that query with Slick in another way. Thanks again! – Iván Portilla Mar 28 '17 at 01:05
  • I don't know Slick, but it seems like that library is your problem. – APC Mar 28 '17 at 04:49