Solved

Accessing remote sequences using dblinks

Posted on 2004-08-09
6
7,210 Views
Last Modified: 2013-12-11
Hi All,

I am trying to access sequence of a remote database using DBlink in Oracle.
( i want sequence.nextval)
     
Is this possible..?

if so, can anybody tell me the syntax for this pls..?

Thanks a lot
Rambabu
0
Comment
Question by:Rambabu_Boyapati
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:sachinb
ID: 11757792
Pl try,

select SEQ_NAME.nextval@DBLINK from dual

0
 

Author Comment

by:Rambabu_Boyapati
ID: 11768612
Hi Sachinb,

This did not work . It gave me error.

I have a feeling that we can't sequences using DB links, but not sure.

Thanks
Rambabu
0
 
LVL 3

Expert Comment

by:sachinb
ID: 11768642
Hi,

Can you post the error?

Thanks,
~Sachin.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Rambabu_Boyapati
ID: 11774272
Hi ,
I am executing this statment.

select error_seq_num.nextval@a001p from dual;

(the sequence exists)

The error is : ORA-02289: sequence does not exist ORA-02063: preceding line from A001P

Thanks
Rambabu
0
 
LVL 3

Accepted Solution

by:
sachinb earned 50 total points
ID: 11775326
This is posted on Metalink - Please try this.

HTH,
~Sachin.

Problem Description:  
====================  
 
You attempt to select nextval from a sequence on a remote database using the  
dual table and you receive an ORA-02289 error.  
   
    ORA-02289: "sequence does not exist"  
        Cause: The specified sequence does not exist, or the user  
               does not have the required privilege to perform  
               this operation.  
       Action: Make sure the sequence name is correct, and that you  
               have the right to perform the desired operation on  
               this sequence.  
 
You can see that the sequence does exist by selecting from the user_sequences  
table on the remote database.  
 
 
Problem Explanation:  
====================  
   
This is a limitation of the dual table.  
 
     
Search Words:  
=============  
   
synonym  
ORA-2289
 
Solution Description:  
=====================  
 
To select nextval from a sequence on a remote database, you can create a  
synonym for the remote sequence or you can change the order of the statement.  
       
   
1. Create a Synonym  
   ----------------  
 
   SQL> create synonym remote_seq for seq_remote@db_link;  
 
   SQL> select remote_seq.nextval from dual;  
 
   NEXTVAL  
   ----------  
            9  
 
 
- OR -  
 
 
2. Change the order of the Statement  
   -------------------------------  
 
   SQL> select seq_remote.nextval from dual@db_link  
 
   ORA-02289: sequence does not exist  
 
   SQL> select seq_remote.nextval@dblink from dual;  
 
   NEXTVAL  
   ----------  
           11  
 
 
Solution Explanation:  
=====================  
 
By creating a synonym for the sequence or changing the order of the select  
statement you will be able to select nextval from the sequence without error.

0
 

Author Comment

by:Rambabu_Boyapati
ID: 11779966
Hi Sachin,

I got the solution from your message.

The user with which i created the DB link does not have the select priviliges on that sequence. I did not check that.

Thanks for that.

Rambabu
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now