Re: Weird behavior with VARCHAR fields (was: ORA-01403 error,
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$ORNONOT NULLNUMBER > T$USERNOT NULLCHAR(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
Re: Weird behavior with VARCHAR fields
Yep, and it's still there in 9.2.0.4 Of course you don't see it when you're non-sys user. I once tried to create a view on top of this varchar 4096 column as user sys, but Oracle had sorted it out in resulting view.. Tanel. kzrtpdtx B) - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 10, 2003 10:39 PM > Speaking of 'weird' character behaviour, how about > this one: > > SQL> desc > 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: > > > > > > > > NameNull?Type > > > > T$ORNONOT NULLNUMBER > > > > T$USERNOT NULLCHAR(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 varia
Re: Weird behavior with VARCHAR fields
Speaking of 'weird' character behaviour, how about this one: SQL> desc 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: > > > > > > NameNull?Type > > > T$ORNONOT NULLNUMBER > > > T$USERNOT NULLCHAR(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
Re: Weird behavior with VARCHAR fields (was: ORA-01403 error,
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: > > > > NameNull?Type > > T$ORNONOT NULLNUMBER > > T$USERNOT NULLCHAR(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
RE: Weird behavior with VARCHAR fields (was: ORA-01403 error,
SQL and PL/SQL are not the same until Oracle 9. There are distinct
differences between the two in earlier releases.
I don't have an 8.0.3 version of Oracle but the following is the result
from 8.1.7:
SQL> create table wb (c1 char(8));
Table created.
SQL> insert into wb values('abcd');
1 row created.
SQL> insert into wb values('abcde');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from wb where c1 = 'abcd';
C1
abcd
1 row selected.
SQL> create or replace procedure wbp as
2v_c1 char(8);
3l_c1 varchar2(8) := 'abcd';
4l_c2 varchar2(8) := 'abcd';
5 begin
6begin
7 select c1 into v_c1 from wb where c1 = 'abcd';
8 dbms_output.put_line('literal: '||v_c1);
9exception
10when no_data_found then null;
11end;
12begin
13 select c1 into v_c1 from wb where c1 = l_c1;
14 dbms_output.put_line('case1: '||v_c1);
15exception
16when no_data_found then null;
17end;
18begin
19 select c1 into v_c1 from wb where c1 = l_c2;
20 dbms_output.put_line('case2: '||v_c1);
21exception
22when no_data_found then null;
23end;
24* end;
25 /
Procedure created.
SQL> exec wbp;
literal: abcd
case2: abcd
PL/SQL procedure successfully completed.
The literal worked. So did the the varchar2 string padded to match the
length of the char column.
Now this is the result if run on 9.2:
SQL> create table wb (c1 char(8));
Table created.
SQL> insert into wb values('abcd');
1 row created.
SQL> insert into wb values('abcde');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from wb where c1 = 'abcd';
C1
abcd
1 row selected.
SQL> create or replace procedure wbp as
2v_c1 char(8);
3l_c1 varchar2(8) := 'abcd';
4l_c2 varchar2(8) := 'abcd';
5 begin
6begin
7 select c1 into v_c1 from wb where c1 = 'abcd';
8 dbms_output.put_line('literal: '||v_c1);
9exception
10when no_data_found then null;
11end;
12begin
13 select c1 into v_c1 from wb where c1 = l_c1;
14 dbms_output.put_line('case1: '||v_c1);
15exception
16when no_data_found then null;
17end;
18begin
19 select c1 into v_c1 from wb where c1 = l_c2;
20 dbms_output.put_line('case2: '||v_c1);
21exception
22when no_data_found then null;
23end;
24* end;
25 /
Procedure created.
SQL> exec wbp;
literal: abcd
case1: abcd
PL/SQL procedure successfully completed.
The literal still works the same, but now the unpadded string finds the
row, but the padded one doesn't.
Moral: It pays to test when upgrading software.
At 08:14 AM 9/10/2003 -0800, you wrote:
Yet I do not understand why it returns a row in SQLPlus and it
does not in a stored procedure... The problem is now fixed, but I'd like
to know the reason it won't work leaving it as it was. It also fails if I
put a string instead of a variable in the stored procedure.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
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).
RE: Weird behavior with VARCHAR fields (was: ORA-01403 error,
Yet I do not understand why it returns a row in SQLPlus and it does not in a stored procedure... The problem is now fixed, but I'd like to know the reason it won't work leaving it as it was. It also fails if I put a string instead of a variable in the stored procedure. Thanks! Fermin. -Mensaje original- De: Tim Gorman [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 10 de septiembre de 2003 16:50 Para: Multiple recipients of list ORACLE-L Asunto: Re: Weird behavior with VARCHAR fields (was: ORA-01403 error, 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: > > NameNull?Type > T$ORNONOT NULLNUMBER > T$USERNOT NULLCHAR(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 --
Re: Weird behavior with VARCHAR fields (was: ORA-01403 error,
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: > > NameNull?Type > T$ORNONOT NULLNUMBER > T$USERNOT NULLCHAR(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).
