SQL & PL/SQL

Hello,

Database: Oracle 12c

I have a requirement where a request will come in for a large dataset, and i want to be able to see if the dataset is larger than 'x', basically throw an exception.  The problem is a request can come in for 1million+ rows of data, and i want to return an error if the dataset is larger than 100k rows in the most efficient way possible.  Here is what i've tried:

Using xmlsequence:

      v_sql_stmt := 'SELECT * from large_table' ;

      v_Cursor := DBMS_SQL.OPEN_CURSOR;

      DBMS_SQL.PARSE(v_Cursor, v_sql_stmt, DBMS_SQL.NATIVE);

      v_refcur := DBMS_SQL.TO_REFCURSOR(v_Cursor);  

      SELECT count(*) into v_count from table(xmlsequence(v_refcur));

This got me an accurate rowcount, but the performance was horrible.

Using a count:

       v_Cursor := DBMS_SQL.OPEN_CURSOR;

      DBMS_SQL.PARSE(v_Cursor, v_sql_stmt, DBMS_SQL.NATIVE);

     v_count_sql_stmt := 'SELECT count(*) from (' || v_sql_stmt  || ')' ;

This worked too, however, when using a bunch of bind variable in the v_sql_stmt, i ended up with a bunch of complex 'if' statements when doing the counts.

Using bulkCollect into an array:

      v_sql_stmt := 'SELECT * from large_table' ;

      v_Cursor := DBMS_SQL.OPEN_CURSOR;

      DBMS_SQL.PARSE(v_Cursor, v_sql_stmt, DBMS_SQL.NATIVE);

      v_refcur := DBMS_SQL.TO_REFCURSOR(v_Cursor);  

      FETCH v_refcur BULK COLLECT INTO v_typ_array;

      v_count  := dbms_sql.last_row_count;

This worked too, but again, i'm not sure the efficiency.   There has to be something i'm missing as this seems like it should be easily done.  Are there any recommendation on how to satisfy the requirement?

Thank you.