3

I want to create a synonym for a sequence in oracle and fetch currval from it.

I created a synonym using this statement.

CREATE SYNONYM NUMGEN FOR MY_SEQ;

when I fetch the currval or extval from NUMGEN it generate error, synonym doesn't exist.

SELECT NUMGEN.currval FROM dual;

Can anyone help me to fetch currval from synonym.

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
Omer
  • 39
  • 1
  • 1
  • 5
  • 2
    There must be something you are not telling us. This does work. See the example here: http://sqlfiddle.com/#!4/883cb/1 –  Dec 09 '12 at 10:49
  • currval doesn't work neither in your example. – mistapink Dec 09 '12 at 11:18
  • In my database even nextval isn't working, i don't understand why. Thanks for the help. – Omer Dec 09 '12 at 11:22
  • Both are working now, but i dont understand why i have execute nextval before executing currval. – Omer Dec 09 '12 at 11:29
  • 3
    You **have** to call `nextval` before you can call `currval`. This is clearly documented in the manual. –  Dec 09 '12 at 13:06
  • [this link explains how to do this](http://www.dbforums.com/showthread.php?1675349-How-to-access-the-Sequence-created-in-other-Schema) – gloomy.penguin Jan 21 '15 at 17:40

1 Answers1

1

You might have not given the privileges to the user. Try giving privilege using either of following query and try.

GRANT SELECT ANY SEQUENCE to NEWDB;

GRANT SELECT ON ORIGINALDB.SEQUENCENAME TO NEWDB;

abhijeet104
  • 486
  • 4
  • 9