INSTR ()

610 pts.
Tags:
InStr function
PL/SQL
PL/SQL block
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 )

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Inprise
    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
    610 pointsBadges:
    report
  • carlosdl
    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 ?
    69,510 pointsBadges:
    report
  • Inprise
    Hi actually i neeed it to support case insenstive functionality only,is there any other means to do this???
    610 pointsBadges:
    report
  • Inprise
    hi my procedure s functioning perfect excluding this logic
    610 pointsBadges:
    report
  • carlosdl
    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 ...
    69,510 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • Inprise
    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
    610 pointsBadges:
    report
  • carlosdl
    I think we won't be able to help without looking at the rest of your code.
    69,510 pointsBadges:
    report
  • Inprise
    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
    610 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following