Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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