Hi Fermin,
given that I understand you correctly:
pl/sql DOES exactly what you want:
it returns 0 rows. That results in a exception
called NO_DATA_FOUND and is assigned
to the ORA-01403 error.
To get a result '0' you have to query
the table using 'Count'.
Greetings,
Guido
>>> [EMAIL PROTECTED] 10.09.2003 12.29 Uhr >>>
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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guido Konsolke
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).
