Fermin, The pl/sql manual has an appendix on char versus varchar2 semantics. I guess SQL*Plus isn't as restrictive(?) and I gues the behaviour might have changed since 7.3. I wonder why baan uses char datatypes chaim
"Guido Konsolke" <[EMAIL PROTECTED]> @fatcity.com on 09/10/2003 10:14:24 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hi Fermin, given that I understand you correctly: pl/sql DOES exactly what you want: it returns 0 rows. That results in a exception called NO_DATA_FOUND and is assigned to the ORA-01403 error. To get a result '0' you have to query the table using 'Count'. Greetings, Guido >>> [EMAIL PROTECTED] 10.09.2003 12.29 Uhr >>> Thank you all of you who answered to my first question. No, the problem has nothing to do with permissions. The record existed in the table, but here it is what I discovered. Can anyone explain please? It may be the normal Oracle behavior, but I believe it was not working like this under 7.3.4 (now we have 8.0.3). I can not confirm though. TTDSLS805501 table definition is as follows: Name Null? Type T$ORNO NOT NULL NUMBER T$USER NOT NULL CHAR(10) If I do an INSERT like the following: INSERT INTO TTDSLS805501 VALUES (151124 , 'exped9') The value 'exped9' for the field T$USER is 6 characters long, while the definition has got 10. After the insertion, I find that the field for the new record is 10 chars long, the last 4 characters being filled with blanks. But here comes the weird behavior. If I construct the 2 following SELECT on this table from SQLPlus, both work OK and return the expected row: SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9'; -> 1 row returned SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9 '; -> 1 row returned But within a stored procedure and using a local variable the following will work OK: SELECT t$orno INTO eskaria FROM TTDSLS805501 WHERE t$user = my_var; where my_var is of type VARCHAR2 and has got the value 'exped9 ' Whilst the following will come up with the ORA-01403 error: SELECT t$orno INTO eskaria FROM TTDSLS805501 WHERE t$user = my_var; where my_var is of type VARCHAR2 and has got the value 'exped9' Is this normal behavior? why does not the "SELECT t$orno FROM TTDSLS805501 WHERE t$user = 'exped9';" statement return 0 rows in the first place? Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).