HomeНаука и техникаRelated VideosMore From: Tech Query Pond

Oracle tutorial : How to create Id with AUTO INCREMENT in Oracle PL SQL

36 ratings | 9777 views
Oracle tutorial: How to create Id with AUTO INCREMENT in Oracle oracle tutorial for beginners sequence in oracle identity key in sql In this Oracle tutorial , we can create an auto increment field using ‘sequence’ object that can be assigned as primary keys. Using Oracle ‘sequence’ object, you can generate new values for a column. An Oracle sequence is an object like a table or a stored procedure. Examples CREATE SEQUENCE SYSTEM.MYSEQ START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER; CREATE OR REPLACE TRIGGER TR_CITY BEFORE INSERT ON CITY FOR EACH ROW BEGIN SELECT LPAD(LTRIM(RTRIM(TO_CHAR(myseq.NEXTVAL))),10,'0') INTO :NEW.id FROM DUAL; END; / Subscribe on youtube: https://www.youtube.com/channel/UCpiyAesWNYOXSz5GPq8lbkA For more tutorial please visit #techquerypond https://techquerypond.wordpress.com https://twitter.com/techquerypond identity column
Html code for embedding videos on your blog
Text Comments (12)
Md.Mizanur Rahaman (2 months ago)
please help me i can n`t create sequence this formate "YYMMDD00000" Example : 18121300001 year/ month/day then ID NUMBER
Md.Mizanur Rahaman (2 months ago)
+Tech Query Pond thanks for text
Tech Query Pond (2 months ago)
Hi you can use below query , "YYMMDD00000" Example : 18121300001 SELECT TO_CHAR(SYSDATE,'YYMMDD')||LPAD(LTRIM(RTRIM(TO_CHAR(SEQ_NAME.NEXTVAL))),5,'0') FROM DUAL OR FOR OUTPUT "YYMMDD1" Example : 1812131 SELECT TO_CHAR(SYSDATE,'YYMMDD')||SEQ_NAME.NEXTVAL FROM DUAL
ALI RAZA (5 months ago)
VERY HELPFULL ,THANKS;
Reshma Bhondave (6 months ago)
i want alphanumeric sequence like start with P00001...
Tech Query Pond (4 months ago)
You can use below code SELECT 'P'||LPAD(LTRIM(RTRIM(TO_CHAR(SEQ_NAME.NEXTVAL))),10,'0') FROM DUAL;
SHASHANK SAINI (10 months ago)
Hello Sir, I am using the sequence object in my db, after deleting the data sequence is not maintained and ID column value is continue increasing. So, I want to know that if we delete the data from the table the sequence is maintained according to table?
Tech Query Pond (9 months ago)
Sequence value is always incremented no matter you delete or truncate the table.A sequence is an independent object if you delete data from the table it does not affect the sequence value.
rajat sharma (10 months ago)
i am getting an error ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword 04071. 00000 - "missing BEFORE, AFTER or INSTEAD OF keyword" *Cause: The trigger statement is missing the BEFORE/AFTER/INSTEAD OF clause. *Action: Specify either BEFORE, AFTER or INSTEAD OF. can you help??
Tech Query Pond (9 months ago)
There may be some syntax issue.Can you comment trigger code which you are executing?
sanni kumar (1 year ago)
Thank you so much ..this is really working and this is that i was searching for..thank you for teach me about a very special topic of oracle sequence and trigger
Tech Query Pond (1 year ago)
Thanks sanni kumar

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.