| Inserting alphanumeric Primary Key [message #208489] |
Mon, 11 December 2006 01:15  |
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
|
Junior Member |
 
|
|
Hi,
I am new to oracle form and developer and i have a question relating to inserting record and i hope someone can help.Here is the scenario:
I have to insert a record in a table, which is :
product_master
(
PRODUCT_NO NOT NULL VARCHAR2(6),
DESCRIPTION VARCHAR2(25),
UNIT_MEASURE VARCHAR2(10),
QTY_ON_HAND NUMBER(8),
REORDER_LVL NUMBER(8),
COST_PRICE NUMBER(8,2),
SELLING_PRICE NUMBER(8,2)
)
I m using sequence to generate primary key but i have to generate an alphanumeric primary key....like P01,P02,P03......P13,P14....
i wrote the following code for button at when_button_pressed trigger :
BEGIN
go_block('product_master');
create_record;
SET_ITEM_PROPERTY('product_master.product_no',updateable,property_off);
SELECT 'P'||p_seq.nextval INTO :product_master.product_no
FROM product_master;
END;
this code is generating product_no like P1,P2,P3....but i need P01,P02,P03.....means i want to start from 01,02,03...
there is one more problem with this code....it is working fine when i m inserting 2nd record and so on...but when i m inserting record for the first time it is showing frm-40102 error (record must be entered or deleted first).
Please help me out to solve this problem.
thanks.
|
|
|
|
| Re: Inserting alphanumeric Primary Key [message #208493 is a reply to message #208489] |
Mon, 11 December 2006 01:21  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This will add a leading '0' to the number; however, are you SURE you'll have no more than 99 records in a table? I mean, will you always have two digits after 'P', or will there be hundreds, thousands, or even more records? In that case, change LPAD's "2" argument to something else (3, 4, ...).SELECT 'P' || LPAD(TO_CHAR(p_seq.NEXTVAL), 2, '0')
INTO :product_master.product_no
FROM dual; |
|
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
