Solved

Sequence does not exists.

Posted on 2013-11-13
11
318 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 10

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
Industry Leaders: 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!

 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39647127
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]
ID: 39647223
For procedure, I am getting sequence does not exists.

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

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 250 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 250 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

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!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

735 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