how to user REGEXP for inserting data from one table to another table [message #615439] |
Wed, 04 June 2014 01:12 |
|
jgjeetu
Messages: 373 Registered: July 2013 Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
I have 2 tables:- emp1 which has one column and emp2 which has 4 columns
following are the script for creating given tables:-
create table emp1(cv11 VARCHAR2(255))
create table emp2 (CV11_1A_DEDBUCKNAME VARCHAR2(255),
CV11_1B_DEDBUCKSTRT VARCHAR2(255),
CV11_1C_DEDBUCKEND VARCHAR2(255),
CV11_1D_DEDBUCKRATE VARCHAR2(255))
emp1 table has data like:-
cv11
1;20091003;20140103;-155.00;|
36;20131005;20131102;-113.00;|
1;20091003;20140104;-329.00;|
I have made one trigger for inserting data from emp1 table to emp2 table:-
create or replace trigger emptrg
after insert on emp1
for each row
begin
insert into emp2(CV11_1A_DEDBUCKNAME,CV11_1B_DEDBUCKSTRT,CV11_1C_DEDBUCKEND,CV11_1D_DEDBUCKRATE)
values(:new.REGEXP_SUBSTR(cv11, '[^;]+', 1, 1),:new.REGEXP_SUBSTR(cv11, '[^;]+', 1, 2),
:new.REGEXP_SUBSTR(cv11, '[^;]+', 1, 3),:new.REGEXP_SUBSTR(cv11, '[^;]+', 1, 4));
end;
After inserting values in EMP2 the data will appear like
CV11_1A_DEDBUCKNAME CV11_1B_DEDBUCKSTRT CV11_1C_DEDBUCKEND CV11_1D_DEDBUCKRATE
1 20091003 20140103 -155.00
original value was:- 1;20091003;20140103;-155.00;|
Note:- The problem is When i use insert command for inserting data from emp1 table to emp2 the REGEXP code works, but when i am using same code inside trigger, it gives syntax error.
I dont know much about Regular expression , My Senior has written code for REGEXP.
I have just used those codes inside trigger.
Now please explain the Trigger code is correct or not , and if it is not correct then why it is not working. How can we use REGEXP inside trigger for inserting data from one table to another table ?
Thanks
|
|
|
|
|
|
|
Re: how to user REGEXP for inserting data from one table to another table [message #615449 is a reply to message #615447] |
Wed, 04 June 2014 01:45 |
|
Littlefoot
Messages: 21806 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Works for me:
SQL> select * From v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> CREATE TABLE emp1 (cv11 VARCHAR2 (20));
Table created.
SQL> CREATE TABLE emp2 (cv11_1a_dedbuckname VARCHAR2 (20));
Table created.
SQL> CREATE OR REPLACE TRIGGER emptrg
2 AFTER INSERT
3 ON emp1
4 FOR EACH ROW
5 BEGIN
6 INSERT INTO emp2 (CV11_1A_DEDBUCKNAME)
7 VALUES (REGEXP_SUBSTR (:new.cv11,
8 '[^;]+',
9 1,
10 1));
11 END;
12 /
Trigger created.
SQL>
|
|
|