Error:SQL problems:ORA-01722: invlid number

[kannanmugundan]

I am using iReport 3.7.2 and i  get the above error when I write my sql and retrieve fields.

my sql looks like

select * from rbapmst_tb where  APMST_DLR_NO = $P{dlrNo}

I have a parameter dlrNo,  but if i replace the parameter with actual value

select * from rbapmst_tb where  APMST_DLR_NO = 'S1100'

It is retrieving all fileds.

It is just a simple String type parameter and the data base column is also  oracle VARCHAR2

 

Please help me why the $P{dlrNo} in not able to get  the fields.

Is this some thing normal in iReport (Please excuse me, if it is already mentioned  as i am new to iReport)

 

Thanks

 

 

[rakeshenoch]

Attach the jrxml for more clarity!!!

[baggypants]

Have you tried putting quotes around the parameter. i.e.

select * from rbapmst_tb where  APMST_DLR_NO = '$P{dlrNo}'

Post Edited by baggypants at 05/27/2010 22:52

[kannanmugundan]

Hi 

 

If I add  quotes   '$P{dlrNo}'

 

I get the error ORA-01006: bind variable does not exist

 

I am attaching my jrxml file

[slow]

"
AND APMST_DLR_NO = $P{dlrNo}
AND DLORG_DLR_NO = $P{dlrNo}
"

are you sure APMST_DLR_NO and also DLORG_DLR_NO are varchar ?

have you tried

select * from rbapmst_tb where  DLORG_DLR_NO = 'S1100'

?

it seems to be a conversion error...
with string parameters don't use ''.

 

[kannanmugundan]

Hi

 

yes  DLORG_DLR_NO  is varchar and if I just replace all parameter with actual value/sql   It does retrieve the fields

 

 

I know that I may be  doing some silly mistake, but unable to figure it out

thanks

Post Edited by kannanmugundan at 05/31/2010 16:28

[slow]

have you tried to define parameters with default values and then when prompt for parameter value use the "default value" button?

with this test you can try to verify if there is an issue passing basic string value.

when you define default values, use:

new String("S1100") without ''

[kannanmugundan]

Hi,

 

I am not running the report, just when editing the SQL I get the above error

Please see the attached Image.

 

If i just replace it with actual value with single quote, it gets all the fields

 

Hope I made it clear