一种选择是按同一列排序两次;第一时间力，例如 * *（所以所有的苹果在所有的香蕉之前，所有的葡萄之前......），然后应用NLS_SORT：
样本数据：

SQL> with test (col) as
  2    (select
  3     'banana'    from dual union all select
  4     'apple'     from dual union all select
  5     'Apple'     from dual union all select
  6     'Banana'    from dual union all select
  7     'Grape'     from dual union all select
  8     'orange'    from dual union all select
  9     'Bananb'    from dual union all select
 10     'Pineapple' from dual
 11    )

查询方式：

12  select * from test
 13  order by lower(col), nlssort(col, 'NLS_SORT = BINARY');

COL
---------
Apple
apple
Banana
banana
Bananb
Grape
orange
Pineapple

8 rows selected.

SQL>

您可以先不区分大小写排序一次，然后再区分大小写排序：

SELECT column_name
FROM   your_table
ORDER BY LOWER(column_name), column_name;

或：

SELECT column_name
FROM   your_table
ORDER BY 
       NLSSORT(column_name, 'NLS_SORT=BINARY_CI'),
       NLSSORT(column_name, 'NLS_SORT=BINARY');

其中，对于样本数据：

CREATE TABLE your_table (column_name) AS
SELECT 'Apple'     FROM DUAL UNION ALL
SELECT 'apple'     FROM DUAL UNION ALL
SELECT 'Banana'    FROM DUAL UNION ALL
SELECT 'banana'    FROM DUAL UNION ALL
SELECT 'Bananb'    FROM DUAL UNION ALL
SELECT 'Grape'     FROM DUAL UNION ALL
SELECT 'orange'    FROM DUAL UNION ALL
SELECT 'Pineapple' FROM DUAL;

两个输出：
| 列名称|
| --|
| 苹果|
| 苹果|
| 香蕉|
| 香蕉|
| 巴南布|
| 葡萄|
| 橙子|
| 菠萝|
fiddle