Solved

Sequence does not exists.

Posted on 2013-11-13
11
313 Views
Last Modified: 2013-11-19
I have a sequence in remote DB. I am able to access the sequence (get nextval)
from a normal query in sql window, but I am not able to access it inside a procedure.

I am using the same username for querying and for procedure.

For procedure, I am getting sequence does not exists.

What could be the reason?
0
Comment
Question by:sakthikumar
  • 2
  • 2
  • 2
  • +5
11 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Does the procedure exist in the same schema? If not, you can access your sequence like this:
schema.sequence.NEXTVAL

Open in new window

0
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
Hi,
1. make sure the relevant DB link is working fine
2. ensure you have been granted with the privileges to use this sequence
0
 

Author Comment

by:sakthikumar
Comment Utility
Hi all
I am sorry, sequence is in the same DB only.

But I was having the public synonym. using that I can query in sql window, but not able to use it inside procedure.

What could be the reason.?
0
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
Is the synonym granted to you? How about to directly refer to the sequence name instead?
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
For procedure, I am getting sequence does not exists.

when do you get this error, at compile time or runtime?!
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.

 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 250 total points
Comment Utility
you can check what sequences you have access to with user_sequences
in your schema
select * from user_sequences@remote_db;

and in the other schemas:
select * from all_sequences@remote_db;

if the grant to the sequence was given via a role to the user
then it will be accessible from a normal query but not from within pl/sql
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
It may be that you have permissions on the sequence through a role, which is not carried over in a procedure. Try adding the AUTHID CURRENT_USER clause in your procedure declaration -
Ex.
create or replace procedure myproc(params in datatypes)
authid current_user as
...
0
 
LVL 23

Accepted Solution

by:
David earned 250 total points
Comment Utility
In your trouble-shooting, may I strongly recommend that you debug first using SQL*Plus rather than PL/SQL.  That's the best way I know of to ensure whether or not your statement is clean.
0
 

Author Closing Comment

by:sakthikumar
Comment Utility
I am not able to find the correct answers, but these are some good useful infos.
0
 
LVL 23

Expert Comment

by:David
Comment Utility
Can you provide your DBMS version, and the relevant portion for the package?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
My guess is the correct solution was the first one selected.

It is likely a GRANT issue.  The GRANT needs to be given directly and not through a ROLE.
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

Suggested Solutions

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…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

728 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

10 Experts available now in Live!

Get 1:1 Help Now