Fermin,
The pl/sql manual has an appendix on char versus varchar2 semantics.
I guess SQL*Plus isn't as restrictive(?) and I gues the behaviour might
have changed since 7.3.
I wonder why baan uses char datatypes
chaim
"Guido Konsolke" <[EMAIL PROTECTED]>
@fatcity.com on 09/10/2003 10:14:24 AM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
