Home » Developer & Programmer » Forms » ORA - 6502 (numeric or value error string) , FRM - 40735 (Windows XP, Oracle 10g)
ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579681] Thu, 14 March 2013 01:13 Go to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
I have made a report with sql injection. there is no problem with the report. but when i run from the form with following string it gives error.

:global.where_clause := 'where request_type = 5 and request_status in (3, 4, 5) and list_approval_date is null';


And no error is there if i passed the string as
:global.where_clause := 'where request_status in (3, 4, 5) and list_approval_date is null';


REQUEST_TYPE is a database column and it is of number type. Can somebody help me how to solve this problem?

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579682 is a reply to message #579681] Thu, 14 March 2013 01:15 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
this string is passed as a parameter to the report from the form ..

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579689 is a reply to message #579682] Thu, 14 March 2013 01:45 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the size of a report parameter that accepts the where clause? Is it too short? What happens if you make it larger?

By the way, you created a report that uses "dynamic SQL". "SQL injection" is a technique used to attack an application.

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579691 is a reply to message #579689] Thu, 14 March 2013 01:52 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
the size of a report parameter is 4000 and it is character type. I haven't changed its size because i think this size can accommodate any size of where_clause.

yes, i have created a report which uses "dynamic SQL". I only changed the where_clause of the sql by passing it through parameter.

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579710 is a reply to message #579691] Thu, 14 March 2013 03:00 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, everything you said sounds reasonable; REQUEST_TYPE is a number, as well as "5" you used in WHERE clause. Report parameter is long enough. Which means - I don't have any other idea at the moment. Hopefully, someone else will assist.

Though - what happens if you try to pass "where request_type = '5' and ..." - i.e. use "5" as a character value? Most probably nothing, but anyway ... try it.

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579712 is a reply to message #579710] Thu, 14 March 2013 03:17 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
I have tried this way also but still the same error...

while trying different ways to write this where_clause i found that if i skip one condition any from 3 conditions in the where clause then no error comes. eg

:global.where_clause := 'where request_status in (3, 4, 5) and list_approval_date is null';

:global.where_clause := 'where request_type = 5 and list_approval_date is null';

:global.where_clause := 'where request_type = 5 and request_status in (3, 4, 5)';


really, i am fed up from such stupid error i am struck in ...

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579714 is a reply to message #579712] Thu, 14 March 2013 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If that's the case, then - although you said that the report parameter is long enough - I'd re-check that fact. It certainly looks like its length is just too small.

Here's how I do that: I send a WHERE clause from a form to a report and store it into report's parameter. Furthermore, I create a lexical parameter and make sure that both of them are large enough. Then, in After Parameter Form trigger, I set lexical parameter's value (let's call it LEX_WHERE). Finally, I use the lexical parameter in report's query, such as
select ...
from ...
where a.id = b.id
  &lex_where             --> here

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579718 is a reply to message #579714] Thu, 14 March 2013 04:02 Go to previous messageGo to next message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
I have done the same as you have mentioned. I have attached the pdf file as screen will give you much better view of the matter.

Anyways, i think that this error is related with the Oracle forms. So, i adopted another way of doing the same job and i m successful.

before, i was passing the where_clause (lexical parameter) from the form which was causing error with some of the where_clause.

then i created other report parameter called as report_type (this will act as choice for the user to get his particular report) and in the report trigger (After Parameter form), i am defining that lexical parameter. and it is going fine. the pdf file will explain all ..
  • Attachment: Doc2.pdf
    (Size: 276.70KB, Downloaded 1383 times)

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579720 is a reply to message #579718] Thu, 14 March 2013 04:08 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then; I'm glad you solved the problem!

Report message to a moderator

Re: ORA - 6502 (numeric or value error string) , FRM - 40735 [message #579721 is a reply to message #579720] Thu, 14 March 2013 04:21 Go to previous message
new_oracle2011
Messages: 174
Registered: March 2011
Location: Qatar
Senior Member
Thanks for the help guys..

Report message to a moderator

Previous Topic: How to Check for a unique value in a text item in a multi-record block
Next Topic: Help with Data Block Based on Procedure--getting compilation error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 01 10:54:46 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.