Solved

Accessing remote sequences using dblinks

Posted on 2004-08-09
6
6,934 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

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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

705 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

17 Experts available now in Live!

Get 1:1 Help Now