Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    20

    Red face Unanswered: Case Insensitive Search In a Stored Procedure

    Case Insensitive Search In a Stored Procedure

    Hi Guys, just wondering if there is way to enable case insensitive search in a Oracle stored procedure?

    Through SQLPlus, I can use "alter session set nls_comp='LINGUISTIC'" and "alter session set NLS_SORT=BINARY_CI" to enable case insensitive search, but this two statement can not be placed in stored procedure. Any ideas to enable case insensitive search in a Oracle stored procedure??

    Thnx a lot

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In PL/SQL:
    Code:
    BEGIN
      EXECUTE IMMEDIATE 'alter session set nls_comp=''LINGUISTIC''';
      EXECUTE IMMEDIATE 'alter session set NLS_SORT=BINARY_CI';
    END;

  3. #3
    Join Date
    Jun 2003
    Posts
    20
    Thnx mate. I found the following statement will also do the work

    dbms_session.set_nls('NLS_COMP', '"LINGUISTIC"');
    dbms_session.set_nls('NLS_SORT', '"BINARY_CI"');

    But the problem is, after these two statements executed, distinct select loose effect... Any ideas??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •