Hi! One more interesting issue with CHAR datatype is, that it is not *always* padded with spaces as sometimes understood. When the CHAR field is NULL, then no spaces are saved into row. But as soon as you update even one single char into it, the full CHAR length is used for this field in a row.
Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 10, 2003 5:49 PM > Fermin, > > You are running into the well documented behavior of the CHAR datatype. Use > VARCHAR2 instead if you wish to avoid those pitfalls. > > Hope this helps... > > -Tim > > on 9/10/03 3:29 AM, Fermin Bernaus at [EMAIL PROTECTED] wrote: > > > > > 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. > > > > > > -----Mensaje original----- > > De: Tim Gorman [mailto:[EMAIL PROTECTED] > > Enviado el: miércoles, 10 de septiembre de 2003 10:44 > > Para: Multiple recipients of list ORACLE-L > > Asunto: Re: ORA-01403 error, help!!! > > > > > > Unless the stored procedure was created with invoker's rights, then it is > > probably executing using the permissions and schema of the account that owns > > it. Who is the owner of the stored procedure? Is it different from who you > > are logged into SQL*Plus as? > > > > The explanation might be that, through some reason like synonyms pointing in > > different directions from the different accounts, the name TTDSLS805501 > > might be resolving to different tables altogether... > > > > > > > > on 9/9/03 10:09 AM, Fermin Bernaus at [EMAIL PROTECTED] wrote: > > > >> > >> If logged in SQL Plus the following SQL returns just one row: > >> > >> SELECT t$orno FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY t$orno; > >> > >> If I do use the same SELECT statement inside a stored procedure and have the > >> returning value stored in a local variable: > >> > >> SELECT t$orno INTO eskaria FROM ttdsls805501 WHERE t$user = 'exped9' GROUP BY > >> t$orno; > >> > >> where eskaria has been declared as: > >> > >> eskaria ttdsls805501.t$orno%TYPE; > >> > >> I get ORA-01403. I have no clue why I am getting this error, can you help > >> please? > >> > >> Many thanks! > >> > >> ............................................. > >> Fermín Bernaus Berraondo > >> Dpto. de Informática > >> SAMMIC, S.A. > >> [EMAIL PROTECTED] > >> http://www.sammic.com > >> Telf. +34 - 943 157 331 > >> Fax +34 - 943 151 276 > >> ............................................. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tim Gorman > 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: Tanel Poder 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).
