INSTR ()
Hi Experts,
My test case s working fine for the INSTR() but the same if i use in implemenation its not raising the error
giving below the pseudo code
IF INSTR (v_lm,pcd,1,1) = 1 OR INSTR(vlm,pc,1,1) = 1 OR INSTR(vlm,pcn,1,1) = 1 THEN
raise_application_error( err_pkg.nt_val, err_pkg.nt_val_msg || sqlerrm);
END IF;
where v_lm is a local variable in the procedure and pcd,pc and pcn are parameter variables of the procedure
i am trying to implement tat value of v_lm should not start with the values of any of the 3( pcd,pc and pcn )
Software/Hardware used:
Software/Hardware used:
ASKED:
January 12, 2009 12:02 PM
UPDATED:
January 16, 2009 3:56 AM
Answer Wiki:
That approach should work.
Just remember that the comparison is case sensitive.
On the other hand, I would not include sqlerrm in the error message, unless this code is part of an exception handler.
If you need more help, please paste here your actual code (not pseudocode) and some example data with which it doesn't work.
To see all answers submitted to the Answer Wiki: View Answer History.
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
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:
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 ?
Hi actually i neeed it to support case insenstive functionality only,is there any other means to do this???
hi my procedure s functioning perfect excluding this logic
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:
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.
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
I think we won’t be able to help without looking at the rest of your code.
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