Linking of Schemas

[jam26]

Hello,

Is there anybody can help me on how can I make an auto number across all my 15 schemas. I need to create it for tagging of my equipment.

Thanks,
Jam

[Pilgrim333]

If there was no application at all then the sequence was working perfeclty from sqlplus

There is no problem with the sequence, with or without the application.

Budget does not come in to play here. The application is already available. It need to be customized by including the functionality of having a auto number generation across multiple schemas of a database. And yes why not an application have more than 150 tables if there are 15 schemas involved. Its just an assumption and nothing else which can be clarified if @OP answers the question of letting know to how many tables are used in each schema by the application.

Budget is meant, if your database can't handle 150 triggers, then you should get a more powerful system.
The application should not be customized, the autonumbering should happen at the database side. Even if the database has a 1000+ tables.

Pilgrim.

[amitpatel66]

It doesnt make sense creating 150 odd triggers. What if in future, number of table increases, one cannot keep creating a trigger.

If and only if the number of tables are less (totalling all the tables across 15 schemas that are used by an application) and there will not be any new table inclusion in future then creating a trigger is acceptable. Again it have to be tested before it could work.

[amitpatel66]

There is no problem with the sequence, with or without the application.

Pilgrim.

There is a problem with the sequence when accessed from application, then what are be discussing here for so so long??

[Pilgrim333]

There is a problem with the sequence when accessed from application, then what are be discussing here for so so long??

The application is not accessing the sequence, the poster just updated the autokey of the application once with the value of the sequence and expected that it would automatically link it with the sequence.

So, there is no problem with the sequence when accessed from the application, because the application is not accessing the sequence.

Pilgrim.

[amitpatel66]

The application is not accessing the sequence, the poster just updated the autokey of the application once with the value of the sequence and expected that it would automatically link it with the sequence.

Pilgrim.

This doesnt mean that the sequence is accessed from an application? If you really dont want to make the sequence work from application then why should it be linked to an application?

Anyways I can utilize this time and rethink of any other solution for this issue rather just increasing the size of this thread, becuase its important that @OP understands what I am trying to say.

[Pilgrim333]

It isn't accessed from the application by the autokey.

@OP

Meanwhile, in googley land...

It seems that this problems is something more people have. Someone posted it on his blog, hopefully it will help you, and you need not go through all the trouble of making (all) the triggers.

Maximo and unique id's

Pilgrim.

[jam26]

Hello Experts,

I already made to link all my schemas to the sequence using Trigger. This may not be possible without your help.

Thank You Very Much!!!!

Jam

[jam26]

Hello Experts,

Now that I have connected the sequence across all schemas, Is it possible that I connect my other database on this sequence using trigger?

Thanks,
Jam

[Pilgrim333]

Hi,

I don't know what you mean. It is possible to use the sequence for numbering in tables in another database. The sequence can be used through a database link. Syntax for that is:

select <sequenceschema>.<seqname>.nextval@<dblink> from Dual ; 

If you just want to insert data in tables in the database where the sequence resides, from other databases and use the sequence, that is no problem. It doesn't matter from where you insert the data in the tables, because you implemented the use of the sequence in the triggers of the tables, the sequence will always be used.

Pilgrim.

[jam26]

Thank you very much Pilgrim. I made a DB link successfully and its now working. :-)


Jam

[Pilgrim333]

Good to hear Jam, congrats on getting it working.

A good lesson, when implementing bussiness rules, autonumbering etc. that needs to be done on a table, always implement it in the table triggers. This way you ensure that these necessities will always be met, no matter what interface is used for manipulating data.

If you have more issues, please let us know.

Pilgrim.