Speaking of 'weird' character behaviour, how about this one: SQL> desc <omitted> Name Null? Type --------------------- -------- ---------------------- ... PREDICATE VARCHAR2(4096) ...
Hmmm...that seems about 96 too many to me... Enjoy... Connor --- Tanel Poder <[EMAIL PROTECTED]> wrote: > 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). ===== Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" ________________________________________________________________________ Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
