Inprise   540 pts.  |   Jan 13 2009  6:03AM GMT

Hi,

I have mentioned my code only above mistakenly mentioned as pseudo code

What u mean here the case sensitive ,where to set the value as case insenstive in INSTR() ,if that s the case also ,it should work in both test case and implementation procedure ,because i used the same example for both

scenario 1
v_lm = ‘GREEN’

pcd = ‘GR’

it raised the error as i used DBMS_OUTPUT.PUT_LINE(’Error’) for this scenario

and

v_lm = ‘GREEN’
pcd = ‘AN’

and this was successful — DBMS_OUTPUT.PUT_LINE(’passed’) for this scenario

Similar way tested other 2 parameter variable with v_lm — it worked fine with test case howver not with implementation procedure

please guide me where i am wrong in this

Carlosdl   29845 pts.  |   Jan 13 2009  1:28PM GMT

Hi Inprise.

There is no direct way to make INSTR work case-insensitive, but you could use the UPPER function to convert everything to upper text before comparison, for example:

If INSTR (UPPER(v_lm),UPPER(pcd),1,1) = 1 or …

I don’t see anything wrong in your code (except that v_lm appears as vlm a couple of times).

Are you sure your procedure doesn’t have some other code that is making this fail ?

Inprise   540 pts.  |   Jan 14 2009  1:32PM GMT

Hi actually i neeed it to support case insenstive functionality only,is there any other means to do this???

Inprise   540 pts.  |   Jan 14 2009  1:35PM GMT

hi my procedure s functioning perfect excluding this logic

Carlosdl   29845 pts.  |   Jan 14 2009  4:31PM GMT

Can you post your complete procedure here ?

As I said before, I don’t se anything wrong in your code, and it should work.

You could also try doing the comparison this way:

if v_lm like pcd||’%’ or v_lm like pc||’%’ or v_lm like pcn||’%’ then …

Kccrosser   1850 pts.  |   Jan 14 2009  7:23PM GMT

Other possibilities - since we only see this fragment…

1. Is there an error/exception handler that is capturing and discarding the error you are raising? From where is this function called?

2. What is the value in “err_pkg.nt_val”? Depending on this value, this could cause odd behavior. Oracle specifies that this value must be between -20000 and -20999. If it is outside this range, you may not see the expected result.

3. Note that “sqlerrm” is probably null at the point where you do a “raise_application_error”, unless this is occurring inside of another error handler. Not sure why you are appending this value.

Inprise   540 pts.  |   Jan 15 2009  7:16AM GMT

Hi Both,

As said earlier my test case s working fine using both instr() and like operator howver not thrwoing error while exceuting thru my procedure

yes it s in the range of error messages only,all error messages all different procedures are catched n err_pkg() only

Is this cud be a oracle or sqldeveloper problem i am thinking now

please guide me

Carlosdl   29845 pts.  |   Jan 15 2009  3:12PM GMT

I think we won’t be able to help without looking at the rest of your code.

Inprise   540 pts.  |   Jan 16 2009  3:56AM GMT

Hi Both,

Thanks a lot!!!
i changed the local variable to paramater variable(p_lm) itself and it s working now

IF INSTR (p_lm,pcd,1,1) = 1 OR INSTR(p_lm,pc,1,1) = 1 OR INSTR(p_lm,pcn,1,1) = 1 THEN