Linking of Schemas

[jam26]

Hi,

I made it this way: I created this sequence in Schema D06SP04D

In creating Sequence:

CREATE SEQUENCE equip
START WITH 70000
INCREMENT BY 1
NOCACHE
NOCYCLE;

Then I link this to other schema like C08ML03B

update C08ML03B.autoke y
set seed =D06SP04D.equip .nextval
where tbname ='ASSETS'

Is there anything wrong with this?


Thanks.

[Pilgrim333]

What happens with the update statement? Is every row updated with the same value? Is that the problem?

If you query the sequence twice, does it give the same number both of the times?

 select  D06SP04D.equip.nextval from dual

Pilgrim

[jam26]

Actually it gives different numbers. But if I connect this sequence in our Maintenance Application (Maximo), It doesn't work in other schema which I linked on this sequence. I expected that before they generate a number, the schema will look first on the last number generated on the sequence.

Thanks.

[Pilgrim333]

A sequence is nothing more then a value stored in the database. If you fetch a value from the sequence, it will be incremented by one and save that number and return that number. No matter from what schema you fetch the next value of the sequence, it should always give you the next value of the sequence. I think something is implemented wrong in the maintenance application when you fetch the sequence value.

As you stated, the sequence works fine when you query the values from dual or do an update statement.

Pilgrim.

[jam26]

I used only this script to connect the application on the sequence.

update C08ML03B.autoke y
set seed =D06SP04D.equip .nextval
where tbname ='ASSETS'

What happend is, at first the system will look into the last number generated by the sequence. For example the last number is 10 then the system generates 11 for new number. Then when I connect to other schema, the system will also gives 11 as the new number but it must be 12. I think there's a problem with the execution on the application because when I tried to run the sequence in sql plus worksheet, it generates correct number series across all schemas

Thanks.

[amitpatel66]

I used only this script to connect the application on the sequence.

update C08ML03B.autoke y
set seed =D06SP04D.equip .nextval
where tbname ='ASSETS'

What happend is, at first the system will look into the last number generated by the sequence. For example the last number is 10 then the system generates 11 for new number. Then when I connect to other schema, the system will also gives 11 as the new number but it must be 12. I think there's a problem with the execution on the application because when I tried to run the sequence in sql plus worksheet, it generates correct number series across all schemas

Thanks.

Which application you are using? Yes you will face such kind of issues becuase its risk using a sequence across multiple schemas. Anyways in this case you can add 1 to your nextval value and do an UPDATE. but again not advisable.

Why dont you try generating such sequence in the application that you are using?

[Pilgrim333]

There is no problem with the sequence. There is no risk of using a sequence over multiple schema's. We have implemented the same thing over here, and we are working with it without any problems. This is what sequences are intended for.

If you can narrow down the problem and still are stuck, let us know.

Is the application that you are using an oracle application?

Pilgrim

[jam26]

I am using Maximo Application. I have multiple schemas on the application and I need to connect those schemas in only one number series. I f I use the sequence generated by the application, it will possibly generates a same number when I connected into other schemas. The directive is to have only one number series in all schemas that's why I created a sequence.

Thanks.

[jam26]

Yes. we are using Oracle on the Maximo Application.

[Pilgrim333]

Can you show the code where the sequence is fetched?

Pilgrim

[jam26]

Sorry Pilgrim. But what do you mean by code?

[Pilgrim333]

I dunno how maximo works, but at some point there has to be a statement that will fetch the sequence.
Or is it the update statement you posted earlier and you are submitting that through Maximo?

Pilgrim.

[amitpatel66]

There is no problem with the sequence. There is no risk of using a sequence over multiple schema's. We have implemented the same thing over here, and we are working with it without any problems. This is what sequences are intended for.

If you can narrow down the problem and still are stuck, let us know.

Is the application that you are using an oracle application?

Pilgrim

You will face the hiccup when the sequence is used with CACHE. You really will with multiple instances. Definately there is no issue with the sequence since it is working fine from sqlplus.

[Pilgrim333]

The sequence is stored in the database memory, using multiple schema's accessing the same sequence under one schema won't result into hiccups. The only way the hiccups will occur is when the cached values are erased, eg.
system failure

Anyhoo, if the main concern is keeping your numbering without gaps, you can always use the nocache option.

Pilgrim

[amitpatel66]

The sequence is stored in the database memory, using multiple schema's accessing the same sequence under one schema won't result into hiccups. The only way the hiccups will occur is when the cached values are erased, eg.
system failure

Anyhoo, if the main concern is keeping your numbering without gaps, you can always use the nocache option.

Pilgrim

But again even that is the case alright. you cannot expect system to be 100% accurate. you need to consider those exceptions.

Yes @OP is using NOCACHE option here. But even then some issue while using sequence across schemas.

@OP,

Please post how your application is connecting to different schemas and using a sequence. Is there any concept like a particular functionalities are implemented for using particular schema and so on?

It would really help if you can post about the application - database communication