Home » SQL & PL/SQL » SQL & PL/SQL » Sequence having number with character (oracle 9i ,XP)
Sequence having number with character [message #404265] Thu, 21 May 2009 02:43 Go to next message
gurusuryas
Messages: 49
Registered: March 2009
Location: Chennai
Member

Hi friends,

i have one table like,

doctor_id | doctor_name | specialization

but doctor_id will be inserted by sequence.
i need doctor_id will be like this

doctor_id
--------------
DOC001
DOC002
DOC003
DOC004


how we can create sequence for this output

Reply please,
Re: Sequence having number with character [message #404270 is a reply to message #404265] Thu, 21 May 2009 02:53 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Concatenation of a string and a sequence number left padded with zeros.
Re: Sequence having number with character [message #404272 is a reply to message #404265] Thu, 21 May 2009 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

2/
What is the purpose of prefixing the sequence number by 'DOC' IN THE DATABASE, why not using the sequence number and if you want this format IN THE DISPLAY then add it at display time?

Regards
Michel
Re: Sequence having number with character [message #404531 is a reply to message #404265] Fri, 22 May 2009 02:36 Go to previous messageGo to next message
gurusuryas
Messages: 49
Registered: March 2009
Location: Chennai
Member

i have some tables like,

1.Doctors_details Table:

doctor_id | doctor_name | specialization

i need doctor_id will be like this

doctor_id
--------------
DOC001
DOC002
DOC003
DOC004

DOC --> Specifies Doctors,

2.Nurses_details table

Nurse_id | Nurse_name | Nurse_Addr |

nurse_id will be like this

nurse_id
------------
NS342
NS353
NS423

NS --> Specifies Nurses

3. Patient_Details Table

patient_id | patient_name | Address

patient_id will be like this

Patient_id
----------
PAT234
PAT254
PAT257

PAT--> Specifies Patient

doctors,nurse,patient ids will be inserted by sequences automatically , not manually
how we can create sequences for these output ?
Re: Sequence having number with character [message #404533 is a reply to message #404531] Fri, 22 May 2009 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answers, why do you repost the same question?

Regards
Michel
Re: Sequence having number with character [message #404538 is a reply to message #404531] Fri, 22 May 2009 03:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will need to create a BEFORE INSERT trigger on each of these tables, and populate the PK value with the next value from the sequence, left padded with zeros, and with DOC or PAT prepended to it.
Re: Sequence having number with character [message #404547 is a reply to message #404538] Fri, 22 May 2009 04:22 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
As JRowbottom said this Example will help you
http://www.oracle.com/technology/oramag/code/tips2003/051103.html

Thanks
Sriram

[Updated on: Fri, 22 May 2009 04:31]

Report message to a moderator

Previous Topic: control cannot go to exception
Next Topic: PLS_INTEGER Vs NUMBER data type
Goto Forum:
  


Current Time: Tue Dec 06 10:03:09 CST 2016

Total time taken to generate the page: 0.10626 seconds