How to select oracle sequence from different schema [message #335164] |
Mon, 21 July 2008 05:10 |
ednms
Messages: 39 Registered: November 2007
|
Member |
|
|
Hi all
Really need your help. I need to select oracle sequence from different schema.
I try to select the sequence from another schema by creating database link and select the sequence from dual@dblink.
This is how i select the sequence:
select TEST_DB_SEQ.nextval from dual@NPCSOLN.DBLINK
but then it throws the error ORA-02289:sequence does not exist, even though TEST_DB_SEQ exist in the schema that i want to select
Hope can help..TQ in advance
|
|
|
|
|
|
Re: How to select oracle sequence from different schema [message #335172 is a reply to message #335168] |
Mon, 21 July 2008 05:45 |
ThomasG
Messages: 3211 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
A "different schema" can't be on a different host, since a different schema would be in the same database.
There can be multiple schemas in one database.
There can be multiple databases on one host.
If your sequence is in a different DATABASE, then you could write a wrapper function on the remote DB and use that, like this example :
I just use this function on remote DBs to get sysdate from there:
create or replace function Now return date is
begin
return(sysdate);
end Now;
I can select the local and remote sysdate then as:
select sysdate, now@dblink from dual;
from Ask Tom.
|
|
|
|
|
|