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).
