Re: Collating sequence question?

While stranded on information super highway Maximus wrote:
:)
:)"Hemant Shah" <shah_at_typhoon.xnet.com> wrote in message
:)news:bguc5d$dr0$1_at_flood.xnet.com...
:)> 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.ht
:)m#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/functi
:)ons82a.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');
:)
:)Try this:
:)
:)EXEC SQL DECLARE LnamePat CURSOR FOR
:) SELECT NLSSORT(lname, 'NLS_SORT=BINARY') as x, rec FROM lidpown.mytable
:) WHERE x >= NLSSORT(:TmpKey, 'NLS_SORT=BINARY') AND x <=
:)NLSSORT(:HighValues, 'NLS_SORT=BINARY')
:) ORDER BY NLSSORT(lname, 'NLS_SORT=BINARY');
:)
:)

 I get runtime error 904 when I try to open the cursor:

 ORA-00904: "X": invalid identifier

:)
:)
:)

-- 
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.