Trigger

[pranavgovind]

All,

Here is my pice of code from the database trigger.
How can i validate 'GS&M (D'Ambrosio)' string. since we have quote in middle of string....

IF :NEW.GENERIC_FN IS NULL AND :NEW.FISCGR_SUPVLASTNAME = 'GS&M (D'Ambrosio)' THEN
:NEW.GENERIC_FN := 'Sales & Marketing';
END IF;

Regards
GT

[Scorby]

Use 2 single quotes together:

IF :NEW.GENERIC_FN IS NULL AND :NEW.FISCGR_SUPVLASTNAME = 'GS&M (D''Ambrosio)' THEN
:NEW.GENERIC_FN := 'Sales & Marketing';
END IF;

[pranavgovind]

It would not work..........

SQL> create table test(no varchar2(20));

Table created.

SQL> create table test1(no varchar2(20));

Table created.

SQL> set scan off
SQL> create or replace trigger trig_test1234
2 before insert on test1 for each row
3 begin
4 if :new.no='GS&M(D''Ambrosio)' then
5 :new.no:='xxxx';
6 end if;
7 end;
8 /

Trigger created.

SQL> select * from test;

NO
--------------------
GS&M (D'Ambrosio)

SQL> insert into test1 select * from test;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

NO
--------------------
GS&M (D'Ambro

Use 2 single quotes together:

IF :NEW.GENERIC_FN IS NULL AND :NEW.FISCGR_SUPVLASTNAME = 'GS&M (D''Ambrosio)' THEN
:NEW.GENERIC_FN := 'Sales & Marketing';
END IF;

[gamyers]

Think you are missing a space in
GS&M(D''Ambrosio) vs
GS&M (D'Ambrosio)

If you are using 10g, there's user-defined quoting
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10807/02_funds.htm#i38404

[pranavgovind]

Sorry. I missed space. It works. Thank you very much...

[pranavgovind]

Dear all, I have one more question...

I have a problem in date conversion during the data load in sqlloader.

Here is the table.
============

SQL> create table test(load_date date);

Table created.

SQL>

Here is the control file
================
LOAD DATA
INFILE 'C:\hruser\hrdev\phase2\test\test_data.dat'
BADFILE 'c:\hruser\hrdev\phase2\test\test_data.bad'
APPEND INTO TABLE test
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS
(LOAD_DATE DATE "dd-mon-yyyy" NULLIF (LOAD_DATE=BLANKS))

Here is the data file content
=====================
20051130
20051230
20050230
20050530
20050923

Here is the trigger. This trigger converts the number into date conversion..


create or replace trigger trg_test
before insert on test
for each row
declare
v_string VARCHAR2(20) ;
v_string1 DATE;
v_month varchar2(3);
begin
IF :NEW.LOAD_DATE NOT LIKE '%/%' AND :NEW.LOAD_DATE NOT LIKE '%-%' THEN
v_string := :NEW.LOAD_DATE;
v_month := CASE SUBSTR(v_string,5,2)
WHEN '01' THEN 'JAN'
WHEN '02' THEN 'FEB'
WHEN '03' THEN 'MAR'
WHEN '04' THEN 'APR'
WHEN '05' THEN 'MAY'
WHEN '06' THEN 'JUN'
WHEN '07' THEN 'JUL'
WHEN '08' THEN 'AUG'
WHEN '09' THEN 'SEP'
WHEN '10' THEN 'OCT'
WHEN '11' THEN 'NOV'
WHEN '12' THEN 'DEC'
END;

v_string1 := SUBSTR(v_string,7,2)||'-'||v_month||'-'||SUBSTR(v_string,1,4);
:new.LOAD_DATE := v_string1;
END IF;
END;
/

I got the error message in log file as below.

Record 1: Rejected - Error on table TEST, column LOAD_DATE.
ORA-01861: literal does not match format string

[davey23uk]

You say the format is dd-mon-yyyy but supply the data as yyyymmdd

Spot the problem?

[pranavgovind]

Davey, It works for me. Thank you.

[WilliamR]

And after fixing the SQL*Loader control file you can get rid of the trigger.

The trigger was wrong anyway because :new.load_date is already a DATE value.

If you do need to convert a value like '20051130' to a date you would normally just use TO_DATE(var,'YYYYMMDD').

[pranavgovind]

Thanks William. I removed the trigger. My original plan is to do the date conversion in db trigger. I have two set of flat file. One flat file has load date with mm/dd/yy:hh24:mi:ss, and another flat file load_date with yyyymmdd.

Now i am using two different control file to load the data. I thought of doing both in one control file. That is the reason, i came up with tirgger. Some how, it did not work in trigger. Thanks

Regards
GT