Hi,
>select banner from v$version;
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
I have the following requirement.
From UI if the user is saving ,multiple records are getting saved into the data base table for a candidate sb_no value.In UI the OS values are comma separated osnames ex,HP Unix,Solaris,Linux but when I have to save it in DB I have to save comma separated id values like 12,26,17 by mapping the osnames with the oses table.Similarly for Lang column.Here the constraint is I cannot fetch the records directly into temp table because the comma separated osname values from UI could exceed 4000 byte which is the maximum size of varchar2 column in DB.That is why the requirement is to convert into comma separated ids and save it in DB column.
The 3 DB table has the following structure:
create table a_main
(ID NUMBER,
sb_no VARCHAR2(30),
Pc_NUMBER VARCHAR2(30),
PC_FLAG CHAR(1),
INlON_ord NUMBER,
SP_OR_LATER_VSN CHAR(1),
OS VARCHAR2(4000),
LANG VARCHAR2(4000)
);
Ex:
In UI For SB_NO=SWB1
'SWB0','P',1,'S','UNIX,SOLARIS,LINUX','ENGLISH,FRENCH,GERMAN'--record 1
'SWB2','P',3,'S','HP UNIX,SOLARIS,LINUX','ENGLISH,USA ENGLISH'---record 2
'SWB3','P',2,'S','DOS,SOLARIS,LINUX','GERMAN,FRENCH'---record 3
Then in DB it will be save as follows( os and lang column are comma separated id values):
| id | SB_NO | Pc_NUMBER | PC_FLAG | INlON_ord | SP_OR_LATER_VSN | OS | LANG |
|---|
| seq.nextval | SWB1 | SWB0 | P | 1 | S | 11,12,56,128(CORRESPONDING ID VALUES OF OS) | SAME AS OS COLUMN |
| seq.nextval | SWB1 | SWB3 | P | 2 | S | 45,78,96,23 | |
| seq.nextval | SWB1 | SWB2 | P | 3 | S | 22,45,60 | |
structure of oses and lang table are as follows:
CREATE TABLE oses(
OSCD VARCHAR2(10 BYTE),
ID NUMBER DEFAULT NULL,
AG_OSCD VARCHAR2(250 BYTE),
);
insert into oses
values('HUX',12,'HP UNIX');
insert into oses
values('SUX',26,'SOLARIS');
insert into oses
values('LUX',17,'LINUX');
CREATE TABLE langs
(
LANGCD CHAR(2 BYTE),
LANGNM VARCHAR2(255 BYTE),
ID NUMBER NOT NULL
);
insert into langs
values('ENG','UK ENGLISH',35);
insert into langs
values('UEG','USA ENGLISH',27);
insert into langs
values('FR','FRENCH',45);
Can anyone please help me.
Thanks in advance.
Regards,
SB2011