Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Trigger trim problem
Oracle Trigger trim problem [message #213813] Fri, 12 January 2007 07:26 Go to next message
sharath160
Messages: 9
Registered: August 2006
Junior Member
Hi,

My trigger is somewhat like below:

AFTER INSERT
ON TEMP_CATALOG
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
existing char(40);
....

select ShipKey into existing from item_table where trim(item_id) = :new.item_id;

update item_table.... where shipkey = existing

if exception then Insert into item_table ......

My main query is concerned with select statement in trigger

1)select ShipKey into existing from item_table where trim(item_id) = :new.item_id;
is working fine, but

2)select ShipKey into existing from item_table where item_id = :new.item_id;
is not working well. It is not selecting any value for shipkey. fyi, item_id in item_table is also char(40). The item_id is generally < 40 spaces.

Normal sql query like
3)select shipkey from item_table where item_id='12334' too is working fine.

Why is it query 2 is not working well? I want query '2' to work well (avoiding trim as it consumes more resources).

Can anybody help?

Regards,
Shab
Re: Oracle Trigger trim problem [message #213816 is a reply to message #213813] Fri, 12 January 2007 07:35 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is not TRIM function that consumes resources, but CHAR datatype. It should be VARCHAR2, both in a table and as a trigger variable datatype.

[Updated on: Fri, 12 January 2007 07:36]

Report message to a moderator

Re: Oracle Trigger trim problem [message #213820 is a reply to message #213816] Fri, 12 January 2007 07:44 Go to previous messageGo to next message
sharath160
Messages: 9
Registered: August 2006
Junior Member
Could be, but I can't change datatype from char to varchar2 in the table item_table table as it is product defined on which i am working.

[Updated on: Fri, 12 January 2007 07:45]

Report message to a moderator

Re: Oracle Trigger trim problem [message #213825 is a reply to message #213820] Fri, 12 January 2007 07:55 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can not? Why?
SQL> CREATE TABLE TEST (dname CHAR(50));

Table created.

SQL> INSERT INTO TEST SELECT dname FROM DEPT;

4 rows created.

SQL> ALTER TABLE TEST MODIFY dname VARCHAR2(50);

Table altered.

SQL> SELECT dname, LENGTH(dname) FROM TEST;

DNAME                                              LENGTH(DNAME)
-------------------------------------------------- -------------
ACCOUNTING                                                    50
RESEARCH                                                      50
SALES                                                         50
OPERATIONS                                                    50

SQL> UPDATE TEST SET dname = trim(dname);

4 rows updated.

SQL> SELECT dname, LENGTH(dname) FROM TEST;

DNAME                                              LENGTH(DNAME)
-------------------------------------------------- -------------
ACCOUNTING                                                    10
RESEARCH                                                       8
SALES                                                          5
OPERATIONS                                                    10

SQL>
Previous Topic: How do I find a gap in sequence?
Next Topic: ora-01722 error when using to_number
Goto Forum:
  


Current Time: Fri Dec 02 16:21:19 CST 2016

Total time taken to generate the page: 0.33014 seconds