SQL & PL/SQL

After knowing usage of NTILE analytical function, i started using it to divide my rows into batches. However I'd like to pass the expression dynamically (depending on the row count), but unfortunately I see some limitations inhibit me from achieving it. The reason I want to do this dynamically is when there are rows <= expression value, NTILE function forms one batch per each record which I don't wish to happen.

If you observe in the below example, it creates 10 batches/buckets when there are only 10 rows, where I wish to have only 1 batch.

SQL> drop table r_dummy;

Table dropped.

SQL>
SQL> create table r_dummy
  2  as
  3  select level id
  4  from dual
  5  connect by level<=10;

Table created.

SQL>
SQL> select ntile(10) over(order by id) batch_no,id
  2  from r_dummy;

  BATCH_NO         ID
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10

10 rows selected.

SQL>
SQL> select ceil(count(*)/10) from r_dummy;

CEIL(COUNT(*)/10)
-----------------
                1

SQL>

I wish to use above same ceil function in my actual query but

with t as
(
select id, ceil((count(*) over())/10) bucket_size
from r_dummy
)
select id,ntile(bucket_size) over(order by id) batch_no
from t;

ERROR at line 6:
ORA-30488: argument should be a function of expressions in PARTITION BY

I had seen the same question being asked by Sven Weller [url https://forums.oracle.com/forums/thread.jspa?threadID=587782]here, would like to know if there is any workaround available to this.

Please help me.