Home » SQL & PL/SQL » SQL & PL/SQL » how to user REGEXP for inserting data from one table to another table (oracle 10g)
how to user REGEXP for inserting data from one table to another table [message #615439] Wed, 04 June 2014 01:12 Go to next message
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 #615442 is a reply to message #615439] Wed, 04 June 2014 01:18 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Not
:new.regexp_substr(cv11, ...)
but
regexp_substr(:new.cv11, ...)
Re: how to user REGEXP for inserting data from one table to another table [message #615444 is a reply to message #615439] Wed, 04 June 2014 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not ":new.REGEXP_SUBSTR(cv11, '[^;]+', 1, 1" but "REGEXP_SUBSTR(:new.cv11, '[^;]+', 1, 1"

Do you understand what [:]NEW is?

Re: how to user REGEXP for inserting data from one table to another table [message #615447 is a reply to message #615444] Wed, 04 June 2014 01:40 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

yup , i realised that after posting query, but after making changes , the code is not working still.
now i am using it for one column only:-

create or replace trigger emptrg
 after insert on emp1
 for each row
 begin
 insert into emp2(CV11_1A_DEDBUCKNAME)
 values(REGEXP_SUBSTR(:new.cv11, '[^;]+', 1, 1));
 end;

still it gives errror i.e
sql statement ignored
& 'REGEXP_SUBSTR' must be declared
Re: how to user REGEXP for inserting data from one table to another table [message #615448 is a reply to message #615447] Wed, 04 June 2014 01:43 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

now everything is fine , it is working.
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 Go to previous message
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>
Previous Topic: Invalid Synonym in SQL Plus Vs Toad
Next Topic: Filter by Count of particular field
Goto Forum:
  


Current Time: Fri Mar 29 02:42:19 CDT 2024