SQL & PL/SQL :: Oracle Primary Key Error On Sequence Number Generator
Mar 11, 2011
I have a table called table 1 and I am trying to insert a few values in this table and i am constantly getting bogged down by a primary key error. Strange thing is this primary key called "ID" on the table is a system generated sequence value number. The error is
"ORA-00001: unique constraint (Schema1.LICN_PK) violated"
Table structure:
*****************************************
CREATE TABLE schema1.table1
(
ID NUMBER(12),
LITM_ID NUMBER(12),
PROG_PROGRAM_CD VARCHAR2(2 BYTE),
SCHED_NBR VARCHAR2(3 BYTE),
SCHD_VERSION_YR NUMBER(4),
SCHD_VERSION_NBR NUMBER(3),
[code]........
****************Insert statement*************
insert into table1
(id,
litm_id,
prog_program_cd,
sched_nbr,
schd_version_yr,
[code].......
why the error is coming up and any way to resolve it. I checked the max sequence number value and kept running it for a while and then tried to run my insert statement but no luck yet.
View 5 Replies
ADVERTISEMENT
Apr 12, 2010
Is there a function or a process to generate and maintain an alphanumeric sequence?
I need to be able to sequentially generate both a 3 and 5 byte case sensitive alphanumeric primary key that uses A-Za-z0-9 characters intermittently of which the 5 byte MAY be a subset of the 3 byte. e.g. the key can be 00000 or a3BD7.
so I need to be able to generate a new 5 char using 3 char base and if that value is not available, generate a unique 5 char value and allocate the substr(1,3) of that value backwards.
Ex1: unique customer location comes in - not a preexisting customer name (same name,different location address doesn't exist) --> select nextval.[5char] into v_long_c to insert into customer_loc table, and substr (v_long_c, 1, 3) for customer name table.
EX 2: unique customer location comes in. customer name exists in customer table with 3char val A39, but that location doesn't exist in customer_location table (5chars are location specific).
I need to take the A39 and generate new 5char for that unique location using that prefix (A39) if possible.
However, a number of a39-- already exist, though not all assigned to the same customer name (we're trying to keep them grouped together but that might not be logically feasible)
How do I select next a39||[2char] for that unique location - and if that value is not available (all 62*62 possibilities have been used for A39--), select nextval.[5charseq] into long_key.
I suspect someone out there knows the functions I can use to create this or has written a package to do just this; I suspect with enough time I could do it, but I don't currently have the time or knowledge to develop it within these deadlines. I thought it would be easier.
My short-sighted solution was to create a static table of all iterations of A-z0-9 5 char values, select one, mark it used and move on. Unfortunately 62^5 is a substantial number (913+million records) and that table took a LOT of space, causing my development server to groan and crack miserably. indexing on it takes a lot of space too (and trying to build multiple indexes exceeded database size). But without an index on the 3char field, selecting an available 5 code from it based on the customer_3char prefix took five minutes - much longer than the fraction of a second I need.
View 5 Replies
View Related
Aug 26, 2010
My requirement is I have to get a sequence no generated for 2 set of groups. Here is an example to better resolve it.
Gr1
Seq Data
--------------
1 Data1
2 Data2
3 Data3
GR
Seq Data
--------------
4 Data1
5 Data2
View 1 Replies
View Related
Jul 15, 2012
I'm using Oracle 11g and I'm trying to set the sequence nextval as default to Primary Key row, when I create the table, Oracle throws an exception that columns are not allowed. Here is an example.
CREATE TABLE EMP (
EMP_ID VARCHAR2(50) PRIMARY KEY DEFAULT MYSECUENCE.NEXTVAL)
View 10 Replies
View Related
Mar 14, 2005
I'm trying to create a sequence for a primary key to simply auto-increment by the default of 1. I have a sql script written to generate mt tables, and I'm not sure how to modify the script to include the sequence. I also just want the sequence for a specific column, ie, PK, not the PK in all tables.
Here's a snippet from my script:
create table image
(
image_id int NOT NULL,
source_id int NOT NULL,
CONSTRAINT image_id_pk PRIMARY KEY (image_id),
CONSTRAINT fk_source_id FOREIGN KEY (source_id) REFERENCES source(source_id)
);
Would I add the create sequence statement right after the create table, and if so, how do I apply the sequence to only 1 table and a single column?
View 5 Replies
View Related
Mar 9, 2011
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
View 4 Replies
View Related
Feb 19, 2011
I have set up a schema using a DDL script in Oracle 10g. I am linking this through to a Visual Basic 2008 fron end. The connection is fine. My database of is for a car hire company project.
I need to know how to create an auto increment for a primary key field, like Access does.
For example here is my table structure for my Rental_Payment table.
-- TABLE RENTAL_PAYMENT
CREATE TABLE RENTAL_PAYMENT (
PAYMENTNO NUMBER NOT NULL,
CARDNO NUMBER NOT NULL,
[Code]....
So for example the first record would appear as:
PAYMENTNO CARDNO AMOUNT CARD_TYPE
======== ===== ====== =======
01
I would then enter values and the next payment number would be 02 etc.
PAYMENTNO CARDNO AMOUNT CARD_TYPE
======== ===== ====== =======
01 123412 £40.00 VISA
02
Visual Basic will usually have a star (*) beside a new entry field so 02 will be
* 02
etc........
I have about creating a sequence. So how can I tie this to my table?
View 12 Replies
View Related
Sep 17, 2012
In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue.
This is the current scenario -
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03
When I try to query based on the time stamp, primary should also go up. To be very clear on what I would like to have, please consider the following example.Without using NOCACHE option, I need to have the data in the following order.
Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03
In other words, sequence number should always increment along with the time.
View 2 Replies
View Related
Apr 16, 2013
I have a specific problem like below of allocating proper pallets to received quantity. row generator query feature.
CREATE TABLE PALLETMASTER
(
PALLETTYPE VARCHAR2(1 BYTE),
CAPACITY NUMBER(2)
);
Insert into PALLETMASTER (PALLETTYPE, CAPACITY) Values ('A', 18);
Insert into PALLETMASTER (PALLETTYPE, CAPACITY) Values ('B', 16);
Insert into PALLETMASTER (PALLETTYPE, CAPACITY) Values ('C', 15);
Insert into PALLETMASTER (PALLETTYPE, CAPACITY) Values ('D', 14);
[code]...
View 12 Replies
View Related
Oct 25, 2011
In my table two colums are there say Col1 and Col2. Data is:
Col1 Col2
----- -----
15 20
So i want to get the result in some Column as:
ROWNUM Col
------- -----
1 15
2 16
3 17
4 18
5 19
6 20
View 1 Replies
View Related
Mar 2, 2013
I have a following select statement where I am not generating a sequence in any of these WHERE clause, GROUP BY or ORDER BY clause, DISTINCT clause, along with a UNION or INTERSECT or MINUS or sub-query.
I have query like this
"SELECT
AER_ID_TEMP.AER_ID,
AER_ID_TEMP.D__PRNO,
AER_ID_TEMP.D__PCNT,
CAST((select char_value from aris_param_data where param_id = 101)||seq_record_id.nextval AS INT) as RECORD_ID
FROM AER_ID_TEMP"
but still encountering a error "ORA-02287 sequence number not allowed"
View 2 Replies
View Related
Oct 15, 2010
CREATE OR REPLACE TRIGGER audit_emp AFTER
INSERT OR UPDATE OR DELETE ON Employee FOR EACH ROW
declare
my emp_audit%ROWTYPE;
begin
if inserting then my.action := 'I';
[code].......
It works alright but i need to enter a sequenced value to a column audit_id which is a column in the emp_audit table when a change takes place, but when i define the variable it gives be an error as bad bind variable so where can i define this column in this trigger so get a sequenced value every time i d or u occurs .
View 5 Replies
View Related
Mar 31, 2011
Is there any way to get the last Sequence Number generated regardless the Sequence name? Something like dbinfo('sqlca.sqlerrd1') within Informix?
Imagine I have 2 sequences. Imagine that I run both several times until 20:00 hours. Imagine that at 21:00 hours I would like to know what was the last generated number created by any of the sequences. I don't wann know wich sequence generated the last NEXTVAL, I only want to know the value of the last NEXTVAL used.
Is it possible without mentionating the Sequence name?
View 4 Replies
View Related
Jul 26, 2010
I am using following Procedure/function for inserting DATA into New table:
PROCEDURE NEMPID IS
OFFICER CHAR(1) := '0';
OFFICIAL CHAR(1) := '9';
CURSOR DOJSUP IS
[Code]....
Get/Retrieved data from personal and cat9 files and Inserted Data stored in file NEMP as
EMP_ID NEMP_ID
------- ---------
111 01-8722
132 01-9224
88 01-8318
114 01-8717
106 01-8517
[Code]....
I want also generate a sequence/serial number for same category/designation of personals/employees exist in personal file ?
Like 001, 002 ,003 at the end of NEMPID field.
EMP_ID NEMP_ID
------- ---------
111 01-8722-001
132 01-9224-002
88 01-8318-003
114 01-8717-004
[Code]....
HOW CAN GENERATE SEQUENCE/SERIAL NUMBER OF SAME CATEGORY EMPLOYEES AND INSERT INTO TABLE NEMP?
View 16 Replies
View Related
Jun 6, 2011
I am facing issue with my sequence number about sequence number generated..I want it to follow the sequence all the time, i.e. it should take next incremental number to last generated seauence, but it generates random one all the time, i.e
s.nextval() = 4
s.nextval() = 5
s.nextval() = 10
s.nextval() = 543
s.nextval() = 544
My sequence defination is as below
CREATE SEQUENCE s
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
View 11 Replies
View Related
Dec 7, 2010
I have Table Data in format :-
--------------------------
ABC
ABC
ABC
XYZ
XYZ
and i have a requirement where I need the output in this format.
-------------
ABC 1
ABC 2
ABC 3
XYZ 1
XYZ 2
what query to be used for this
View 2 Replies
View Related
Feb 21, 2013
I want to insert same sequence number for repeated data else new sequence.
my data is as below
EMP_NAMEEMP_INV
PrasCTI
PrasCTI
ShariqWitness
KateCTI
ShariqAssisted
ShariqWitness
the first 2 record are same Pras CTI and Pras CTI so i want my sequence generate value once as below
1 Pras CTI
1 Pras CTI
2 Shariq Witness
3 Kate CTI
4 Shariq Assisted
2 Shariq Witness
Shariq and witness are repeating so same sequence number but Kate CTI occured once so nextval of sequence
How can i acivee it
CREATE TABLE EMP_INV
(
EMP_NAME VARCHAR2(100 BYTE),
EMP_INV VARCHAR2(100 BYTE)
)
Insert into EMP_INV
(EMP_NAME, EMP_INV)
Values
('Shariq', 'Witness');
[Code]...
View 12 Replies
View Related
Jun 1, 2010
I have a result set with three columns as 'Product Category', 'Product' & 'QtySales' and 10 rows, sorted in the order Product Category, Product. This means, a product category will have one or more products under it.
Now i want to add a fourth column to my result set, which should display a incremental number sequence from starting from 1, 2, 3.. for each row. Also when the value of the Product Category (1st column) changes, this sequence should be restarted again from 1.
Col1 Col2 Col3 Col4
PC1 P1 10 1
PC1 P2 20 2
PC2 P3 30 1
PC2 P4 10 2
PC2 P5 15 3
PC3 P6 25 1
View 1 Replies
View Related
May 17, 2012
I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..
Quote:NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3
I need a SQL to get the data like
Quote:NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50
Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
View 12 Replies
View Related
Feb 13, 2013
I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.
create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));
insert into tb_test
(select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');
I am having ORA-00937 : not a single-group group function error
View 23 Replies
View Related
Dec 5, 2012
create sequence seq minvalue 1 maxvalue 99999 start with 1 increment by 1;
create table t ( id number, b date);
insert into t values (seq.nextval,sysdate);
update t set b=sysdate-1 where id=seq.currval;
update t set b=sysdate-1 where id=seq.currval
*
ERROR at line 1:
ORA-02287: sequence number not allowed here
how can i update as above ..
View 7 Replies
View Related
Jul 5, 2010
I have a table:
Name
_____
Smith Street
Smith Street
John Street
Ed Street
Ed Street
Ed Street
and need to assign sequence numbers only when the record (Name) changes, e.g. :
Name Seq
_____ ____
Smith Street 1
Smith Street 1
John Street 2
Ed Street 3
Ed Street 3
Ed Street 3
I have experimented with row_number partition but then i just get the sequence returning to 1 when the name value changes.
If I grouped the records by Name I would like to have unique, sequential numbers: 1, 2, 3 but where there is the same name I would like the sequence to stop and the number to replicate?
View 9 Replies
View Related
Mar 27, 2012
In my form I have a submit button, when i click on it a sequence number to be generated and displayed in a text box.
I have created a sequence and tried inserting into table, it works fine however it's not getting displayed in form.
Note: Tried giving sequence.sequence_name.nextval in initial value of the text box, didn't work showed FRM-11324 error.
--When button pressed--
DECLARE
v_seq number(20);
BEGIN
select seq_name.nextval
into v_seq
from dual;
[Code]...
View 15 Replies
View Related
Dec 21, 2011
i want to get serial numbers in oracle forms. i have one data block with 10 rows and a non database display item with corresponding length as that of the original data block .
when ever i am executing the form i want to see the serial number generated in the display item and when i inserted a new item or row it should show the corresponding rownum.
eg:
empno name serialno
5 tom 1
4 tinil 2
6 sofy 3
7 john 4
1 albert 5
2 michel 6
i want to get the above answer using forms.
View 7 Replies
View Related
Apr 1, 2013
I want the query to generate the sequenctial number from 1 for the new financial year in some query.
View 6 Replies
View Related
Oct 3, 2010
I ve a data in table as follows
code status seqnce Length
B1 AVL 1 10
B2 AVL 2 10
B3 ASG 3 10
B4 AVL 4 10
B5 AVL 5 10
B6 AVL 6 10
B4 ASG 7 10
B4 ASG 8 10
I need to group the AVL status codes with the sum of lengths Ex) B1 to B2 - 20 B4 to B6 - 30 like that.
View 5 Replies
View Related
Jun 13, 2013
I have a CSV file with 100 records and one of the column as FILE_ID. I want to load one unique number for all 100 recs not for every records.
suppose my sequence returns 3 as next val i want to load 3 for all 100 records. How to implement this in control file or sh file . I am using shell script to call sqlldr.
View 2 Replies
View Related
Jun 12, 2013
SELECT
DECODE(BREINV1.NAMEKEY, NULL,'0','1') "BRE_INV1",
DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,
[Code]....
My problem, I have the query which works correctly. However as you can see there is always a sequence number in the end of variables and I am not sure the total number of cases, so I have to create a loop for the same query.
I am doing the same things on BREINV1 BREINV2 BREINV3 BREINV4... AND BRENUMINV1 BRENUMINV2... so the sequence should add a number in the end of variables...
View 39 Replies
View Related
May 17, 2011
is there any query to find number of primary keys present in a table.
View 13 Replies
View Related
Nov 26, 2012
I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.
So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....
I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.
So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?
View 12 Replies
View Related
