Re: Collating sequence question?

While stranded on information super highway John Russell wrote:
:)On Thu, 7 Aug 2003 15:29:19 +0000 (UTC), Hemant Shah
:)<shah_at_typhoon.xnet.com> wrote:
:)
:)>
:)>Folks,
:)>
:)> Can I setup Oracle server/database so that it does not use any collating
:)> sequence (i.e. do a byte by byte comparisions)?
:)
:)The NLS_SORT parameter can be set to the value BINARY:
:)
:)http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch4.htm#1004875
:)
:)Haven't had hands-on experience to know how pervasive the results are.
:)I see ORDER BY clauses explicitly calling the NLSSORT function when
:)they want to ensure a particular linguistic order:
:)
:)http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions82a.htm#84558
:)
:)John
:)--
:)Photo gallery: http://www.pbase.com/john_russell/

I tried NLS_SORT = BINARY but it does not work.

Here is my problem.

I have a table whose primary column is char(10), the value of this character is always 10 characters long and may contain non printable characters including 0x00.

Given a partial key I want to fetch all the rows where the value of the column is greater than NULL padded key.

Exmaple: If the partial key value is 'AAA' then I would like to fetch all the rows where the key is >= 'AAA0x000x000x000x000x000x000x00' and key <= '0xff0xff0xff0xff0xff0xff0xff0xff0xff0xff'

I declare cursor as follows:

EXEC SQL DECLARE LnamePat CURSOR FOR

     SELECT lname, rec FROM lidpown.mytable 
      WHERE lname >= :TmpKey AND lname <= :HighValues
     ORDER BY NLSSORT(lname, 'NLS_SORT=BINARY');

When I do a FETCH I get ORA-01403 error. There are records starting with AAA, and AAB in the table.

-- 
Hemant Shah                           /"\  ASCII ribbon campaign
E-mail: NoJunkMailshah_at_xnet.com       \ /  --------------------- 
                                       X     against HTML mail
TO REPLY, REMOVE NoJunkMail           / \      and postings      
FROM MY E-MAIL ADDRESS.           
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind,                Above opinions are mine only.
it's backed up on tape somewhere.      Others can have their own.