sakthikumar
asked on
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?
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?
Hi,
1. make sure the relevant DB link is working fine
2. ensure you have been granted with the privileges to use this sequence
1. make sure the relevant DB link is working fine
2. ensure you have been granted with the privileges to use this sequence
ASKER
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.?
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.?
Is the synonym granted to you? How about to directly refer to the sequence name instead?
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
For procedure, I am getting sequence does not exists.
when do you get this error, at compile time or runtime?!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
...
Ex.
create or replace procedure myproc(params in datatypes)
authid current_user as
...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am not able to find the correct answers, but these are some good useful infos.
Can you provide your DBMS version, and the relevant portion for the package?
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.
It is likely a GRANT issue. The GRANT needs to be given directly and not through a ROLE.
Open in new window