Home » Developer & Programmer » Forms » Inserting alphanumeric Primary Key  () 1 Vote
Inserting alphanumeric Primary Key [message #208489] Mon, 11 December 2006 01:15 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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;
Re: Inserting alphanumeric Primary Key [message #208496 is a reply to message #208493] Mon, 11 December 2006 01:35 Go to previous messageGo to next message
pooja_09
Messages: 28
Registered: June 2005
Location: Delhi
Junior Member

thanks buddy...it is working...i know it was very stupid question but as i m new in this i may need this kind of help from u people very often...once again thanks a lot.
Re: Inserting alphanumeric Primary Key [message #208669 is a reply to message #208489] Mon, 11 December 2006 19:55 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I suggest you put the 'product_no' code into the When-Create-Record trigger, not the When-Button-Pressed.

David
Previous Topic: Session Maintenance in Forms
Next Topic: Network Messaging
Goto Forum:
  


Current Time: Sat Dec 03 08:13:26 CST 2016

Total time taken to generate the page: 0.17905 seconds