Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sequence does not exists.

Posted on 2013-11-13
11
Medium Priority
?
327 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
[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
  • 2
  • 2
  • 2
  • +5
11 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39647070
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 11

Expert Comment

by:HuaMinChen
ID: 39647071
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
ID: 39647117
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39647127
Is the synonym granted to you? How about to directly refer to the sequence name instead?
0
 
LVL 14

Expert Comment

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

when do you get this error, at compile time or runtime?!
0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 750 total points
ID: 39647256
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 32

Expert Comment

by:awking00
ID: 39648711
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 750 total points
ID: 39649197
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
ID: 39659162
I am not able to find the correct answers, but these are some good useful infos.
0
 
LVL 23

Expert Comment

by:David
ID: 39659507
Can you provide your DBMS version, and the relevant portion for the package?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39659622
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

721 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