We help IT Professionals succeed at work.

inserting value in table in oracle form

chalie001
chalie001 asked
on
411 Views
Last Modified: 2018-02-02
hi i what to insert value in my screen like this
IN my screen the use will enter value like this
the user will pass this value passed for the screen to appear 1234-12-125-8328 its code is 5890851


Associted_Account Ass_quantity Ass_type    End_item_Code                  Nha_code
8745155258732            23        A                     9874166787831                  3748177602234

8745-15-525-8732 9874-16-678-7831

after saving the value should display like this

Associted_Account Ass_quantity Ass_type    End_item_Code        Nha_code
8745-15-525-8732            23        A                  9874-16-678-7831    3748-17-760-2234


am only saving in one table ASSOCIATED_Account_ITEM after saving the value will be like o r the value which will be inserted ill be like

Insert into ASSOCIATED_Account_ITEM (code,ASS_code,ASS_QUANTITY,ASS_TYPE,END_ITEM_code,NHA_code)  values(5890851,1890855,500,'D',2853826,3857007);


this is my test data
create table account_ITEM_tt  
(  
  code          NUMBER(11) not null,  
  serial_code       VARCHAR2(20) not null,  
  nrm          varchar(10),
  mcr          VARCHAR2(2) default '7' not null  
  
);  
alter table account_ITEM_tt  
  add constraint code_ITEM_PK primary key (code);  
 -- accNo= mcr + serial_code  
    
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (5890851,'1258328','12',1234);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (6853822,'1734937','13',9887);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (7857003,'1602830','14',987);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (8146674,'1875331','13',2344);  
   5325-14-242-9490 accNo = nrm+mcr+serial_code 8745-15-525-8732
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (1890855,'5258732','15',8745);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (2853826,'6787831','16',9874);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (3857007,'7602234','17',3748);  
  Insert into account_ITEM_tt (code,serial_code,mcr,nrm) values (4146678,'8875635','18',9847);

9874-16-678-7831 


create table account_ITEM_ww  
(  
  code                 NUMBER(11) not null  
    
);  
alter table account_ITEM_ww  
  add constraint code_ITEM_ww_PK primary key (code);  
    
  alter table account_ITEM_ww  
  add constraint FK_ww_code foreign key (code)  
  references account_ITEM_tt (code)  
  deferrable initially deferred;  
Insert into account_ITEM_ww (code)values(5890851);    
Insert into account_ITEM_ww (code)values(6853822);    
Insert into account_ITEM_ww (code)values(7857003);    
Insert into account_ITEM_ww (code)values(8146674);  

Insert into account_ITEM_ww (code)values(1890855);    
Insert into account_ITEM_ww (code)values(2853826);    
Insert into account_ITEM_ww (code)values(3857007);    
Insert into account_ITEM_ww (code)values(4146678);  
    
create table ASSOCIATED_Account_ITEM  
(  
  code          NUMBER(11) not null,  
  ass_code      NUMBER(11) not null,  
  ass_quantity NUMBER(11,2),  
  ass_type     VARCHAR2(1),  
  end_item_code NUMBER(11),  
  nha_code      NUMBER(11)  
);  
  
  
alter table ASSOCIATED_Account_ITEM  
  add constraint PK_ASS_ITEM primary key (code,ass_code);  
    
  alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_ASS_QTY  
  check (ass_quantity  > 0);  
    
alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_ASS_code_EIS  
  check (ass_code <> end_item_code);  
alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_ASS_code_NHA  
  check (ass_code <> nha_code);  
alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_code_ASS_code  
  check (code <> ass_code);  
alter table ASSOCIATED_Account_ITEM  
  add constraint CHK_ASS_code_EIS  
  check (code <> end_item_code);  
alter table ASSOCIATED_Account_ITEM  
  add constraint FK_ASS_CII_ASS_code foreign key (ASS_code)  
  references account_ITEM_ww (code);  
    
alter table ASSOCIATED_Account_ITEM  
  add constraint FK_ASS_CII_END_ITEM_code foreign key (END_ITEM_code)  
  references account_ITEM_ww (code);  
    
alter table ASSOCIATED_Account_ITEM  
  add constraint FK_ASS_CII_NHA_code foreign key (NHA_code)  
  references account_ITEM_ww (code);  
    
alter table ASSOCIATED_Account_ITEM  
  add constraint FK_ASS_CII_code foreign key (code)  
  references account_ITEM_ww (code);  
  
  
  Insert into ASSOCIATED_Account_ITEM (code,ASS_code,ASS_QUANTITY,ASS_TYPE,END_ITEM_code,NHA_code)  values(5890851,6853822,500,'D',7857003,8146674);

i pre-insert trigger like this
decalre
cursor get_code is
         select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
              where nrm = substr(:blockName.global_code,1,4)
               and  mcr = substr(:blockname.global_code,5,2);
               and  serial_code = substr(:blockname.globalcode,10,4)
               and ww.code = tt.code;


cursor get_ass_code is
         select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
              where nrm = substr(:blockName.ass_code,1,4)
               and  mcr = substr(:blockname.ss_code,5,2);
               and  serial_code = substr(:blockname.ass_code,10,4)
               and ww.code = tt.code;


cursor get_nha_code is
         select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
              where nrm = substr(:blockName.nha_code,1,4)
               and  mcr = substr(:blockname.nha_code,5,2);
               and  serial_code = substr(:blockname.nha_code,10,4)
               and ww.code = tt.code;


cursor get_end_code is
         select ww.code from ccount_ITEM_ww ww,account_ITEM_tt tt
              where nrm = substr(:blockName.endcode_code,1,4)
               and  mcr = substr(:blockname.end_code,5,2);
               and  serial_code = substr(:blockname.end_code,10,4)
               and ww.code = tt.code;



begin

--- how can i put those cursor in one palce so i can do this
   
	for curr in get_code loop
                    :blockname.codevalue := curr.applicode;
          end loop;
  
         for curr in get_ass_code loop
                    :blockname.ass_code_ent := curr.code;
          end loop;


        for curr in get_nha_code loop
                    :blockname.nhacode_ent := curr.code;
          end loop;


         for curr in get_end_code loop
                    :blockname.end_code_ent := curr.code;
          end loop;




end;

  

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
select regexp_replace(mycol,'([0-9]{4})([0-9]{2})([0-9]{3})([0-9]{4})','\1-\2-\3-\4')

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.