• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9997
  • Last Modified:

Accessing remote sequences using dblinks

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
Rambabu_Boyapati
Asked:
Rambabu_Boyapati
  • 3
  • 3
1 Solution
 
sachinbCommented:
Pl try,

select SEQ_NAME.nextval@DBLINK from dual

0
 
Rambabu_BoyapatiAuthor Commented:
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
 
sachinbCommented:
Hi,

Can you post the error?

Thanks,
~Sachin.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rambabu_BoyapatiAuthor Commented:
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
 
sachinbCommented:
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
 
Rambabu_BoyapatiAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now