Home » RDBMS Server » Server Utilities » DB trigger (merged)
DB trigger (merged) [message #385162] Fri, 06 February 2009 13:23 Go to next message
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 1173 times)

Report message to a moderator

Re: DB trigger (merged) [message #385164 is a reply to message #385162] Fri, 06 February 2009 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> select to_number('20-','99S') from dual;
TO_NUMBER('20-','99S')
----------------------
                   -20

1 row selected.

Regards
Michel

Report message to a moderator

Re: DB trigger (merged) [message #385165 is a reply to message #385164] Fri, 06 February 2009 14:26 Go to previous messageGo to next message
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.

Report message to a moderator

Re: DB trigger (merged) [message #385166 is a reply to message #385165] Fri, 06 February 2009 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use a function during load, have a look at the documentation.

Regards
Michel

Report message to a moderator

Re: DB trigger (merged) [message #385179 is a reply to message #385162] Sat, 07 February 2009 00:37 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member
please contact with me
i will help you.


contact me Rizwanadmani@gmail.com


please send me your all detail and what you do.

Report message to a moderator

Re: DB trigger (merged) [message #385182 is a reply to message #385179] Sat, 07 February 2009 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member
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.

Report message to a moderator

Re: DB trigger (merged) [message #385204 is a reply to message #385191] Sat, 07 February 2009 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just
no "to_number(:no,'99s')",
and get rid of the function?

It is great you finally showed your skills.

Also Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: DB trigger (merged) [message #385212 is a reply to message #385162] Sat, 07 February 2009 12:19 Go to previous messageGo to next message
Littlefoot
Messages: 21808
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>

Report message to a moderator

Re: DB trigger (merged) [message #385213 is a reply to message #385162] Sat, 07 February 2009 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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??

As far as I know, no actual minus sign is really stored in the NUMERIC datatype field.

Whether the minus sign appears on the left or the right is STRICTLY a data presentation issue & NOT a storage issue!

This discussion is much like debating whether time is stored in 12 hour format or 24 hour format; where the answer is neither format.

Report message to a moderator

Re: DB trigger (merged) [message #385246 is a reply to message #385213] Sun, 08 February 2009 06:59 Go to previous messageGo to next message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member
You can load data into any table either TEMP or ANY OTHER NAME, you just need to have the table with appropriate columns with appropriate datatypes.

Thanks.

[Updated on: Sun, 08 February 2009 07:00]

Report message to a moderator

Re: DB trigger (merged) [message #385252 is a reply to message #385246] Sun, 08 February 2009 08:51 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
m_golam_hossain
You can load data into any table either TEMP or ANY OTHER NAME

Well, not exactly true. Perhaps "temp" or "any other name" is occupied. You know, "occupied"? A problem for the one on the wrong side of the door? /forum/fa/2883/0/
ORA-00955: name is already used by an existing object

Report message to a moderator

Re: DB trigger (merged) [message #386456 is a reply to message #385252] Sun, 15 February 2009 04:17 Go to previous message
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.

Report message to a moderator

Previous Topic: Exp Oracle9i user/schema (Error 19206 encountered)
Next Topic: ORA-01502: index or partition of such index is in unusable state
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Apr 29 11:38:07 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.