V$ tables not recognized in a sql statement of a shell script

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Is possible that for the 10g client you either have a the date format set in a local login.sql file or set in the $ORACLE_HOME/sqlplus/admin/glogin.sql. If you can find this date formatting, then you should set it the same for your 11g client and it should work.

Essentially, when you put in a date string Oracle look at the nls_date_format to see how a date is formatted by default. If that is different than how you have it in a sting, Oracle might have an issue handling the date. you can also use the to_Date funtion to force a particular date format to work.