1

I want to insert a new row in my table. There I want to put the ID with the help of seq_name.nextval. So how to know the sequence name for that particular table?

Improve this question
8
  • 3
    There is no association between sequences and tables. I may create a table TAB1 and use a sequence tab1_seq, seq_tab1, sequence_for_tab1, ... Commented Jul 4, 2018 at 8:00
  • You will have to look in existing code that inserts into the table and see which sequence it uses. Or, upgrade to Oracle 12c and start using identity columns like everyone else. Commented Jul 4, 2018 at 8:39
  • In Oracle versions before 12c the only way to achieve this is by using a custom trigger : stackoverflow.com/a/25660884/3930237 Commented Jul 4, 2018 at 8:53
  • @Aleksej how to know what is the seq we have to use--> tab1_seq, seq_tab1,sequence_for_tab1? and how to check the existing sequence in oracle> Commented Jul 4, 2018 at 9:26
  • I created a new sequence to use as below: CREATE SEQUENCE "Kt_seq" MINVALUE 1 MAXVALUE 100 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ; Then I tried to use it for insertion but It throws error as "SQL Error: ORA-02289: sequence does not exist " even I created synonym for the sequence Kt_seq. How should I use it for insertion? Commented Jul 4, 2018 at 10:09

1 Answer 1

Reset to default
5

To use a sequence to generate IDs, you create it normally in the schema where the table is.

As user application user GEM_APP:

CREATE TABLE my_table (id NUMBER, col1 ...);
CREATE SEQUENCE my_seq;

The application user itself (and f.i. it's stored procedures) can use the sequence directly:

INSERT INTO my_table (id, col1) VALUES (my_seq.nextval, 'bla');

However, other users need the correct privileges. Usually, you grant select rights on the sequence to the same users or roles you grant insert rights on the table:

GRANT SELECT, INSERT ON my_table TO user_xy;
GRANT SELECT ON my_seq TO user_xy;

Then the other user can insert data into the table, but must qualify the schema:

INSERT INTO gem_app.my_table(id, col1) VALUES (gem_app.my_seq.nextval, 'bla');

You can create aliases to hide the schemas, some people like them, some not, but I would definitely not recommend to use PUBLIC synonyms as they are hard to control and create all kind of namespace clashes.

Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

Thanks for suggestion, this helps.
This is clear now,But earlier I was trying to access table (Other scenario) with other user, but it didn't work without synonym.Is it necessary to make the synonym or is there any other way to do the same?
Silly me, I forgot the SELECT privilege on the table. GRANT INSERT on my_table allows only to INSERT, but not to see the table. In normal life, it's always the combination of 'GRANT SELECT,INSERT,UPDATE,DELETE ON my_table', but I wanted to keep the answer short and clear.
No , you were absolutely right , my question is now with the different scenario " I was trying to access table (Other scenario) with other user, but it didn't work without synonym.Is it necessary to make the synonym or is there any other way to do the same?"
Synonyms are not necessary. You can always specify the table or sequence etc with the schema name (gem_app.my_table). If the other user cannot select from the table, the privilege is missing. (Or the other user is looking at the wrong place, for instance the dictionary view ALL_TABLES instead of USER_TABLES, or in SQL Developer the folder 'Other Users' etc.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.