Solved

Accessing remote sequences using dblinks

Posted on 2004-08-09
6
8,778 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

631 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