The following URL will take you to the CHAR vs. VARCHAR semantics
section in the PL/SQL developers guide.
As Tim says, use varchar2 if possible. If not possible, you should
closely study the following
http://tinyurl.com/mvfj
Jared
On Wed, 2003-09-10 at 03:29, Fermin Bernaus 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: Fermin Bernaus
> 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: Jared Still
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).
