Hi All,

I created a table with a primary key field. My Java application increment this value by 1. Now I want to add sequence in this table so that I don't have to create new code. I already have data in this table and other table refers to this table.

Manually I can check the latest value in the table in my local development environment and create the sequence but I want to write some script so that anyone can use in any environment.

Can anyone help in this?

Thanks,
Anjib

>Can anyone help in this?
I don't know about your capabilities, but I do know my limitations.
I am unable to write code going against the DB when I don't know table name or column names.

COPY & PASTE results from following SQL:

SELECT * FROM V$VERSION;

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
=============================

AGENCY (agency_id, agency_code, agency_name)
GROUP (group_id, group_name, agency_id)

SEQUENCE & TABLE are 100% independent objects.
SEQUENCE can exist without TABLE.
TABLE can exist without SEQUENCE.

>I want to write some script so that anyone can use in any environment.
what exactly does this script need to do?

My problem is I had already create a table without sequence and have data in it. Now how can i add sequence in that table without corrupting existing data?

>My problem is I had already create a table without sequence and have data in it.
>Now how can i add sequence in that table without corrupting existing data?
In some/many/most cases SEQUENCE is used by INSERT TRIGGER.
By definition application is NOT currently implemented to expect/use TRIGGER to populate AGENCY_ID
So application code must be changed at the same time TRIGGER & SEQUENCE are deployed.
If application is working OK now, I see no advantage to make any change to use SEQUENCE.

You can create an on insert trigger using the sequence and igore what the application wants to insert. for example in the trigger you can have

:new.agency_id := agency_s1.nextval;

and no matter what the application wants to put in, you would use the sequence.

I understand that whenever TRIGGER & SEQUENCE are deployed code need to be changed too. Thanks for that information.

Now consider the situation where you do development and push the build to production. Some other person will do production deployment.You have no control of production.
You have already pushed the schema w/o sequence to the production and people are using it. So there are millions of data and some number to represent latest data. Suppose in production you have record from 1,2,.....555. Now if I want to add the sequence and change in production DB if I can know last number is 555 I can say my sequence start at 556 or 600 or whatever value greater than 555 to create a sequence. Real problem is when you don't know what is the latest increment value. You can't just write script and say create a sequence with 200. That will surely bring conflict.

So I am trying to find the solution to avoid that conflict.

And yes application is running by creating helper method to generate next sequence number for table. But I think adding sequence will be easy. Isn't so?

>But I think adding sequence will be easy. Isn't so?
Nothing is impossible for the person who does not have to do it.
If the change was trivial, then you would not be here asking for us to do your job for you.

SELECT MAX(AGENCY_ID) FROM AGENCY;

SQL Script

create sequence test_seq
start with (SELECT MAX(AGENCY_ID) FROM AGENCY)
increment by 1
nomaxvalue;

no good...

First find maximum AGENCY_ID (let's say it is equal to 51345).

Then create a sequence, using the "above number + 1" as the starting value:

>First find maximum AGENCY_ID (let's say it is equal to 51345).
Between above & below AGENCY_ID could be incremented by other session.
>Then create a sequence, using the "above number + 1" as the starting value:

By all means. But applications have to be maintained (as if you don't know that). Usually, it is done during non-working hours. If there are none (i.e. the application & database are up 24/7), @unzip will probably say so. For other situations, that shouldn't be a problem.