Home » SQL & PL/SQL » SQL & PL/SQL » Strange trigger behavior with UPPER() LOWER() (Oracle 9.2.0.8)
Strange trigger behavior with UPPER() LOWER() [message #412942] Mon, 13 July 2009 09:07 Go to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

Hello,

when creating a new trigger

CREATE OR REPLACE TRIGGER Riegel_RTR_B_GueteIstOf
BEFORE UPDATE OF GueteIst ON Riegel
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
  vUserID     GLO_USERGROUP.GLO_USERID%TYPE;

LOCATION   CONSTANT   GLOLOG.TYPLOCATION := 'Trigger Riegel_RTR_B_GueteIstOf';
BEGIN
    SELECT GLO_USERID INTO vUserId FROM GLO_USERGROUP
    WHERE  UPPER(GLO_USERID)= gloenv.getUsername
    AND    GROUPID = 'TP_Planer';

    ...


i encountered a strange error:

################################################################
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "MIDAS3.RIEGEL_RTR_B_GUETEISTOF"
line 11
ORA-04088: error during execution of trigger 'MIDAS3.RIEGEL_RTR_B_GUETEISTOF'
################################################################

line 11 is: WHERE UPPER(GLO_USERID)= gloenv.getUsername

GLO_USERID contains a VARCHAR(20) with letters in upper and lowercase like UserName. No blanks/special chars. The stored procedure gloenv.getUsername returns a VARCHAR(20) in uppercase, the username of the session.

The trigger works fine if i don't use the translation to uppercase for GLO_USERID or lowercase for GLO_USERID and gloenv.getUsername. As soon as i add a UPPER() or LOWER() in that line i get the error above.

This only happens if used in the trigger. The select statement works just fine if i execute it manually.

I am open to any idea/workaround. Thanks in advance for your help.
Re: Strange trigger behavior with UPPER() LOWER() [message #412944 is a reply to message #412942] Mon, 13 July 2009 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
desc GLO_USERGROUP.

Quote:
The stored procedure gloenv.getUsername returns a VARCHAR(20) in uppercase, the username of the session.

Prove it.

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 and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Mon, 13 July 2009 09:28]

Report message to a moderator

Re: Strange trigger behavior with UPPER() LOWER() [message #412946 is a reply to message #412942] Mon, 13 July 2009 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>GLO_USERID contains a VARCHAR(20)
Interesting datatype.

what happens if/when returned string is longer than 20?
Re: Strange trigger behavior with UPPER() LOWER() [message #412947 is a reply to message #412942] Mon, 13 July 2009 09:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you paste the results of the following query:
SELECT text
FROM all_source
WHERE owner = 'MIDAS3'
AND   name  = 'RIEGEL_RTR_B_GUETEISTOF'
order by line;


Could you also post the results of
DESC GLO_USERGROUP;
Re: Strange trigger behavior with UPPER() LOWER() [message #412971 is a reply to message #412942] Mon, 13 July 2009 12:42 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I'd say your problem is in gloenv.getUsername. Maybe doing a TO_NUMBER on a string.
Re: Strange trigger behavior with UPPER() LOWER() [message #413046 is a reply to message #412971] Tue, 14 July 2009 02:29 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

@Michel Cadot
sorry for not using the formatter.

The prove is it does not matter if i use gloenv.getUsername or set a constant of say WHERE UPPER(GLO_USERID)= 'ORACLEFAQ'. The problem persists. If i use an actual username as the constant the trigger works for this user. The problem only arises when using upper() or lower().

@BlackSwan
Then it will not be matched?!

@JRowbottom
SELECT text 
FROM all_source 
WHERE owner = 'MIDAS3' 
AND   name  = 'RIEGEL_RTR_B_GUETEISTOF'
order by line;

TEXT
TRIGGER Riegel_RTR_B_GueteIstOf  
BEFORE UPDATE OF GueteIst ON Riegel  
REFERENCING OLD AS OLD NEW AS NEW  
FOR EACH ROW  
DECLARE  
  vUserID     GLO_USERGROUP.GLO_USERID%TYPE;   
  
  LOCATION   CONSTANT   GLOLOG.TYPLOCATION := 'Trigger Riegel_RTR_B_GueteIstOf';  
BEGIN  
    SELECT GLO_USERID INTO vUserId FROM GLO_USERGROUP    
    WHERE  UPPER(GLO_USERID)= gloenv.USERNAME  
    AND    GROUPID = 'TP_Planer';  
  
    --Istguetenaenderung von Hand und Aenderer in Gruppe TP_Planer?  
    IF NVL(:new.GueteIst_Mod,'AUTO') = 'HAND' THEN  
      --check ob alte und neue Guete Oberflaechenbewertung haben (is_numeric)  
      IF LENGTH(TRIM(TRANSLATE(substr(:old.GueteIst,5,1),'0123456789',' '))) is NULL THEN  
          IF LENGTH(TRIM(TRANSLATE(substr(:new.GueteIst,5,1),'0123456789',' '))) is NULL THEN  
              --Uebernahme der Oberflaechenbewertung der alten Guete in die neue  
              :new.GueteIst := substr(:new.GueteIst,1,4) || substr(:old.GueteIst,5,1);  
              gloLogPrj.info('Guetenumwertung durch TP Bediener: ' || gloenv.USERNAME || ' OF wurde erhalten.' ||  
              :old.GueteIst || ' > ' || :new.GueteIst,LOCATION);  
          END IF;  
      END IF;  
    END IF;  
  
  EXCEPTION  
  WHEN NO_DATA_FOUND THEN  
      gloLogPrj.info('Guetenumwertung durch nicht TP Bediener: '|| gloenv.USERNAME || ' Guete: ' || 
      :old.GueteIst || ' > ' || :new.GueteIst,LOCATION);  
END;


DESC MIDAS3.GLO_USERGROUP;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GLO_USERID                                NOT NULL VARCHAR2(20)
 GROUPID                                   NOT NULL VARCHAR2(20)
 DTANGELEGT                                         DATE


@joy division
The problem persists with a constant instead of gloenv.getUsername.

Re: Strange trigger behavior with UPPER() LOWER() [message #413048 is a reply to message #413046] Tue, 14 July 2009 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The prove is it does not matter if i use gloenv.getUsername or set a constant of say WHERE UPPER(GLO_USERID)= 'ORACLEFAQ'.

The proof matters no one must trust you.
And indeed:
first post
UPPER(GLO_USERID)= gloenv.getUsername

last post
UPPER(GLO_USERID)= gloenv.USERNAME

Which one do we trust?

And you didn't say that if you use a constant you got the same behaviour. How could we know that? Do/can we trust this claim?

Regards
Michel
Re: Strange trigger behavior with UPPER() LOWER() [message #413051 is a reply to message #412942] Tue, 14 July 2009 03:08 Go to previous messageGo to next message
isthisadagger
Messages: 14
Registered: April 2008
Location: Germany
Junior Member

I am sorry but how am supposed to prove to you the error i am getting is real? Post a screenshot?
I have been playing around with different stored procedures. One is called username the other getusername. I did not see the difference when posting but once again: The error remains even without a stored procedure!
Re: Strange trigger behavior with UPPER() LOWER() [message #413056 is a reply to message #413051] Tue, 14 July 2009 03:46 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
can you run a simple query and see if that works
SELECT UPPER(GLO_USERID) FROM GLO_USERGROUP
Previous Topic: how to update a column from table which it was took from input procedure
Next Topic: join two tables with no common coloumn
Goto Forum:
  


Current Time: Sat Dec 10 11:10:21 CST 2016

Total time taken to generate the page: 0.08391 seconds