Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

Sequence does not exists.

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
sakthikumar
Asked:
sakthikumar
  • 2
  • 2
  • 2
  • +5
2 Solutions
 
chaauCommented:
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
 
HuaMinChenBusiness AnalystCommented:
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
 
sakthikumarAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
HuaMinChenBusiness AnalystCommented:
Is the synonym granted to you? How about to directly refer to the sequence name instead?
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
For procedure, I am getting sequence does not exists.

when do you get this error, at compile time or runtime?!
0
 
Geert GOracle dbaCommented:
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
 
awking00Commented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
sakthikumarAuthor Commented:
I am not able to find the correct answers, but these are some good useful infos.
0
 
DavidSenior Oracle Database AdministratorCommented:
Can you provide your DBMS version, and the relevant portion for the package?
0
 
slightwv (䄆 Netminder) Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now