Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Accessing remote sequences using dblinks

Posted on 2004-08-09
6
Medium Priority
?
9,296 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 150 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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