Home » SQL & PL/SQL » SQL & PL/SQL » How to select oracle sequence from different schema (Oracle 10g)
How to select oracle sequence from different schema [message #335164] Mon, 21 July 2008 05:10 Go to next message
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 #335165 is a reply to message #335164] Mon, 21 July 2008 05:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When you have access to the other SCHEMA you can just do :

select OTHER_SCHEMA.TEST_DB_SEQ.nextval from dual;


You only need database links when you want to connect to another DATABASE.


Re: How to select oracle sequence from different schema [message #335168 is a reply to message #335165] Mon, 21 July 2008 05:24 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Thx for the reply...

but what if the schema is not in the same host? i have try with schema with the same host it doing fine but if from different host it return the same error as i mentioned before.
Re: How to select oracle sequence from different schema [message #335170 is a reply to message #335168] Mon, 21 July 2008 05:38 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Act what i mean is to select sequence from other database.anyone can help?
Re: How to select oracle sequence from different schema [message #335172 is a reply to message #335168] Mon, 21 July 2008 05:45 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.

Re: How to select oracle sequence from different schema [message #335175 is a reply to message #335172] Mon, 21 July 2008 05:57 Go to previous messageGo to next message
mfinn
Messages: 9
Registered: July 2008
Location: UK
Junior Member
Try

SELECT mysequence.NEXTVAL@mydblink
  FROM dual
/


instead of

SELECT mysequence.NEXTVAL
  FROM dual@mydblink
/

Re: How to select oracle sequence from different schema [message #335177 is a reply to message #335172] Mon, 21 July 2008 05:58 Go to previous messageGo to next message
ednms
Messages: 39
Registered: November 2007
Member
Tq so much, it works!
Re: How to select oracle sequence from different schema [message #335193 is a reply to message #335177] Mon, 21 July 2008 07:00 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Tq ?!

Are you that grateful, that you can not be bothered to write those extra few letters?
Please do not use IM-speak in the forum.
Previous Topic: Problem in joining 2 queries
Next Topic: PL/SQL: ORA-04052: error occurred when looking up remote object
Goto Forum:
  


Current Time: Sat Dec 03 01:23:27 CST 2016

Total time taken to generate the page: 0.14317 seconds