Home » SQL & PL/SQL » SQL & PL/SQL » sequence cretion
sequence cretion [message #270551] Thu, 27 September 2007 08:27 Go to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Hi All,
I want to create a sequence so that sequence come as P1,P2,P3,..Pn.
help me.
Thanks and Regards
Sunil Gaurav

[mod-edit] illiterate IM speak removed.

[Updated on: Thu, 27 September 2007 08:28] by Moderator

Report message to a moderator

Re: sequence cretion [message #270559 is a reply to message #270551] Thu, 27 September 2007 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are lucky another moderator removed you IM speak before I saw it otherwise you didn't get any answer from me.
Read and follow OraFAQ Forum Guide.

It is not possible to create a sequence that returns such value but you can use a sequence-based function that does it.
SQL> create sequence s;

Sequence created.

SQL> create or replace function f return varchar2 is
  2    val pls_integer;
  3  begin
  4    select s.nextval into val from dual;
  5    return 'P'||val;
  6  end;
  7  /

Function created.

SQL> select f from dual;
F
-----------------------------------------------------------
P1

1 row selected.

SQL> /
F
-----------------------------------------------------------
P2

1 row selected.

SQL> /
F
-----------------------------------------------------------
P3

1 row selected.

SQL> /
F
-----------------------------------------------------------
P4

1 row selected.

Regards
Michel

Re: sequence cretion [message #270605 is a reply to message #270559] Thu, 27 September 2007 11:34 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
However, if this "ID" (or whichever name we might give to this sequence of yours) always contains 'P', you might consider:
a) not to store 'P' at all (but only a number)
b) store 'P' (and, perhaps, another letter(s) when it appears) in another table column

For reporting purposes, it is easy to concatenate 'P' with the rest of the sequence.
Re: sequence cretion [message #270696 is a reply to message #270551] Thu, 27 September 2007 15:34 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I would agree to lose the letter P from the id, and paste it on if you need to merely display it.

Also note that sequences won't guarantee sequential values, only unique ones.
Re: sequence cretion [message #270805 is a reply to message #270559] Fri, 28 September 2007 01:23 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Thanks alot Michel.
Regards
Sunil Gaurav
Re: sequence cretion [message #270808 is a reply to message #270805] Fri, 28 September 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't forget the other posters and you should think about their valuable advices.

Regards
Michel
Re: sequence cretion [message #270836 is a reply to message #270808] Fri, 28 September 2007 02:29 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps he didn't like the other advices Smile
Re: sequence cretion [message #270857 is a reply to message #270836] Fri, 28 September 2007 03:24 Go to previous message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
I appreciate smartin and Littlefoot for their lucid advice.And Michel thanks again for the code.
Thanks and Regards
Sunil Gaurav
Previous Topic: something about user_constraints and foreign key
Next Topic: Select Query with date comparison
Goto Forum:
  


Current Time: Thu Dec 08 08:10:02 CST 2016

Total time taken to generate the page: 0.13742 seconds