DB trigger (merged) [message #385162] |
Fri, 06 February 2009 13:23 |
shrinika
Messages: 306 Registered: April 2008
|
Senior Member |
|
|
Hello, I have flat file, it has numbers with - sign appended on righ hand side. when i load into table, i need to move negative sign on left hand side... Since the field is NUMBER data type. I am using trigger... somehow, it is not working..
Here is the data file content.
34|xxx
-20|yyy
20-|hi
30|dd
4|scott
1-|tiger
45-|ra
984|last
Here is my control file
load data
infile 'test.txt'
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'
replace
into table test
fields terminated by "|"
trailing nullcols
(no,
name)
Table has two filds.
NO - NUMBER
NAME - VARCHAR2(50)
Here is my trigger to move the negative sign from right side to left side.
SQL> create trigger trg_test before insert on test
2 for each row
3 begin
4 SELECT
5 decode(instr(:new.no,'-',-1,1),0,:new.no,'-'||substr(:new.no,1,instr(:new.no,'-',-1,1)-1))
6 INTO :new.no
7 FROM DUAL;
8 end;
9 /
Trigger created.
The data load is not successful... Any help is appreciated..
I know that, we can load into temp table with CHAR data type and change the data. Is there a way we can load with out temp table??
I attached the log file for your info...
-
Attachment: test.log
(Size: 1.91KB, Downloaded 1330 times)
|
|
|
|
Re: DB trigger (merged) [message #385165 is a reply to message #385164] |
Fri, 06 February 2009 14:26 |
shrinika
Messages: 306 Registered: April 2008
|
Senior Member |
|
|
Michel - My problem is different... My input data is coming from flat file... I am using temp tables to resolve this problem. Anyhow, thank you for your response.
|
|
|
|
|
Re: DB trigger (merged) [message #385182 is a reply to message #385179] |
Sat, 07 February 2009 01:00 |
|
Michel Cadot
Messages: 68728 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Why don't you post a solution here?
Still trying to promote yourself and post your email like in all your other answers?
Maybe spamming us with it may lead to be spammed by it...
Regards
Michel
[Updated on: Sat, 07 February 2009 01:00] Report message to a moderator
|
|
|
Re: DB trigger (merged) [message #385191 is a reply to message #385162] |
Sat, 07 February 2009 04:03 |
|
ctl file....................
LOAD DATA
INFILE 'c:\test.txt'
TRUNCATE
INTO TABLE test
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( no "testin(:no)",
name
)
-------------------------end--------------------------------
Function
_________
create or replace function testin(tno in char)
return number
is
a number;
begin
select to_number(tno,'99s')
into a
from dual;
return(a);
end;
_______________________end_________________________________
other your need query added for this functions
thanks.
|
|
|
|
Re: DB trigger (merged) [message #385212 is a reply to message #385162] |
Sat, 07 February 2009 12:19 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Well, perhaps you could include the logic into the control file (no triggers, no temporary tables), such as:
Control file:load data
infile *
replace
into table test
fields terminated by '|'
trailing nullcols
( no "decode(substr(:no, -1),
'-', -1 * to_number(substr(:no, 1, instr(:no, '-') - 1)),
:no
)",
name
)
begindata
34|xxx
-20|yyy
20-|hi
30|dd
4|scott
1-|tiger
45-|ra
984|last
Table description and the result
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------
NO NUMBER
NAME VARCHAR2(50)
SQL> $sqlldr scott/tiger control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Sub Vel 7 19:15:29 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 7
Commit point reached - logical record count 8
SQL> select * from test;
NO NAME
---------- --------------------------------------------------
34 xxx
-20 yyy
-20 hi
30 dd
4 scott
-1 tiger
-45 ra
984 last
8 rows selected.
SQL>
|
|
|
|
|
|
Re: DB trigger (merged) [message #386456 is a reply to message #385252] |
Sun, 15 February 2009 04:17 |
m_golam_hossain
Messages: 89 Registered: August 2008 Location: Uttara, Dhaka, Bangladesh
|
Member |
|
|
@LittleFoot,
Error Message ORA-00955 is invoked when someone tries to create a database object in the same name as already exists. It is not related with Oracle Load command/tool where you are inserting the rows. And "ANY OTHER NAME" is not a name of a table.
Load Command can be implemented on any user created tables, provided specified columns with specified datatypes exist.
Thanks.
Mohd. Golam Hossain
Dhaka, Bangladesh.
|
|
|