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