char datatype - ora-01722 [message #622749] |
Fri, 29 August 2014 02:09 |
narang79
Messages: 131 Registered: June 2010
|
Senior Member |
|
|
SELECT (DATEOFFICE) INTO V_DATE FROM tbltimeregister
WHERE (PAYCODE)= :paycode;
it give me error ora-01722 no data found
i cant understand because paycode is char field in database and :paycode
is also a char field in form level
if i run this statement pass 600630
SELECT (DATEOFFICE) INTO V_DATE FROM tbltimeregister
WHERE (PAYCODE)= '600630';
it gives me result no error show
Lalit : Added code tags
In future, please do yourself using code tags
Modified topic title from "char datatype" and added the error number
[Updated on: Fri, 29 August 2014 02:47] by Moderator Report message to a moderator
|
|
|
|
Re: char datatype [message #622752 is a reply to message #622750] |
Fri, 29 August 2014 02:36 |
narang79
Messages: 131 Registered: June 2010
|
Senior Member |
|
|
i want to get result through parameter(:paycode)
when i check the output of parameter that show same result 600630
but give me error on my select statement
|
|
|
Re: char datatype [message #622753 is a reply to message #622749] |
Fri, 29 August 2014 02:41 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
narang79 wrote on Fri, 29 August 2014 12:39
if i run this statement pass 600630
SELECT (DATEOFFICE) INTO V_DATE FROM tbltimeregister
WHERE (PAYCODE)= '600630';
it gives me result no error show
For example :
SQL> select * from emp where ename=1;
select * from emp where ename=1
*
ERROR at line 1:
ORA-01722: invalid number
ename is varchar2, with ename=1 Oracle raises ORA-01722: invalid number. Probably, :paycode is not passed as varchar2.
To confirm, use to_char and see :
SQL> select * from emp where ename=to_char(1);
no rows selected
[Updated on: Fri, 29 August 2014 02:42] Report message to a moderator
|
|
|
Re: char datatype [message #622754 is a reply to message #622753] |
Fri, 29 August 2014 02:46 |
narang79
Messages: 131 Registered: June 2010
|
Senior Member |
|
|
no sir :paycode is also varchar2 in form level
and paycode in table is char in database
i think char is the problem
i use trim function but it is not use in devloper 2000
|
|
|
Re: char datatype [message #622756 is a reply to message #622754] |
Fri, 29 August 2014 02:54 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
narang79 wrote on Fri, 29 August 2014 13:16no sir :paycode is also varchar2 in form level
and paycode in table is char in database
I don't believe you. Ok, let's see :
SQL> create table t (nm char(5));
Table created.
SQL> insert into t values('scott');
1 row created.
SQL> var nm varchar2(5);
SQL> exec :nm := 'scott';
PL/SQL procedure successfully completed.
SQL> select * from t where nm=:nm;
NM
-----
scott
|
|
|
|
|
|
|
Re: char datatype - ora-01722 [message #622786 is a reply to message #622749] |
Fri, 29 August 2014 10:33 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
narang79 wrote on Fri, 29 August 2014 03:09i cant understand because paycode is char field in database and :paycode is also a char field in form level
I bet :paycode is not CHAR but rather varchar2:
SQL> create table tbl(c char(5))
2 /
Table created.
SQL> insert into tbl values('X')
2 /
1 row created.
SQL> variable v char(5)
SQL> exec :v := 'X';
PL/SQL procedure successfully completed.
SQL> select * from tbl where c = :v;
C
-----
X
SQL> variable v varchar2(5)
SQL> exec :v := 'X';
PL/SQL procedure successfully completed.
SQL> select * from tbl where c = :v;
no rows selected
SQL>
SY.
|
|
|
|
Re: char datatype - ora-01722 [message #622803 is a reply to message #622793] |
Fri, 29 August 2014 17:01 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
No Lalit,
I didn't miss your post. I just wasn't sure what you were trying to show. You used char(5), assigned it 5 character value and compared it with 5 character varchar2. Obviously it returned results, while OP experienced "no data found". Issue here is in CHAR to VARCHAR2 comparison. CHAR is converted to VARCHAR2. And CHAR(5) means any value is right-padded to 5 characters. This creates no issue using 'SCOTT' since it is 5 character string and your post shows that. What you should have used is less than 5 character value. KING, for example. Then assigning 'KING' to CHAR(5) column NM would result in 'KING ' stored as column NM value, while assigning 'KING' to VARCHAR2(5) bind varibale :v would result in 'KING' stored as :v value. Therefore, comparing NM = :v would return no rows since 'KING ' != 'KING' and that's what my example shows. So bottom line: stay away from CHAR.
SY.
|
|
|
Re: char datatype - ora-01722 [message #622806 is a reply to message #622803] |
Sat, 30 August 2014 05:34 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Ok, now I see where the confusion is. OP said contradicting things :
narang79 wrote on Fri, 29 August 2014 13:16 :paycode is also varchar2 in form level
and,
narang79 wrote on Fri, 29 August 2014 12:39 :paycode is also a char field in form level
He is yet to clarify. CM already asked him to do so.
[Updated on: Sat, 30 August 2014 05:34] Report message to a moderator
|
|
|