Linking of Schemas

[Pilgrim333]

Well, that is expected, the hiccups if you use the cache option. But i don't see the relation between this and the use of multiple schemas.

I am really curious about the issues you will encounter when you are using a sequence over multiple schemas. Could you be more specific about the problems that can occur?

Pilgrim.

[amitpatel66]

Well, that is expected, the hiccups if you use the cache option. But i don't see the relation between this and the use of multiple schemas.

I am really curious about the issues you will encounter when you are using a sequence over multiple schemas. Could you be more specific about the problems that can occur?

Pilgrim.

You can see one of them here....An applications using multiple schemas but want a single sequence to be used across. But NEXTVAL gives the old value first time when called from different schemas

[Pilgrim333]

No, you can't say by forehand that this is caused by using multiple schemas. That is absurd. Probably the application is fetching the sequence wrong.

The OP already stated that it works when used from the different schemas, only when using it from the application it is not incrementing the sequence. So, this is not caused by using multiple schemes.

Pilgrim.

[amitpatel66]

Well you can just go thorugh my words in my previous post. an APPLICATION using multiple schemas. You forgot the word APPLICATION my friend.

[Pilgrim333]

In that case the multiple schemas is irrelevant. Oracle ensures that a sequence can be used over multiple schemas. So if an application is causing problems, the fault is with the application and not with multiple schemas.

Just to set things rights, the use of a sequence over multiple schemas is no problem. When using an applicationm the application has to fetch the sequence correctly, if it doesn't then it won't work correctly using multiple schemas or not.

Pilgrim.

[amitpatel66]

Well you are saying the same that I said in different phrase.


Couple of issues that might be the reason are:

1. Application not able to fetch correct value
2. Since multiple schemas are used, applications is not able to generate NEXTVAL instead it gives OLD value first.

Let @OP come back with some more inputs which would help.

[jam26]

Well what happened here is there's an original autokey generated by our application per schema. What I did is I changed the autokey of application to the sequence I created.

I used this script to change the autokey of application to the sequence.

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

The D06SP04D is the original schema where I created the sequence. The C08ML03B is another schema which I am trying to connect to the sequence of schema D06SP04D.

Thanks.

[amitpatel66]

Well if the application itself generates an auto key then why dont you make use of that. It should be probably the better way to do so.

[Pilgrim333]

Well what happened here is there's an original autokey generated by our application per schema. What I did is I changed the autokey of application to the sequence I created.

I used this script to change the autokey of application to the sequence.

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

The D06SP04D is the original schema where I created the sequence. The C08ML03B is another schema which I am trying to connect to the sequence of schema D06SP04D.

Thanks.

There is your problem. The autokey from the application and the sequence are out of sync. Probably you just updated the autokey once, and then are fetching numbers from the autokey. But if you do that, the sequence won't get updated. Your options are as follows:

1. Use the autokey of the application, as stated above.
2. Link every column of every table in all the schemas that make use of the sequence to the sequence.
3. Find a way to link the autokey to the sequence, so that it fetches a new value from the sequence every time it's used.


Pilgrim.

[jam26]

My objective is to have a number series across all 15 schemas. If I let the autokey generates the number it will also generates 15 same numbers.

I think Pilgirm is right, I need to link all the schemas to the sequence I made but my problem is how the sequence automatically increment once I link all these schemas on it.

Thanks.

[Pilgrim333]

You can do it by creating a row level trigger on all the tables that need the sequence as numbering. In the trigger, get the next value from the sequence and put it in the apropriate column, overwriting any value inserted. If you have a lot of tables, it will be a lot of work. But then you are sure that the right value of the sequence will be used.

Pilgrim.

[jam26]

Wow! does it mean if I have 15 schemas, it will be 15 tables? Can you teach me how to do that please.

Thanks.

[Pilgrim333]

Well, If you have more table per schema that use the sequence then it will be more tables.

Here is some more info on triggers, and there even is an example of how to use the sequence for a table key. Experiment with it and if you have any questions, just ask.

Triggers

Pilgrim.

[jam26]

Ok I will check this. Is it true that the trigger is risky to use?

Thanks.

[Pilgrim333]

Risky in what way? You just have to be sure that the code does exactly what you want. Test it good before implementing it in production.

Pilgrim.