Home » SQL & PL/SQL » SQL & PL/SQL » char datatype - ora-01722
char datatype - ora-01722 [message #622749] Fri, 29 August 2014 02:09 Go to next message
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 #622750 is a reply to message #622749] Fri, 29 August 2014 02:24 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ORA-01722 is invalid number, not no data found. So, which one of these do you get? Possible solution might depend on that.
Re: char datatype [message #622752 is a reply to message #622750] Fri, 29 August 2014 02:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
narang79 wrote on Fri, 29 August 2014 13:16
no 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 [message #622757 is a reply to message #622756] Fri, 29 August 2014 03:03 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Confirm which error you are getting - as LF pointed out the error code to gave doesn't match the description.
Supply a desc of tbltimeregister.
Is tbltimeregister a table or a view?

[Updated on: Fri, 29 August 2014 03:03]

Report message to a moderator

Re: char datatype [message #622769 is a reply to message #622757] Fri, 29 August 2014 04:34 Go to previous messageGo to next message
narang79
Messages: 131
Registered: June 2010
Senior Member
view
Re: char datatype [message #622770 is a reply to message #622769] Fri, 29 August 2014 04:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You don't really want to get help, do you?

Because, if you would want it, you'd actually answer questions people ask. Your replies are close to useless chit-chat.
Re: char datatype [message #622771 is a reply to message #622769] Fri, 29 August 2014 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I asked for three things, you answered one.
now answer the other two.
Re: char datatype - ora-01722 [message #622786 is a reply to message #622749] Fri, 29 August 2014 10:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
narang79 wrote on Fri, 29 August 2014 03:09
i 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 #622793 is a reply to message #622786] Fri, 29 August 2014 11:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Solomon,

Perhaps, you missed my post above. I have already posted exact steps(except the varchar2 in your last step). Just waiting for OP to feedback.
Re: char datatype - ora-01722 [message #622803 is a reply to message #622793] Fri, 29 August 2014 17:01 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: Re : Email with Attachment Body content not coming
Next Topic: Create view in PLSQL
Goto Forum:
  


Current Time: Thu Mar 28 09:55:48 CDT 2024